ASP Insert into command

joshua_mccrea · August 7th, 2003, 12:34 PM

Hello everyboy, this is my first time using this service and hope to use it more often. I've been working on this problem for a few hours now and can't seem to come up with any reason why it won't work. My code:

SQL = "INSERT into LOCATION (Location_ID,Locations,Location_Label,Location_Des cription,"
SQL = SQL & "County,Route,Begin_Log,End_Log,SECT_Length,Type_C ode,NLF_ID)"
SQL = SQL & " VALUES "
SQL = SQL & "('" & LocationID & "," & Location & "',"
SQL = SQL & "'" & LocLabel & "," & Request("LocDesc") & "',"
SQL = SQL & "'" & Request("County") & "," & Request("Route") & "',"
SQL = SQL & "'" & BeginLog2 & "," & EndLog2 & "',"
SQL = SQL & "'" & Section & "," & Request("FundType") & "',"
SQL = SQL & "'" & NLFID & "')"
response.write SQL
Set rs= adoDataConn.execute(SQL)

And the printout from the SQL print is:

INSERT into LOCATION (Location_ID,Locations,Location_Label,Location_Des cription,County,Route,Begin_Log,End_Log,SECT_Lengt h,Type_Code,NLF_ID) VALUES ('DEL04204000600,DEL-US-42','DEL-US-42 04.00-06.00,By the Zoo','DEL,42','04.00,06.00','02.00,F','SDELUS00042 **C')

I get an error explaining that the number of query values and the number of destination fields are not equal. I've counted them up and I get 11 fields in the table and 11 variables that I'm trying to insert. I'd appreciate any help on this matter.

Thanks

chintu · August 7th, 2003, 12:42 PM

Actually if you carefully take a look at your printed out sql statement its like this:

INSERT into LOCATION (Location_ID,Locations,Location_Label,Location_Des cription,County,Route,Begin_Log,End_Log,SECT_Lengt h,Type_Code,NLF_ID) VALUES ('DEL04204000600,DEL-US-42','DEL-US-42 04.00-06.00,By the Zoo','DEL,42','04.00,06.00','02.00,F','SDELUS00042 **C')

and 'DEL0420...,DEL-US-42' is considered as one field as the first field is not ended bu a quote, nor the second field started with a quote, rather you joined them as one field by bringing them together into one set of quotes. got it? just re write the sql and it should work.

Chintu

joshua_mccrea · August 7th, 2003, 12:53 PM

Thanks chintu, that fixed that problem pretty quick. I had noticed those quotes but wasn't thinking straight, and didn't really take them into consideration.

joshua_mccrea · August 7th, 2003, 01:13 PM

I guess that one problem seems to breed other problems. Now I'm looking at an error message:

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'DEL-US-42 04.00-06.00'.

/locations.asp, line 116

I'm using the same code except the changes to do away with the single quotes.

chintu · August 7th, 2003, 02:26 PM

can u response.write the sql query? and possibly post it here...

Chintu

planeswalk · August 13th, 2003, 10:35 PM

Hi,

Just make sure that you check whether each entry in your SQL statement is fully enclosed by single-quotes. Looking at your original SQL statement I see a lot of values that are not fully enclosed by single quotes so you have to really check each and every entry.

Cheers!

Marlon Villarama
Support Team
Web Burner Hosting
[email protected]
www.webburner.com
Toll-Free: 877-535-2876