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&安培; ('与& QTEXT&放大器;',
SQL = SQL&安培; '与& 复选框&放大器; ')
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&安培; ('与& QTEXT&放大器;',
SQL = SQL&安培; '与& 复选框&放大器; ')
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屋!