Using a Multi-select Listbox as an SQL parameter for a GridView

Hi All,

I'm confused. Using VWD2005 with VB.NET. Using a multiple select Listbox as a paramater in the SQL statement that populates the gridview.

First of all I'm surprised that since the gridviews sqldatasource wizard lets you step through all the way to selecting a listbox control as a parameter in a SQL statement, Surely the wizard should automatically detect that the selected listbox is multi-select and asp should generate the code to handle multiple selected items?

Anyway, I've read various posts and cannot establish what the correct ASP.NET solution is?

 This is what I currently have and it isn't working:

Listbox1 listing Countries -  multi-select, autopostback set to true.

On event selectIndexchanged of Listbox1, I loop through as follows:

        If ListBox1.SelectedIndex > -1 Then
            Dim SQLString1 As String
            Dim Item As ListItem
            For Each Item In ListBox1.Items
                If Item.Selected Then
                     SQLString1 = SQLString1 & "'' & Item.Text & "',"
                End If
            Next
            SQLString1 = Left(SQLString1.Trim, SQLString1.Trim.Length - 1)
            TextBox1.Text = SQLString1
        End If

The result of Textbox1 would look like this: 'Germany','South Africa'

The Gridview uses an SQLDataSource with SelectCommand = "SELECT * FROM [ShowCustomersView1] WHERE ([Country] IN(@Country))"> 

             <SelectParameters>
                <asp:ControlParameter ControlID="TextBox1" Name="Country" PropertyName="Text"
                    Type="String" />
            </SelectParameters>

 So therefore I expect the @Country parameter to be set to TextBox1 and the SQL statement should be executed as : "SELECT * FROM [ShowCustomersView1] WHERE ([Country] IN('Germany','South Africa'))"

This SQL statement works if I remove the variable parameter and actually type the literals as above in the SQL statement.

It also works if I remove the single quotes from the loop and have only 1 Country selected and populating TextBox1.

Please can someone help me - what is the CORRECT solution for this must-be common problem? thank you for any help.

0
dotdodont
7/24/2007 3:14:46 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

3 Replies
751 Views

Similar Articles

[PageSpeed] 28

Try

If Item.Selected Then
    Sqlstring += chr(146) & item.text & chr(146) & ","
End If

Hope this helps

DK

0
DkUltra
7/24/2007 4:48:01 PM

Hi DK,

Thanks for the fast response, however I still end up with the same problem.

I do think it has something to do with the way the @country parameter is parsed - ie i think 'Germany','South Africa' is  being compared to a single Country in the Database.

So I assumed it was inserting an extra set of quotes around the entire string - ie ''Germany','South Africa''. or "'Germany','South Africa'"

So, now at the end of the loop I remove the left most quote and the right most quote, assuming it would be added again when parsed. I've tried this with single and then double quotes Chr(34) all round.

But still no luck.

I'm beginning to wonder if instead I have to build the entire SelectCommand SQL statement in the loop, but why would that be any different...

Thanks
 

 

0
dotdodont
7/25/2007 11:04:07 AM

Hi:

  You've a duplicate post. This post will be closed. For further disscussion please go here:

http://forums.asp.net/p/1137442/1820529.aspx#1820529

Thanks.

 


Sincerely,
Allen Chen
Microsoft Online Community Support

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
-1
Allen
7/26/2007 5:15:13 AM
Reply: