CODEWITHSUNDEEP
sqlsyntaxvbscriptado
howdybaby
howdybaby

Reputation: 307

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)

Upvotes: 1

Views: 488

Answers (2)

GolezTrol
GolezTrol

Reputation: 116100

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).

Upvotes: 2

Rob
Rob

Reputation: 3381

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;"

Upvotes: 1

Related Questions