Add more than 1 record at once ?
 

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

Re: Add more than 1 record at once ?
 

David wrote:
> 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

--
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"

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("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


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

Re: Add more than 1 record at once ?
 

An INSERT statement looks like this:

INSERT tablename(columnname, 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:uyh6Pv40DHA.540@tk2msftngp13.phx.gbl...
>
> 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
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!