EOF and BOF error while querying SQL database

Question

I try to query the SQL database from vbs, but when the record is not found, I get an error

ADODB.Field: either BOF or EOF - True, or the current record has been deleted. The requested operation requires the current record.

I think I need to use the IF NOT statement to capture if the record is not found, but I cannot figure out where she needs to go.

Do Until objFile.AtEndofStream strAppName = objFile.ReadLine ConnString="DRIVER={SQL Server};SERVER=aardvark002;UID=***;PWD=***;DATABASE=DEW_Users" SQL = "USE Win7AppData SELECT " & Chr(34) & strCountry & Chr(34) & " FROM AppsByCountry WHERE Application = '" & strAppName & "'" Set Connection = CreateObject("ADODB.Connection") Set Recordset = CreateObject("ADODB.Recordset") Connection.Open ConnString Recordset.Open SQL,Connection strApproval = Recordset(strCountry) If StrApproval = "YES" Then strApproval = "Approved" Else strApproval = "Denied" End If objExcel.Cells(intRow, 1).Value = strAppname objExcel.Cells(intRow, 2).Value = strCountry objExcel.Cells(intRow, 3).Value = strApproval intRow = intRow + 1 Loop 

Answer 1

The bit is rusty on my VBScript, but you should be able to use .EOF in the recordset to check if it is at the end:

 Recordset.Open SQL,Connection If Recordset.EOF = false Then ' have some rows, do what you want with them End If 

W3Schools Link

Answer 2

You may have another error, if you have more than one record as a result, then you need to move your record pointer, if you do not want to end with an infinite loop, I also cut your code a little on the other hand, you need to close the connection, if You are not going to use it again.

 ConnString="DRIVER={SQL Server};SERVER=aardvark002;UID=***;PWD=***;DATABASE=DEW_Users" Set Connection = CreateObject("ADODB.Connection") Connection.Open ConnString Do Until objFile.AtEndofStream strAppName = objFile.ReadLine SQL = "USE Win7AppData SELECT " & Chr(34) & strCountry & Chr(34) & " FROM AppsByCountry WHERE Application = '" & strAppName & "'" Set Recordset = Connection.Execute(SQL) Do While not Recordset.EOF strApproval = Recordset(strCountry) If StrApproval = "YES" Then strApproval = "Approved" Else strApproval = "Denied" End If objExcel.Cells(intRow, 1).Value = strAppname objExcel.Cells(intRow, 2).Value = strCountry objExcel.Cells(intRow, 3).Value = strApproval intRow = intRow + 1 Recordset.MoveNext End If Loop Connection.Close Set Connection = nothing 

Answer 3

I checked Recodset.EOF and Recordset.BOF to make sure that both of them are False, but every time I received the indicated error. It took me several hours, but I finally realized that if you call Recordset.Fields.count , then EOF and BOF will be changed to True .

Hope this can be helpful.