CODEWITHSUNDEEP
sqlstored-proceduresparameters
Laurence
Laurence

Reputation: 7823

How to pass parameters when exec stored procedure

This will work. 

SelectCommand="SELECT [CompanyName], [Country] FROM [Customers] WHERE ([Country] = ?)">
    <SelectParameters>        
       <asp:Parameter DefaultValue="Mexico" Name="Country" Type="String" />        
    </SelectParameters>

But this won't work. It will give "Incorrect syntax near '?'." error message.

SelectCommand="EXEC Docs.[DocumentList] @UserName = ?, @Service = ?, @YearGroup = ?, @Category = ?">
<SelectParameters>
   <asp:Parameter DefaultValue="8882074" Name="UserName" />
   <asp:Parameter DefaultValue="9" Name="Service" />
   <asp:Parameter DefaultValue="1" Name="YearGroup" />
   <asp:Parameter DefaultValue="1" Name="Category" />
</SelectParameters>

This doesn't work either.

    <asp:SqlDataSource ID="ListViewSqlDataSource" runat="server" 
        ConnectionString="<%$ *** %>" 
        SelectCommand="EXEC [Docs].[CategoryList]" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:Parameter DefaultValue="8882074" Name="UserName" />
            <asp:Parameter DefaultValue="9" Name="Service" />
        </SelectParameters>
    </asp:SqlDataSource>

Gives me this error. """Could not find stored procedure ''."""

But changing the selectcommand to this works fine. 

    SelectCommand="EXEC [Docs].[CategoryList] @UserName = 8882074, @Service = 9"

So how do i pass parameters when executing stored procedure? Thanks.

Upvotes: 1

Views: 5898

Answers (1)

marc_s
marc_s

Reputation: 754973

If you want to call a stored procedure, you should:

  • set the SelectCommand to just the stored procedure name
  • set the SelectCommandType to SelectCommandType.StoredProcedure

With this, all the usual parameter stuff works as before!

SelectCommand="Docs.[DocumentList]" SelectCommandType="StoredProcedure"

Update: the SelectCommand should be JUST the stored proc name! No EXEC or anything else......

Try this:

<asp:SqlDataSource ID="ListViewSqlDataSource" runat="server" 
    ConnectionString="<%$ *** %>" 
    SelectCommand="[Docs].[CategoryList]"   **JUST THE STORED PROC NAME !!**
    SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:Parameter DefaultValue="8882074" Name="UserName" />
        <asp:Parameter DefaultValue="9" Name="Service" />
    </SelectParameters>
</asp:SqlDataSource>

Upvotes: 1

Related Questions