Excel VBA query recordset value from SQL SP Type Mismatch error

Question

Using SQL Server 2012 \ Excel 2010. Excel calls a SQL SP that will return either a 1 or 2 and I then need to perform something else based on that.

Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.RecordSet
Dim WSP1 As Worksheet

Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set rs = New ADODB.RecordSet

' Log into our SQL Server, and run the Stored Procedure
con.Open "Provider=SQLOLEDB;Data Source=xxxxxxxx;Initial Catalog=xxxxxxx;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con

' Set up the parameter for our Stored Procedure
' (Parameter types can be adVarChar,adDate,adInteger)
cmd.Parameters.Append cmd.CreateParameter("User", adVarChar, adParamInput, 15, Trim(Range("C7").Text))

Application.StatusBar = "Running stored procedure..."

cmd.CommandText = "usp_GL_Code_Access"

Set rs = cmd.Execute(, , adCmdStoredProc)

If rs = 1 Then

MsgBox "true"

Else

MsgBox "false"

rs.Close
Set rs = Nothing
Set cmd = Nothing

con.Close
Set con = Nothing

At the moment I'm just using the msgbox true\false to see the value from the rs\SQL SP, but I keep receiving the error message 'Type Mismatch' and it highlights the

IF rs = 1 Then

Line.

Any ideas?

Answer 1

I think doing it like this would be better.

The function inserts SQL Server data to the target Excel range using ADO.

Function ImportSQLtoRange(ByVal conString As String, ByVal query As String, _
    ByVal target As Range) As Integer

    On Error Resume Next

    ' Object type and CreateObject function are used instead of ADODB.Connection,
    ' ADODB.Command for late binding without reference to
    ' Microsoft ActiveX Data Objects 2.x Library

    ' ADO API Reference
    ' http://msdn.microsoft.com/en-us/library/ms678086(v=VS.85).aspx

    ' Dim con As ADODB.Connection
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")

    con.ConnectionString = conString

    ' Dim cmd As ADODB.Command
    Dim cmd As Object
    Set cmd = CreateObject("ADODB.Command")

    cmd.CommandText = query
    cmd.CommandType = 1         ' adCmdText

    ' The Open method doesn't actually establish a connection to the server
    ' until a Recordset is opened on the Connection object
    con.Open
    cmd.ActiveConnection = con

    ' Dim rst As ADODB.Recordset
    Dim rst As Object
    Set rst = cmd.Execute

    If rst Is Nothing Then
        con.Close
        Set con = Nothing

        ImportSQLtoRange = 1
        Exit Function
    End If

    Dim ws As Worksheet
    Dim col As Integer

    Set ws = target.Worksheet

    ' Column Names
    For col = 0 To rst.Fields.Count - 1
        ws.Cells(target.row, target.Column + col).Value = rst.Fields(col).Name
    Next
    ws.Range(ws.Cells(target.row, target.Column), _
        ws.Cells(target.row, target.Column + rst.Fields.Count)).Font.Bold = True

    ' Data from Recordset
    ws.Cells(target.row + 1, target.Column).CopyFromRecordset rst

    rst.Close
    con.Close

    Set rst = Nothing
    Set cmd = Nothing
    Set con = Nothing

    ImportSQLtoRange = 0

End Function

Code comments:

The query parameter can contain a SELECT or EXECUTE query.
The resulting data will be inserted starting from the top left cell of the target range.
Using Object types and the CreateObject function instead of direct use of ADO types
lets to avoid setting ActiveX Data Objects 2.x Library references on user computers.
This code works in Microsoft Excel 2003-2016.
Always use Set Nothing statements for ADODB.Connection and ADODB.Recordset objects to free resources.

Answer 2

rs is a recordset object, comparing it to 1 is not valid, its not the value returned from the procedure.

If 1 or 2 is a value from a SELECT then to read the single value:

if rs.eof then
   msgbox "no rows"
else
   result = rs.collect(0)
   msgbox result
end if