Add more than 1 record at once ?

Discussion in 'ASP General' started by David, Jan 5, 2004.

  1. David

    David Guest

    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
     
    David, Jan 5, 2004
    #1
    1. Advertising

  2. David

    Bob Barrows Guest

    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"
     
    Bob Barrows, Jan 5, 2004
    #2
    1. Advertising

  3. David

    David Gordon Guest

    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!
     
    David Gordon, Jan 5, 2004
    #3
  4. 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" <> wrote in message
    news:...
    >
    > 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!
     
    Aaron Bertrand - MVP, Jan 5, 2004
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. googler
    Replies:
    4
    Views:
    516
  2. Vinod I
    Replies:
    3
    Views:
    439
    Kelly Leahy
    Jan 16, 2004
  3. Gancy
    Replies:
    4
    Views:
    194
    Rasto Levrinc
    Feb 3, 2005
  4. Steven D'Aprano
    Replies:
    0
    Views:
    105
    Steven D'Aprano
    Dec 23, 2013
  5. Replies:
    3
    Views:
    92
    Gary Herron
    Dec 23, 2013
Loading...

Share This Page