SQL Server Returns Empty Recordset ADODB.Connection

3
votes

I'm running Classic ASP on IIS and have a script to create a connection to a SQL Server database using the following code:

Dim adoConn As Object
adoConn = Server.CreateObject("ADODB.Connection")
adoConn.Open ("Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Engineering_Test; User ID=*********; Password=*******")

I then run SQL select queries off of this connection as follows:

Dim rs As Object
rs = Server.CreateObject("ADODB.Recordset")

SQL = "SELECT [User ID] FROM [Users] WHERE [Username]='" & username & "' AND [Password]='" & password & "'"
rs.Open(SQL, adoConn, 3, 3)

All was working fine, but then I discovered I had lots of sleeping processes on the server and it had began refusing connections because I presumably had maxed out all of the allowed connections to the server. In reality, what I saw on screen was my SQL queries returning empty recordsets when in fact they should have contained multiple rows.

So I killed all of the sleeping processes on the server, restarted SQL Server and IIS, however, the SQL queries I am executing are still returning empty recordsets, no errors are displayed and everything compiles as expected.

When I login to the server using remote desktop and execute the exact same queries in SQL Server Management Studio (accessed via the same user credentials) the queries return complete recordsets.

Is there anything else I can do/check to resolved this issue? It is truly baffling me!

3
sql-serverasp-classicadoadodb
Regarding your empty recordset issue does prefixing your statements with SET NOCOUNT ON; help? – Martin Smith
No, unfortunately not, it still returns an empty recordset and now the value of the rs.RecordCount is equal to -1 instead of 0 – user1145348
check that if you are using SQLOLEDB driver you have installed correspond with a SQL server 2000 installed in your server, if not then use the adequate native client driver. In some cases i have reported odd behaivor when not using the correct driver – Rafael
You should also look to change your query to a parameterised version as your current system is wide open for a SQL injection attack. Queue reference to XKCD xkcd.com/327 – Kevin Ross

3 Answers

1
votes

Try adding this line to the code, before opening the recordset

rs.CursorLocation = adUseClient

OR

rs.CursorLocation = 3

Post that, use rs.RecordCount to check for the number of records returned.

0
votes

Try doing this instead:

dim adoConn, dsn
set adoConn = server.createobject("adodb.connection")
dsn = "Provider=SQLOLEDB; Data Source=localhost; Initial Catalog=Engineering_Test; User ID=*********; Password=*******"
conn.connectiontimeout = 300
conn.commandtimeout = 300
response.buffer = true

if conn.state = 0 then
  conn.open dsn
end if

sql = "SELECT [User ID] FROM [Users] WHERE [Username]='" & username & "' AND [Password]='" & password & "'"
set rs = conn.execute(sql)

if rs.eof then
  response.write("No user matches the criteries.")
else
  id = rs("User ID")

  response.write("The user's ID: " & id)
end if

response.buffer will help you if you need to extract a lot of rows from the database later on, here you can use response.flush to write the buffer to the user

0
votes

Make sure your closing your connection after using it, I don't see the close connection in your code or any reference to it. If your only hitting the database once a day or so you'll be fine but if your hitting the database constantly then your exceeding your connection limit. If your not seeing errors that's kind of weird but you could be suppressing them somewhere.

Dim rs As Object
rs = Server.CreateObject("ADODB.Recordset")

SQL = "SELECT [User ID] FROM [Users] WHERE [Username]='" & username & "' AND [Password]='" & password & "'"
rs.Open(SQL, adoConn, 3, 3)

...  DO SOMETHING ...

rs.Close '<-- Close the current connection when done using it.