CODEWITHSUNDEEP
asp.net
Jack
Jack

Reputation: 10037

ASP.net: Sqldatasource and Session variable

<asp:HiddenField ID="hfDualInitials" runat="server" Visible="false" OnInit="hfDualInitials_OnInit" />
    <asp:SqlDataSource ID="sdsStoreNo" runat="server" ConnectionString="<%$ ConnectionStrings:ConnStr %>"
        SelectCommand="select * from AccountCancellation_Process 
                       where store_num in (select distinct storeno from franchisedata where initials in (@DualInitials))
                       order by CustomerName ASC" >
        <SelectParameters>
            <asp:ControlParameter ControlID="hfDualInitials" DbType="String" Name="DualInitials" />
        </SelectParameters>                     
    </asp:SqlDataSource>

I have a Sqldatasource with the above select command and the below code to set the hiddenfield value

Protected Sub hfDualInitials_OnInit(ByVal sender As Object, ByVal e As EventArgs)        
    Dim _DualInitials As String = "('P2','7T')"
    Session.Add("DualInitials", _DualInitials)
    Me.hfDualInitials.Value = Session("DualInitials")
End Sub

I'm mocking the Session with ('P2','7T') that is going to pass into the above sql command. when i run the query:

select * from AccountCancellation_Process where store_num in (select distinct storeno from franchisedata where initials in ('P2','7T'))

it return some data but in my Sqldatasource select command. It return nothing. my guess is because of the where initials in (@DualInitials) the ( ) that is already in the hiddenfield but if i remove the ( ) and just have @DualInitials. I will get "Incorrect syntax near '@DualInitials'."

Does anyone know any workaround or where i get it wrong?

Upvotes: 2

Views: 2514

Answers (3)

Joey Morgan
Joey Morgan

Reputation: 271

Without running the sample, I can't be sure, but what about 

WHERE initials IN (<%=Eval(@DualInitials)%>)

Upvotes: 0

Robert C. Barth
Robert C. Barth

Reputation: 23315

You can't paramterize an IN statement in SQL like that. You'll have to use string concatenation of the SQL instead (bad) or some other technique like parsing a delimited string.

Upvotes: 0

Ruslan
Ruslan

Reputation: 1761

Check out answers to the ADO.NET TableAdapter parameters question.

You have a query with a string parameter, not an array parameter. So, when you pass "('P2','7T')" you think that the final query is

WHERE initials IN ('P2', '7T')

In reality it is

WHERE initials IN ('(''P2'', ''7T'')')

If it is only going to be two initials then just rewrite using the OR statement. Otherwise I don't know good solution outside of those mentioned in the other thread.

Upvotes: 1

Related Questions