SQL与ASP语法 [英] SQL with ASP Syntax

问题描述

我测试了在查询分析器SQL查询，它工作得很好，在这里它是：

  INSERT INTO问题（QuestionText，QuestionType）
 VALUES（'测试，测试）
     DECLARE @testID INT
        SET @testID = @@身份
         INSERT INTO问题（QuestionText）
         VALUES（@testID）
 

（我只是测试了@@ IDENTITY函数）

但是当我尝试和实现它在我的网站（我用在与ASP经典结合SQL）我得到一个错误，可有人告诉我，我做错了什么吗？以下是我已经把在asp：

 集RS =的Server.CreateObject（ADODB.recordset）
         rs打开选择的问题*，康涅狄格州         SQL =INSERT INTO问题（QuestionText，QuestionType）
         SQL = SQL＆安培; 价值观
         SQL = SQL＆安培; （'与＆amp; QTEXT＆放大器;'，
         SQL = SQL＆安培; '与＆amp; 复选框＆放大器; '）
         SQL = SQL＆安培; DECLARE @testID INT
         SQL = SQL＆安培; SET @testID = @@身份
         SQL = SQL＆安培; INSERT INTO问题（QuestionText）
         SQL = SQL＆安培; 价值观
         SQL = SQL＆安培; （@testID）         在错误恢复下一个
         conn.Execute SQL，recaffected
         如果ERR＆所述;大于0则
         的Response.Write（发生错误）
         其他
回复于（新增的数据）
         万一         conn.close
 

解决方案

您必须在您的SQL没有空格，当你连接起来，所以这样的：

  SQL =INSERT INTO问题（QuestionText，QuestionType）
SQL = SQL＆安培; 价值观
SQL = SQL＆安培; （'与＆amp; QTEXT＆放大器;'，
SQL = SQL＆安培; '与＆amp; 复选框＆放大器; '）
SQL = SQL＆安培; DECLARE @testID INT
SQL = SQL＆安培; SET @testID = @@身份
SQL = SQL＆安培; INSERT INTO问题（QuestionText）
SQL = SQL＆安培; 价值观
SQL = SQL＆安培; （@testID）
 

将要产生

 INSERT INTO问题（QuestionText，QuestionType）VALUES（''，''）宣布@testID INTSET @testID = @@ identityINSERT INTO问题（QuestionText）VALUES（@testID）;
 

正如你可以看到你结束了无效的语法在这里：

 定义@testID INTSET @testID = @@ identityINSERT
 

一个快速解决将是添加空格和/或<一个href=\"http://sqlblog.com/blogs/aaron_bertrand/archive/2009/09/03/ladies-and-gentlemen-start-your-semi-colons.aspx\"相对=nofollow>用分号正常终止语句：

  SQL =INSERT INTO问题（QuestionText，QuestionType）
SQL = SQL＆安培; 价值观
SQL = SQL＆安培; （'与＆amp; QTEXT＆放大器;'，
SQL = SQL＆安培; '与＆amp; 复选框＆放大器; '）;
SQL = SQL＆安培; DECLARE @testID INT;
SQL = SQL＆安培; SET @testID = @@身份;
SQL = SQL＆安培; INSERT INTO问题（QuestionText）
SQL = SQL＆安培; 价值观
SQL = SQL＆安培; （@testID）;
 

更妙的是使用参数化查询的为好，当前的code不是类型安全，容易受到SQL注入，而不能采取查询计划缓存的优势。

最后 @@ IDENTITY 几乎从不正确的函数使用，您应该使用的 SCOPE_IDENTITY（）本 - 更多信息，请参阅此答案

---

修改

我知道这只是用于测试的原型类型的查询，但你可以改变你的查询到一个单一的声明如下：

  INSERT INTO问题（QuestionText，QuestionType）
输出inserted.QuestionText INTO问题（QuestionText）
输出inserted.QuestionText
VALUES（？，？）;
 

第一个输出语句将插入你再次插入到表中的QuestionText价值，第二个输出将这个值返回给ASP。

I'm testing out a sql query on a query analyzer and it works just fine, here it is:

INSERT INTO Questions(QuestionText, QuestionType)
 VALUES('test','test')
     DECLARE @testID int
        SET @testID = @@identity
         INSERT INTO Questions(QuestionText)
         VALUES (@testID)

(I'm just testing out the @@identity function)

However as soon as I try and implement it on my site (i'm using SQL in conjunction with asp Classic) I get an error, can someone tell me what i'm doing wrong please? Here is what I have put in the asp:

 set rs=Server.CreateObject("ADODB.recordset")
         rs.Open "Select * from Questions", conn

         sql="INSERT INTO Questions(QuestionText, QuestionType)"
         sql=sql & " VALUES "
         sql=sql & "('" & qtext & "',"
         sql=sql & "'" & "checkbox" & "')" 
         sql=sql & "DECLARE @testID int"
         sql=sql & "SET @testID = @@identity"
         sql=sql & "INSERT INTO Questions(QuestionText)"
         sql=sql & " VALUES "
         sql=sql & "(@testID)"

         on error resume next
         conn.Execute sql,recaffected
         if err<>0 then
         Response.Write("An Error Has Occured")
         else
Response.write("Data Added")
         end if

         conn.close

解决方案

You have no spaces in your SQL when you are concatenating it, so this:

sql="INSERT INTO Questions(QuestionText, QuestionType)"
sql=sql & " VALUES "
sql=sql & "('" & qtext & "',"
sql=sql & "'" & "checkbox" & "')" 
sql=sql & "DECLARE @testID int"
sql=sql & "SET @testID = @@identity"
sql=sql & "INSERT INTO Questions(QuestionText)"
sql=sql & " VALUES "
sql=sql & "(@testID)"

Is going to yield

"INSERT INTO Questions(QuestionText, QuestionType) VALUES ('', '')DECLARE @testID intSET @testID = @@identityINSERT INTO Questions(QuestionText) VALUES (@testID);

As you can see you end up with invalid syntax here:

DECLARE @testID intSET @testID = @@identityINSERT

A quick fix would be to add spaces and/or terminate your statements properly with a semi-colon:

sql="INSERT INTO Questions(QuestionText, QuestionType)"
sql=sql & " VALUES "
sql=sql & "('" & qtext & "',"
sql=sql & "'" & "checkbox" & "');" 
sql=sql & "DECLARE @testID int;"
sql=sql & "SET @testID = @@identity;"
sql=sql & "INSERT INTO Questions(QuestionText)"
sql=sql & " VALUES "
sql=sql & "(@testID);"

Better still would be to use parameterised queries as well, your current code is not type safe, vulnerable to sql injection, and cannot take advantage of query plan caching.

Finally @@IDENTITY is almost never the correct function to use, you should be using SCOPE_IDENTITY() for this - See this answer for more info

---

EDIT

I know this is only a proto type query for testing, but you could transform your query into a single statement as follows:

INSERT INTO Questions(QuestionText, QuestionType)
OUTPUT inserted.QuestionText INTO Questions (QuestionText)
OUTPUT inserted.QuestionText
VALUES (?, ?);

The first output statement will insert the value of QuestionText you are inserting into the table again, the second output will return this value to ASP.

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