CODEWITHSUNDEEP
sql-serverasp-classicadoadodb
user1145348
user1145348

Reputation: 31

SQL Server Returns Empty Recordset ADODB.Connection

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!

Upvotes: 3

Views: 9338

Answers (3)

Robert
Robert

Reputation: 3074

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.

Upvotes: 0

shahkalpesh
shahkalpesh

Reputation: 33476

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.

Upvotes: 1

MicBehrens
MicBehrens

Reputation: 1798

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

Upvotes: 0

Related Questions