?
Solved

2147217900 Syntax error in INSERT INTO statement

Posted on 2003-12-12
5
Medium Priority
?
1,045 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1050 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

When it comes to write a Context Sensitive Help (an online help that is obtained from a specific point in state of software to provide help with that state) ,  first we need to make the file that contains all topics, which are given exclusive IDs. …
This article was originally published on Monitis Blog, you can check it here . Today it’s fairly well known that high-performing websites and applications bring in more visitors, higher SEO, and ultimately more sales. By the same token, downtime…
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Suggested Courses

770 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