ASP.net: Sqldatasource and Session variable

Question

<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?

score 1 · Answer 1 · edited May 23 '17 at 11:49

1

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.

edited May 23 '17 at 11:49

Community

1
1

answered Mar 18 '09 at 17:55

Ruslan

1,761
9
16

Any particular reason why I got -1 here? The IN statement treats the parameter as a string and not a sequence. I gave a solution to Jack's problem. wtf? – Ruslan Mar 18 '09 at 19:57

score 0 · Answer 2 · answered Mar 18 '09 at 19:45

0

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.

answered Mar 18 '09 at 19:45

Robert C. Barth

22,687
6
45
52

score 0 · Answer 3 · answered Nov 10 '11 at 23:04

0

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

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

answered Nov 10 '11 at 23:04

Joey Morgan

271
3
7

ASP.net: Sqldatasource and Session variable

3 Answers3