Help with inserting array contents to SQL Server 2000

I've been doing this in Access, but cannot find the answer to how to do it with SQL Server.

From a web form, a user can select a number of different dates. The selected dates are held as text (not DateTime) in an ArrayList.

Clicking the Submit button writes the contents of the form to a database table.

This works for Access:

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= datesArray.Item(i)
Next i

insSQL &= "VALUES (@.typEvent, @.starts, @.ends, @.starts, @.ends, @.attend, @.title, @.room, @.department, @.contact, @.address, @.telephone, @.email, @.telefax, "
For i = 0 to datesArray.Count - 1
insSql &= "#" & datesArray.Item(i) & "#, "
Next i

It doesn't work for SQL Server, and when trying to insert the value "01/29/2007" I get the error message: "The name '#1' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted."

I have also tried the line:

For i = 0 to datesArray.Count - 1
insSql &= satesArray.Item(i)
Next i

and get: "Incorrect syntax near the keyword 'VALUES'."

I'm not sure where to find the information to correct my error.

Any help would be appreciated.

Tinker

On a quick review, before looking into the code, you dont need to enclose "#" around the dates like you do for Access. So remove that and give it a shot again.|||

And your dates have to be in single quotes '01/29/2007' to work correctly because you pass them as string, and be sure that your date format is equal to data format strings used by server because if server uses dd/mm/yyyy this date will fail or if you server use mm-dd-yyyy it will fail also.

Best solution is to remove / and - from string, do test and you will see results

see posthttp://forums.asp.net/thread/1553054.aspx for examples how data formating could crash.

|||

Thank you -- and you, too, ndinakar. I was able to remember that the #'s are required for Access, but couldn't find the information on using single quotes for SQL server on my own.

That solved it, and I am grateful for your help.

Tinker