Click here to Skip to main content
15,896,474 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:
I am able to Save Data to a Access databse , now when i add new entry i try to refresh the database but the new entry dont show until i close the program and open it back up. I have 2 buttons one Update and the other Refresh. I am using Parameters. It would be better to use one meathod to do both (thats over my head at the moment) so i have 2 buttons. I dont get any errors when i try to Refresh so I dont know where to start.
 
Thanks 
 
My Update code working fine.

<pre lang="c#"><pre>private void btnUpdate_Clicked(object sender, RoutedEventArgs e)  
       {  
           using (var myCon = new OleDbConnection(ConfigurationManager.ConnectionStrings["Connection"].ToString()))  
           {  
               using (var myCmd = new OleDbCommand("insert into [Sheet1](Box1,Box2,Box3,Box4,Box5,Box6,Box7,Box8)Values(@nm1,@nm2,@nm3,@nm4,@nm5,@nm6,@nm7,@nm8)", myCon))  
               {                      
                   myCmd.Parameters.AddWithValue("@nm1", txt_Box1.Text);  
                   myCmd.Parameters.AddWithValue("@nm2", txt_Box2.Text);  
                   myCmd.Parameters.AddWithValue("@nm3", txt_Box3.Text);  
                   myCmd.Parameters.AddWithValue("@nm4", txt_Box4.Text);  
                   myCmd.Parameters.AddWithValue("@nm5", TXT_Box5.Text);  
                   myCmd.Parameters.AddWithValue("@nm6", txt_Box6.Text);  
                   myCmd.Parameters.AddWithValue("@nm7", txt_Box7.Text);  
                   myCmd.Parameters.AddWithValue("@nm8", txt_Box8.Text);  
  
                   try  
                   {  
                       myCon.Open();  
                       int z = myCmd.ExecuteNonQuery();  
                       if (z > 0)  
                       {  
                           MessageBox.Show("Data Inserted");  
  
                      }  
  
                   }  
                   catch (Exception ex)  
                   {  
                        MessageBox.Show(ex.Message);
                        return;
                   }  
               }  
           }  
  
              
       }  


My Refresh is not refreshing the data base no errors givens.

C#
private void btn_Refresh_Click(object sender, EventArgs e)  
        {  
  
            using (var myCon = new OleDbConnection(ConfigurationManager.ConnectionStrings["Connection"].ToString()))  
            {  
                using (var myCmd = new OleDbCommand("UPDATE Sheet1 SET Box1 =@nm1,Box2 = @nm2, Box3=@nm3, Box4 = @nm4, Box5 = @nm5,Box6  = @nm6, Box7 = @nm7 WHERE Box8 = @nm8", myCon))  
                {                      
                    myCmd.Parameters.AddWithValue("@nm1", txt_Box1.Text);  
                    myCmd.Parameters.AddWithValue("@nm2", txt_Box2.Text);  
                    myCmd.Parameters.AddWithValue("@nm3", txt_Box3.Text);  
                    myCmd.Parameters.AddWithValue("@nm4", txt_Box4.Text);  
                    myCmd.Parameters.AddWithValue("@nm5", TXT_Box5.Text);  
                    myCmd.Parameters.AddWithValue("@nm6", txt_Box6.Text);  
                    myCmd.Parameters.AddWithValue("@nm7", txt_Box7.Text);  
                    myCmd.Parameters.AddWithValue("@nm8", txt_Box8.Text);  
  
                    try  
                    {  
                        myCon.Open();  
                        int z = myCmd.ExecuteNonQuery();  
                        MessageBox.Show("Data Updated");  
                         
                    }  
                    catch (Exception ex)  
                    {  
                        //Handle exception as needed  
                    }  
                }  
            }  
  
             
        }  


Get Data

C#
private void loadgrid()
      {
          OleDbConnection con = new OleDbConnection();
          con.ConnectionString = ConfigurationManager.ConnectionStrings["Connection"].ToString();
          con.Open();
          OleDbCommand cmd = new OleDbCommand();
          cmd.CommandText = "Select * from [Sheet1]";
          cmd.Connection = con;
          OleDbDataReader rd = cmd.ExecuteReader();
          dataGrid.ItemsSource = rd;

      }


What I have tried:

Hours of looking on the internet and sample programs
Posted
Updated 1-Nov-17 10:49am
v5
Comments
Dave Kreskowiak 1-Nov-17 14:26pm    
Your question doesn't make much sense. Both of your button Click handlers are writing data to the database, which I assume is an Excel worksheet.

INSERT adds new data, while Update updates an existing record.

What kind of application are you writing and where is your code that reads the database data and binds it to whatever control(s) you're using to display the data? This is the code that's missing and what you need to be executing after you do your SQL Insert or Update operations.
Dave Kreskowiak 1-Nov-17 15:31pm    
Close and reopen WHAT FILE?

Also, I'm suspicious of what you're calling an Access file, unless you actually have a table in your Access database called "Sheet1". That normally says Excel to me.


Simple: your INSERT doesn't work, and your catch block discards the information and ignores it.
Why?
This:
myCmd.Parameters.AddWithValue("@nm1", txt_Box1);  
myCmd.Parameters.AddWithValue("@nm2", txt_Box2);  
Should be:
myCmd.Parameters.AddWithValue("@nm1", txt_Box1.Text);  
myCmd.Parameters.AddWithValue("@nm2", txt_Box2.Text);  


Don't leave blank catch blocks - they hide errors instead of letting you know what needs fixing...
 
Share this answer
 
Comments
Member 12349103 1-Nov-17 14:54pm    
that was a copy and paste error, sorry.
Not sure if I interpret the question correctly, but in the btnUpdate_Clicked event you insert the data and in btn_Refresh_Click you update the data.

Where do you fetch the newly inserted or update data from the database into the program?

I would imagine that you have an update/save button which either updates or inserts the record depending if it's an existing one or new and the the refresh button would do a SELECT statement in order to fetch the data from the database. Since neither of the buttons now fetch the data I guess that you only do the fetch when the program open and this is why you don't see the new data in your program after saving.

Also, as already mentioned do handle exceptions properly, at minimum show the message text to the user.
 
Share this answer
 
Comments
Wendelius 1-Nov-17 16:42pm    
What happens if you call the loadgrid method in the end of the update or refresh method?

Also in the DML statements you update a table called Sheet1 while in the select you query HotSheet. If this is a different table, how is it refreshed?
Member 12349103 1-Nov-17 17:00pm    
Wendelius

well done I added loadgrid method to the end of my Refresh_Click event and it worked.

Thank you
Wendelius 2-Nov-17 0:28am    
Glad if I could help.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900