[Damage]

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

[Damage]

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)"

[rgfirefly24]

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')"

[Trogdor]

Please post the exact table definition.

[Damage]

"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

[Trogdor]

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.)

[Damage]

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.

[eclipsed4utoo]

using brackets around the names would have worked.

[Damage]

yeah, as you can see by the quote, i found that out afterwards 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