C# - issue with updating data adapters in datagridview

See more:

SQL-CE

C#

SQL-Server

forms

VS2012

Hi everyone!

I am having an issue with dataadapter update methods. Here is what I have so far:

List<string> tempColumnList = new List<string>();
                List<string> tempColumnFinalList = new List<string>();

                SqlCeCommand getTableListingCommand = new SqlCeCommand("SELECT table_name FROM information_schema.tables " + 
                    "WHERE table_name <> 'engineandtransmissioncodes' AND table_name <> 'enginecodemaster' AND table_name <> 'transmissioncodemaster' AND table_name <> 'vehicles' AND table_name <> 'waveform_files'", conn);
                getTableListingCommand.CommandType = CommandType.Text;
                getTableListingCommand.ExecuteNonQuery();
                                
                SqlCeDataReader getTableListingCommand_dr = getTableListingCommand.ExecuteReader();
                
                DataTable dt_temp = new DataTable();

                while (getTableListingCommand_dr.Read())
                {                    
                    dt_temp.Clear();                    

                    BindingSource bstemp = new BindingSource();
                    SqlCeDataAdapter da_temp = new SqlCeDataAdapter("SELECT * FROM " + getTableListingCommand_dr.GetString(0), conn);
                    SqlCeCommandBuilder scb_temp = new SqlCeCommandBuilder(da_temp);

                    tempColumnList.Clear();
                    tempColumnFinalList.Clear();                    

                    SqlCeCommand getColumnListingCommand = new SqlCeCommand("SELECT column_name FROM information_schema.columns WHERE table_name = @table_name", conn);
                    getColumnListingCommand.CommandType = CommandType.Text;
                    getColumnListingCommand.Parameters.AddWithValue("@table_name", getTableListingCommand_dr.GetString(0));
                    getColumnListingCommand.ExecuteNonQuery();

                    SqlCeDataReader getColumnListingCommand_dr = getColumnListingCommand.ExecuteReader();

                    while (getColumnListingCommand_dr.Read())
                    {                        
                        tempColumnList.Add(getColumnListingCommand_dr.GetString(0));
                        //MessageBox.Show(getTableListingCommand_dr.GetString(0) + " " + getColumnListingCommand_dr.GetString(0));
                    }               
                                        
                    foreach (string s in tempColumnList)
                    {
                        if (dataGridView1.Columns.Contains(s))
                        {                            
                            tempColumnFinalList.Add(dataGridView1.Rows[dataGridView1.NewRowIndex - 1].Cells[s].Value.ToString());
                        }
                        else //if(dataGridView1.Columns.Contains(s) == false)
                        {
                            tempColumnFinalList.Add(dataGridView1.Rows[dataGridView1.NewRowIndex - 1].Cells["KEY"].Value.ToString());
                        }                                           
                    }

                    string[] tempColumnFinalListArray = tempColumnFinalList.ToArray();                   

                    da_temp.Fill(dt_temp);
                    //dataGridView1.DataSource = dt_temp;
                    //bstemp.DataSource = dt_temp;

                    dt_temp.Rows.Add(tempColumnFinalListArray);
                    da_temp.Update(dt_temp);

The expected output would be to add the row contained in the tempColumnFinalListArray to the dt_temp datatable to each of the tables in my datatable (with non-null *correct* values).

The actual output is it updates the first table in my database correctly and then updates the rest of the tables that succeed it with rows that are null (other than the "key" value.

The gettablelistingcommand returns all table names. The getColumnListingCommand returns all columns in all tables.

The foreach(string s in tempColumnList) is supposed to add each value of the new row to the database. The reason for the if/else statement is because some tables have "KEY" and some have "VEHICLE_KEY". I didn't know how to differentiate from that. It is supposed to add it to a list and then convert it to an array to be added to the respective table via a datarow.

Can anyone shed some light for me as to what I'm missing so the tables can get updated properly?