[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

-----Original Message-----
From: 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 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/dba-sqlserver/attachments/20030305/74ad6f59/attachment.html>