<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.2800.1126" name=GENERATOR></HEAD>
<BODY>
<DIV><SPAN class=825005518-05032003><FONT face=Arial color=#0000ff size=2>In
situtaions like this I do lots of testing eliminating one thing at a time.
Given what you have already tried, what I would do is change the sproc so it
doesn't require an input parameter and then hard code a value (active or %) into
your sproc. Try running it from the app to see if it works. If it
does, you know for sure the problem is in how you are passing the
parameter. If the problem is in passing the parameter, I might put the
parameter in a variable in your FE and then do something
like:</FONT></SPAN></DIV>
<DIV><SPAN class=825005518-05032003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=825005518-05032003>Me.RecordSource = "EXEC spfrmCustomers
@AccStatus = " & strAccStatus</SPAN></DIV>
<DIV><SPAN class=825005518-05032003></SPAN> </DIV>
<DIV><SPAN class=825005518-05032003><FONT face=Arial color=#0000ff
size=2>--Susan</FONT></SPAN></DIV>
<DIV><SPAN class=825005518-05032003></SPAN> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> David Emerson
[mailto:davide@dalyn.co.nz] <BR><B>Sent:</B> Wednesday, March 05, 2003 12:44
PM<BR><B>To:</B> dba-sqlserver@databaseadvisors.com<BR><B>Subject:</B> Re:
[dba-SQLServer]Calling a Sproc with Parameter<BR><BR></FONT></DIV>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 class=cite cite="" type="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
</P></BLOCKQUOTE></BODY></HTML>