Passing varibles to a CreateParameter Method. Is this Possible?

[rodrunner79]

Hello,

I am creating a sub that executes a stored procedure with parameters. On the CreateParameter method, instead of defining the arguments for every parameter I want to append, I want to derive them from a recordset. But when I call the sub, I keep getting a "Mismatch" error and VBA highlights the CreateParameter method line that throws off the error. The reason why I want to do this is because I want to store all my stored procedures, the parameter names and data types in a local/linked table in Access. Is this possible?

Here's my code:

Function ExecuteStoredProcedure(ByVal lngUtilityID As Long, _
   varFirstValue As Variant, varSecondValue As Variant)
 
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim strSQL As String
   Dim cnn As ADODB.Connection
   Dim cmd As ADODB.Command
   Dim prm As ADODB.Parameter

   
   strSQL = "SELECT A.* " & _
            "FROM tblSytemUtility AS A " & _
            "WHERE A.Category = 'Stored Procedure' " & _
             "And A.SPID = " & lngUtilityID & ";"

   Set db = CurrentDb()
   Set rs = DBEngine(0)(0).OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)
   
   Set cnn = New ADODB.Connection
   cnn.Provider = "MSDASQL"

   Set cnn = New ADODB.Connection
   cnn.ConnectionString = "driver={SQL Server};" & _
      "server=192.168.1.255,1433;uid=TestUser;pwd=defaultpass;database=MYSQLDB"
   cnn.Open
 
   Set cmd = New ADODB.Command
   cmd.ActiveConnection = cnn
   cmd.CommandType = adCmdStoredProc
   cmd.CommandText = rs!UtilityName ' Stored procedure name derived from recordset

   Set prm = cmd.CreateParameter(rs!CustomField1, rs!CustomField2, rs!CustomField3, rs!CustomField4, varFirstValue)
   cmd.Parameters.Append prm
   cmd.Parameters(rs!CustomField1).Value = varFirstValue

   Set prm = cmd.CreateParameter(rs!CustomField5, rs!CustomField6, rs!CustomField7, rs!CustomField8, varSecondValue)
   cmd.Parameters.Append prm
   cmd.Parameters(rs!CustomField5).Value = varSecondValue


   cmd.Execute
   cnn.Close
 
   rs.Close
   db.Close
    
   Set rs = Nothing
   Set db = Nothing
   
   Set cnn = Nothing
   Set cmd = Nothing

End Function

Can't means won't try.

 

[Andrzejek]

According to this site, Set Parameter syntax is:
[tt]Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)[/tt]

Where:
Name - String
Type -DataTypeEnum
Direction - ParameterDirectionEnum
Size - Long
Value - Variant

So the question is: what do you pass with these parameters:[tt]
rs!CustomField1
rs!CustomField2
rs!CustomField3
rs!CustomField4
varFirstValue[/tt]


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rodrunner79]

To Andrzejek,

All the values I will be passing to the arguments of the CreateParemeter method would be table-derived values as if I hard-coded them. Take the following example:

Dim prm As ADODB.Parameter
Set prm = command.CreateParameter ("@CustomerID", adInteger, adParamInput, 0, 13)

Basically, instead of hardcoding the name of my variable or parameter name, data type, direction, size and value in VBA, I will perform a use DAO.recordset to derive them from a table instead.

The rs!CustomerField1 would be the name of the variable/parameter (e.g. @CustomerID)
The rs!CustomerField2 would be the data type of the variable/parameter (e.g. adInteger)
and so on.

When I call the Function named ExecuteStoredProcedure from the application, I pass the following arguments:
ExecuteStoredProcedure(ByVal lngUtilityID As Long, varFirstValue As Variant, varSecondValue As Variant)

Basically, the lngUtility is the record ID of the stored procedure values I want to pass to the DAO.recordset so that it can lookup the data elements to pass to the actual ADO command.

Hope this make sense.



Can't means won't try.

 

[Andrzejek]

I understand your point, but what I am trying to say is: what are the acctual values you use?

Debug.Print rs!CustomField1
Debug.Print rs!CustomField2
Debug.Print rs!CustomField3
Debug.Print rs!CustomField4
Debug.Print varFirstValue)

Set prm = cmd.CreateParameter(rs!CustomField1, rs!CustomField2, rs!CustomField3, rs!CustomField4, varFirstValue)

Since Name wants to be a String, you may want to do:

Set prm = cmd.CreateParameter([blue]CStr([/blue]rs!CustomField1[blue])[/blue], rs!CustomField2, rs!CustomField3, rs!CustomField4, varFirstValue)


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rodrunner79]

The actual values I'm deriving from table and passing to the arguments are:
CreateParameter ("SessionID", "adInteger", adInputParameter, 255, valuefromfunctioncall)

I also uploaded the error I'm getting:

Can't means won't try.

 

[Andrzejek]

Second parameter (Type - DataTypeEnum) you have as String: "adInteger"

CreateParameter needs to have DataTypeEnum, either adInteger, or 3

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[rodrunner79]

Sorry forgot to trim the "". The value adInteger stored in the table/recordset does not include the quotes so when I pass it to the method as an argument rs!CustomField2, I expect it to return it as adInteger but maybe I'll store numbers instead and try it as you suggest. I will let you know. Thanks for working with me.

Can't means won't try.

 

[SkipVought]

Do CreateParameter for ALL possible fields. In your sql code use a Select Case to concatenate the appropriate Syntax to the WHERE clause.