语法不正确 [英] Incorrect syntax near

问题描述

我还在学习经典ASP和SQL。我正在尝试将CS​​V文件中的数据导入SQL 2005表。 ASP页面正常工作，直到我尝试上传文件。当我上传文件时，第一行数据被看到并保存在记录集中，然后它会产生以下错误消息：

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

我知道错误不是在ASP页面中，因为另一个导入例程使用相同的代码，只有变量名称不同，因为ASP中没有提到AutoresponderID。

AutoresponderID是SQL表中的字段名称，在下面提到存储过程。

语法错误在哪里？这是存储过程：

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

来自OP的代码阻止评论

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

OP中包含的代码块Jibesh的评论[/编辑]

Code block included from OPs Comment by Jibesh[/Edit]

推荐答案

当您使用值显式插入单行时，您需要使用此语法。

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 )

在插入语句中将SELECT更改为VALUES。

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

解决方案导致使用ASP函数删除列名称周围的双引号并将xls文件保存为CSV，将TextDelimiter设置为单引号。

存储过程没有任何问题。

我这样做感谢所有协助我努力的人。

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.

这篇关于语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！