Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

INSERT fails when specify columns

Status
Not open for further replies.
DanX

DanX

Programmer
Jan 16, 2001
13
US
I have an ASP page for inserting a new record in a database. The original code functioned fine, but omitted the column list. I plan to add fields to the database, so I would like to add the column list to ensure the data goes to the right place after the table change. Unfortunately after I added the column list, though I checked the syntax a million times, I get:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement. /info/revisedtrack/track_directory_insert.asp, line 187


The original SQL (ignore numfill, just a function to pad values):

SQL = "INSERT INTO Track_Table VALUES ('" &numfill(Trackname)& "','"
SQL = SQL & "" &numfill(Request.Form("Contactname"))& "','" &numfill(Request.Form("Address"))& "','"
SQL = SQL & "" &numfill(Request.Form("City"))& "','" &numfill(Request.Form("comstate"))& "','"
SQL = SQL & "" &numfill(Request.Form("Postalcode"))& "','" &numfill(Request.Form("Phone"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio1"))& "','" &numfill(Request.Form("radio2"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio3"))& "','" &numfill(Request.Form("radio4"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio5"))& "','" &numfill(Request.Form("radio6"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio7"))& "','" &numfill(Request.Form("radio8"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio9"))& "','" &numfill(Request.Form("radio10"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio11"))& "','" &numfill(Request.Form("radio12"))& "','"
SQL = SQL & "" &numfill(Request.Form("radio13"))& "','"
SQL = SQL & "" &numfill(Request.Form("Website"))& "','"
SQL = SQL & "" &numfill(Request.Form("emailAddr"))& "','No','"
SQL = SQl & "" &numfill(Request.Form("comcount"))& "', '" &date&"')"

My modified statement:

SQL = " INSERT INTO Track_Table "
SQL = SQL & "(Track_Name,Contact_Name,Address,City,State,Zip,Phone_Number,"
SQL = SQL & "T_Indoor,T_Outdoor,T_Off_Road,T_Oval,T_Banked,T_Dirt,T_Carpet,"
SQL = SQL & "T_Concrete,T_Asphalt,T_Hobby_Shop,T_AC_Power,T_Automatic_Lap,"
SQL = SQL & "T_Food,URL,Email,Country,Date) "
SQL = SQL & "VALUES ('" &numfill(Trackname)& "','"
etc. continuing as above.

Is there something specific to Access SQL that I need to know here? Or could it be that by adding the column list I exceeded some limits on the length of the statement? I could change the whole thing to recordset.addnew format I suppose, but I hate to leave it like this. Any helpful hints would appreciated. And by the way, what are pros and cons of INSERT vs. Addnew?


 
Status
Not open for further replies.

Similar threads

Swi
  • Locked
  • Question
Jet Sql Statement - Select Distinct Multiple Columns 1
Replies
4
Views
869
Swi
Swi
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
1K
TheresAlwaysAWay
TheresAlwaysAWay
Moss100
  • Locked
  • Question
Query super slow when foreign key is set to no duplicates
Replies
1
Views
211
CreativeStrat
CreativeStrat
MattGreer
  • Locked
  • Question
VBA Query - Close and Reopen db Connection when SQL Changes? 2
Replies
4
Views
492
Andrzejek
Andrzejek
03Explorer
  • Locked
  • Question
Pivot with custom header columns 1
Replies
5
Views
190
dhookom
dhookom

Part and Inventory Search

Sponsor

Back
Top