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