www.webdeveloper.com
Results 1 to 14 of 14

Thread: ASP Form connection to MySQL DB trouble.

  1. #1
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35

    ASP Form connection to MySQL DB trouble.

    Looking for some help with a connection problem to MySQL DB.
    I'm attempting to send data from a form I've developed using ASP to a MySQL database, but I seem to have done something wrong in the connection code. When I fill out the form the data is sent to a second almost identical looking form where the user can verfy that the data is correct. If data is correct the user then sends the data to the DB. The user is then supposed to see a Confirmation page. Instead I get the following:

    HTTP 500 - Internal server error
    Internet Explorer

    The connection code I have is as follows:

    <% @language = VBScript %>
    <%
    Option Explicit
    Response.expires = 0
    Dim duplicate
    Dim lngHRID, lngNominatorID, lngAwardID, txtSavingType, curSavingsValue, memCertificateVerbage
    Dim objConn, objRS, strConnection, strQuery

    'Assign variables
    lngHRID = trim(request.form("HRID"))
    lngNominatorID = trim(request.form("NominatorsID"))
    lngAwardID = trim(request.form("nomType"))
    txtSavingType = trim(request.form("SavingsType"))
    curSavingsValue = trim(request.form("SavingsValue"))
    memCertificateVerbage = trim(request.form("CertificateVerbage"))

    Set objConn = Server.CreateObject("ADODB.Connection")
    strConnection = "DSN=MySQL_Recognition;Database=Recognition;"
    strConnection=strConnection & "UID=cooper;PWD=password;"
    objConn.Open strConnection
    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.open "tblRecognition", objConn, 1, 3, 2

    'Add a new record by calling the AddNew method
    objRS.AddNew
    objRS.Fields("HRID") = lngHRID
    objRS.Fields("NominatorID") = lngNominatorID
    objRS.Fields("AwardID") = lngAwardID
    objRS.Fields("SavingsType") = txtSavingType
    objRS.Fields("SavingsValue") = curSavingsValue
    objRS.Fields("CertificateVerbage") = memCertificateVerbage
    objRS.Update

    objRS.close
    objConn.Close
    Set objRS = Nothing
    response.redirect "../awardNomination/confirmNom.asp"
    %>


    I'm new to both ASP and MySQL so be please be gentle.

    If you need the code for the form, please let me know.

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    try changing this:

    Set objRS = Server.CreateObject("ADODB.Recordset")
    objRS.open "tblRecognition", objConn, 1, 3, 2

    'Add a new record by calling the AddNew method
    objRS.AddNew
    objRS.Fields("HRID") = lngHRID
    objRS.Fields("NominatorID") = lngNominatorID
    objRS.Fields("AwardID") = lngAwardID
    objRS.Fields("SavingsType") = txtSavingType
    objRS.Fields("SavingsValue") = curSavingsValue
    objRS.Fields("CertificateVerbage") = memCertificateVerbage
    objRS.Update

    objRS.close

    to this:
    Code:
    Dim sql
    sql = "INSERT INTO tblRecognition (" &_
          "HRID, NominatorID, AwardID, SavingsType, SavingsValue, CertificateVerbage) " &_
          "VALUES (" & lngHRID & ", " & lngNominatorID & ", " & lngAwardID & ", '" &_
          txtSavingType & "', " & curSavingsValue & ", '" & memCertificateVerbage & "')"
    
    objConn.Execute(sql)
    be sure to escape single quotes and check for valid values in the form submit data before attem,pting the insert

  3. #3
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    I tried it but I'm still getting the same HTTP 500 error. This is what I have now as my code:

    <% @language = VBScript %>
    <%
    Option Explicit
    Response.expires = 0
    Dim duplicate
    Dim lngHRID, lngNominatorID, lngAwardID, txtSavingType, curSavingsValue, memCertificateVerbage
    Dim objConn, objRS, strConnection, strQuery

    'Assign variables
    lngHRID = trim(request.form("HRID"))
    lngNominatorID = trim(request.form("NominatorsID"))
    lngAwardID = trim(request.form("nomType"))
    txtSavingType = trim(request.form("SavingType"))
    curSavingsValue = trim(request.form("SavingsValue"))
    memCertificateVerbage = trim(request.form("CertificateVerbage"))

    Set objConn = Server.CreateObject("ADODB.Connection")
    strConnection = "DSN=MySQL_Recognition;Database=Recognition;"
    strConnection=strConnection & "UID=cooper;PWD=password;"
    objConn.Open strConnection

    Dim sql
    sql = "INSERT INTO tblRecognition (" &
    "HRID, NominatorID, AwardID, SavingType, SavingsValue, CertificateVerbage) " &
    "VALUES (" & lngHRID & ", " & lngNominatorID & ", " & lngAwardID & ", '" &
    txtSavingType & "', " & curSavingsValue & ", '" & memCertificateVerbage & "')"

    objConn.Execute(sql)
    objConn.Close
    Set objRS = Nothing
    response.redirect "confirmNom.asp"
    %>


    Any ideas?
    BTW: Thanks for your help!

    Also I changed the name fo the SavingsType form field to match the variable in the code. Just to keep myself from getting confused.

  4. #4
    Join Date
    Feb 2003
    Posts
    2,745
    what line is the error occurring on. in internet explorer, go to tool, options, advanced and make sure that "show firendly http error messages" is NOT checked. this will cause it to show you more descriptive error messages.

    Then, just b4 this line:
    objConn.Execute(sql)

    put in a response.write sql

    to see what that displays.

  5. #5
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    This is the error message:

    Microsoft VBScript compilation error '800a03ea'

    Syntax error

    /recognition/forms/insertRecognition.asp, line 23

    sql = "INSERT INTO tblRecognition (" &
    --------------------------------------^

  6. #6
    Join Date
    Feb 2003
    Posts
    2,745
    do you have line breaks in your sql definition? if so, did you put &_ after them like this?
    Code:
    sql = "INSERT INTO tblRecognition (" &_
          "HRID, NominatorID, AwardID, SavingsType, SavingsValue, CertificateVerbage) " &_
          "VALUES (" & lngHRID & ", " & lngNominatorID & ", " & lngAwardID & ", '" &_
          txtSavingType & "', " & curSavingsValue & ", '" & memCertificateVerbage & "')"

  7. #7
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    Okay, I corrected that mistake.

    Now the error is:

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt-max]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 ' 'kkk')' at line 1

    /recognition/forms/insertRecognition.asp, line 28


    The 'kkk' above is what I typed into the text area named "CertificateVerbage".

  8. #8
    Join Date
    Feb 2003
    Posts
    2,745
    what do you get when u response.write sql

    ?

  9. #9
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    I'm not sure I understand. Can you elaborate?

  10. #10
    Join Date
    Feb 2003
    Posts
    2,745
    Code:
    Dim sql
    sql = "INSERT INTO tblRecognition (" &_
          "HRID, NominatorID, AwardID, SavingsType, SavingsValue, CertificateVerbage) " &_
          "VALUES (" & lngHRID & ", " & lngNominatorID & ", " & lngAwardID & ", '" &_
          txtSavingType & "', " & curSavingsValue & ", '" & memCertificateVerbage & "')"
    
    Response.Write "<p>" & sql & "</p>"
    
    objConn.Execute(sql)
    This will write out the SQL statement to the web page. We want to do this to debug. If there isa problem with the SQL being passed to the database, it should become obvious if we can see the actual values we are passing. Let me know what it displays. If it displays nothing but an error message, the put a response.end on the line after Response.Write "<p>" & sql & "</p>".

  11. #11
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    "I see" said the blind man.

    I'll try it and let you know.

    Thanks again.

  12. #12
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    I am going to have to put off more testing until after next week.

    I am going on vacation after today and wont be able to work on it again until I come back after Thanksgiving.

    I want to thank you for all your help and I'll be in touch again on the 29th.

    Thanks Again!

  13. #13
    Join Date
    Apr 2003
    Location
    Houston, TX
    Posts
    35
    Russell,

    I was able to do what you asked right before I leave.

    This is the error I am receiving:

    INSERT INTO tblRecognition (HRID, NominatorID, AwardID, SavingType, SavingsValue, CertificateVerbage) VALUES (0472924, 123456, 26, 'Cost Savings', 100000, 'eeeeeeeeeeeee')

    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [MySQL][ODBC 3.51 Driver][mysqld-4.0.20a-nt-max]Unknown column 'SavingType' in 'field list'

    /recognition/forms/11insertRecognition.asp, line 30

  14. #14
    Join Date
    Feb 2003
    Posts
    2,745
    there ya go. looks like you spotted the problem:

    Unknown column 'SavingType' in 'field list'

    maybe it's SavingsType ?

    Have a nice vacation.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles