SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    What's wrong with this SQL statement in ASP?

    Hi, when I try to insert something in the database, I get the next error:

    Code:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14' 
    
    [Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. 
    
    /offerte/request_code.asp, line 78
    This is the SQL query:

    Code:
    INSERT INTO tblCode SET bedrijf='Chi Kwadraat',website='http://www.chikwadraat.com',voornaam='Thomas',naam='De Schampheleire',email='thomasds@gmx.net',functie='Web Developer',telefoon='03/3255776',adres='Eyendijkstraat 35',postcode='2100',gemeente='Deurne',land='BelgiŽ',code='qnftngqx',datum='6/2/2001'
    and this is the ASP code to generate the query:
    Code:
        SQL = "INSERT INTO tblCode SET " & _
    	 "bedrijf='" & bedrijf & "'," & _
    	 "website='" & website & "'," & _
    	 "voornaam='" & voornaam & "'," & _
    	 "naam='" & naam & "'," & _
    	 "email='" & email & "'," & _
    	 "functie='" & functie & "'," & _
    	 "telefoon='" & telefoon & "'," & _
    	 "adres='" & adres & "'," & _
    	 "postcode='" & postcode & "'," & _
    	 "gemeente='" & gemeente & "'," & _
    	 "land='" & land & "'," & _
    	 "code='" & code & "'," & _
    	 "datum='" & Date & "'"
    Where is the error. I tried the SQL out in a local MySql server and there it works. What's wrong. I use an Access database.

    Thanks
    Thomas

  2. #2
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure that the INSERT syntax that you're using works for Access:

    Code:
    INSERT INTO <table>
    SET <field name> = <field value>
    Try using either of the following:
    Code:
    INSERT INTO <table>
    (<field name>)
    VALUES (<field value>)
    Code:
    INSERT INTO <table>
    VALUES (<field value>)
    One other thing is to do with the date field. I think Access 2000 supports the use of single quotes around dates but earlier versions require #'s.

  3. #3
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I tried it using the first one (...) VALUES (...) and I get the same error.

    This is the SQL:

    Code:
    INSERT INTO tblCode SET (bedrijf,website,voornaam,naam,email,functie,telefoon,adres,postcode,gemeente,land,code,datum) VALUES ('Chi Kwadraat','http://www.chikwadraat.com','Thomas','De Schampheleire','thomasds@gmx.net','Web Developer','033255776','Eyendijkstraat 35','2100','Deurne','BelgiŽ','da2ga3dk','6/2/2001')
    What's wrong. The "datum" column is normal text, so I think that's not a problem.

    Btw, I don't know if it's detail (I think so) but I execute the SQL using:

    Code:
        Session("DatabasePath") = "d:\html\users\chikwadraatcom\database\offerte.mdb"
        Dim DataConnection, cmdDC
          
       'Maak database connectie
        Set DataConnection = Server.CreateObject("ADODB.Connection")
        DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _
                            "DBQ=" & Session("DatabasePath") & ";"
    
    	If DataConnection.Execute(SQL) Then
                 ...
                 End If
    Thanks
    Thomas

  4. #4
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I already spot one error in the latest code. I forgot to let out the SET in the new form. At the moment the SQL is:

    Code:
    INSERT INTO tblCode (bedrijf,website,voornaam,naam,email,functie,telefoon,adres,postcode,gemeente,land,code,datum) VALUES ('Chi Kwadraat','http://www.chikwadraat.com','Thomas','De Schampheleire','thomasds@gmx.net','Web Developer','033255776','Eyendijkstraat 35','2100','Deurne','BelgiŽ','yvn2voy6','6/2/2001')
    the error I get is
    Code:
    Microsoft VBScript runtime error '800a000d' 
    
    Type mismatch 
    
    /offerte/request_code.asp, line 79
    What is this?
    Thanks

  5. #5
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is it possible that there's something wrong with

    Code:
    If DataConnection.Execute(SQL) Then
    ...
    End If
    ??
    Thomas

  6. #6
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well the error makes it look like something is wrong with the parameters. Check that none of the variables should be integers or other non-string datatypes.

  7. #7
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thomas,

    I'm sorry I haven't mentioned this earlier, but have you tried just using ASP instead of SQL to add records to a database? It is my preferred method as there are much less errors associated with it.

    I'd be happy to explain if you'd rather take this course. As for the SQL Strings, they are not my preferred method because they are long and there's much more errors. I cannot see the exact error in this statement.

    Hope this helps!

    'Till next time..
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can't use the objConn.Execute method to test to see if there were any errors. You'll need to check the connection errors collection.

    Code:
    On Error Resume Next
    
    'Assuming that you've created and opened connection object
    'Clear the errors collection
    objConn.Errors.Clear
    
    'sql string building goes here
    objConn.Execute(strSQL)
    
    'Check the errors count
    If objConn.Errors.Count > 0 Then
      'Something went wrong
    Else
      'Everything ok
    End If

  9. #9
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The database fields all are denoted as "text".

    How can I add records usig ASP instead of SQL? I never heard of that method.

    thanks for your help
    Thomas

  10. #10
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can add new records with an open recordset using the AddNew method, try the following link.

    http://www.msdn.microsoft.com/librar...k/mdae9f5k.htm

  11. #11
    Say WHA?! goober's Avatar
    Join Date
    Sep 2000
    Location
    United States
    Posts
    1,921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, yes, using ASP is different than using a plain old SQL Statment.

    I'm going to be very detailed about this, in case anyone else would like to know for the future. Connect to your database by dimming all variables, etc., in this fashion:
    Code:
    <%dim conn, RS, conn_str, SQL1
    
    Set conn = Server.CreateObject("ADODB.Connection")
    Set RS = Server.CreateObject("ADODB.Recordset")
    conn_str = "provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=D:\FolderName\FolderName\DBName.mdb"
    conn.ConnectionString = conn_str
    Conn.Open
    RS.Open "TableName", conn, adOpenKeyset, adLockOptimistic
    Alright, all you have up to this point is the standard Recordset (the way I was taught to do it by an ADO book). Now, we must define the fields, and values we want to add. We do this by using an array. the Fields I am using are "Date", "Title", and "Description", because I have taken them from a project I recently completed. The values are the same array, only filled with variables. Notice that you must match up the first field with the first value (i.e "Title" is first in the first array and GTitle is first in the second array for it's value). Here's the code to complete the operation:
    Code:
    <%dim varFields, varValues
    varFields = Array("Date", "Title", "Description")
    varValues = Array(GDate, GTitle, GDescription)
    RS.AddNew varFields, varValues
    RS.Update%>
    That's all there is to it. From there on, you can close your connection and recordset objects, etc., or do whatever you like with them.

    Hope this helps! Any questions?

    'Till next time..
    Sean Killeen [LinkedIn] [Twitter] [Web]

    Warning: Reality.sys corrupted. Universe halted. Reboot? (Y/N)

  12. #12
    SitePoint Zealot Jppr's Avatar
    Join Date
    Dec 2000
    Location
    Belgium
    Posts
    132
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Finally!

    After completing Shane's steps with the error checking all the errors where gone and everything works smoothly now.

    Thanks go out to Shane, goober and Anarchos for sticking with me in this problem.

    PS goober, I still sticked to the "plain old SQL", because I also use this in PHP. However thanks for your explanation. At least I know that it exists now!

    Cheers everybody!
    Thomas


Bookmarks

Posting Permissions

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