Getting Syntax error when executing ASP ADO Insert

Question

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.

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
– paulsm4, Commented Jan 1, 2013 at 23:57

Mate · Accepted Answer · 2013-01-01 21:32:19Z

2

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

answered Jan 1, 2013 at 21:32

Mate

5,3272 gold badges36 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Fionnuala Over a year ago

Are you sure that all data types are text? Parameters would be much safer.

Mate Over a year ago

no, but the error does not indicate a problem of data type. Also it would be nice if the OP tell us the types of each column

paulsm4 · Accepted Answer · 2013-01-01 21:47:42Z

1

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()":

http://www.freevbcode.com/ShowCode.asp?ID=3687

edited Jan 1, 2013 at 21:47

answered Jan 1, 2013 at 21:40

paulsm4

123k23 gold badges175 silver badges245 bronze badges

Collectives™ on Stack Overflow

Getting Syntax error when executing ASP ADO Insert

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related