Add more than 1 record at once ?

D

David

Hi,

Quick question.

I have a form on an asp page which is used to add additional users to
the DB, i.e. adding a UserID (PK on table), Password & Email.

The companies are allowed a max of 3 users. If the company only has
the default 1 user and wishes to add another 1 or 2, then the form
displays the current user details, not in text boxes as this is not an
adit data form. The additional user/s can be entered into 1/2 rows of
text boxes, user 2 & 3.

If they decide to add 2 new users, how do I add the additional 2
records to my db table on submit ?

I am using a MySQL DB.


This is the code I have from an update form which can edit the default
user.
How can I change this code to add the 2 new records ?

___________________________________________

<%

vUser = request.form("user")
vPass = request.form("pass")
vEmail = request.form("email")

uSQL = "UPDATE OrderStatusAccess SET "
uSQL = uSQL & "UserID= '" & vUser & "'"
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"
uSQL = uSQL & " WHERE CustomerID='" & session("customer") & "';"

Set RS = adoDataConn.Execute(uSQL)
%>
__________________________________________________________


Thanks for your help


David
 
B

Bob Barrows

David said:
Hi,

Quick question.

I have a form on an asp page which is used to add additional users to
the DB, i.e. adding a UserID (PK on table), Password & Email.

The companies are allowed a max of 3 users. If the company only has
the default 1 user and wishes to add another 1 or 2, then the form
displays the current user details, not in text boxes as this is not an
adit data form. The additional user/s can be entered into 1/2 rows of
text boxes, user 2 & 3.

If they decide to add 2 new users, how do I add the additional 2
records to my db table on submit ?

I am using a MySQL DB.


This is the code I have from an update form which can edit the default
user.
How can I change this code to add the 2 new records ?

___________________________________________

<%

vUser = request.form("user")
vPass = request.form("pass")
vEmail = request.form("email")

uSQL = "UPDATE OrderStatusAccess SET "
uSQL = uSQL & "UserID= '" & vUser & "'"
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"
uSQL = uSQL & " WHERE CustomerID='" & session("customer") & "';"

Set RS = adoDataConn.Execute(uSQL)

Why "Set RS"??? This query is not returning a record! Why force ADO to go to
the time and trouble of creating a recordset object that will simply be
discarded? Do this instead:

adoDataConn.Execute uSQL,,129

(129 is the addition of two constants: adCmdText (1) and adExecuteNoRecords
(128) - your queries will execute more efficiently if you specify the
commandtype and execution options instead of making ADO guess)
%>
__________________________________________________________


Thanks for your help


David

Create 2 INSERT statements using the passed values and execute them as
above.

Bob Barrows
 
D

David Gordon

Thanks Bob,

I have this code:

_________________________________________

for i = 1 to 2 (either 1 or 2 records added)

vUser = request.form("user")
vPass = request.form("pass")
vEmail = request.form("email")

uSQL = "INSERT into OrderStatusAccess "
uSQL = uSQL & "UserID= '" & vUser & "'"
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"

uSQL = uSQL & " WHERE CustomerID='" & session("customer") & "';"

adoDataConn.Execute uSQL,,129

next

___________________________________________

How do I adjust this code to accept the multiple records from the form ?

I get the following error:

SQLState: 42000
Native Error Code: 1064
[TCX][MyODBC]You have an error in your SQL syntax. Check the manual that
corresponds to your MySQL server version for the right syntax to use
near 'UserID= '', Password = '' , Email ='' WHERE CustomerID='20'' at


Thanks
David
 
A

Aaron Bertrand - MVP

An INSERT statement looks like this:

INSERT tablename(columnname, columnname) VALUES('value', 'value')

There is no columnname = value syntax in an INSERT.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top