Sean Connolly

Hi.

I am developing a form which contains of the following:

1 ListBox, 1 ComboBox and 1 DataGridView.

The ListBox contains a list of Sql Server databases currently stored which contain multiple tables. The ComboBox is populated with the names of the tables and the DataGridView is populated with the selected table.

My code is as follows:

private void BankComboBox_SelectedIndexChanged(object sender, EventArgs e)
{
    // Place here the code to display the current bank.
    int i = this.BankComboBox.SelectedIndex;
    if (i < 0)
        i = 0;
    try
    {
        DispDataAdapter = new SqlDataAdapter("USE " +
            this.TablesListBox.Text.Replace(' ', '_') + "_Table " +
            "SELECT * FROM Bank" + i.ToString(), MainWindow.myConn);
        BankData.Rows.Clear();
        DispDataAdapter.Fill(BankData);
    }
    catch { }
}

private void BankComboBox_Click(object sender, EventArgs e)
{
    // Place here the code to record the current displayed bank.
    int i = this.BankComboBox.SelectedIndex;
    if (i < 0)
        i = 0;
    try
    {
        DispDataAdapter.Dispose();
        DispDataAdapter = new SqlDataAdapter("USE " +
            this.TablesListBox.Text.Replace(' ', '_') + "_Table " +
            "SELECT * FROM Bank" + i.ToString(), MainWindow.myConn);
        SqlCommandBuilder BankBuilder = new SqlCommandBuilder(DispDataAdapter);
        BankBuilder.GetUpdateCommand();
        DispDataAdapter.Update(BankData);
        DispDataAdapter.Dispose();
        BankBuilder.Dispose();
    }
    catch { }
}


The DataGridView (which doesn't allow AutoGenerateColumns) is programmatically created and initialized to a private static DataTable called BankData. The SqlDataAdapter is a private static declaration too!

The code for Displaying the selected table is working perfectly, either when the form is opened, the ListBox selection is changed or the combo box is changed by the user.

The part that isn't working is the SqlCommandBuilder. It won't record any changes. I tied this into the ComboBox Click event so no index changes before recording the DataGridView.

When the program is traced, the part that is being caught in the 'try' statement is the command:

        BankBuilder.GetUpdateCommand();

It jumps directly to the catch { } statement, even if there are changes made to the DataGridView.

The select command for the DataAdapter is identical in both the event functions above.

Can somone please please please help me to fix this quickly!

Thanks in advance!

Sean

 



Re: .NET Framework Data Access and Storage Tackling the SqlCommandBuilder once again!

Sean Connolly

Never mind, I have fixed the problem.

I put a call to the SqlCommandBuilder in the startup of the display. I then created a separate call to make sure the connection was using the correct database and made a call to the SqlCommandBuilder with just the SELECT statement.