Click here to Skip to main content
13,090,503 members (54,099 online)
Rate this:
Please Sign up or sign in to vote.
I am still learning Classic ASP and SQL. I am trying to import data from a CSV file into a SQL 2005 table. The ASP page works fine until I try to upload the file. When I upload the file the first row of data is seen and held in the record set and then it produces this error message:
EXECUTE ImportAutoresponderIDList_Insert @FileName = 'IMW-212932-AutoresponderIDs-201212.csv' , @"AutoresponderID" = '701488' , @"AutoresponderName" = 'Love - Upgrade - Bronze Package'
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'AutoresponderID'.
/admin/Spitfire/Import1SCExportFiles/ImportAutoresponderList.asp, line 107

I know the error is not in the ASP page as another import routine uses this same code with only the variable names being different AND because AutoresponderID is not mentioned in the ASP.
AutoresponderID is a field name in the SQL table and is mentioned in the stored procedure.
Where is the syntax error? Here is the stored procedure:

ALTER PROCEDURE [dbo].[ImportAutoresponderIDList_Insert]
 @FileName varchar(255)
 , @AutoresponderID int
 , @AutoresponderName varchar(255)
	INSERT INTO ImportAutoresponderIDList
	 , [AutoresponderID]
	 , [AutoresponderName]
	 , @AutoresponderID
	 , @AutoresponderName

Code Block From OPs Comment
Dim sqlImportAutoresponderIDListTemplate
 sqlImportAutoresponderIDListTemplate = "EXECUTE ImportAutoresponderIDList_Insert" & vbCrLf
 sqlImportAutoresponderIDListTemplate = sqlImportAutoresponderIDListTemplate & " @FileName = '" & SQLEncode(strFileName) & "'" & vbCrLf
 arrColumnList(0) = "FileName"
 Do While lngOffset < Len(strLine)
  lngColumn = lngColumn + 1
  lngLength = InStr(lngOffset, strLine, strColumnDelimiter)
  If lngLength < 1 Then
    lngLength = Len(strLine) + 1
  End If
    lngLength = lngLength - lngOffset
   strColumn = Replace(Mid(strLine, lngOffset, lngLength), "/", "")
   lngOffset = lngOffset + lngLength + 1
   sqlImportAutoresponderIDListTemplate = sqlImportAutoresponderIDListTemplate & " , @" & Replace(strColumn, " ", "") & " = '[" & strColumn & "]'" & vbCrLf
  arrColumnList(lngColumn) = strColumn
  ElseIf lngLine > 1 Then
	If strLine = "" Then
  Exit Do
 End If
'Response.Write ", " & lngLine
 Dim sqlImportAutoresponderIDList
 sqlImportAutoresponderIDList = sqlImportAutoresponderIDListTemplate
 Do While lngOffset < Len(strLine)
  lngColumn = lngColumn + 1
  strColumn = ExtractColumn(strLine, lngOffset, strColumnDelimiter)
  If Left(strColumn, 1) = """" Then
    strColumn = Mid(strColumn, 2, Len(strColumn) - 1)
  End If
  If Right(strColumn, 1) = """" Then
    strColumn = Mid(strColumn, 1, Len(strColumn) - 1)
  End If
  sqlImportAutoresponderIDList = Replace(sqlImportAutoresponderIDList, "[" & arrColumnList(lngColumn) & "]", Replace(strColumn, "'", "''"))
  Dim rsImportAutoresponderIDList
  Set rsImportAutoresponderIDList = CreateObject("ADODB.Recordset")
  Response.Write "<p>" & sqlImportAutoresponderIDList & "" & vbCrLf
   rsImportAutoresponderIDList.Open sqlImportAutoresponderIDList, Application("DATABASE_Name")
  Set rsImportAutoresponderIDList = Nothing
End If

[Edit]Code block included from OPs Comment by Jibesh[/Edit]
Posted 15-Jan-13 11:25am
Updated 15-Jan-13 12:40pm
TheLadyHawke 15-Jan-13 18:10pm
If I did not say it right I will say it again differently.
I am not trying to retrieve data from a table and put it into another table. I am uploading a CSV file and putting it into a table, therefore there is no data yet.
TheLadyHawke 15-Jan-13 18:13pm
I think I said that wrong too... I mean there is no data in the SQL table yet. There is data in the CSV file.
jibesh 15-Jan-13 18:21pm
how you are executing this SP from your code ? can you share that too
Sergey Alexandrovich Kryukov 15-Jan-13 18:18pm
This error reporting is really poor, isn't it? :-)
TheLadyHawke 15-Jan-13 18:36pm
Yes, Sergey, it's, in my opinion, too generic.
jibesh 15-Jan-13 18:51pm
what is the contents of this variable ' sqlImportAutoresponderIDListTemplate' what it actually do?
which part of the code actually performs the SP execution. sorry am confused with the code here.
TheLadyHawke 15-Jan-13 19:00pm
"EXECUTE ImportAutoresponderIDList_Insert"
This is what executes the stored procedure
sqlImportAutoresponderIDListTemplate is just a varible name within the ASP code
jibesh 15-Jan-13 19:05pm
in that case your store procedure comes with arguments so you need to construct a string like this
"EXECUTE ImportAutoresponderIDList_Insert FileName,AutoResponderId,ResponderName"

the final string should be something like this
"EXECUTE ImportAutoresponderIDList_Insert 'AutoResponderFile.txt',1,'DummyName'"

can you debug/check for the above string construction. and I saw a loop do you want to iterate through the csv files and insert each entry into the database using the above SP
TheLadyHawke 15-Jan-13 19:27pm
I think I know what you are asking. I will try to answer it.
From the Dim sql all the way to the end of the Do While Loop, is the instructions to put into the column called FileName the name of the file that the application will upload, it is then to check the file being uploaded and locate in the first line, and subsequent columns, the corresponding column names that match the sql table field names, then put the data in those columns into the correct fields with the FileName being repeated in the FileName field column.
Did I answer what you were looking for?
The stored procedure is to place the information in the table.
jibesh 15-Jan-13 19:40pm
You answered somewhat but looks like you the way you construct the SP is wrong.
1. Can you explain the above with a sample data
2. post the value of the field sqImportAutoRespinderIdListTemplate after the first do-while loop
3. post the value of the field sqImportAutoRespinderIdList after the second loop

please be note that you dont need to pass the column name to execute a SP just pass the values as arguments just like we call Methods/Functions in programming language
TheLadyHawke 15-Jan-13 21:03pm
1. Sample data (which is different all the time): @FileName = 'IMW-212932-AutoresponderIDs-201212.csv' , @"AutoresponderID" = '701488' , @"AutoresponderName" = 'Love - Upgrade - Bronze Package'
2.1st Do While Loop Value sample: 'IMW-212932-AutoresponderIDs-201212.csv' (@FileName is the sql table field name - the first loop is to determine what that file name is and then the SP puts that determined name in the column of @FileName - the column name, FileName, is not passed to SQL)
3.2nd Do While Loop Value sample: '701488' and 'Love - Upgrade - Bronze Package' (AutoresponderID & AutoresponderName are the column names of the CSV file and corresponding sql field names - the SP is to take the data in those columns (not the column names) and put them in the corresponding fields).
The ASP code is a reusable code so that only the variable names change.
The SQL code can also be reusable with only the column names changing.
Both these codes were created by someone else, used and reused. All I had to do is create the sql table and modify the SP and the ASP code to fit the columns of the CSV file. Both the ASP and SQL codes already work with other files appropriately.
I have done an A to B comparison of both the ASP and SQL codes and can not find any syntax errors. This is why I am perplexed.
jibesh 15-Jan-13 21:48pm
just make a simple correction in preparing the SP string do not use the column names in the SP instead use the value directly. remove @FileName='IMW-212932-AutoresponderIDs-201212.csv' instead use 'IMW-212932-AutoresponderIDs-201212.csv' alone like wise remove 'AutoresponderID'='701488' and so one.

The SP(Stored Procedure) contains the Table name and column names where to inset your data, all you need to do is just pass the data alone. No need to pass Field/Column Name with data.

replace the field usages with data alone in the SP you will get the result.

so the final string must be like 'EXECUTE ImportAutoresponderIDList_Insert 'IMW-212932-AutoresponderIDs-201212.csv',701488,'Love - Upgrade - Bronze Package', also the argument type for the SP are Varchar, int and Varchar so you need to pass the same datatype

hope this helps
TheLadyHawke 15-Jan-13 22:23pm
I am sorry. I must have misunderstood what you were asking and therefore gave you the wrong information. I can not use the direct values as they change all the time.

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

When you are explicitly inserting a single row with values, you need to use this syntax.
INSERT INTO ImportAutoresponderIDList
( [FileName], [AutoresponderID], [AutoresponderName])
VALUES ( @FileName, @AutoresponderID, @AutoresponderName )
TheLadyHawke 15-Jan-13 18:04pm
I tried it and got this error in SQL
Msg 102, Level 15, State 1, Procedure ImportAutoresponderIDList_Insert, Line 11
Incorrect syntax near '='.
Marcus Kramer 15-Jan-13 18:13pm
There is no "=" anywhere in the snippet so I'm not sure how you got that.
TheLadyHawke 15-Jan-13 18:22pm
I am sorry I did try something else. But I did try your code and it still gave me the same error as the original error message. Do you want to see the ASP code anyway?
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

Change "SELECT" into "VALUES" in your insert statement.
TheLadyHawke 15-Jan-13 18:04pm
I tried that and got this error message:
Msg 102, Level 15, State 1, Procedure ImportAutoresponderIDList_Insert, Line 16
Incorrect syntax near '@FileName'.
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

The solution resulted in using an ASP Function to remove the double quotes around the column names and save the xls file to CSV setting the TextDelimiter to single quotes.
There was nothing wrong with the Store Procedure.
I do thank all those who assisted me in their efforts.
richcb 16-Jan-13 14:01pm
Glad you got it working.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy |
Web01 | 2.8.170813.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100