ADO - parameter query

[CData]

Greetings, need to reach out for some help - as I've hit the wall on this.

-----------------this Public function works - when I don't pass Parameters & not using a store procedure- and wasn't written by me
so I think it will work with Parameters but I can't seem to pass them correctly ----

Public Function ExecuteSQL(sqlString As String, ParamArray Params() As Variant) As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim cn As New ADODB.Connection
Dim inputParam As Variant
Set cn = GetConnection() ' GetConnection code is in another module
cmd.ActiveConnection = cn
cmd.CommandText = sqlString
For Each inputParam In Params
Set inputParam = cmd.CreateParameter(, GetParameterType(inputParam), adParamInput, Len(Nz(inputParam, " ")), inputParam)
cmd.Parameters.Append inputParam
Next inputParam
cmd.CommandType = adCmdText
Set ExecuteSQL = cmd.Execute()

Exit Function '---------------------------------------------------------------------------------------------------------------


At the form object am trying to pass/call:
dim sqlString as String
dim Params as Variant

Here's one way I've tried:
Call ExecuteSQL(sqlString, "@ProdPre", adVarChar, adParamInput, 200, "Y") ERROR "ODBC: EXPECTS PARAMETER @ProdPre value which was not supplied"

Another way I've tried:
Call ExecuteSQL(sqlString, Params("@ProdPre", adVarChar, adParamInput, 200, "Y")) TYPE MISMATCH

sqlString = "SomeDBname.dbo.someProcedureName"

This is what works when the code is all at the local form object:------------------------------------------------------------------------

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm1 As ADODB.parameter
Set cn = New ADODB.Connection
cn.ConnectionString = "ODBC;Description=underwriting;DRIVER=SQL Server;SERVER=XYSQL01HQ;UID=marks;Trusted_Connection=Yes;Network=DBMSSXYZ"
cn.Open

Set cmd = New ADODB.Command

With cmd
.ActiveConnection = cn

.CommandText = "SomeDBname.dbo.someProcedureName"

Set prm1 = cmd.CreateParameter("@ProdPre", adVarChar, adParamInput, 200)
cmd.Parameters.Append prm1
prm1.Value = "Y"
End With

Set rs = New ADODB.Recordset
With rs
.. this part of the code removed for brevity...
...
End Sub -------------------------------------------------------------------------------------------------------

so wanting to use a global module call rather than code at each form module - but I just can't seem to get the parameters passed....

[HansV]

Do the examples in sp_executesql (Transact-SQL) help?

[CData]

not so much - at lease at the point I am at the moment.... possibly later..

right now I summarize my confusion point to be this:
Public Function ExecuteSQL(sqlString As String, ParamArray Params() As Variant) As ADODB.Recordset

I can't seem to pass the ParamArray correctly.

Because ParamArray is optional, when I only pass sqlString everything works.

ParamArray has those 5 values of my original post....Every variation I set up in the form object to pass them to the statement above gets one of the two error messages shown in CAPS....

The assumption at the moment is that the public function will work correctly if it receives the parameters correctly from the form object...am going to sanity check that assumption this morning....

[HansV]

Use this:

Code: Select all

Public Function ExecuteSQL(sqlString As String, ParamArray Params() As Variant) As ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim cn As New ADODB.Connection
    Dim inputParam As ADODB.Parameter
    Dim i As Long
    If (UBound(Params) + 5) Mod 5 <> 4 Then
        MsgBox "We need 5 arguments per parameter"
        Exit Function
    End If
    Set cn = GetConnection
    cmd.ActiveConnection = cn
    cmd.CommandText = sqlString
    For i = 0 To UBound(Params) Step 5
        Set inputParam = cmd.CreateParameter(Params(i + 0), Params(i + 1), Params(i + 2), Params(i + 3), Params(i + 4))
        cmd.Parameters.Append inputParam
    Next i
    cmd.CommandType = adCmdText
    Set ExecuteSQL = cmd.Execute()
End Function

and call it like this:

Code: Select all

    Dim rs As ADODB.Recordset
    Set rs = ExecuteSQL(sqlString, "@ProdPre", adVarChar, adParamInput, 200, "Y")

[CData]

so after going thru a ton of online dialogs - - it's really a mess between dao, ado and ado.net ….depending on one's search words....

what is NOT working: Set rs = ExecuteSQL(sqlString, "@ProdPre", adVarChar, adParamInput, 200, "Y")
sqlString is name of store procedure, "Y" = value needed by stored procedure

what is working: Set rs = ExecuteSQL(sqlString, "@ProdPre", adVarChar, adParamInput, 200)
sqlString is: "name of store procedure Y"

the Public Function does have 5 parameters - am guessing at this point that if unprovided is ignored and the original author thought that 5 might be max....
the key was putting the SP's value with it's name in the sql string....which I found in one of the dialogs....