"Syntax error with insert into statement"

I am interacting with my database by way of asp and ado...unfortunately, it seems i have a problem with the following query - my browser reports an error "syntax error with insert into statement"....

any ideas on why this might be????? the database is Access


insertstr = "insert into Project(ProjectName, Department, Priority, Start_date, Est_end_date, IT_proj_mgr, Bus_mgr, Bus_owner) Values('" & proj_name & "', " & department & ", '" & priority & "', #" & begdate & "#, #" & findate & "#, " & IT_pm_id & ", " & bus_pm_id & ", " & bus_owner_id & ")"

Where are the quotes around this bit of data, is it text data or an int?

department is the id of a department, so it is an integer - no quotes required

I really can't see the problem.

Try using debug.print insertstr before the execute statement and then post the sql statement. That way we can see what is getting passed with sample data in there. Makes it much easier to see any faults.

bella,

I completely agree with a.koepke.
I haven't used the debug.print, just print the insertstr variable and don't really execute the query, so you would be able to see exactly what is being passed.

In sql staments a few things you might want to check on:

1. The table doesn't have any columns that can not be empty
and were not included in the insert statment.

bella,

I completely agree with a.koepke.
I haven't used the debug.print, normally I just print the insertstr variable and don't really execute the query, so you would be able to see exactly what is being passed.

In sql staments a few things you might want to check on:

1. The table is not open for read only. create a dumy table and try inserting somthing into it just to make sure insertion in to the db from asp is possible.

2. The table doesn't have any columns that can not be empty "NOT Null" property in MySQL or "zero length allowed" in ACCESS
a. and were not included in the insert statment.
b. or the value passed was blank string.

3. The integer values retrieve from the FORM is not blank
eg.
Dim x = Request.(xFromForm)
insert into table( col1str. col2int. col3str ) values ( "v1", x, "v2)
This will generate:
insert into table( col1str. col2int. col3str ) values ( "v1", , "v2)
causing errors.

4. The date format is valid.

5. Stings passed in variables do not include '
if strx = "I'll get this done"
insert into (strvalue) Values (' " & x & " ')
will result in
insert into (strvalue) Values ('I'll get this done') which will cause error

Note: I think, and I have done it before, the date is passed as '2000/02/02' not like the way you have done it #2000/02/02#

Hope this was of any help

turned out i had some problems with my client side scripts that weren't changing field values correctly...consequently some of the values being passed were blank which caused the dramas

thankyou for all your help anyway though!!!!

Awesome... remember when you have problems with SQL its always a good idea to print out the SQL so you can see what is going on. Easiest way to debug SQL.