Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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:
IMW-212932-AutoresponderIDs-201212.csv
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:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[ImportAutoresponderIDList_Insert]
 @FileName varchar(255)
 , @AutoresponderID int
 , @AutoresponderName varchar(255)
AS
BEGIN
	SET NOCOUNT ON

	INSERT INTO ImportAutoresponderIDList
	(
	 [FileName]
	 , [AutoresponderID]
	 , [AutoresponderName]
	)
	SELECT
	 @FileName
	 , @AutoresponderID
	 , @AutoresponderName
END


Code Block From OPs Comment
VB
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
  Loop
  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, "'", "''"))
  Loop
  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
Updated 15-Jan-13 12:40pm
v5
Comments
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? :-)
—SA
TheLadyHawke 15-Jan-13 18:36pm    
Yes, Sergey, it's, in my opinion, too generic.

When you are explicitly inserting a single row with values, you need to use this syntax.
SQL
INSERT INTO ImportAutoresponderIDList
( [FileName], [AutoresponderID], [AutoresponderName])
VALUES ( @FileName, @AutoresponderID, @AutoresponderName )
 
Share this answer
 
Comments
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 '='.
fjdiewornncalwe 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?
Change "SELECT" into "VALUES" in your insert statement.
 
Share this answer
 
Comments
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'.
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.
 
Share this answer
 
v2
Comments
Richard C Bishop 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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900