8 Replies - 3461 Views - Last Post: 06 September 2008 - 05:40 PM Rate Topic: -----

#1 Damage  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,079
  • Joined: 05-June 08

Insert Yes/No

Posted 03 September 2008 - 02:15 AM

Heya
I'm having a bit of trouble with this insert statement and I'm not sure if it's cause of the autonumber field or the yes/no ones(using ACCESS)
the table has 7 columns
picID - autonnumber
vehicleID - text
PathToImage - text
front - Yes/No
left - Yes/No
right - Yes/No
back - Yes/No

vehicleID and pathtoimage are both string variables

this gives me a plain old syntax error message

"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front, Left, Right, Back) VALUES ('" + vehicleIDNo + "','" + imagePath + "', 1, 0, 0, 0 )"



i've tried this as well (gives me "Conversion from string "INSERT INTO tblVehicleImages ( V" to type 'Double' is not valid)
"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front, Left, Right, Back) VALUES ('" + vehicleIDNo + "','" + imagePath + "'," + 0 + "," + 0 + "," + 1 + "," + 0 + ")"



any ideas where i'm messing up?

This post has been edited by Damage: 03 September 2008 - 02:17 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Insert Yes/No

#2 Damage  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,079
  • Joined: 05-June 08

Re: Insert Yes/No

Posted 03 September 2008 - 08:25 PM

i know that this works(not sure why)
"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front) VALUES ('" + vehicleIDNo + "','" + imagePath + "',True)"




but not this
"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Left) VALUES ('" + vehicleIDNo + "','" + imagePath + "',True)"


Was This Post Helpful? 0
  • +
  • -

#3 rgfirefly24  Icon User is offline

  • D.I.C Lover
  • member icon


Reputation: 260
  • View blog
  • Posts: 1,419
  • Joined: 07-April 08

Re: Insert Yes/No

Posted 04 September 2008 - 05:58 PM

it seems to me that your insert statements need a little tweeking. your left, right, front, back are yes/no so they are expecting a Y or N (not 100% about access)

so try this:

"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front) VALUES ('" + vehicleIDNo + "','" + imagePath + "', 'Y')"


Was This Post Helpful? 0
  • +
  • -

#4 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Insert Yes/No

Posted 05 September 2008 - 08:35 AM

Please post the exact table definition.
Was This Post Helpful? 0
  • +
  • -

#5 Damage  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,079
  • Joined: 05-June 08

Re: Insert Yes/No

Posted 06 September 2008 - 06:17 AM

"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front) VALUES ('" + vehicleIDNo + "','" + imagePath + "', 'Y')"


returns a data mismatch error

Quote

Please post the exact table definition.


you mean this?

It's an MS access 2007 DB

the table name is tblVehicleImages

picID - autonnumber
vehicleID - text
PathToImage - text
front - Yes/No
left - Yes/No
right - Yes/No
back - Yes/No

i've set all the yes/no fields to default to 0

It's supposed to insert the path of an Image(each side of a car, so front is a picture from the front etc)
Like I said before, this works
"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front) VALUES ('" + vehicleIDNo + "','" + imagePath + "',True)"



But the problem arises when I start trying to insert data pertaining to any of the other sides, so this

"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Left) VALUES ('" + vehicleIDNo + "','" + imagePath + "',True)"


and this
"INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front, Left) VALUES ('" + vehicleIDNo + "','" + imagepath + "',False, True)"


and this
 "INSERT INTO tblVehicleImages ( VehicleID, PathToImage, Front, Left,Right,Back) VALUES ('" + vehicleIDNo + "','" + imagepath + "',False, True,false,false)"


all throw syntax errors

This post has been edited by Damage: 06 September 2008 - 06:19 AM

Was This Post Helpful? 0
  • +
  • -

#6 Trogdor  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 15
  • View blog
  • Posts: 627
  • Joined: 06-October 06

Re: Insert Yes/No

Posted 06 September 2008 - 09:15 AM

Ah, i have an idea.
I think you are using fieldnames that are illegal.
Both 'left' and 'right' are terms that are possible to use in a join (as in: left outer join).
Try escaping the fieldnames (so 'left' instead of left) or changing the suspicious fieldnames a bit (posLeft instead of left, etc.)
Was This Post Helpful? 1
  • +
  • -

#7 Damage  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,079
  • Joined: 05-June 08

Re: Insert Yes/No

Posted 06 September 2008 - 04:41 PM

thanks man,you were right.They are reserved words, i never would have made the connection(Especially since access doesn't give an error,bastards). using single quotes doesn't work but i changed the names and it works perfectly. Thanks again

Quote

If a reserved word is already in use, you can avoid error messages by surrounding each occurrence of the word with brackets ([ ]). However, the best solution is to change the name to a nonreserved word.

Was This Post Helpful? 0
  • +
  • -

#8 eclipsed4utoo  Icon User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1524
  • View blog
  • Posts: 5,957
  • Joined: 21-March 08

Re: Insert Yes/No

Posted 06 September 2008 - 05:12 PM

using brackets around the names would have worked.
Was This Post Helpful? 0
  • +
  • -

#9 Damage  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 27
  • View blog
  • Posts: 1,079
  • Joined: 05-June 08

Re: Insert Yes/No

Posted 06 September 2008 - 05:40 PM

yeah, as you can see by the quote, i found that out afterwards :P luckily i have barely referenced that table so far, so for best practise i just changed the names. But it's good to know for future reference. I just wish access would give some indication of the illegal use of reserved words. At my last job there was a huge problem because someone had stuck in a reserved word(like this) and the app kept throwing up unrelated error descriptions.Freaking hassle to find and fix
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1