Having trouble refreshing database without restarting application

Joel Reid

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.Text,typeof(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

9 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kirtan Patel17y ago
Hey Friend ,

upload your project files(zip) with database on

http://mediafire.com

and send me download link in mail [email protected]

i will solve your delete problem :)
0
Sam Hobbs17y ago
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.
0
Joel Reid17y ago
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
0
Sam Hobbs17y ago
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.
```
0

Joel Reid17y ago

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 Hobbs17y ago
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.
0
Joel Reid17y ago
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
0
Sam Hobbs17y ago
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.
0
Kirtan Patel17y ago
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();

        }

0