PDA

View Full Version : SQL Loop Error '3021'



Kindly_Kaela
02-08-2007, 02:32 PM
Hi everyone!

The below is the code I'm using to draw data from SQL. Problem I'm running into is, if there is no data that fits my sorting criteria, I get an error message.

Run-Time Error '3021'
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.


Public Sub SQL_Loop()

Dim cn As New Connection
Dim rs As Recordset
Dim BucketName As String
Dim rngExList As Range

BucketName = "ProductA"

Set rs = cn.Execute("Select * from XISOC_Leads where PracticeGroup = '" + BucketName + "' order by CreatedOn")

rs.MoveFirst
Do While rs.EOF = False

r = r + 1
rngExList.Cells(r, 1).Value = rs("AE")
rngExList.Cells(r, 2).Value = rs("CreatedOn")
rs.MoveNext

Loop

End Sub


In this example, it wouldn't find any ProductA and produce an error.

Any suggestions so I can avoid the error message?

Thanks!
Kaela
:cloud9:

mdmackillop
02-08-2007, 02:53 PM
Have a look at Johnske's article (http://vbaexpress.com/forum/showthread.php?t=9782). If you still have problems, let us know

Kindly_Kaela
02-08-2007, 02:56 PM
Yea, I've been using an Error Handler, but I was hoping for another solution.

Oh well, thanks!

CBrine
02-08-2007, 02:56 PM
Change this

Do While rs.EOF = False

to

Do Until rs.EOF


I believe it's just your loop trying to read a record after you reach the end.

HTH
Cal

PS- Just noticed that you never actually assign your connection string to the object "cn"

Kindly_Kaela
02-08-2007, 03:11 PM
Same error message. Thanks for tying!


Do Until rs.EOF

mdmackillop
02-08-2007, 03:17 PM
How about
On Error Resume Next
Do While rs.EOF = False
If Err.Number = 3021 Then GoTo ErrHandler

Bob Phillips
02-08-2007, 03:20 PM
I haven't tested this, but you need to check BOF and EOF.

In addition to what Cal said about the connection string, I have change the declaration of cn as Dim as a New object is not good practice, you should always set it.



Public Sub SQL_Loop()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim BucketName As String
Dim rngExList As Range

Set cn = New ADODB.Connection

BucketName = "ProductA"

Set rs = cn.Execute("Select * from XISOC_Leads where PracticeGroup = '" + BucketName + "' order by CreatedOn")

With rs

If Not (.BOF And .EOF) Then
.MoveFirst

Do While .EOF = False

r = r + 1
rngExList.Cells(r, 1).Value = rs("AE")
rngExList.Cells(r, 2).Value = rs("CreatedOn")
.MoveNext

Loop

Else
MsgBox "No records returned."
End If

End With

End Sub

CBrine
02-08-2007, 08:10 PM
Kaela,
You are going to need to hunt down the connection string for the data source you are using. It's most likely going to need an IP address, and a user/password combo. I'm putting a example of how I do my ADO connections so you get an idea of sequence and what order things need to be done. I've used late binding to make it more portable, but you can also reference the Active Data Object library to get access to the intellisense options.


Private Sub CommandButton1_Click()
Dim oConn As Object
Dim ConnString As String
Dim ssql As String
Dim rsRecordset As Object
'Create object
Set oConn = New Connection
'Create connection string
ConnString = "Connection String" 'This is where you put your connection string
'Open the connection
oConn.Open ConnString, , 1
'Check State of connection to confirm it's open
If oConn.State = False Then
MsgBox "Connection Failed"
End
End If
'Add your ssql query
ssql = "Your SQL query"
'Execute the query
Set rsRecordset = oConn.Execute(ssql)
'Cycle through your recordset
Do Until rs.EOF
MsgBox rsRecordset(0)
rsRecordset.MoveNext
Loop
End Sub



HTH
Cal

Kindly_Kaela
02-09-2007, 11:04 AM
MD, this seems to be working (for now). My error handler was working until all of a sudden this morning. I tried your comman (pasted below) and so far so good :)

On Error Resume Next
Do While rs.EOF = False
If Err.Number = 3021 Then Goto ErrHandler

Sorry for the confusion, the connection string is working fine. I just didn't want to paste my company's login information. Here it is....


cn.Open ("Provider=blah;" & _
"Driver={SQL Server};" & _
"Server=blah;" & _
"Database=blah_MSCRM;" & _
"Uid=blah;" & _
"Pwd=blah")


Thanks everyone, I'm going to leave this unsolved for now....just in case it cracks again.

Kaela