Passing Parameters to Stored Procedures 1

[Buster49]

I have a problem with the following code; in the debug.print statement the value of rsJuvos.recordcount is always -1. There is nothing wrong with the data; using an alternative method of passing the SQL statement as a string works.

Can anyone spot the problem?

Private Sub GetRecords2(NINumber As String)
Dim cmdJuvos As New ADODB.Command
Dim paramNI As New ADODB.Parameter
Dim rsJuvos As New ADODB.Recordset
Set paramNI = cmdJuvos.CreateParameter
With paramNI
.Type = adChar
.Size = 9
.Direction = adParamInput
.Value = NINumber
End With
With cmdJuvos
.CommandText = "GetJuvos"
.CommandType = adCmdStoredProc
.ActiveConnection = con
.Parameters.Append paramNI
End With
Set rsJuvos = cmdJuvos.Execute
Debug.Print paramNI.Value, rsJuvos.RecordCount
Set cmdJuvos = Nothing
Set paramNI = Nothing
Set rsJuvos = Nothing
End Sub

 

[SQLSister]

IN the debug print is the value of the parameter correct?

Have you run the stored procedure from query analyzer with that parameter value? What result did you get?


Questions about posting. See faq183-874

 

[Buster49]

Yes, in the debug print the value of the parameter is correct and I have run some examples in Query Analyser and it works.

 

[donutman]

Try this:

Set rsJuvos = cmdJuvos.Execute
    rsJuvos.MoveLast
    Debug.Print paramNI.Value, rsJuvos.RecordCount

I can never remember all the situations where/when RecordCount property is not set, but there are several.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Buster49]

Unfortunately that did not work, it produced the error message "Rowset does not support fetching backward

 

[vongrunt]

Argh... ADO. By default result is opened through server-side cursor and recordCount is not available. Try this:

rsJuvos.cursorLocation = adUseClient
rsJuvos.Open cmdJuvos

 

[donutman]

Hmm, sounds like your con object is using an ODBC provider instead of SQLOLEDB and/or you are getting a forward-only recordset. It would be best to post your question in the VB forum. But you might try changing your connection object or using
While not rsJuvos.EOF
rsJuvos.MoveNext
Wend
instead of the rsJuvos.MoveLast (not that you'd want to implement such a kludge).
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Oh...vongrunt has it. Say, what gives with the double underline and green font?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]