sql query not working when used with ADO

I'm trying to learn how to use ADO to manipulate databases so I am aware this is a very basic question but I still need help. I ran a query in a sql query analyzer and it executed just fine:

SELECT TOP 1 QuestionType FROM Questions
ORDER BY QuestionID DESC;

However I tried to put it in my ADO script and I got an error, the query wont complete. Can someone tell me why? Again, sorry if this is just a simple syntax error. Here is this script that is giving an error:

  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 & "'" & "test" & "');" 
  sql=sql & "SELECT TOP 1 QuestionID FROM Questions"
  sql=sql & "ORDER BY QuestionID DESC;"


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

(The script is just trying to retrieve the most recent ID after inserting some test values)

Answers


Run the insert and the select as two separate commands. Or, if your database supports it, run a statement that returns the new ID immediately (like insert with returning into).


You need a space before ORDER (and one after the ;):

sql="INSERT INTO Questions(QuestionText, QuestionType)"
sql=sql & " VALUES "
sql=sql & "('" & qtext & "',"
sql=sql & "'" & "test" & "'); " 
sql=sql & "SELECT TOP 1 QuestionID FROM Questions"
sql=sql & " ORDER BY QuestionID DESC;"

Need Your Help

How to use a gradient fill (GradientFill) with ClosedXML

c# openxml-sdk closedxml

I'm working on a C#/ASP.NET web thing and want to do an Excel export. First I found OpenXML, made some first steps, but it's really hard to use. Now, I'm using ClosedXML and it's quite a relief - s...

Determine if location is within a certain region in Google Map

.net google-maps google-maps-api-3 ionic-framework

In the Google Map shown above, there are 2 markers pointed by green arrows. They are the centers of 2 different regions indicated by blue circles in dotted lines. The marker pointed by an orange ar...