Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Beginner
Sep 21, 1999



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Avoid Using Refresh() Method

Refresh method of Parameters collection is used to populate the Parameter
objects in the collection. This method queries the data provider to
populate the collection with Parameter objects containing specific details
of the parameters. After execution of this method, you can access this
collection to stuff in the values of the parameters or to check other
properties of parameters.

You should avoid the usage of this method because it makes a hit to the
server to get the parameter details. If you are using SQL Server, you can
always run SQL Trace utility to verify that a call to Refresh makes a hit
to the server to get the parameters. But during development stage, you can
always use this method to know the exact details about the parameters and
later on you can use CreateParameter() method of Command object to populate
the Parameters collection using code.

Here is a sample method which uses both Refresh and CreateParamter() to
build the parameter information for the stored procedure:

Private Sub BuildParameterInfo(ByVal lngSampleIdNo As Long, 
strSampleName As String, strSampleDesc As String) Dim oConnection As ADODB.Connection ' Connection Object Dim oCommand As ADODB.Command ' Command Object ' Create a new Connection Object Set oConnection = New ADODB.Connection ' Create a new Command Object Set oCommand = New ADODB.Command ' Open the connection oConnection.Open "DSN=Sample", "sa", "" ' Set connection into the Command Object Set oCommand.ActiveConnection = oConnection ' Set Command text property to the name of stored procedure to be
executed oCommand.CommandText = "sp_AddRecordToSampleTable" oCommand.CommandType = adCmdStoredProc ' ** ** Quick but Expensive way to build the parameter information by
calling refresh method With oCommand.Parameters .Refresh .Item(1).Value = lngSampleIdNo .Item(2).Value = strSampleName .Item(3).Value = strSampleDesc End With ' ** ** Efficient way to build parameter information by calling
CreateParameter() method and then ' ** ** appending it to the Parameters collection With oCommand.Parameters .Append oCommand.CreateParameter("SampleIdNo", adInteger, adParamInput, , lngSampleIdNo) .Append oCommand.CreateParameter("SampleName", adVarChar, adParamInput, 20, strSampleName) .Append oCommand.CreateParameter("SampleDesc", adVarChar, adParamInput, 200, strSampleDesc) End With ' Call Execute of Command Object to execute the stored procedure oCommand.Execute End Sub
Deepak Pant
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date