Introduction to ADO - The Command Object

  • Andy Warren

    SSC Guru

    Points: 119676

  • VR

    SSC Journeyman

    Points: 88

    I have used command object a lot, mostly in the "right" way that has been mentioned. I think that a clearer example of the wrong way could have been explained. If you how wrong something can be, then it will be easier to understand why not to follow in that direction.

  • Andy Warren

    SSC Guru

    Points: 119676

    I'll keep in that mind for the follow up. Thanks for the feedback.

    Andy

  • michael.rosqvist

    Old Hand

    Points: 379

    Maybe you could elaborate on the

    cmd.Name -parameter. I have had some problems with that.

  • Andy Warren

    SSC Guru

    Points: 119676

    Problems in what way?

    Andy

  • nasdaq

    SSC Journeyman

    Points: 88

    I have tried to use the Command object in the past, although I have been able to use input paramaters I have been unable to use input/output or just output, can you explain a little more in depth about those? Also is there any benifit to using the ADO "Hints" over the constants?

  • Andy Warren

    SSC Guru

    Points: 119676

    You might want to look at a later article I did that covers a VB add in that will generate the command object code for you, a real time saver. I use output params regularly with no problem, just a matter of getting everything set up right. What hints are you referring to?

    Andy

  • erichner

    SSC Journeyman

    Points: 76

    Thanks for the article. I found it very interesting and helpful.

    I still have this problem:

    ' Parameter 1

    sParmName1 = "@Cusip"

    ocommand.Parameters.Append ocommand.CreateParameter(sParmName1, adVarChar, adParamInput)

    params(sParmName1).Value = sSymbol

    ' Parameter 2

    sParmName2 = "@Price" '

    Set ADOprm = ocommand.CreateParameter(sParmName2, adNumeric, adParamInput)

    ocommand.Parameters.Append ADOprm

    ocommand.Parameters(sParmName2).Value = sPrice

    The stored procedure is:

    create procedure dbo.UpdateLastPriceRaw

    (

    @Cusip VARCHAR(15),

    @Price NUMERIC(17,9)

    )

    AS

    Parameter 1 works fine - but Parameter 2 brings the following error:

    The precision is invalid.

    ?err.Number

    -2147467259

    If I use the params.refrsh method it works fine (commenting out the ADOPrm SET line)

    Is there a way to find out what the error means and how to solve it without using params.refresh?

  • Andy Warren

    SSC Guru

    Points: 119676

    I'll try to look tonight, but a good way to resolve things like this is to do parameters.refresh, then iterate the collection to dump out all the property settings to see what it's using.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • David.Poole

    SSC Guru

    Points: 75193

    I do a lot of Web development and one of the problems I find is that the constants (adCmdStoredProcedure) used to indicate command types are rarely defined outside of an MS app. For example, I use DreamWeaver quite a bit.

    I have the ADO2.6 command reference book, and yes, in appendix B it has the definitions of these constants, but wouldn't it be great if someone wrote a book that listed the constants and their values before the example code?

    Incidentally, I have a problem where I am trying to access two separate databases within a Site Server implementation.

    If I have a page that accesses my user database using my connection string then everything works OK. Ditto the Site Server functionality.

    If I try and use both together on the same page everything goes beserk!

    I have tried defining two separate connections but I keep getting Accessed Denied error messages.

    Both SiteServer and my user databases are using Windows Integrated security. Both have the required user set up and yet I still get Access Denied!

    If I use SA then I am allowed in but there is no way I want to release a piece of code that uses the SA login and password!

    Has anyone had anything similar?

  • Andy Warren

    SSC Guru

    Points: 119676

    Missing constants definitely makes the reading/work harder - I guess DreamWeaver doesn't get the type library idea? No good idea on your problem, only thought that came to mind was if it works for SA, must be permissions.

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • gljjr

    SSCrazy Eights

    Points: 9970

    erichner,

    Rather than using the

    SET ADOprm = ocommand.CreateParameter(sParmName2, adNumeric, adParamInput)

    Try

    SET ADOPrm = ocommand.CreateParameter (sParmName2)

    ADOPrm.Type = ADNumeric

    ADOPrm.Size = 17

    ADOPrm.Precision = 9 'Note: I could be wrong on this as I'm doing it from memory and it's been over a year since I've done it!

    ADOPrm.Value = sPrice

    Etc...

    Then do your append to the parameters collection. That's the good thing about using the variable for the parameter. You have full access to all the properties that can be set. Sure it's more lines of code but it's also much more accurate.

    David,

    MS used to provide the ADO declarations in a single file that you could reference in your asp files. I never did that though and just looked up the values from VB and put the raw values in as the file was quite large and typically you would only need one or two things from it.

    Gary Johnson

    DBA

    Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • jscontreras

    Old Hand

    Points: 380

    Good article, I found it very interesting. These examples are really good but only address one procedure at a time. Taking the OOP methodology I'm in the process of building a COM object that accepts an infinite number of parameters in/out. I've been working on this and I have received all sorts of errors whenever I try to user Param Arrays.

    This is what I have which works fine, however it's fix to parameters.

    Dim objADOCMD As ADODB.Command

    Public Function ExecuteStoredProcedure(ByVal sProcName As String, Optional ByVal PARAM1 As String, Optional ByVal PARAM2 As String, Optional ByVal ParamValue1 As String, Optional ByVal ParamValue2 As String, Optional ByVal FS1 As Integer, Optional ByVal FS2 As Integer) As ADODB.Recordset

    On Error GoTo Err_sProcedure

    '\B/------------------< Execute stored procedures with/without parameters >------------------

    Set objADOCMD = New ADODB.Command

    With objADOCMD

    .ActiveConnection = Conn

    .CommandText = sProcName

    .CommandType = adCmdStoredProc

    If PARAM1 <> "" And PARAM2 <> "" Then

    .Parameters.Append .CreateParameter(PARAM1, adVarChar, adParamInput, FS1, ParamValue1)

    .Parameters.Append .CreateParameter(PARAM2, adVarChar, adParamInput, FS2, ParamValue2)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 <> "" And PARAM2 = "" Then

    .Parameters.Append .CreateParameter(PARAM1, adVarChar, adParamInput, FS1, ParamValue1)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 = "" And PARAM2 <> "" Then

    .Parameters.Append .CreateParameter(PARAM2, adVarChar, adParamInput, FS2, ParamValue2)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 = "" And PARAM2 = "" Then

    Set ExecuteStoredProcedure = .Execute

    End If

    End With

    Set objADOCMD = Nothing

    '\B/------------------< Execute stored procedures with/without parameters >------------------

    Err_sProcedure:

    Err_DBConnection:

    If Err.Number <> 0 Then

    MsgBox "Error occurred trying to call the stored procedure " & sProcName & vbCrLf & Err.Number & vbCrLf & Err.Description

    End If

    End Function

    Where sProcName is procedure name

    Param1 is parameter name

    Param2 is parameter name 2

    FS1 is the field size - this is important

    FS2 is the second field size

    Now to the code that is failing

    Public Function ExecuteSP(sProcName As String, ParamArray aParams()) As ADODB.Recordset

    Dim objCMD As ADODB.Command

    Set objCMD = New ADODB.Command

    With objCMD

    .ActiveConnection = Conn

    .CommandText = sProcName

    .CommandType = adCmdStoredProc

    If aParams(0) Is Nothing Then

    Set ExecuteSP = .Execute

    Else

    Set ExecuteSP = .Execute(, aParams)

    End

    End With

    Set objCMD = Nothing

    End Function

    This is how to call this function:

    Dim objRS AS Adodb.recordset

    Set objRS = ExecuteSP("procName",Nothing)

    Set objRS = ExecuteSP("procName","FirstName","LastName")

    I'm still trying to understand the parameter array. Any thoughts or ideas.

    Thanks

    JMC


    JMC

  • Andy Warren

    SSC Guru

    Points: 119676

    I like objects, but I think what you're doing (though very common) abstracts the intent one layer too much. I'd rather see a fully declared proc inside a method call, or in more complex circumstances, a full object where properties map to params. Today's opinion anyway. Here is some sample code for the paramarray.

    Sub Test(ParamArray X() As Variant)

    Dim J As Integer

    For J = LBound(X()) To UBound(X)

    Debug.Print X(J)

    Next

    End Sub

    Private Sub Form_Load()

    Call Test("A", "B", "C")

    End Sub

    Andy

    http://www.sqlservercentral.com/columnists/awarren/

  • cdodds64

    SSC Enthusiast

    Points: 170

    'Nothing' should only be used when dealing with object and in VB, an array is not an object. So checking if aParams(0) is nothing will cause an error if the array element is not an object. Alternatively, you could check the ubound of the array instead and also check to see if elements equal vbNullString. Also, if passing the array is optional you could declare it that way.

    I agree with the previous comments that this is possibly an abstraction too far - it may get messy when the client needs to specify more than just the parameter names i.e. types, values etc

    quote:


    Good article, I found it very interesting. These examples are really good but only address one procedure at a time. Taking the OOP methodology I'm in the process of building a COM object that accepts an infinite number of parameters in/out. I've been working on this and I have received all sorts of errors whenever I try to user Param Arrays.

    This is what I have which works fine, however it's fix to parameters.

    Dim objADOCMD As ADODB.Command

    Public Function ExecuteStoredProcedure(ByVal sProcName As String, Optional ByVal PARAM1 As String, Optional ByVal PARAM2 As String, Optional ByVal ParamValue1 As String, Optional ByVal ParamValue2 As String, Optional ByVal FS1 As Integer, Optional ByVal FS2 As Integer) As ADODB.Recordset

    On Error GoTo Err_sProcedure

    '\B/------------------< Execute stored procedures with/without parameters >------------------

    Set objADOCMD = New ADODB.Command

    With objADOCMD

    .ActiveConnection = Conn

    .CommandText = sProcName

    .CommandType = adCmdStoredProc

    If PARAM1 <> "" And PARAM2 <> "" Then

    .Parameters.Append .CreateParameter(PARAM1, adVarChar, adParamInput, FS1, ParamValue1)

    .Parameters.Append .CreateParameter(PARAM2, adVarChar, adParamInput, FS2, ParamValue2)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 <> "" And PARAM2 = "" Then

    .Parameters.Append .CreateParameter(PARAM1, adVarChar, adParamInput, FS1, ParamValue1)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 = "" And PARAM2 <> "" Then

    .Parameters.Append .CreateParameter(PARAM2, adVarChar, adParamInput, FS2, ParamValue2)

    Set ExecuteStoredProcedure = .Execute

    ElseIf PARAM1 = "" And PARAM2 = "" Then

    Set ExecuteStoredProcedure = .Execute

    End If

    End With

    Set objADOCMD = Nothing

    '\B/------------------< Execute stored procedures with/without parameters >------------------

    Err_sProcedure:

    Err_DBConnection:

    If Err.Number <> 0 Then

    MsgBox "Error occurred trying to call the stored procedure " & sProcName & vbCrLf & Err.Number & vbCrLf & Err.Description

    End If

    End Function

    Where sProcName is procedure name

    Param1 is parameter name

    Param2 is parameter name 2

    FS1 is the field size - this is important

    FS2 is the second field size

    Now to the code that is failing

    Public Function ExecuteSP(sProcName As String, ParamArray aParams()) As ADODB.Recordset

    Dim objCMD As ADODB.Command

    Set objCMD = New ADODB.Command

    With objCMD

    .ActiveConnection = Conn

    .CommandText = sProcName

    .CommandType = adCmdStoredProc

    If aParams(0) Is Nothing Then

    Set ExecuteSP = .Execute

    Else

    Set ExecuteSP = .Execute(, aParams)

    End

    End With

    Set objCMD = Nothing

    End Function

    This is how to call this function:

    Dim objRS AS Adodb.recordset

    Set objRS = ExecuteSP("procName",Nothing)

    Set objRS = ExecuteSP("procName","FirstName","LastName")

    I'm still trying to understand the parameter array. Any thoughts or ideas.

    Thanks

    JMC


Viewing 15 posts - 1 through 15 (of 31 total)

You must be logged in to reply to this topic. Login to reply