maddening insert error on access database

[blueindian1]

Hello all,

I have a pretty funky error in with an access database. Check out this code:

'create statment to insert into media'
mSQL = "insert into media(media_title, media_type_no, media_artist_no) values ("
mSQL = mSQL & """" & title & ""","
mSQL = mSQL & typeNo & ","
mSQL = mSQL & artistNo & ");"

response.write mSQL
'execute the query'
set rsTypes = connAdd.execute(mSQL)

when i execute this code, i get the following error:

type no is 3, artist no is 2, title is test title
insert into media(media_title, media_type_no, media_artist_no) values ("test title",3,2); 
Microsoft OLE DB Provider for ODBC Drivers error '80040e10' 

[Microsoft][ODBC Microsoft Access 97 Driver] Too few parameters. Expected 1. 

/mediatrac/asp/newmediaconfirm.asp, line 36

however, and this is the interesting part, if if modify the code and remove media_title, the query executes and i get my record. here's that code

'create statment to insert into media'
mSQL = "insert into media(media_type_no, media_artist_no) values ("
mSQL = mSQL & typeNo & ","
mSQL = mSQL & artistNo & ");"

response.write mSQL
'execute the query'
set rsTypes = connAdd.execute(mSQL)

I have that problem on ALL my queries. According to MS, the only way you get that error is to have column names mispelled in the query, but I have double and triple checked the names, and in several cases I even changed the name to "X" just for kicks and grins.

Any help here would be much appreciated

 

[Mighty]

Don't know if it will make any difference, but I never use double quotes when inserting data. Have you tried using single quotes? Also, there is no need for the semi-colon if you are doing it through ASP

mSQL = "insert into media(media_title, media_type_no,"
mSQL = mSQL & " media_artist_no) values ('" & title & "',"
mSQL = mSQL & typeNo & ","
mSQL = mSQL & artistNo & &quot"

Let me know if you have any luck with this?


Mise Le Meas,

Mighty

 

[blueindian1]

thanks!

that worked like a charm!