CODEWITHSUNDEEP
c#asp.netsql-server
Nate Pet
Nate Pet

Reputation: 46222

SelectParameters for IN Condition - Passing Parameter

I have the following in my .aspx file

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:KrySQL %>" OnSelecting="DataSourceSelecting"
       SelectCommand="Select ID, RoomNum from [dbo].[MBiology] WHERE LocId IN @LocId" >
    <SelectParameters>
       <asp:Parameter Name="LocId" />
    </SelectParameters>        
</asp:SqlDataSource>

My goal is to pass a comma delimited list or just one value for @LocId

In my code behind, I have the following:

SqlDataSource1.SelectParameters["LocID"].DefaultValue = "(" + valint.ToString() + ")";

I thought this should work as I have the parenthesis and all.

I am getting the following error message:

Incorrect syntax near '@LocId'

Upvotes: 0

Views: 424

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726599

Unfortunately, you cannot do it like this: .NET does not let you pass an entire list in a single SQL parameter.

There are two ways of implementing what you need:

  • Generating individual parameters for the members of the IN list dynamically, or
  • Using table-valued parameters (requires an ability to change your DB schema to add a type).

Upvotes: 1

Related Questions