How to retrieve the record in comma seperated string from multiple database column?

Here is the structure of my database:

i have textbox1 and textbox2 and 8 checkboxes in my ASP.NET (VB.NET) webform.

i want to retrieve the seats on the particular dates means if i enter 11/12/2010 in textbox1 then in textbox开发者_如何转开发2 the output would be from seat_select column, let say (1,2,3) where date would be 11/12/2010

If i enter 13/12/2010 in textbox1 then in textbox2 the output would be 1,2

How to do this in VB.NET?

Markup:

<asp:TextBox runat="server" ID="txtDate" />
<asp:Button runat="server" ID="cmdGet" Text="Get" OnClick="cmdGet_Click" />
<asp:TextBox runat="server" ID="txtSeat" />

Code-behind (C#) - formatting on presentation layer:

protected void cmdGet_Click (object sernder, EventArgs e)
{
    DateTime d;
    if (!DateTime.TryParseExact(txtDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
        throw new InvalidOperationException("Input was in incorrect format");

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "SELECT seat_select FROM table_seats WHERE date <= @date";
        command.Parameters.AddWithValue("@date", d);

        connection.Open();
        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
        using (DataTable table = new DataTable())
        {
            adapter.Fill(table);
            txtSeat.Text = String.Join(", ", table.AsEnumerable().Select(r => r.Field<int>("seat_select")));
        }
    }
}

Don't forget to add the reference to System.Data.DataSetExtensions.dll, System.Core.dll.

Code-behind (C#) - formatting on data layer:

protected void cmdGet_Click (object sernder, EventArgs e)
{
    DateTime d;
    if (!DateTime.TryParseExact(txtDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out d))
        throw new InvalidOperationException("Input was in incorrect format");

    using (SqlConnection connection = new SqlConnection(connectionString))
    using (SqlCommand command = connection.CreateCommand())
    {
        command.CommandText = "DECLARE @seats NVARCHAR(MAX); SELECT @seats = COALESCE(@seats + ', ', '') + seat_select FROM table_seats WHERE date <= @date";
        command.Parameters.AddWithValue("@date", d);

        connection.Open();
        txtSeat.Text = (string)command.ExecuteScalar();
    }
}

I'm not sure if that's what you mean, but - broadly speaking - I would execute the select to get the relevant data from the database and iterating on the reader I would split the seat_select using Split and add each element to a list. I would then use Linq's Distinct to remove duplicates and display that.

Hope this is what you meant and that it helps

you can use the following query to return you a comma separate list of seat_select and then use linq to do a distinct on the returned data.

declare @seats nvarchar(max)
select @seats = seat_select + ',' +  isnull(@seats,'') from tableName where [date] ='11/12/2010'
return @seats