[RESOLVED] SqlDataSource w/ session var collection

[NStuff42]

Hey everyone. I have an SqlDataSource that uses a session parameter in its select statement. Everything works fine with one value in the parameter (as shown below) but I would like to pass multiple values to my select statement and have had no luck. Has anyone ever done this? Or know some other way of achieving the same goal (to select based on multiple values)? Thanks in advance.

This is what I have now (that works):

    Me.Session("myweb_labDiagCodes") = 1

        <asp:SqlDataSource ID="SqlDSDiagnosticCodes" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>" 
            SelectCommand="SELECT [code], [memo] FROM [icd9_codes] WHERE ([ID] = @ID) ORDER BY [code], [memo]">
            <SelectParameters>
                <asp:SessionParameter Name="ID" SessionField="myweb_labDiagCodes" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>

And this is what I am trying to do:

    Me.Session("myweb_labDiagCodes") = {1,2,3,4}

        <asp:SqlDataSource ID="SqlDSDiagnosticCodes" runat="server" 
            ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>" 
            SelectCommand="SELECT [code], [memo] FROM [icd9_codes] WHERE ([ID] = @ID) ORDER BY [code], [memo]">
            <SelectParameters>
                <asp:SessionParameter Name="ID" SessionField="myweb_labDiagCodes" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>

[gep13]

Hey,

Session variables store objects, they are not strongly typed. To an extent, there will be some conversions done for you, i.e. when you did this:

Me.Session("myweb_labDiagCodes") = 1

It was able to extract that value as an integer, and pass that as the parameter.

However, when you try this:

 Me.Session("myweb_labDiagCodes") = {1,2,3,4}

You are storing an array of integers in the session variable, and as a result, it can't then extract that and pass it as individual parameters.

Why do you need more than one parameter though? Your SQL statement only has one?!?

Gary

[NStuff42]

My goal is to pass multiple values to my select statement (i.e. “SELECT * FROM myTable WHERE ID = 1 OR ID = 2 OR…”). I don’t know if this is possible but I know it cannot be done the way I am currently trying. Any ideas?

[gep13]

Hey,

It certainly can be done, but you would need to pass in multiple parameters, you can't do it with only one parameter.

You would be able to use the OR approach that you are suggesting, or you might want to think about using the "in" keyword:

http://www.techonthenet.com/sql/in.php

Either way, you would need something like:

[CODE]
<asp:SqlDataSource ID="SqlDSDiagnosticCodes" runat="server"
ConnectionString="<&#37;$ ConnectionStrings:ASPNETDBConnectionString %>"
SelectCommand="SELECT [code], [memo] FROM [icd9_codes] WHERE ([ID] = @ID1 OR @ID2 OR @ID3 OR @ID4) ORDER BY

, [memo]">
            <SelectParameters>
                <asp:SessionParameter Name="ID1" SessionField="myweb_labDiagCodes1" Type="Int32" />
<asp:SessionParameter Name="ID2" SessionField="myweb_labDiagCodes2" Type="Int32" />
<asp:SessionParameter Name="ID3" SessionField="myweb_labDiagCodes3" Type="Int32" />
<asp:SessionParameter Name="ID4" SessionField="myweb_labDiagCodes4" Type="Int32" />
            </SelectParameters>
        </asp:SqlDataSource>

And put the separate values into separate session variables.

The above was written without the aid of the IDE, so could be subject to error.

Gary

[NStuff42]

Thanks for the reply. Is there a way of doing this when you do not know the variable count? The variables are provided via csv input from a textbox so I cannot be sure how many they will pass.

[gep13]

Hey,

The only way I can think of doing it would be to not use the SessionParameters in the aspx page, but rather use the Selecting Event of the SqlDataSource.

This event fires before the Select actually happens, and a result you have a change to modify the parameters in the Select, and I guess the actual Select Statement that is executed.

This would allow you to configure both, and allow you to handle the case of not knowing which variables you have.

You can find an example of that in use here:

http://www.4guysfromrolla.com/demos/.../112206-1.aspx

Having said that, I would question why you need to do this? What exactly are you trying to achieve. Why can you not just have one parameter, and call it different times for different variables. It is hard to know what is the best approach, without some more information.

Gary

[NStuff42]

Thanks, I created a custom SQL select statement in the Page load event.

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Try

            ' Declare connection string objects
            Dim connString As System.Configuration.ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ASPNETDBConnectionString")

            ' Raise error if connection string is empty
            If connString.ConnectionString = Nothing Then
                Err.Raise(513, "Page_Load", "Connection string is empty!")
            End If

            ' Declare search string variable to store search variables. Set default value to return 0 records
            Dim SQLstring As String = "ID = 0"

            ' Build search strings from input
            For Each SearchString As String In Strings.Split(Me.Session("myweb_labDiagCodes"), ",")
                SQLstring += " OR [ID] = '" & Strings.Trim(SearchString) & "'"
            Next

            ' Get data from database
            Dim sql As New Data.SqlClient.SqlDataAdapter("SELECT [ID], [diagcode], [memo] FROM [icd9_codes] WHERE (" & SQLstring & _
                                                         ") ORDER BY [diagcode], [memo]", connString.ConnectionString)
            Dim sqlDT As New Data.DataTable

            ' Fill data table
            sql.Fill(sqlDT)

            ' Remove current datasource Set rgDiagnosticCodes datasource to new data
            Me.rgDiagnosticCodes.DataSourceID = Nothing
            Me.rgDiagnosticCodes.DataSource = sqlDT
            Me.rgDiagnosticCodes.Rebind()
        Catch ex As Exception
            ' Set the error in the session variable
            Me.Session("myweb_error") = ex

            ' Redirect to error page
            Me.Response.Redirect("~/errorpage.aspx")
        End Try
    End Sub

Now I can store the IDs in the session like this and they get loaded everytime the page loads.

Me.Session("myweb_labDiagCodes") = "1,2,3,4,5"