Susan Zeller
szeller at cce.umn.edu
Thu Mar 6 09:29:56 CST 2003
FYI, if you are going to use a variable, the correct syntax is the first
one you tried:
Me.RecordSource = "EXEC spfrmCustomers @AccStatus='" &
strAccStatus & "'"
I've had this happen to me before now that I think of it. It's not a
bad idea ot wipe out whatever is in the property box for input
parameters before you save a form.
Sounds like you did some good detective work.
--Susan
-----Original Message-----
From: David Emerson [mailto:davide at dalyn.co.nz]
Sent: Wednesday, March 05, 2003 5:02 PM
To: dba-sqlserver at databaseadvisors.com
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