I've written a stored procedure in T-SQL for the Northwind SQL Server sample database using the technique demonstrated at The code is as follows:
When run from Query Analyser, I can use named parameters to specify whatever criteria I want, leave the others out and it returns the appropriate data. So far so good.
I went over to Access, created a new form and wrote some VBA ADO code to connect to SQL Server and retrieve the data.
With no parameters it worked great, retrieving all data.
When I specified one parameter which was not the first one, no data was retrieved.
By using SQL Profiler to monitor this connection, I found out that the code it submitted to the server doesn't specify the parameter name, and this is why it was failing.
For example, if I chose London as the city, I would expect it to submit something like
EXEC ps_Customers_SELECT_CustomerDetails @City_Name='London';
but it was submitting
EXEC ps_Customers_SELECT_CustomerDetails 'London'
- so it was trying to match a contact name against a city name, no wonder there was no data returned.
I can get around this by including null values for the parameters not required as follows (this is my completed code for the moment):
Platforms in use in my test environment:
* SQL Server 2000 SP4 Developer Edition
* Windows 2000 Professional SP4
* Access XP SP3
* Reference to ActiveX Data Objects 2.8 Library in Access.
Questions:
1. Why does ADO not pass across the parameter name as mentioned above? Can I force it to pass it across?
2. Does anybody have any comments regarding this code?
I'm new to running SQL code from Access, and am likely to need to upsize a fair number of systems soon and rewrite the frontends for optimal operation in both Access and Excel.
I'd like to get any bad habits out of the way before they get too ingrained in my head.
John
Code:
CREATE PROCEDURE dbo.ps_Customers_SELECT_CustomerDetails
@Contact_Name varchar(30) = NULL,
@Company_Name varchar(30) = NULL,
@City_Name varchar(30) = NULL,
@Country_Name varchar(30) = NULL
AS
SELECT Customers.CompanyName,
Customers.ContactName,
Customers.ContactTitle,
Customers.Address,
Customers.City,
Customers.PostalCode,
Customers.Country
FROM Customers
WHERE Customers.ContactName = COALESCE(@Contact_Name,Customers.ContactName) AND
Customers.CompanyName = COALESCE(@Company_Name,Customers.CompanyName) AND
Customers.City = COALESCE (@City_Name, Customers.City) AND
Customers.Country = COALESCE(@Country_Name,Customers.Country)When run from Query Analyser, I can use named parameters to specify whatever criteria I want, leave the others out and it returns the appropriate data. So far so good.
I went over to Access, created a new form and wrote some VBA ADO code to connect to SQL Server and retrieve the data.
With no parameters it worked great, retrieving all data.
When I specified one parameter which was not the first one, no data was retrieved.
By using SQL Profiler to monitor this connection, I found out that the code it submitted to the server doesn't specify the parameter name, and this is why it was failing.
For example, if I chose London as the city, I would expect it to submit something like
EXEC ps_Customers_SELECT_CustomerDetails @City_Name='London';
but it was submitting
EXEC ps_Customers_SELECT_CustomerDetails 'London'
- so it was trying to match a contact name against a city name, no wonder there was no data returned.
I can get around this by including null values for the parameters not required as follows (this is my completed code for the moment):
Code:
Private Sub cmdViewData_Click()
' Variables for database connection
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
On Error GoTo err_cmdViewData_Click
' Connect to the database
cnn.Open strDBConnect
cnn.CursorLocation = adUseClient
' Set up the command object
With cmd
.ActiveConnection = cnn
.CommandType = adCmdStoredProc
.CommandText = "ps_Customers_SELECT_CustomerDetails"
If Len(Me.txtContactName & "") > 0 Then
.Parameters.Append .CreateParameter("Contact_Name", adVarChar, adParamInput, 30, Me.txtContactName)
Else
.Parameters.Append .CreateParameter("Contact_Name", adVarChar, adParamInput, 30, Null)
End If
If Len(Me.txtCompanyName & "") > 0 Then
.Parameters.Append .CreateParameter("Company_Name", adVarChar, adParamInput, 30, Me.txtCompanyName)
Else
.Parameters.Append .CreateParameter("Company_Name", adVarChar, adParamInput, 30, Null)
End If
If Len(Me.txtCity & "") > 0 Then
.Parameters.Append .CreateParameter("City_Name", adVarChar, adParamInput, 30, Me.txtCity)
Else
.Parameters.Append .CreateParameter("City_Name", adVarChar, adParamInput, 30, Null)
End If
If (Me.cboCountry & "") > 0 Then
.Parameters.Append .CreateParameter("Country_Name", adVarChar, adParamInput, 30, Me.cboCountry)
Else
.Parameters.Append .CreateParameter("Country_Name", adVarChar, adParamInput, 30, Null)
End If
Set rs = .Execute
End With
Set Me.Recordset = rs
Me.txtContactName.ControlSource = "ContactName"
Me.txtCompanyName.ControlSource = "CompanyName"
Me.cboCountry.ControlSource = "Country"
Me.txtContactTitle.ControlSource = "ContactTitle"
Me.txtPostcode.ControlSource = "Postalcode"
Me.txtCity.ControlSource = "City"
Me.txtAddress.ControlSource = "Address"
DoCmd.OpenForm Me.Name, acFormDS ' Switch to Datasheet view to see data in a list format.
Exit_cmdViewData_Click:
If Not (cnn Is Nothing) Then cnn.Close
Exit Sub
err_cmdViewData_Click:
MsgBox Err.Number & ": " & Err.Description
Resume Next
End SubPlatforms in use in my test environment:
* SQL Server 2000 SP4 Developer Edition
* Windows 2000 Professional SP4
* Access XP SP3
* Reference to ActiveX Data Objects 2.8 Library in Access.
Questions:
1. Why does ADO not pass across the parameter name as mentioned above? Can I force it to pass it across?
2. Does anybody have any comments regarding this code?
I'm new to running SQL code from Access, and am likely to need to upsize a fair number of systems soon and rewrite the frontends for optimal operation in both Access and Excel.
I'd like to get any bad habits out of the way before they get too ingrained in my head.
John
