Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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?
Posted 8-Mar-13 12:58pm
Edited 8-Mar-13 13:00pm
v3

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 130
4 DamithSL 95
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 8 Mar 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100