November 16, 2001 at 12:00 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/awarren/introductiontoadothecommandobject.asp>http://www.sqlservercentral.com/columnists/awarren/introductiontoadothecommandobject.asp
December 5, 2001 at 4:33 am
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.
December 5, 2001 at 4:55 am
I'll keep in that mind for the follow up. Thanks for the feedback.
Andy
January 21, 2002 at 12:23 am
Maybe you could elaborate on the
cmd.Name -parameter. I have had some problems with that.
January 21, 2002 at 4:43 am
March 20, 2002 at 7:47 am
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?
March 20, 2002 at 8:25 am
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
March 11, 2003 at 12:33 pm
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?
March 11, 2003 at 12:45 pm
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
March 13, 2003 at 2:25 am
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?
March 13, 2003 at 3:33 am
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
March 13, 2003 at 6:34 pm
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.
March 28, 2003 at 8:58 am
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
March 28, 2003 at 5:42 pm
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
March 30, 2003 at 11:05 pm
'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 30 total)
You must be logged in to reply to this topic. Login to reply