Need Urgent Help

[M.Waqas Aslam]

hello!
i have shifted my application from mssql server 2000 to mssql server 2005 , now the prob is the command object is not returning the recordset if command text is stored procedure , here is my code please check it and guide me through errors .

dim cmdsp as command
dim rs as new ADODB.recordset
set cmdsp = new commadn 
cmdsp.activeconnection = cn
cmdsp.commandtext = "MyStoredProcedure"
cmdsp.commandtype = adcmdstoredproc


set rs= cmdsp.execute 'here i got an error the state of rs is zero , but if i used this code at mssql server 2000 then it will return the records.

please help me in this , and if you have some better code then please also post it here, i have even tried this also

rs.open "exec myprocedurename",myCon,adOpenStatic,AdLockReadOnly

but this code is also not working at my end ,please check it out as i need help very urgently ,

Best Regards

M.Waqas Aslam

[M.Waqas Aslam]

as earlier i face the same prob with mssql server 2000 due to my connection string , so please also note this , i am using this connection string

Provider=SQLNCLI;Server=myServerAddress;Database=myDataBase;Trusted_Connection=yes;

Regards

[BitBlt]

If you copied the code verbatim from your source, then you have a typo on line 3...you have "commadn" instead of "command". Just pointing that out...probably not the actual issue.

That being said, I don't see anything else gigantically wrong with your code snippet. It appears out of context though...I don't see a declaration or open statement for your "cn" variable. Is it possible the scope of that variable is incorrect?

Then also, it could be that the stored proc is simply returning no records, or your SQL Server security context is incorrect, or you have to explicitly set the Schema name as part of your StoredProc name. Last, it might be that SQL Server is returning some other error message that you aren't trapping.

Anyway, here's a snippet of code that gives you all the important moving parts. See if there is anything in here that you AREN'T doing somewhere, and fix as appropriate.

Private Sub MySub()
On Error GoTo MySubError                   ' Specifically for handling error conditions
Dim i As Integer                           ' just an interator variable
Dim cn As ADODB.Connection                 ' defining the connection object
Dim cmdsp As Command                       ' You could also use ADODB.Command
Dim rs As New ADODB.Recordset

Set cn = New ADODB.Connection              ' Establish the connection
                                           ' Set the connection string and open the connection.
cn.ConnectionString = "Provider=SQLNCLI;Server=MyServerAddress;Database=MyDatabase;Trusted_Connection=yes;"
cn.Open
                                           '  (you will of course need to change to the appropriate server and database names.)

Set cmdsp = New Command                    ' Fixed your typo  ;-)
cmdsp.ActiveConnection = cn                ' Associate the command with the connection
cmdsp.CommandText = "dbo.MyStoredProc"     ' Notice the schema name is explicitly included
cmdsp.CommandType = adCmdStoredProc        '
Set rs = cmdsp.Execute                     ' Execute the command

If Not (rs.EOF) Then                       ' Check to see if you have any rows to look at
    rs.MoveFirst                           ' Go to the beginning...
    Do Until rs.EOF                        ' Loop through the rows
        For i = 0 To rs.Fields.Count - 1   ' Loop through the returned columns and show the names and values
            Debug.Print rs.Fields(i).Name & ": " & rs.Fields(i).Value
        Next i
        rs.MoveNext                        ' go to the next row
    Loop
End If

MySubResume:
If rs.State <> adStateClosed Then          ' Check the recordset state; close it if it isn't already closed
    rs.Close
End If
If cn.State <> adStateClosed Then          ' Check the connection state; close it if it isn't already closed
    cn.Close
End If
Set rs = Nothing                           ' Clean up memory
Set cn = Nothing
Exit Sub                                   ' Get out!

MySubError:                                ' Error handling...
If cn.Errors.Count > 0 Then                ' First, check to see if there are any SQL errors returned by your connection
    For i = 0 To cn.Errors.Count - 1       ' and loop through them to display them all...there might be more than one!
        Debug.Print cn.Errors(i).Number & ":" & cn.Errors(i).Description
    Next i
Else
    If Err.Number <> 0 Then                ' Next, see if there are any other non-SQL errors to report
        MsgBox "Some other error: " & Err.Description, vbOKOnly + vbCritical, "Error" & Err.Number
    End If
End If

Resume MySubResume                         ' Now resume so you can close objects and release memory

End Sub

This routine was tested against a SQL2005 instance (with the "live" names altered for publication here), so I know it works.

Hope this helps! Good luck!