Using Parameters & ADO with SQL Server? 2

[jgoodman00]

I have the following sub, which passes parameters into a SQL server sp.

Private Sub cmdViewManager_Click()
On Error Resume Next 'GoTo Cmd_Err
Dim cmd As ADODB.Command


Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "qryGenerateResults"
cmd.Parameters("@HierarchyID&quot = Me.txtAssetCriteria
cmd.Parameters("@LocationID&quot = Me.txtBRSCriteria
cmd.Parameters("@Priority&quot = Me.txtPriorityCriteria

There is a problem with the parameters collection of the cmd object. If SQL server has been installed locally, the collection is available, & can be assigned correctly. However, if the local computer has not had an installation of SQL server the cmd object, & its parameters collection within is incorrect. It merely gives an error stating Incorrect syntax near ')'.

I have searched the knowledge base, but to no avail. Has anybody else seen this?

James Goodman

 

[jimmythegeek]

I have not seen it done like your example above. I always thought that the parameters had to be appended to the command collection. The below works for me no matter where the SQL Server is installed.

==========
Dim adoCMD as ADODB.Command
Dim adoPRM as ADODB.Parameter

Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = adoCN ' global connection string
.CommandType = adCmdStoredProc
.CommandText = "BuildTransmit_MoveRuns" ' Name of Stored Procedure
Set adoPRM = .CreateParameter("Transport", adChar, adParamInput, 2, Me.cboTransport)
.Parameters.Append adoPRM
Set adoPRM = .CreateParameter("strRuns", adVarChar, adParamOutput, 2048)
.Parameters.Append adoPRM
.Execute

' Assign the output string values to the row sources of the combo boxes
Me.lstAvailableRuns.RowSource = .Parameters.Item("strRuns&quot
End With
Set adoPRM = Nothing
Set adoCMD = Nothing Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[cmmrfrds]

Going off of what Jimmy has said, this method (cmd.Parameters("@Priority&quot = Me.txtPriorityCriteria) of establishing parameters requires a precall to the server to obtain the parameter information. If the parameters are explicitly appended before the execute then ADO can use the appended collection to determine parameter types, sizes, etc.. One thing that could cause a problem would be a varchar parameter if the size isn't explicitly stated prior to the call. Possibly the varchar parameter on the current call is larger than the last use of the parameter.

 

[jgoodman00]

cmmrfrds,
All three of the parameters are varchar(200) fields, which are used as part of an SQL statement which is dynamically created & then generated. It uses the In to generate a subquery for each parameter (the parameters entered can be quite extensive).
You said about a varchar not being explicitly stated prior to the call. All three parameters are explicitly declared in the sp. Is this what you meant?

jim,
I got the method for executing an sp with parameters from:

http://www.advisor.com/Articles.nsf/aid/BALTA15

It looks to be a workable method for submitting parameters to the sp, I just cannot work out what is being modified by a SQL server (or msde) installation. The obvious one is an active x library, but I dont know how to be sure...

Obviously I dont want to go around installing & then uninstalling SQL server on every client machine!!!



James Goodman

 

[cmmrfrds]

What I was referring to is on the ADO side not the SP side. When you declare a varchar parameter explicitly in ADO it will require that a size be set. Like so. Set adoPRM = .CreateParameter("strRuns", adVarChar, adParamOutput, 2048). Notice the size is set at 2048 for the varchar field, which ADO will use for the parameter. It is set each time through the code. In your case ADO is going to build a parameter size from the data i.e. cmd.Parameters("@Priority&quot = Me.txtPriorityCriteria. It will build the size the first time it is run and subsequent runs will use that size for the parameter. If the next execution has a larger parameter it will error since the size was set smaller the first time through the ADO code. So, if your actual data was 20 characters the first time and 30 characters the subsequent run the varchar size is still at 20 characters.
I am not sure this is happening but is possible the way your code is set up. For this, the solution is to use the method described by Jimmy where the parameters are explicitly defined.

 

[jgoodman00]

I tried the method jim has suggested & it works, thanks .

However, I am still a little confused as to what the CreateParameter method does, when I already have a parameter with the same name defined in the sp.


I am also a little confused as to what is being changed by installing SQL server, which means the cmd.parameters approach work. On a machine without SQL server, the sp itself works, so I can only think that installing SQL server somehow changes the ADO library files, or access methods... James Goodman

 

[jimmythegeek]

When you create the parameter in code, it is on the ADO side, not on the SQL Server side. The ADO Command object has to know what parameters to send to SQL Server, so that when you hit the .Execute line, the ADO call can properly send the correct parameter information and datatypes to the SQL Server Stored Procedure.

I also don't understand why you are installing SQL Server more than once, especially locally to any machine. SQL Server is designed to sit on a server (ideally, by itself), and then you can get to it from anywhere on the network. Why would you need to install it more than once? (unless, of course, you have multiple servers, i.e. Production, Development, Testing, etc.).

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jgoodman00]

SQL sever is only installed on our server. However, I also installed it on my laptop, so I can continue development when I travel. My workstation initially had the SQL server installed on it, before we migrated it to the server (after testing it). The remaining computers on our network have never had a SQL server installation, & this was the only thing I could think of which might affect the method I was using. Installing SQL server as a test on one of the local workstations proved this correct, although I am still uncertain as to what exactly it changed.


Thanks for your help James Goodman

 

[cmmrfrds]

One thing that might have happened is the ADO libraries that were installed. Windows 2000 pro comes with the libraries as part of the operating system. The install of SQL Server will also install ADO libraries. It may only do this is it needs the libraries - they don't already exist. Win 9X may or may not have the libraries installed, these libraries MDAC may have been downloaded and installed on the Win 9X PC or the installation of SQL Server would install the libraries. Maybe it is some combination of these types of things. What OS are these PC's where you needed to install SQL Server running. If you have some Win 9X PC's you can download the latest MDAC libraries from Microsoft.

 

[jgoodman00]

The workstations are all running Windows 2k pro. They are fully up to date as well...

James Goodman