Troubleshooting with MS SQL stored procedures

[h0pal0ng42]

hi, have a procedure that needed amending and thus required another parameter. My inital VBa code to run this procedure was:

Dim rs As ADODB.Recordset
GetData rs, "sp_TM_GetCurrentRoleTasks", Me.cboRole
Set Me.cboTask.Recordset = rs
 
the middle line changed to: 
GetData rs, "sp_TM_GetCurrentRoleTasks ", CStr(Me.cboRole) & ", " & CStr(Me.txtUserID)
 
the Get Data function which had been working fine is:
Public Sub GetData(ByRef rsIn As ADODB.Recordset, strProc As String, Optional strArgs As String)
Dim cn As ADODB.Connection
 
        Set cn = New ADODB.Connection
        cn.ConnectionString = "Provider=sqloledb;Data Source=IUDATA.hscni.net;Initial Catalog=USERS;Trusted_Connection=Yes"
        cn.Open
 
        Set rsIn = New ADODB.Recordset
        With rsIn
             Set .ActiveConnection = cn
            .ActiveConnection.CommandTimeout = 60
            .Source = strProc & " " & strArgs
            .CursorType = adOpenDynamic
            .LockType = adLockPessimistic
            .CursorLocation = adUseClient
            .Open
        End With
End Sub

yet now I get an error 7965:The object you entered is not a valid recordset property on the 3 rd line:
Set Me.cboTask.Recordset = rs

The procedure runs fine in an SQL query window with the source so I don't understand why I'm getting the error.

UPdate: on further investigation, the problem is not with the parameters as I initially thought - but seems to be with the stored procedure returning rowcount messages - even with using NOCOUNT.

final update: the procedure has (suddenly) started working with NOCOUNT behaving as expected. I'm not sure what I've changed so it may be of little help to others. Thanks for trying.

[jimatqsi]

h0pal0ng42,
Welcome to Bytes.

What version of Access are you using? I'm not familiar with the Recordset property of a combo box. I normally set the Rowsource property equal to the SQL code string.

Jim

[zmbd]

So let's treat this as I would any other MS built in function by using named parameters:

Public Sub GetData(ByRef rsIn As ADODB.Recordset, strProc As String, Optional strArgs As String) 

So our origninal would be:
GetData _
   rsIn := rs, _
   strProc := "sp_TM_GetCurrentRoleTasks", _
   strArgs := Me.cboRole 

Now you have:
GetData _
   rsIn := rs, _
   strProc := "sp_TM_GetCurrentRoleTasks", _
   strArgs := CStr(Me.cboRole) & ", " & CStr(Me.txtUserID)

Now I find that most errors occur when the string is constructed within the function; thus change this:

Now you have:
Dim zstrArgs
 
zstrArgs = CStr(Me.cboRole) & ", " & CStr(Me.txtUserID)
 
GetData _
   rsIn := rs, _
   strProc := "sp_TM_GetCurrentRoleTasks", _
   strArgs := zstrArgs
 

Add a debug.print after the zstrArgs and you will be able to tell if the string is being resolved correctly.

The next item is to address, where are your quotes for the string values? I would guess that Me.cboRole was returning a numeric value, now you're attempting to use a string value.

Then finally, did you do a debug.compile, I'll bet that chokes. Follow the basic troubleshooting steps as outlined:[*]> Before Posting (VBA or SQL) Code

[zmbd]

jimatqsi:
The recordset property has been around for some time now.
I haven't used it much; however, you can actually use it like we do for recordsetclone on a form to do a quick search within the control.
It will also populate the control on an open recordset; however, I've found that you have to leave the recordset open or the values disappear whereas with the rowsource, the values are queried once and stay put.

[jimatqsi]

Thanks for the tip, z, that's interesting to know. I should have passed this one by as soon as I saw ADO in use. I stick to DAO whenever possible.

Jim

[zmbd]

you can use this with dao as well it's part of the cbo object model. (^_^)