Solved

2147217900 Syntax error in INSERT INTO statement

Posted on 2003-12-12
5
1,032 Views
Last Modified: 2008-04-01
I am trying to write to my db from a form.  Everyting works fine until I wanted to write an image name into a text field.

the sql string and the error output is as below;

>>>

The resource test2 has been add to the database

INSERT INTO products (name, descShort, description, resid, price, discount, Author, productCode, image, catCode, priority, featured, discontinued, live, prodinfo) VALUES ('test2', ' shsfh shsfxghsfg sxfghszfgh xfgjhxfg', 'erh szfdtz fghh zfghh azet hnc', 6, 4, False, 'ddsfsg', 'test123', 'marriage-report.jpg', 2, 1, False, False, False, False)

Data Entry was processed.

-2147217900 Syntax error in INSERT INTO statement.

>>>

The string will work if I comment out the code that writes to the image field but when I put it back the error is still there.  The string looks fine and as I said it will write to the db so it can't be a permissions error can it?

I am writing the page in asp, the db is access 2000.

I am also using the following functions to write the form fields to the db.

>>>

function sqlFieldNameInsert(fn)

'fn is the variable containing the field name to insert

if Request.form(fn) <> "" then
      theSql = theSql + theComma + fn
      theComma = ", "
End If

end function

>>>

function sqlFieldValueInsert(fv)

'fv is the variable containing the field value to insert

if Request.form(fv) <> "" then
  theSql = theSql + theComma + "'" + Request.form(fv) + "'"
  theComma = ", "
End If

end function

>>>

the functions above seem to work but I've included them just so that you have an idea of what is going on.  there is also a sqlFieldValueInsertInt function for adding numeric data which is the same as the second function above but without 'theComma' added to the string.

This is a problem I've had for a while and I've just come back to it and I'm still stumped as to why it won't work.

Any suggestions?
0
Comment
Question by:crmapley
  • 2
  • 2
5 Comments
 
LVL 10

Expert Comment

by:D_M_D
ID: 9928819
Try this SQL Statement...Need single qoutes..


"INSERT INTO products (name, descShort, description, resid, price, discount, Author, productCode, image, catCode, priority, featured, discontinued, live, prodinfo) VALUES ('test2', ' shsfh shsfxghsfg sxfghszfgh xfgjhxfg', 'erh szfdtz fghh zfghh azet hnc', '6','4', 'False', 'ddsfsg', 'test123', 'marriage-report.jpg', '2', '1', 'False', 'False', 'False', 'False')"


--------
D_M_D
0
 
LVL 1

Author Comment

by:crmapley
ID: 9928988
Tried it but still the same error.

I thought you weren't supposed to put quotes around numeric or boolean data so I shouldn't have expected it to work should I? Or is access quite forgiving on this matter?

one thing I've thought of but never been able to find an answer to is 'does an sql string have a limit in either the amount of fields or characters it can write?'.  That would explain why the addition of one more field stops it working.

Maybe if I split the string up it would work?  But two insert statements would create two different records with only half the info needed in each wouldn't it?

As you can tell I'm still finding my way with the sql language so you may need to explain things a bit.
0
 
LVL 35

Accepted Solution

by:
YZlat earned 350 total points
ID: 9929117
try changing database field name from "image" to something else- 'image' could be a reserved word
0
 
LVL 1

Author Comment

by:crmapley
ID: 9929167
Hey, give the man the points.  It worked.

thanks mate!
0
 
LVL 35

Expert Comment

by:YZlat
ID: 9929218
why did you give me a B?
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

"In order to have an organized way for empathy mapping, we rely on a psychological model and trying to model it in a simple way, so we will split the board to three section for each persona and a scenario and try to see what those personas would Do,…
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
The viewer will learn how to dynamically set the form action using jQuery.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question