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("u ser")
vPass = request.form("p ass")
vEmail = request.form("e mail")

uSQL = "UPDATE OrderStatusAcce ss SET "
uSQL = uSQL & "UserID= '" & vUser & "'"
uSQL = uSQL & ", Password = '" & vPass & "' "
uSQL = uSQL & ", Email ='" & vEmail & "'"
uSQL = uSQL & " WHERE CustomerID='" & session("custom er") & "';"

Set RS = adoDataConn.Exe cute(uSQL)
%>
_______________ _______________ _______________ _____________


Thanks for your help


David

[Bob Barrows]

Re: Add more than 1 record at once ?

David wrote:[color=blue]
> 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("u ser")
> vPass = request.form("p ass")
> vEmail = request.form("e mail")
>
> uSQL = "UPDATE OrderStatusAcce ss SET "
> uSQL = uSQL & "UserID= '" & vUser & "'"
> uSQL = uSQL & ", Password = '" & vPass & "' "
> uSQL = uSQL & ", Email ='" & vEmail & "'"
> uSQL = uSQL & " WHERE CustomerID='" & session("custom er") & "';"
>
> Set RS = adoDataConn.Exe cute(uSQL)[/color]

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.Exe cute uSQL,,129

(129 is the addition of two constants: adCmdText (1) and adExecuteNoReco rds
(128) - your queries will execute more efficiently if you specify the
commandtype and execution options instead of making ADO guess)
[color=blue]
> %>
> _______________ _______________ _______________ _____________
>
>
> Thanks for your help
>
>
> David[/color]

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

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[David Gordon]

Re: Add more than 1 record at once ?


Thanks Bob,

I have this code:

_______________ _______________ ___________

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

vUser = request.form("u ser")
vPass = request.form("p ass")
vEmail = request.form("e mail")

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

uSQL = uSQL & " WHERE CustomerID='" & session("custom er") & "';"

adoDataConn.Exe cute 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


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Aaron Bertrand - MVP]

Re: Add more than 1 record at once ?

An INSERT statement looks like this:

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

There is no columnname = value syntax in an INSERT.

--
Aaron Bertrand
SQL Server MVP

http://www.aspfaq.com/

"David Gordon" <david@scene-double.co.uk> wrote in message
news:uyh6Pv40DH A.540@tk2msftng p13.phx.gbl...[color=blue]
>
> Thanks Bob,
>
> I have this code:
>
> _______________ _______________ ___________
>
> for i = 1 to 2 (either 1 or 2 records added)
>
> vUser = request.form("u ser")
> vPass = request.form("p ass")
> vEmail = request.form("e mail")
>
> uSQL = "INSERT into OrderStatusAcce ss "
> uSQL = uSQL & "UserID= '" & vUser & "'"
> uSQL = uSQL & ", Password = '" & vPass & "' "
> uSQL = uSQL & ", Email ='" & vEmail & "'"
>
> uSQL = uSQL & " WHERE CustomerID='" & session("custom er") & "';"
>
> adoDataConn.Exe cute 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it![/color]