Add more than 1 record at once ?

[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

 

[Bob Barrows]

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

 

[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

 

[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.