Massive Insert Statement not working....Help!!!

[pewilson]

I inserting data into a table, but I think my sql string is way too long! The syntax message I get is "Syntax error in INSERT INTO statement." Im not sure if I need to break it up, what method would I go about breaking it up. I am including the sql string in hopes that someone can help me out. Thanks in advance.


<!--#include file=&quot;Conn.asp&quot;-->
<%

dim strSQL


strSQL =&quot;INSERT INTO Request(Request_Date,Request_Time,Employee_ID,Employee_Name,Employee_Status,&quot;&_
&quot;Employee_Department,Airline_Preference,Departure_City,Departure_Code,Departure_Date,Departure_Time, &quot;&_
&quot;1st_DepartureCity,1st_DepartureCode,1st_DepartureAirTravel,1st_AirPreference,1st_Hotel,1st_Car, &quot;&_
&quot;1st_aDate,1st_aTime,1st_aCity,1st_aAddress,1st_bDate,1st_bTime,1st_bCity,1st_bAddress, &quot;&_

&quot;2nd_DepartureCity,2nd_DepartureCode,2nd_DepartureAirTravel,2nd_AirPreference,2nd_Hotel,2nd_Car, &quot;&_
&quot;2nd_aDate,2nd_aTime,2nd_aCity,2nd_aAddress,2nd_bDate,2nd_bTime,2nd_bCity,2nd_bAddress, &quot;&_

&quot;3rd_DepartureCity,3rd_DepartureCode,3rd_DepartureAirTravel,3rd_AirPreference,3rd_Hotel,3rd_Car, &quot;&_
&quot;3rd_aDate,3rd_aTime,3rd_aCity,3rd_aAddress,3rd_bDate,3rd_bTime,3rd_bCity,3rd_bAddress, &quot;&_

&quot;4th_DepartureCity,4th_DepartureCode,4th_DepartureAirTravel,4th_AirPreference,4th_Hotel,4th_Car, &quot;&_
&quot;4th_aDate,4th_aTime,4th_aCity,4th_aAddress,4th_bDate,4th_bTime,4th_bCity,4th_bAddress)&quot;


strSQL = strSQL & &quot;VALUES('&quot;& request.form(&quot;txtDate&quot &&quot;','&quot;
strSQL = strSQL & request.form(&quot;txtTime&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeID&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeName&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeStatus&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeDepartment&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtAirlinePreference&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtDepartureCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtDeptCode&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txtDepartureDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;cboDepartureTime&quot &&quot; &quot;& request.form(&quot;cboTime&quot & &quot;','&quot;

strSQL = strSQL & request.form(&quot;txt1stDepartureCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txt1stDepartureCode&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk1stAirTravel&quot & &quot;,'&quot;
strSQL = strSQL & request.form(&quot;cbo1stDeparturePreference&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk1stHotelPref&quot & &quot;,&quot;
strSQL = strSQL & request.form(&quot;chk1stCar&quot & &quot;,'&quot;

strSQL = strSQL & request.form(&quot;1st_atxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_acboTime&quot &&quot; &quot;& request.form(&quot;1st_acboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_atxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_atxtAddress&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_btxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_bcboTime&quot &&quot; &quot;& request.form(&quot;1st_bcboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_btxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;1st_btxtAddress&quot & &quot;','&quot;

strSQL = strSQL & request.form(&quot;txt2ndDepartureCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txt2ndDepartureCode&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk2ndAirTravel&quot & &quot;,'&quot;
strSQL = strSQL & request.form(&quot;cbo2ndDeparturePreference&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk2ndHotelPref&quot & &quot;,&quot;
strSQL = strSQL & request.form(&quot;chk2ndCar&quot & &quot;,'&quot;

strSQL = strSQL & request.form(&quot;2nd_atxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_acboTime&quot &&quot; &quot;& request.form(&quot;2nd_acboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_atxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_atxtAddress&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_btxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_bcboTime&quot &&quot; &quot;& request.form(&quot;2nd_bcboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_btxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;2nd_btxtAddress&quot & &quot;','&quot;

strSQL = strSQL & request.form(&quot;txt3rdDepartureCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txt3rdDepartureCode&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk3rdAirTravel&quot & &quot;,'&quot;
strSQL = strSQL & request.form(&quot;cbo3rdDeparturePreference&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk3rdHotelPref&quot & &quot;,&quot;
strSQL = strSQL & request.form(&quot;chk3rdCar&quot & &quot;,'&quot;

strSQL = strSQL & request.form(&quot;3rd_atxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_acboTime&quot &&quot; &quot;& request.form(&quot;3rd_acboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_atxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_atxtAddress&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_btxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_bcboTime&quot &&quot; &quot;& request.form(&quot;3rd_bcboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_btxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;3rd_btxtAddress&quot & &quot;','&quot;

strSQL = strSQL & request.form(&quot;txt4thDepartureCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;txt4thDepartureCode&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk4thAirTravel&quot & &quot;,'&quot;
strSQL = strSQL & request.form(&quot;cbo4thDeparturePreference&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;chk4thHotelPref&quot & &quot;,&quot;
strSQL = strSQL & request.form(&quot;chk4thCar&quot & &quot;,'&quot;

strSQL = strSQL & request.form(&quot;4th_atxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_acboTime&quot &&quot; &quot;& request.form(&quot;2nd_acboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_atxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_atxtAddress&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_btxtDate&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_bcboTime&quot &&quot; &quot;& request.form(&quot;2nd_bcboSetting&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_btxtCity&quot & &quot;','&quot;
strSQL = strSQL & request.form(&quot;4th_btxtAddress&quot & &quot;')&quot;

response.write strSQL

Set oConnStatus = server.createobject(&quot;adodb.connection&quot
oConnStatus.Open strconn

oConnStatus.execute(strSQL)

response.redirect &quot;ReviewRequest.asp&quot;

set oConnStatus = nothing

%> Paul
paul_wilson74@hotmail.com

 

[timtom]

It might be that there's no comma between these two values -

strSQL = strSQL & request.form(&quot;cboDepartureTime&quot &&quot; &quot;& request.form(&quot;cboTime&quot & &quot;','&quot;

Or it might be something else. Try writing the strSQL to the screen (as you've done) without executing it and look to see if it's all correct as the database will see it. Maybe there's an apostrophe in one of your form fields which is breaking it. Good luck

 

[SashiRaveendran]

also some of these sound like they are numeric data types. If you have defined a field as int or long or some numeric data type do not enclose its value in single quotes. I see that employeeID has single quotes around it. This is ok if your id field is a string but if its not then remove the single quotes

...
strSQL = strSQL & request.form(&quot;txtTime&quot & &quot;',&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeID&quot & &quot;,'&quot;
strSQL = strSQL & request.form(&quot;txtEmployeeName&quot & &quot;'
...


timtom also raised a good point, if any of yuor values contained a single quote it will breake your sql string. So for safety you should check each string for single quotes before adding it to the sql string

 

[jlgdeveloper]

You can make life easier by not using the insert statement, and adding each individually. You would then be able to step though it item by item, and see where it errors.

example

open a recordset

s = &quot;select top 1 * from shcedule&quot; '*** approximate
'*** open the recordset, read and write
'*** add a new record
rs.addnew
'*** add each item
rs(&quot;departure_date&quot = request.form(&quot;departure_date&quot
rs(&quot;departure_date&quot = request.form(&quot;departure_date&quot
rs(&quot;departure_date&quot = request.form(&quot;departure_date&quot
rs(&quot;departure_date&quot = request.form(&quot;departure_date&quot
'*** etc etc
'*** update record
rs.update

The insert is more efficient, but yours is a bear and this would help. Jonathan Galpin

http://www.keen.com/categories/categorylist_expand.asp?sid=5215353&gid=0&pmode=0

 

[pewilson]

I went through each section and did a test to see what was causing the error. The error was occurring if left the fields with time/date missing I got the data type mismatch. Problem #1 figured out. However; the second problem may be more complex, but if I only fill out half of the form, I recieved this error messsage &quot;syntax error in INSERT INTO statement&quot;. I have a good idea this may be due to not all the fields being filled in. I think what I may have to do is right an if then statement. I have a plan of action, but I will your expertise. I want to use a checkbox to enable certain fields. I know how to do this in VB, but not very well in VBscript. If anyone out there can assist me in how to disable/enable fields in ASP I would greatly appreciate it. Thanks in advance.

Paul
paul_wilson74@hotmail.com