Solved

2147217900 Syntax error in INSERT INTO statement

Posted on 2003-12-12
5
1,028 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
"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,…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now