Getting Syntax error when executing ASP ADO Insert

0
votes

I am concatenating different values and I get the following sql statement:

INSERT INTO Ads (Position, Type, AdType, Link, Width, Height, Path, Korder ) VALUES ('left','1','left1','',1024,768,'FILE1',1)

I really do not see here any errors, however, it says me

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement.

/adm/uploadAdPic.asp, line 68

sql="INSERT INTO Ads (Position, Type, AdType, Link, Width, Height, Path, Korder )"
            sql=sql & " VALUES "
            sql=sql & "('" & position & "',"
            sql=sql & "'" & adType & "',"
            sql=sql & "'" & position & adType & "',"
            sql=sql & "'" & link & "',"
            sql=sql & "" & width & ","              
            sql=sql & "" & height & ","
            sql=sql & "'" & path & "',"
            //sql=sql & "" & korder & ","
            sql=sql & "" & korder & ")"
            //sql=sql & "0)"

            Response.Write(sql)

            //on error resume next
            conn.Execute sql,recaffected      //THIS IS LINE 68

Can you, please, help me to find syntax error.

EDIT: I have found sollution by myself, but it also contains in the answer below. Position is reserved word. I tried to modify my insert statement removing different fields and I found out that Position field makes an error. So I renamed Position to VertPos and it works.

2
asp-classicado
But you don't always have the luxury of changing the schema. So keep the Microsoft "[]" syntax in mind. Or the equivalent MySql "``" syntax. - paulsm4

2 Answers

2
votes

Position is a reserved word in Jet SQL, try change to [Position].

As a general recommendation, add [] to all columns name.

    sql="INSERT INTO Ads ([Position], [Type], [AdType], [Link], [Width], [Height], [Path], [Korder] )"
                sql=sql & " VALUES "
                sql=sql & "('" & position & "',"
                sql=sql & "'" & adType & "',"
                sql=sql & "'" & position & adType & "',"
                sql=sql & "'" & link & "',"
                sql=sql & "" & width & ","              
                sql=sql & "" & height & ","
                sql=sql & "'" & path & "',"
                //sql=sql & "" & korder & ","
                sql=sql & "" & korder & ")"
                //sql=sql & "0)"

                Response.Write(sql)

                //on error resume next
                conn.Execute sql,recaffected      //THIS IS LINE 68
1
votes

1) The error is because some of your column names happen to be MSSQL keywords:

' SUGGESTED CHANGE:
INSERT INTO Ads (
  [Position], [Type], [AdType], [Link], [Width], [Height], [Path], [Korder]) 
VALUES ('left','1','left1','',1024,768,'FILE1',1)

2) You're probably much better off using a command object instead of a "naked insert":

Here's an example that shows how to use "objCom.parameters.append()":