SQL with ASP Syntax

Question

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

Answer 1

Here are 2 simple and ready to use functions in classic ASP to execute SQL stuff

'---------------------------------------------------------------------------------------
' Generates Recordset from SQL String and either Connection String or Connection Object 
' (works with both), 3rd paramter bForUpdate (true/false) specifies if the returned 
' Recordset is read-only or allows updates
'
' Returns ADODB Recordset as Result
' Example Usage:
'
'   Dim conn : Set conn = Server.CreateObject("ADODB.Connection)
'   conn.open "YOUR CONNECTION STRING"
'   Dim rs, strSQL
'   strSQL = "Select * From Table;"
'   Set rs = GenerateRecordSet(strSQL, conn)
'   If not rs.EOF Then
'    ...
'   End If
'---------------------------------------------------------------------------------------
Function GenerateRecordSet(byVal sqlstring, byVal connDSN, byVal bForUpdate) 'as ADODB.Recordset
' Create a Recordset based on SQL Statement
    Dim oRecSet
    Set oRecSet = Server.CreateObject("ADODB.Recordset")
    oRecSet.CursorLocation = 3
    If bForUpdate Then
       oRecSet.open sqlstring, connDSN, 3,2
    Else
       oRecSet.open sqlstring, connDSN
    End If   
    Set GenerateRecordSet = oRecSet
End Function

'---------------------------------------------------------------------------------------
' Execute a Stored Procedure or sql statement without return values
' Requires sql string to execute and either DSN connection string to database or 
' ADODB.Connection object
' Returns Nothing*
' (using Function instead of Sub that programmers can use FN(param) without
' getting any errors)
'---------------------------------------------------------------------------------------
Function ExecSP(byVal sqlstring, byVal connDSN) 'nothing
    Dim bCloseConn : bCloseConn = False
    If Not isObject(connDSN) Then
       Dim connStr : connStr = connDSN
       bCloseConn = True
       Set connDSN = Server.CreateObject("ADODB.Connection")
       connDSN.ConnectionTimeout = 10000
       connDSN.CommandTimeout = 10000
       connDSN.Open connStr 
    End If     
    connDSN.Execute "SET DEADLOCK_PRIORITY LOW" 'Optional, you can comment it out, if not needed
    connDSN.Execute sqlstring
    If bCloseConn Then
       connDSN.close
       Set connDSN = Nothing
    End If 
End Function

Answer 2

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.