Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

2147217900 Syntax error in INSERT INTO statement

Posted on 2003-12-12
5
Medium Priority
?
1,048 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

Dramatic changes are revolutionizing how we build and use technology. Every company is automating, digitizing, and modernizing operations. We need a better, more connected way to work together as teams so we can harness the insights from our system…
When the s#!t hits the fan, you don’t have time to look up who’s on call, draft emails, call collaborators, or send text messages. An instant chat window is definitely the way to go, especially one like HipChat. HipChat is a true business app. An…
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

610 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