Help with this statement...

Results 1 to 3 of 3

Thread: Help with this statement...

  1. #1
    Garth Guest

    Default Help with this statement...

    Hi,<BR><BR>I am having trouble inserting info into an access db, only when trying to submit info into more than 3 columns at a time.<BR><BR>my code is listed below:<BR><BR>SQLstmt = "INSERT INTO Table1 (Item1,Item2,Item3)"<BR>SQLstmt = SQLstmt & " VALUES (&#039" & request.form("form1") & "&#039,&#039" & request.form("form2") & "&#039,&#039" & request.form("form3") & "&#039)"<BR>conn.execute(SQLstmt)<BR><BR><BR> is works fine, BUT, if I modify the code to the following:<BR><BR>SQLstmt = "INSERT INTO Table1 (Item1,Item2,Item3,Item4)"<BR>SQLstmt = SQLstmt & " VALUES (&#039" & request.form("form1") & "&#039,&#039" & request.form("form2") & "&#039,&#039" & request.form("form3") & "&#039,&#039" & request.form("form4") & "&#039)"<BR>conn.execute(SQLstmt)<BR><BR>I then get the following error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e14&#039 <BR><BR>[Microsoft][ODBC Microsoft Access 97 Driver] Syntax error in INSERT INTO statement<BR><BR>So what am I missing here? (I also modified the db and inserted the &#039Item4&#039 column)<BR><BR>Many thanks,<BR><BR>Garth<BR>

  2. #2
    Join Date
    Dec 1969

    Default RE: Help with this statement...

    Garth,<BR>Without knowing the data structure (including data types) of the table you are trying to update, it could be almost anything. It may be that you are trying to pass character data types into numeric table columns, you may be passing invalid/illegal characters into a field, or you could even be trying to put more data into a field than the field is designed to take. The point is, there are any number of problems that could be creating this error. <BR>I am not aware of any problem inserting more than three fields into a database at a time. In fact, I have inserts that send as many as 100 columns of data into tables at a time.<BR>Keep in mind :<BR>1. Make sure the data types of fields being inserted into match the data type of the data being inserted.<BR>2. Make sure you aren&#039t passing illegal characters into a field.<BR>3. Make sure the data being inserted is not longer than the field into which it is being inserted.<BR>4. Make sure that MEMO fields are the last set of fields in your insert statement. This can create MAJOR issues.<BR>5. Print your INSERT statement to your screen to see what it is sending to the database. This very often provides you with the clue(s) you need to fix the problem.<BR><BR>Good luck!<BR><BR>Daniel Anderson<BR><BR>

  3. #3
    Garth Guest

    Default RE: Help with this statement...

    Hi,<BR><BR>I created a new db, and made sure that they all the fields were formatted to the correct data types (leaving the memo fields last). <BR><BR>It works!<BR><BR>Thanks,<BR><BR>Garth

Posting Permissions

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