传递SQL变量ASP
问题描述：
只是有点语法问题在这里。我使用SQL和ASP和有此code迄今：
Just a bit of a syntax problem here. I'm using SQL and ASP and have this code thus far:
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;"
on error resume next
conn.Execute sql,recaffected
if err<>0 then
Response.Write("An Error Has Occured")
else
end if
我将如何通过SQL变量 @testID 来ASP这样就可以在的Response.Write 编写>
How would I pass the SQL variable @testID to ASP so that it could be written within the response.write
例如：
Dim, ASPID
ASPID = @testID
Response.write(ASPID)
（显然这是错误的）
谁能告诉我该怎么做呢？
很抱歉，如果这是对某些人来说有点太简单了：P
(obviously this is wrong) Could anyone tell me how to do this? Sorry if this is a bit too simple for some people :P
答
试试这个;
' What is this for?
'set rs=Server.CreateObject("ADODB.recordset")
'rs.Open "Select * from Questions", conn
Dim cmd, rs, sql, new_id, yourconnstring
sql = ""
sql = sql & "INSERT INTO Questions(QuestionText, QuestionType) VALUES (?, ?);" & vbCrLf
sql = sql & "SELECT SCOPE_IDENTITY();"
Set cmd = Server.CreateObject("ADODB.Command")
With cmd
.ActiveConnection = yourconnstring 'No need for separate connection object
.CommandType = adCmdText
.CommandText = sql
.Parameters.Append(.CreateParameter("@questiontext", adVarWChar, adParamInput, 255)
.Parameters.Append(.CreateParameter("@questiontype", adVarWChar, adParamInput, 255)
Set rs = .Execute(, Array(qtext, "checkbox"))
If Not rs.EOF Then new_id = rs(0)
Call rs.Close()
Set rs = Nothing
End With
Set cmd = Nothing
If Len(new_id) < 1 Then
Call Response.Write("An Error Has Occured")
Else
Call Response.Write("ID is " & new_id)
End If
没测试过，但是这是我倾向于这样做，而不必担心处理撇号和恶意code。
Not tested but this is how I tend to do this without having to worry about handling apostrophes and malicious code.