ADO Error

[comart]

I am creating my recordset using the execute method of the command object. When I try to print the fields from the recordset, I get an error message stating that I'm either at EOF or BOF. What am I doing wrong?

'recordset and connection variables
Dim cmd As Command
Dim rs As Recordset
Dim Cnn As Connection
Dim strCnn As String
'record variables
Dim strPrompt As String
Dim strName As String
Dim strParameter As String
Dim prm As Parameter


Set Cnn = New Connection
Set cmd = New Command


strPrompt = "Enter an author's name (e.g., Cox,Joyce): "
strName = Trim(InputBox(strPrompt, "ActiveCommandX Example"))
strCnn = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\Biblio.mdb"



'create SQL command string
cmd.CommandText = "SELECT * FROM [All Titles] WHERE Author = ?"
Set prm = cmd.CreateParameter("AuthorName", adChar, adParamInput, 50, strName)
cmd.Parameters.Append prm


Cnn.Open strCnn
Set cmd.ActiveConnection = Cnn
'create the recordset by executing command string
Set rs = cmd.Execute


Debug.Print "Author = '" & rs!Author & "' Title= '" & rs!Title
' clean up
rs.Close
Cnn.Close
Set rs = Nothing
Set Cnn = Nothing

[James Longstreet]

Your query returned 0 rows.
you should always add a test into your code for EOF && BOF seeing if you don't you will get this error if you try to navigate through the recordset.

Test your query in the database before executing it in code and make sure that it is executing properly...what database is this off?



Jim Hewitt
Software Developer
Liberty Tax Service
www.LibertyTax.com

[aknudsen]

The first response was a good point.
Also realize that if you have a NULL in a database field, you won't be able to concatenate it onto your display string since VB doesn't count NULL as vbNullString. I tend to do this:

dim strOutput as string

strOutput = iif(isnull(rs.fields("column_name").value),vbNullstring,rs.fields("column_name").value)