Incorrect syntax near

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

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 15-Jan-13 11:25am

TheLadyHawke

Updated 15-Jan-13 12:40pm

v5

Add a Solution

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.

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.

3 solutions

Solution 2

Change "SELECT" into "VALUES" in your insert statement.

Posted 15-Jan-13 11:34am

Richard C Bishop

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'.

Solution 1

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 )

Posted 15-Jan-13 11:33am

fjdiewornncalwe

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?

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

TheLadyHawke · Accepted Answer · 2013-01-16T07:44:00

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.

Posted 16-Jan-13 7:44am

TheLadyHawke

Updated 16-Jan-13 7:46am

v2

Comments

Richard C Bishop 16-Jan-13 14:01pm

Glad you got it working.