Having trouble refreshing database without restarting application

Question

I'm for the most part at a loss trying to figure out how to update this. The scenario is that I am inserting some data on a form. My insert button runs a command that inserts the data to a CallTracker table. public void LogCalltoDB(string technicianName string territory string emBod string date string callCategory) { string connectionString = ConfigurationSettings.AppSettings[Inventory2].ToString(); SqlConnection myCon = new SqlConnection(connectionString); SqlCommand myCommand = new SqlCommand(INSERT INTO CallTracker (techName territoryID problem date problemCategory) VALUES (@technicianName @territory @emBod @date @callCategory) myCon); myCommand.Parameters.Add(new SqlParameter(@technicianName technicianName)); myCommand.Parameters.Add(new SqlParameter(@territory territory)); myCommand.Parameters.Add(new SqlParameter(@emBod emBod)); myCommand.Parameters.Add(new SqlParameter(@date date)); myCommand.Parameters.Add(new SqlParameter(@callCategory callCategory)); myCon.Open(); myCommand.ExecuteNonQuery(); myCon.Close(); } So after this data gets inserted into my table I want this to reflect changes on another tab where I search these log entries by a certain territory ID # without restarting my application. I believe it makes a difference that I am using the tab control with 3 separate tabs. The first tab is where I create and insert the entry the 2nd tab is searching entries by territory ID and 3rd tab is searching by a tech name. I've tried messing with the databindings as well as the tableadapter being updated and filled and still its not working unless I restart the application. The code I use to search on my 2nd tab is try { this.callTrackerTableAdapter.FillByTerritory(this.inventory2DataSet.CallTracker new System.Nullable (((int)(System.Convert.ChangeType(terrCallTextBox.Texttypeof(int)))))); } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } How can I change my code to refresh my table so I don't have to restart the application to search the log entries? Thank you. Joel

Kirtan Patel · Answer

Hey Friend  upload your project files(zip) with database on http://mediafire.com and send me download link in mail kirtan007@gmail.com i will solve your delete problem :)

Sam Hobbs · Answer

Using the code I provided I think you just need to write the SqlDataAdapter.DeleteCommand then execute in a manner that the InsertCommand is executed. Then when you do the Fill the DataGridView will automatically be updated. There is probably a more efficient way to get the DataGridView updated after changes such as these but I assume this is good enough for what you are doing.

Joel Reid · Answer

Thanks Sam I've been able to clean up my code quite a bit with your suggestions. I'm going to attempt to tackle the issue of creating a deleteCommand that deletes the selected row from the gridview as well as from the database. Any ideas to help me get started would be much appreciated! Joel

Sam Hobbs · Answer

I think I have it. So try this maybe in a test proect. In the Form class use the following items: DataTable dt = new DataTable(); SqlCeDataAdapter da = new SqlCeDataAdapter(); SqlCeConnection TestConnection; Then in the Form constructor or Load event: TestConnection = new SqlCeConnection(connectionString); da.SelectCommand = new SqlCeCommand(Select * from BoundUpdated TestConnection); da.InsertCommand = new SqlCeCommand(INSERT INTO BoundUpdated(WhenDone) VALUES (@WhenDone) TestConnection); da.InsertCommand.Parameters.Add(new SqlCeParameter(@WhenDone WhenDone)); TestConnection.Open(); da.Fill(dt); dataGridView1.DataSource = dt; Then in the button click: dt.Clear(); da.InsertCommand.ExecuteNonQuery(); da.Fill(dt); Note that I simplified the code both for you and the computer and I am using a different database and table and such. I hope you can see how to do it in your code. The call to DataTable.Clear is important. As for deleting I think that will also do that; try it.

Joel Reid · Answer

Sam I figured out a solution. I took Kirtan's code made a few changes added it to my search code instead of insert code to reflect the changes. Here is what I did. This code remains the same except i changed to using statements: You can see I didn't put Kirtans Code in this block. public void LogCalltoDB(string technicianName string territory string emBod string date string callCategory) { string connectionString = ConfigurationSettings.AppSettings[Inventory2].ToString(); using (SqlConnection myCon = new SqlConnection(connectionString)) { using (SqlCommand myCommand = myCon.CreateCommand()) { myCommand.CommandText = INSERT INTO CallTracker (techName territoryID problem date problemCategory) VALUES (@technicianName @territory @emBod @date @callCategory); myCommand.Parameters.Add(new SqlParameter(@technicianName technicianName)); myCommand.Parameters.Add(new SqlParameter(@territory territory)); myCommand.Parameters.Add(new SqlParameter(@emBod emBod)); myCommand.Parameters.Add(new SqlParameter(@date date)); myCommand.Parameters.Add(new SqlParameter(@callCategory callCategory)); myCon.Open(); myCommand.ExecuteNonQuery(); myCon.Close(); } } } And now for my search code I use : In this block Kirtan's code is a bit modified private void button1_Click(object sender EventArgs e) { string connectionString = ConfigurationSettings.AppSettings[Inventory2].ToString(); using (SqlConnection myCon = new SqlConnection(connectionString)) { using (SqlCommand myCommand = myCon.CreateCommand()) { myCommand.CommandText = SELECT techName territoryID problem date problemCategory FROM CallTracker WHERE territoryID = @territoryID; myCommand.Parameters.Add(new SqlParameter(@territoryID terrCallTextBox.Text)); myCon.Open(); SqlDataAdapter da = new SqlDataAdapter(myCommand); DataTable dt = inventory2DataSet.CallTracker; da.Fill(dt); callTrackerDataGridView.DataSource = dt; myCon.Close(); } } } The problem I currently face is deleting a user selected row from the gridview and also removing that row from the database table and then updating the database and the gridview. Any help there would be much appreciated and I'll keep this updated if I find a solution.

Sam Hobbs · Answer

It is an interesting problem. I think it must be a common problem; the question of how to update a bound control after the table is updated. I would like to know the answer so I will try to figure it out. I will let you know when I have something figured out.

Joel Reid · Answer

Hello That seems to work however I'd like to be able to search by territory ID. The code you provided doesn't allow me to do that. How can I change the search to refill the gridview with the changes I've made without having to use a new data table? Joel

Sam Hobbs · Answer

I think you need to use one connection opened once at the beginning of the application and not closed until the end of it if that is possible instead of opening and closing a connection for each insert.

Kirtan Patel · Answer

Friend You Should Write code like below so that you dont have to restart application to Reflect changes in DataGridView after Inserting if my answer helps you please mark  Do you like this answer check box please :) private void button1_Click( object sender EventArgs e) { string connectionString = ConfigurationSettings.AppSettings[ Inventory2 ].ToString(); SqlConnection myCon = new SqlConnection (connectionString); SqlCommand myCommand = new SqlCommand ( INSERT INTO CallTracker (techName territoryID problem date problemCategory) VALUES (@technicianName @territory @emBod @date @callCategory)  myCon); myCommand.Parameters.Add( new SqlParameter ( @technicianName  technicianName)); myCommand.Parameters.Add( new SqlParameter ( @territory  territory)); myCommand.Parameters.Add( new SqlParameter ( @emBod  emBod)); myCommand.Parameters.Add( new SqlParameter ( @date  date)); myCommand.Parameters.Add( new SqlParameter ( @callCategory  callCategory)); myCon.Open(); myCommand.ExecuteNonQuery(); //Code to Fill Data GridView in Tab3 myCommand = new SqlCommand ( Select * from CallTracker con); SqlDataAdapter da = new SqlDataAdapter (myCommand); DataTable dt = new DataTable (); da.Fill(dt); //Suppose Your DataGriDview in Tab is DataGridView1 dataGridView1.DataSource = dt; myCon.Close(); }