SQL problem

[rmc]

Right, I'm designing a webadmin interface for an online entertainment guide. I'm currently having problems with a page which allows the user to choose which movies (using checkboxes) they want to associate with a certain cinema on a certain date. So far so good.

However, I've added the capability to enter a short string into a text area to specify showing times and other conditions. This is where my SQL breaks down.

The page which accepts entry from this form does so in a loop:

'Cycle through the selected checkboxes and generate SQL command

For intCount = 1 to Request.Form("Movie&quot.Count

'Increment DiaryID to allow multiple entries with same date and cinema ID
intDiaryID = intDiaryID + 1

'Get MovieID from current Checkbox
intMovieID = Request.Form("Movie&quot (intCount)

'Get the text from the Info Text box for current checked entry
strInfo = Request.Form(intDiaryID)

'SQL Command generation and execution
objComm.CommandText = "INSERT INTO Diary (DiaryID, DateID, CinemaID, MovieID, Info) VALUES" & _
"( " & intDiaryID & ", " & intDateID & ", " & intCinemaID & ", " & intMovieID & ", " & strInfo & &quot"

objComm.Execute
objComm.Close
Set objComm = Nothing

Next

I'm getting the following error:

Microsoft JET Database Engine error '80040e14'

Syntax error in INSERT INTO statement

The line causing the error is obviously the SQL, and the only change is that I've tagged on the text string field name and variable it is held in when processed from the form, (Info and strInfo respectively_

I've also checked there's no problem with required fields / disallowing empty zero length.

Any ideas?

 

[Antzz]

Just before you execute the SQL statement, print the SQL statement using Response.write and check the syntax. It is possibly a very minor error...a missing comma or something.

 

[palbano]

> Just before you execute the SQL statement, print the SQL statement using
> Response.write and check the syntax. It is possibly a very minor
> error...a missing comma or something.

Man, if we had a dollar for each time that response was needed in this forum.... we'd have hundereds of dollars B-)

-pete