Hi, I'm having a problem getting an array (from a Label) into a querystring parameter. Please can someone tell me what I am doing wrong.

Select Statement

            SelectCommand="SELECT * FROM tbl_subProduct, tbl_material, tbl_product, tbl_subCategory, tbl_topCategory, tbl_topLevelProduct WHERE [tbl_subProduct].[numProductID] = [tbl_product].[numProductID] AND [tbl_product].[numTopLevelProductID] = [tbl_topLevelProduct].[numTopLevelProductID] AND [tbl_topLevelProduct].[numSubCategoryID] = [tbl_subCategory].[numSubCategoryID] AND [tbl_subCategory].[numTopCategoryID] = [tbl_topCategory].[numTopCategoryID] AND [tbl_material].[numMaterialID] = [tbl_product].[numMaterialID] AND fldArray=@fldArray">
            <SelectParameters>
            <asp:QueryStringParameter Name="fldArray" QueryStringField="fldArray" />
            </SelectParameters>

Array Statement

        Dim arrSelection As Array = Split(ids_Label.Text, ",")

        Dim i
        Dim strQuery As String

        strQuery = " "

        For i = 0 To UBound(arrSelection)
            If i > 0 Then
                strQuery = strQuery & " OR tbl_subProduct.numSubProductID = " & arrSelection(i)
            Else
                strQuery = strQuery & "tbl_subProduct.numSubProductID = " & arrSelection(i)
            End If
        Next

        Dim fldArray As String = strQuery

Any help would be appreciated

Um, that isn't a QueryStringParameter. A QueryStringParameter would be one where you get the value from the QueryString, e.g. MyPage.aspx?id=27. In this case you'd use a QueryStringParameter to get the id of 27 from the QueryString to use in your SqlDataSource. In your case it looks like you need to tweak your SQL statement a bit and maybe assign a value to the parameter in the OnSelecting event handler of your SqlDataSource.
 

Hi Ramblor,

Yes I have modified my Sql statement but still have a problem, but it is in the array I think because I have the following in my array:-

Product IDs: 214,215,
(tbl_subProduct.numSubProductID = 214) AND (tbl_subProduct.numSubProductID = 215) AND (tbl_subProduct.numSubProductID = )

It's the last one as blank? Could this be from the DB as the first number in the DB is 13? Or is it my splitting of the Array?

My array statement is

        Dim arrSelection As Array = Split(ids_Label.Text, ",")

        Dim i As Integer
        Dim strQuery As String

        strQuery = ""

        For i = 0 To UBound(arrSelection)
            If i > 0 Then
                strQuery = strQuery & " AND (tbl_subProduct.numSubProductID = " & arrSelection(i) & ")"
            Else
                strQuery = strQuery & " (tbl_subProduct.numSubProductID = " & arrSelection(i) & ")"
            End If
        Next

        querystringLabel.Text = strQuery

            SelectCommand="SELECT * FROM tbl_subProduct, tbl_material, tbl_product, tbl_subCategory, tbl_topCategory, tbl_topLevelProduct WHERE [tbl_subProduct].[numProductID] = [tbl_product].[numProductID] AND [tbl_product].[numTopLevelProductID] = [tbl_topLevelProduct].[numTopLevelProductID] AND [tbl_topLevelProduct].[numSubCategoryID] = [tbl_subCategory].[numSubCategoryID] AND [tbl_subCategory].[numTopCategoryID] = [tbl_topCategory].[numTopCategoryID] AND [tbl_material].[numMaterialID] = [tbl_product].[numMaterialID] AND fldArray=@fldArray">
            <SelectParameters>
            <asp:QueryStringParameter Name="fldArray" QueryStringField="querystringLabel" />
            </SelectParameters>

Try removing the last ','...

ids_Label.Text = Trim(Mid(ids_Label.Text, 1, Len(ids_Label.Text) - 1))     ' Get rid of the final  ","

You can split a string and ignore empty entries using the String.Split method, e.g. 

Dim delim() As String = {","}
Dim s As String = "214,215,"
Dim a As Array = s.Split(delim, StringSplitOptions.RemoveEmptyEntries)

However, I'm still not entirely sure how you're trying to get these values into your SelectCommand. You're still using a QueryStringParameter which seems inappropriate when your values aren't in the QueryString.

Hi Ramblor,

Maybe if I explain what I'm doing this will make more sense.

I have a repeater control with checkboxes which have as their text object the id field of the products table, the ids of the checkboxes is then passed into a session object (for saving into new DB later) then the array is split from the session with a view of displaying the info from the DB using the id fields.

That's why I am using the querystringparameter. I need to display a gridview/datalist/repeater of the products selected with the checkboxes.

Is this a good idea?

I'm not 100% on what you're trying to do but here goes nothing just in case I've understood you correctly. I don't think you should be using a QueryStringParameter since your parameter values are not in the QueryString. I'm not sure of how this situation is best dealt with, i.e. selecting records based on one or more values you have selected. I've seen examples of passing a comma-delimited list into a stored procedure, splitting this using a custom function and then doing a SELECT using IN, e.g.

-- For example @queryParameter = '123,124,176,199'
SELECT * FROM MyTable WHERE ID IN ( fnSplit(@queryParameter)  )      -- fnSplit in this line would be a custom function which splits a parameter (lots of examples of this if you do a search for "custom sql split function"

I also found this example, which makes use of the FilterExpression property of the SqlDataSource. So in your case you'd declare your SqlDataSource (note I haven't included your whole query, but if you try this leave out the AND fldArray=@fldArray from the WHERE clause): 

<asp:SqlDataSource ID="SqlDataSource1" 
	runat="server" 
	ConnectionString="<%$ ConnectionStrings:MyConnectionString %>" 
	SelectCommand="SELECT * FROM tbl_subProduct ..." 
	FilterExpression="tbl_subProduct.numSubProductID = {0}"
	OnFiltering="SqlDataSource1_Filtering">
</asp:SqlDataSource>

In code-behind, handle the OnFiltering event:  
    Public Sub SqlDataSource1_Filtering(ByVal Sender As Object, ByVal E As SqlDataSourceFilteringEventArgs) Handles SqlDataSource1.Filtering

        Dim delim() As String = {","}
        Dim str As String = "214,215,216,"
        Dim arr As Array = str.Split(delim, StringSplitOptions.RemoveEmptyEntries)

        Dim exp As String = ""

        For Each s As String In arr
            If exp <> "" Then
                exp = exp & " OR "
            End If
            exp = exp & "tbl_subProduct.numSubProductID=" & s
        Next

        If exp <> "" Then
            SqlDataSource1.FilterExpression = exp
        End If

    End Sub
 I'm not a VB programmer so apologies if there are syntax errors if you do try this - I had to make it up in my head from a c# version I got working.