Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    13

    Unhappy Unanswered: Using Asp To Add A Record In A Mysql Database

    Hi

    I have made the following code, this file is opened when my HTML form is submitted. When run, it does go through it, and works without error BUT DOES NOT ADD ANYTHING TO THE DATABASE!

    Please help


    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object
    Dim rsAddComments 'Holds the recordset for the new record to be added
    Dim strSQL 'Holds the SQL query to query the database
    'Create an ADO connection object

    Set adoCon = Server.CreateObject("ADODB.Connection")
    'Set an active connection to the Connection object using a DSN-less connection

    adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=***;PA SSWORD=***;OPTION=3;"
    'Create an ADO recordset object

    Set rsAddComments = Server.CreateObject("ADODB.Recordset")
    'Initialise the strSQL variable with an SQL statement to query the database



    strSQL ="SELECT tblUsers.User_Name, tblUsers.User_passwd, tblUsers.User_level, tblUsers.NAME, tblUsers.MAIL_ID, tblUsers.Place, tblUsers.COUNTRY FROM tblUsers;"

    'Set the cursor type we are using so we can navigate through the recordset

    rsAddComments.CursorType = 3

    'Set the lock type so that the record is locked by ADO when it is updated

    rsAddComments.LockType = 3
    'Open the recordset with the SQL query

    rsAddComments.Open strSQL, adoCon
    'Tell the recordset we are adding a new record to it

    strSQL="INSERT INTO tblUsers (User_Name,User_passwd,User_level,NAME,MAIL_ID,Pla ce,COUNTRY)"
    strSQL=strSQL & " VALUES "
    strSQL=strSQL & "('" & Request.Form("name") & "',"
    strSQL=strSQL & "'" & Request.Form("comments") & "',"
    strSQL=strSQL & "'" & ("= 1") & "',"
    strSQL=strSQL & "'" & Request.Form("USERNAME") & "',"
    strSQL=strSQL & "'" & Request.Form("MAIL_ID") & "',"
    strSQL=strSQL & "'" & Request.Form("Place") & "',"
    strSQL=strSQL & "'" & Request.Form("COUNTRY") & "')"



    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects

    rsAddComments.Close

    Set rsAddComments = Nothing

    Set adoCon = Nothing
    'Redirect to the asp page
    Response.Redirect "main.asp"

    %>

    THE HTML has the form:


    User Name:
    Password:
    YOUR NAME:
    Email:
    CITY:
    COUNTRY:

    AND A SUBMIT BUTTON

  2. #2
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    Hai..

    Do you get any error from the page..or does it execute without any error ?

    Ok you can do one think, just print your query strSQL before calling the update method of recordset and then copy the query and execute it in the mysql client and see whether there is any error...

    Hope this helps..Do let me know whether this solved your problem.
    Sudar

    --
    My Blog

  3. #3
    Join Date
    Nov 2004
    Posts
    13
    When this ASP file is run, there is no error, and as you see in the end of the ASP, it redirects to the new page with error.

    But it does not add anything to the DATABASE.
    I used the SQL code to place it straight in the MYSQL CLient without problem.
    So i beleive it has something to do with the UPDATE part.

    What do you mean by printing the strSQL before calling the update : do you mean placing some print code in the ASP?

    Thanks
    Suraj

    --------------------------------------------------------------------------------

    Hai..

    Do you get any error from the page..or does it execute without any error ?

    Ok you can do one think, just print your query strSQL before calling the update method of recordset and then copy the query and execute it in the mysql client and see whether there is any error...

    Hope this helps..Do let me know whether this solved your problem.
    __________________
    With regards,
    Sudar

    --
    My Blogs

    My SQL Blog
    Any thing Interesting
    Personal

  4. #4
    Join Date
    Jul 2004
    Location
    Mars
    Posts
    137

    Thumbs up

    I meant to print the contents of the variable strSQL and then exit the script before calling the update method. And then copy the content of the strSQL variable and then execute in the MySQL client. By doing so u can findout if there is any problem with the formating the query(like mismatch in quotes etc..).

    try that.. and do let me know what happened...
    Sudar

    --
    My Blog

  5. #5
    Join Date
    Nov 2004
    Posts
    13
    I am not sure have to go ahead with this. I ended the script before the update method, and dont know how to place strSQL on the client, because its in ASP. If i put the sql directly in, it has no problems and creates a record.
    Last edited by adventure_ke; 11-16-04 at 09:45.

  6. #6
    Join Date
    Nov 2004
    Posts
    13
    I am not sure how to go about the code above as i cant figure out the problem.

    So i tried the following change in code and get the following error

    Microsoft OLE DB Provider for ODBC Drivers error '80004005'

    [MySQL][ODBC 3.51 Driver][mysqld-4.1.7-nt]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 '.`tblUsers` ( User_Name,User_passwd,User_level,NAME,MAIL_ID,Plac

    /logins/addnewuser.asp, line 34


    <%
    'Dimension variables
    Dim adoCon 'Holds the Database Connection Object

    Dim rsAddComments 'Holds the recordset for the new record to be added

    Dim strSQL 'Holds the SQL query to query the database

    'Create an ADO connection object
    Set adoCon = Server.CreateObject("ADODB.Connection")

    'Set an active connection to the Connection object using a DSN-less connection
    adoCon.Open "DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;USER=xyz;PA SSWORD=***;OPTION=3;"

    'Create an ADO recordset object
    Set rsAddComments = Server.CreateObject("ADODB.Recordset")

    'Initialise the strSQL variable with an SQL statement to query the database
    strSQL = "SELECT tblUsers.User_Name, tblUsers.User_passwd, tblUsers.NAME, tblUsers.MAIL_ID, tblUsers.Place, tblUsers.COUNTRY FROM tblUsers;"

    'Set the cursor type we are using so we can navigate through the recordset
    rsAddComments.CursorType = 2
    'Set the lock type so that the record is locked by ADO when it is updated
    rsAddComments.LockType = 3

    'Open the recordset with the SQL query
    rsAddComments.Open strSQL, adoCon
    'Tell the recordset we are adding a new record to it

    rsAddComments.AddNew
    'Add a new record to the recordset
    rsAddComments.Fields("User_Name") = Request.Form("name")
    rsAddComments.Fields("User_passwd") = Request.Form("comments")
    rsAddcomments.Fields("NAME") = Request.Form("USERNAME")
    rsAddcomments.Fields("MAIL_ID") = Request.Form("MAIL_ID")
    rsAddcomments.Fields("Place") = Request.Form("Place")
    rsAddcomments.Fields("COUNTRY") = Request.Form("COUNTRY")

    'Write the updated recordset to the database
    rsAddComments.Update

    'Reset server objects
    rsAddComments.Close
    Set rsAddComments = Nothing
    Set adoCon = Nothing
    'Redirect to the guestbook.asp page
    Response.Redirect "main.asp"

    %>

  7. #7
    Join Date
    Nov 2004
    Posts
    13
    Quote Originally Posted by Sudar
    I meant to print the contents of the variable strSQL and then exit the script before calling the update method. And then copy the content of the strSQL variable and then execute in the MySQL client. By doing so u can findout if there is any problem with the formating the query(like mismatch in quotes etc..).

    try that.. and do let me know what happened...

    As per the above, i did the following after the sql

    Response.Write strSQL

    and got

    INSERT INTO tblUsers (User_Name,User_passwd,NAME,MAIL_ID,Place,COUNTRY) VALUES ('s1','s2','s3','s4','s5','s5')

    Which when placed in mysql client creates a record
    Last edited by adventure_ke; 11-16-04 at 11:23.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •