How to pass a parameter to SQL Stored Procedure from Visual basic?

[ii128]

How to pass a parameter to SQL Stored Procedure from Visual basic?

 

[cdidion]

Use command objects:

Set oCmd = New ADODB.Command
With oCmd
.NamedParameters = True
.CommandType = adCmdStoredProc
.CommandText = "uspDeleteSavedCriteria"
.Parameters.Append .CreateParameter("xuSavedCriteriaUID", adGUID, adParamInput, , msSavedCriteriaUID)
.ActiveConnection = xoConn
.Execute
End With

The namedparameters property is new with ado 2.6 and speeds up stored proc calls if you know how to build the parameters for the call. If you don't you can try this:

Set oCmd = New ADODB.Command
With oCmd
.ActiveConnection = xoConn
.CommandType = adCmdStoredProc
.CommandText = "uspDeleteSavedCriteria"
.Parameters.Refresh
.Parameters("xuSavedCriteriaUID&quot.value = msSavedCriteriaUID
.Execute
End With

But it takes an extra trip to the database to fill the parameters collection for you (=Slower).

Hope this helps. -Chris Didion
Matrix Automation, MCP

 

[ii128]

But How can I pass the parameter through data Environment?