Data Types in CreateParameter statement causing type mismatch

Data Types in CreateParameter statement causing type mismatch

Post by Michael Hop » Fri, 25 Jan 2002 11:31:34



I'm writing a function that will return a recordset from a stored procedure.
The function takes three arguments, database name, procedure name, and a
string array of the parameter values.  I'm using the openschema for
retreiving the parameter data_type.  All is fine except when the data type
is varchar (data type = 129) I get the type mismatch. This is the Procedure
code:

Public Function fncRetStoredProcedure(sDb As String, sProcName As String,
sValues() As String) As Object

    Dim i As Integer
    Dim prmInput As ADODB.Parameter
    Dim oCommand As New ADODB.Command
    Dim sInputParam As String

    On Error GoTo fncRetStoredProcedure_Error

    Call fncCloseRecordset

    oConx.Properties(0) = sDb
    oCommand.ActiveConnection = oConx
    oCommand.CommandText = sProcName
    oCommand.CommandType = adCmdStoredProc

    If sValues(0) <> -1 Then
        Set oRS = New ADODB.Recordset
        Set oRS = oConx.OpenSchema(adSchemaProcedureParameters, Array(sDb,
Empty, sProcName, Empty))
        If oRS!PARAMETER_NAME = "RETURN_VALUE" Then oRS.MoveNext
        For i = 0 To UBound(sValues) - 1
            sInputParam = "Param" & Trim(Str(i))
            Select Case oRS!DATA_TYPE
                Case adDBTimeStamp
                    Set prmInput = oCommand.CreateParameter(sInputParam,
oRS!DATA_TYPE, oRS!PARAMETER_TYPE, CVDate(sValues(i)))
                Case adChar '(VarChar is same)
                    Set prmInput = oCommand.CreateParameter(sInputParam,
oRS!DATA_TYPE, oRS!PARAMETER_TYPE, , sValues(i))
                Case Else
                    Set prmInput = oCommand.CreateParameter(sInputParam,
oRS!DATA_TYPE, oRS!PARAMETER_TYPE, sValues(i))
            End Select
            oCommand.Parameters.Append prmInput
            oRS.MoveNext
        Next i
    End If

    Set fncRetStoredProcedure = oCommand.Execute

Exit Function
fncRetStoredProcedure_Error:
    Err.Raise vbObjectError, , "Procedure - fncRetStoredProcedure
(clsDbData)"

End Function

Mike Hope

 
 
 

Data Types in CreateParameter statement causing type mismatch

Post by Greg Obleshchu » Fri, 25 Jan 2002 14:00:53


Micheal,
    Hi , there is another way here.  Seeing how you are doing an OpenSchema
why not let the ADO populate the parameters collection itself.
Public Function fncRetStoredProcedure(sDb As String, sProcName As String,
sValues() As String) As Object

    Dim i As Integer
    Dim prmInput As ADODB.Parameter
    Dim oCommand As New ADODB.Command
    Dim sInputParam As String

    On Error GoTo fncRetStoredProcedure_Error

    Call fncCloseRecordset

    oConx.Properties(0) = sDb
    oCommand.CommandText = sProcName
    oCommand.CommandType = adCmdStoredProc
    oCommand.ActiveConnection = oConx
'    loop throguht parameter collection
     oCommand.execute
end function

If you set the commandtext and commandtype BEFORE you assign the
Activeconnection ADO will build the parameter collection for you.  This is
done by querying the SQL server and retriveing the parameter(in the same way
you do it or there abouts).

--
I hope this helps
regards
Greg O MCSD
Document any SQL server database
AGS SQL Scribe http://www.ag-software.com/ags/ags_scribe_index.asp


> I'm writing a function that will return a recordset from a stored
procedure.
> The function takes three arguments, database name, procedure name, and a
> string array of the parameter values.  I'm using the openschema for
> retreiving the parameter data_type.  All is fine except when the data type
> is varchar (data type = 129) I get the type mismatch. This is the
Procedure
> code:

> Public Function fncRetStoredProcedure(sDb As String, sProcName As String,
> sValues() As String) As Object

>     Dim i As Integer
>     Dim prmInput As ADODB.Parameter
>     Dim oCommand As New ADODB.Command
>     Dim sInputParam As String

>     On Error GoTo fncRetStoredProcedure_Error

>     Call fncCloseRecordset

>     oConx.Properties(0) = sDb
>     oCommand.ActiveConnection = oConx
>     oCommand.CommandText = sProcName
>     oCommand.CommandType = adCmdStoredProc

>     If sValues(0) <> -1 Then
>         Set oRS = New ADODB.Recordset
>         Set oRS = oConx.OpenSchema(adSchemaProcedureParameters, Array(sDb,
> Empty, sProcName, Empty))
>         If oRS!PARAMETER_NAME = "RETURN_VALUE" Then oRS.MoveNext
>         For i = 0 To UBound(sValues) - 1
>             sInputParam = "Param" & Trim(Str(i))
>             Select Case oRS!DATA_TYPE
>                 Case adDBTimeStamp
>                     Set prmInput = oCommand.CreateParameter(sInputParam,
> oRS!DATA_TYPE, oRS!PARAMETER_TYPE, CVDate(sValues(i)))
>                 Case adChar '(VarChar is same)
>                     Set prmInput = oCommand.CreateParameter(sInputParam,
> oRS!DATA_TYPE, oRS!PARAMETER_TYPE, , sValues(i))
>                 Case Else
>                     Set prmInput = oCommand.CreateParameter(sInputParam,
> oRS!DATA_TYPE, oRS!PARAMETER_TYPE, sValues(i))
>             End Select
>             oCommand.Parameters.Append prmInput
>             oRS.MoveNext
>         Next i
>     End If

>     Set fncRetStoredProcedure = oCommand.Execute

> Exit Function
> fncRetStoredProcedure_Error:
>     Err.Raise vbObjectError, , "Procedure - fncRetStoredProcedure
> (clsDbData)"

> End Function

> Mike Hope