SQL Server 错误 80040e14 在结果集中使用经典 ASP

Question

我正在尝试使用经典的ASP和SQL Server从数据库中为矢量的每个位置选择结果。到目前为止的代码：

FOR EACH x IN Tabela
sql = "SELECT DISTINCT tborders.family AS family, tborders.qty AS qty, tborders.los AS los, CONVERT(DATE, tborders.mrd_date) AS mrd FROM [DASH].[dashboard_db].[dbo].[tb_family] AS tbfamily INNER JOIN [DASH].[dashboard_db].[dbo].[tb_started_zero] AS tborders ON tbfamily.[family] = tborders.[family] WHERE tborders.[Order Number] = "&x&""
SET rs = conn.execute(sql)
IF rs.EOF = false THEN
mrd(counter) = rs("mrd") 
family(counter) = rs("family") 
los(counter) = rs("los") 
qty(counter) = rs("qty") 
counter=counter+1
END IF
NEXT

请注意，tborders.[Order Number]是一个int类型化的值。由于某种原因，我遇到了此错误：

Microsoft SQL 服务器本机客户端 11.0 错误"80040e14">

"="附近的语法不正确。

/asplearning/act/validate-schedule-line.asp， line 46

我尝试删除SET，但后来我的结果集未被识别为对象。我很确定这些类型很好，我已经尝试过：

if isNumeric(x) THEN
response.write("<h1>it is numeric</h1>")
else
response.write("<h1>not numeric</h1>")
end if

它为Tabela的每个位置写了"它是数字"。任何人都可以帮助解决似乎是什么问题吗？

Answer 1

看起来你有一个空项目(集合的第一个或最后一个项目(。

我强烈建议您使用sp_executesql。 这将使用已编译的执行计划，这将验证您的参数(针对 sql 注入(。

FOR EACH x IN Tabela
if len(x) > 0 then
sql = "exec sp_executeSql N'SELECT DISTINCT tborders.family AS family, tborders.qty AS qty, tborders.los AS los, CONVERT(DATE, tborders.mrd_date) AS mrd FROM [DASH].[dashboard_db].[dbo].[tb_family] AS tbfamily INNER JOIN [DASH].[dashboard_db].[dbo].[tb_started_zero] AS tborders ON tbfamily.[family] = tborders.[family] WHERE tborders.[Order Number] = @OrderNumber', N'@OrderNumber int', @orderNumber = " & x
SET rs = conn.execute(sql)
IF rs.EOF = false THEN
mrd(counter) = rs("mrd") 
family(counter) = rs("family") 
los(counter) = rs("los") 
qty(counter) = rs("qty") 
counter=counter+1
END IF
rs.close
end if
NEXT