Francisco Tapia
my.lists at verizon.net
Thu Mar 6 01:39:57 CST 2003
intresting :S -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 3:01 PM Subject: Re: [dba-SQLServer]Calling a Sproc with Parameter : Thanks for your suggestions Susan and Francisco, : : The sproc works if I open it from Access and manually enter the parameter. : : Tried all suggestions below - no difference. : : Tried changing the name of the parameter - same result. : : Tried (with same result) - : Me.RecordSource = "EXEC spfrmCustomers @AccStatus='" & : strAccStatus & "'" : Me.RecordSource = "EXEC spfrmCustomers @AccStatus='" strAccStatus : Me.RecordSource = "EXEC spfrmCustomers strAccStatus" : : Finally, tried clearing the InputParameter property on the form : (was @AccStatus='%') and bingo! One happy-chappy :) Seemed to be some : conflict with this but I don't understand why. : : David : : : At 5/03/2003, you wrote: : >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://www.databaseadvisors.com :