CODEWITHSUNDEEP
c#asp.net
Vishal Shah
Vishal Shah

Reputation: 4124

Deleting column causes column does not exist in table error

I'm populating a DataSet from an Sql query. I fetch some extra columns in my query as i need them to for further queries. I delete those columns after im' done with them, but i get a Column "columnname" doesn't exist in "dataset" error when i try to bind the DataSet to a GridView.

Any ideas what could be the cause?

Edit: here's the code The actual error message: "Column 'BID' does not belong to table results."

String command = "SELECT B.BID, B.NAME 'Name', B.FARE 'Fare', B.DEPARTURE 'Departure', B.MAX_SEATS 'MAX', NULL 'Seats Available' "+
                 "FROM ROUTE R, BUS B "+
                 "WHERE R.FROM_LOCATION = @from AND R.TO_LOCATION = @to ";
if(_ac) command += "AND B.AC = @ac ";
if(_volvo) command += "AND B.VOLVO = @volvo ";
if(_sleeper) command += "AND B.SLEEPER = @sleeper ";
command += "AND B.RUNS_ON LIKE '%"+day+"%' AND R.RID = B.RID";
SqlCommand cmd = new SqlCommand(command, con);

cmd.Parameters.AddWithValue("@from", fromValue);
cmd.Parameters.AddWithValue("@to", destValue);
if(_ac) cmd.Parameters.AddWithValue("@ac", _ac);
if(_volvo) cmd.Parameters.AddWithValue("@volvo", _volvo);
if(_sleeper) cmd.Parameters.AddWithValue("@sleeper", _sleeper);

SqlDataAdapter adapter = new SqlDataAdapter(cmd);
_results = new DataSet();

con.Open();
adapter.Fill(_results, "results");

DataTable dt = _results.Tables["results"];
if(dt.Rows.Count > 0 ) {
    for(int i = 0; i < dt.Rows.Count; i++) {
        DataRow row = dt.Rows[i];
        int max = Int32.Parse(row["MAX"].ToString());
        String bid = row["BID"].ToString();
        cmd.CommandText = "SELECT B.MAX_SEATS-SUM(RS.SEATS_BOOKED) 'REMAIN', RS.BID "+
                          "FROM RESERVATION RS, BUS B "+
                          "WHERE RS.DATE_JOURNEY = @date AND RS.BID = @bid AND B.BID = RS.BID "+
                          "GROUP BY B.MAX_SEATS, RS.BID";
        cmd.Parameters.AddWithValue("@date", dateValue);
        cmd.Parameters.AddWithValue("@bid", bid);

        SqlDataReader reader = cmd.ExecuteReader();
        if(reader.Read()) {
            max = Int32.Parse(reader["REMAIN"].ToString());
        }

        // If all seats are booked, remove the row from the table.
        if(max == 0) { dt.Rows.Remove(row); }
        else {
            row["Seats Available"] = max;
        }

        reader.Close();
        cmd.Parameters.Clear();
        dt.AcceptChanges();
    }

    if(dt.Columns.CanRemove(dt.Columns["BID"]))
        dt.Columns.Remove("BID");
    if(dt.Columns.CanRemove(dt.Columns["MAX"]))
        dt.Columns.Remove("MAX");

    dt.AcceptChanges();
    _results.AcceptChanges();

    // Bind the results to a GridView
    GridView1.DataSource = _results;
    GridView1.DataBind();

    // Dispaly Results Panel
    results.Visible = true;
}
else {
    message.Text = "No bus found";
    message.Visible = true;
}

}

GridView1 Markup:

<asp:GridView ID="GridView1" runat="server" onrowcreated="GridView1_RowCreated" EnableViewState="False" GridLines="Horizontal">
    <Columns>
    <asp:TemplateField>
        <ItemTemplate>
            <asp:Literal ID="RadioButtonMarkup" runat="server"></asp:Literal>
        </ItemTemplate>
    </asp:TemplateField>
    </Columns>
</asp:GridView>

Database columns:
ROUTE: RID, FROM_LOCATION, TO_LOCATION
BUS: RID, BID, NAME, AC, VOLVO, SLEEPER, FARE, MAX_SEATS, RUNS_ON, DEPARTURE
RESERVATIONS: TID, PID, BID, SEATS_BOOKED, DATE_BOOKED, DATE_JOURNEY

EDIT: Just noticed, if i only remove column 'MAX', it works, i see the GridView without that column, but removing 'BID' somehow gives me that error.

EDIT: Solved! Was trying to access the BID column in the RowCreated method, which caused this. Thanks to all who helped.

Upvotes: 1

Views: 1479

Answers (3)

System Down
System Down

Reputation: 6270

If this is the markup of your grid, I would suggest that you modify the grid to specifically show the columns that you need.

Upvotes: 0

Lost in Alabama
Lost in Alabama

Reputation: 1653

I'd suggest adding the alias "BID" to B.BID in your first sql statement. Maybe that's what it's looking for.

Upvotes: 1

Pabuc
Pabuc

Reputation: 5638

Ok, I had written a very long reply but just deleted it. Are you sure you are binding the right datasource to your grid?

Maybe:

GridView1.DataSource = _results.Tables["results"];

or

GridView1.DataSource = dt;

would work.

Upvotes: 0

Related Questions