Francisco H Tapia
my.lists at verizon.net
Wed Mar 5 13:16:11 CST 2003
Since it works in QA, now try executing it from the stored procedure tab in your ADP, look for spfrmCustomers and double click it, then enter the parameter manually (into the input box)... If this works fine, then the error lies in the call Modify for grins... Me.RecordSource = "EXEC spfrmCustomers @AccStatus = 'Active'" to Me.RecordSource = "EXEC spfrmCustomers 'Active'" OR Me.RecordSource = "EXEC spfrmCustomers @AccStatus = "& chr(39) & "Active" & chr(39) -Francisco http://rcm.netfirms.com ----- Original Message ----- From: "David Emerson" <davide at dalyn.co.nz> To: <dba-sqlserver at databaseadvisors.com> Sent: Wednesday, March 05, 2003 10:43 AM Subject: Re: [dba-SQLServer]Calling a Sproc with Parameter : I have used query analyser and run the sproc both with entering % and : Active as the parameter. Both work OK. : : I can't seem to find any information on "error 2353 - Bad query : parameter." Anyone know of a source that will explain this? (I tried : Access help, BOL, Google). : : Here is the sproc in case I have missed something - : : ALTER PROCEDURE spfrmCustomers : : ( : @AccStatus varchar(20) = '%' : ) : : AS : SET NOCOUNT ON : : SELECT dbo.tblCustomers.*, : case : when [Residential]=1 : then CSurname + C1stName : else TradingName : end : as SortOrder : FROM dbo.tblCustomers : WHERE AccStatus like @AccStatus : ORDER BY SortOrder : : If @@Error <> 0 : Begin : Select 'Unexpected error occurred: ', @@Error : Return @@Error : End : else : return 0 : : : : David : : At 5/03/2003, you wrote: : : : Both points are valid, but in this case the Sproc must be able to handle : the : '%'. Assuming that you are not executing Dynamic Sql in your sproc you : maybe manipulating the sproc to select all Customers not just the active : ones... Do you have access to Query Analyzer? if so I would test the : sproc : in there to make sure it executes w/o errors. I have found certain : errors : to be Access Only. Such as selecting data from a linked table on : another : server. : : : -Francisco : http://rcm.netfirms.com <http://rcm.netfirms.com/> : ----- Original Message ----- : From: "Djabarov, Robert" <Robert.Djabarov at usaa.com> : To: <dba-sqlserver at databaseadvisors.com> : Sent: Wednesday, March 05, 2003 7:13 AM : Subject: RE: [dba-SQLServer]Calling a Sproc with Parameter : : : I will have to respectfully disagree with the previous recommendations. : LIKE will prevent your sp from compiling in the first place, while the : usage : of EXEC is very proper in the original code. Although the suggested : form is : still valid, specifying named parameters is actually encouraged for the : clarity of the code, and MS fully supports EXEC sp_PROC : @Parm1='<value>', : @Parm2=<value>, etc. : : Robert Djabarov : Senior SQL Server DBA : USAA IT/DBMS : * (210) 913-3148 - phone : * (210) 753-3148 - pager : : -----Original Message----- : From: Mike and Doris Manning [ mailto:mikedorism at ntelos.net : <mailto:mikedorism at ntelos.net> ] : Sent: Wednesday, March 05, 2003 7:25 AM : To: dba-sqlserver at databaseadvisors.com : Subject: RE: [dba-SQLServer]Calling a Sproc with Parameter : : Two things. : : 1) Make sure your sproc is looking for LIKE @AccStatus and not = : @AccStatus : : 2) When you use EXEC, you don't add the name of the parameter. : "EXEC spfrmCustomers 'Active'" : : Doris Manning : Database Administrator : Hargrove Inc. : www.hargroveinc.com <http://www.hargroveinc.com/> : -----Original Message----- : From: dba-sqlserver-admin at databaseadvisors.com : [ mailto:dba-sqlserver-admin at databaseadvisors.com : <mailto:dba-sqlserver-admin at databaseadvisors.com> ] On Behalf Of David : Emerson : Sent: Tuesday, March 04, 2003 07:01 PM : To: dba-SQLServer at databaseadvisors.com : Subject: [dba-SQLServer]Calling a Sproc with Parameter : I have a button on a form which is used to filter the records. The code : is - : : Private Sub cmdCustomerFilter_Click() : : On Error GoTo Err_cmdCustomerFilter_Click : : If Me!cmdCustomerFilter.Caption = "Show Active" Then : Me.RecordSource = "EXEC spfrmCustomers @AccStatus = 'Active'" : <- : Problem line : Me!cmdCustomerFilter.Caption = "Show All" : Else : Me.RecordSource = "EXEC spfrmCustomers @AccStatus='%'" : Me!cmdCustomerFilter.Caption = "Show Active" : End If : ..... : : End Sub : : In the sproc the parameter is - : : @AccStatus varchar(20) = '%' : : When I click on the button I get error 2353 - Bad query parameter. I : can : run the query in Query analyser and it runs ok. : : If I leave the @AccStatus = 'Active' off then a message appears telling : me : that the procedure expects the parameter (so this tells me it is calling : the : correct sproc) : : I think the problem is in the call but can't put my finger on it. Any : help? : : : : Regards : : David Emerson : DALYN Software Ltd : 25b Cunliffe St, Johnsonville : Wellington, New Zealand : Ph/Fax (877) 456-1205 : : _______________________________________________ : dba-SQLServer mailing list : dba-SQLServer at databaseadvisors.com : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver : <http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> : http://www.databaseadvisors.com <http://www.databaseadvisors.com/> : : Regards : : David Emerson : DALYN Software Ltd : 25b Cunliffe St, Johnsonville : Wellington, New Zealand : Ph/Fax (877) 456-1205 : :