Avatar of Abdullah Abdulkarim
Abdullah Abdulkarim
Flag for United States of America asked on

Data is not saving in the database

I have problem how to save the data into database, when I click on the save everything seems fine and saved and I can see the data in the app form but when I close the app and open the database "accdb" I did not see the data.

Can you please help me. I do not know where my mistake
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Windows.Forms;


namespace AlbahraniNetwork2
{
    public partial class Add : Form

          
    {
       
         string appPath = Path.GetDirectoryName(Application.ExecutablePath);
         class Util
         {
             static public string GetUserDataPath()
             {

                 string appPath = Path.GetDirectoryName(Application.ExecutablePath);
                 if (!appPath.EndsWith("\\"))
                     appPath += "\\";

                 return appPath;
        
            }
        }



        OleDbConnection _Conn = new OleDbConnection();

        OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Util.GetUserDataPath() + "Database31.accdb");
         
        //OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb");
        OleDbCommand cmd;
        OleDbDataReader dr;



        public Add()
        {
            InitializeComponent();
           
         
        }

        private void Add1_Click(object sender, EventArgs e)


        {
            

            try{
                sc.Open();
                cmd = new OleDbCommand("insert into AlbahraniNetwork(FirstName,SecondName,LastName,Phone,Cell,Email,Address) values('" + FirstName.Text + "','" + SecondName.Text + "','" + LastName.Text + "','" + Phone.Text + "','" + Cell.Text + "','" + Email.Text + "','" + Address.Text + "')", sc);
                cmd.ExecuteNonQuery();
                MessageBox.Show(" ¿¿ ¿¿¿¿¿ ¿¿¿ ¿¿ ¿¿¿¿ ¿¿¿¿¿");
               
                 }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);



                sc.Close();

            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Index newform = new Index();
            this.Hide();
            newform.ShowDialog();
            
        }


        private void button3_Click_1(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button1_Click(object sender, EventArgs e)
        {

            string connetionString = null;
            OleDbConnection connection ;
            OleDbCommand command ;
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            DataView dv ;
            string oledb = null;
           


            string appPath = Path.GetDirectoryName(Application.ExecutablePath);
            if (!appPath.EndsWith("\\"))
                appPath += "\\";
            connetionString = (@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + appPath + "Database31.accdb");



            
          
            oledb = "Select * from AlbahraniNetwork";
            connection = new OleDbConnection(connetionString);
            try
            {
                connection.Open();
                command = new OleDbCommand(oledb, connection);
                adapter.SelectCommand = command;
                adapter.Fill(ds, "Add New");
                adapter.Dispose();
                command.Dispose();
                connection.Close();

               


                
            }
            catch (Exception ex)
            {
                MessageBox.Show (ex.ToString());
            }
        }
    }
        }

       

    

Open in new window

.NET ProgrammingC#Microsoft Access

Avatar of undefined
Last Comment
hjgode

8/22/2022 - Mon
Meir Rivkin

in line 106 u create new dataset where u use in line 128.
but u don't populate the dataset before calling adapter.Fill(ds, "Add New").
Abdullah Abdulkarim

ASKER
Thanks but the same thing even if I delete all the code 106 and 128 for  new button  it is still wont save the data it gives me you have  been saved successfully but when I review the data in the database nothing saved.
Meir Rivkin

but where do u fill the dataset?
Your help has saved me hundreds of hours of internet surfing.
fblack61
UnifiedIS

It looks like you are building your connection strings differently in button1_click and add1.  Are you sure you are saving the data to the correct database?
Am I reading correctly that button1_click is where you are pulling data and add1_click is your "save" button?

You could try putting a breakpoint on line 64 (cmd.executenonquery...) and check the commandtext property of the cmd (cmd.CommandText).  Copy/paste the commandtext to a new query in Access and see if it runs.  
Readability would be improved if you used better names for your button controls.
Scott McDaniel (EE MVE )

Is this on a Windows 7 machine? If so, then you might be dealing with virtualization issues if you've placed the Access db in the PRogram Files folder. Try temporarily defining a static path like C:\MyDBFolder\MyDatabase.accdb and see if your writes work. If they do, you'll have to change the path to your database to one that isn't virutalized.
Abdullah Abdulkarim

ASKER
but where do u fill the dataset?
if I decide to delete the button which contains fill code, it will not effect the save or add button. I  am trying to save data inside theDatabase31.accdb. when I run the app works fine and add a new data and save , says saved and if I search I can see the data while the app run after I stop the app and run it again ,  the data disappear because it has not saved in the data. I change the path around because I have problem to do setup to it. If I return the path as it was , it works fine and save the data perfect. The old bath before I change it is
OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb");

Open in new window

   
I change it to
  OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Util.GetUserDataPath() + "Database31.accdb");

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Abdullah Abdulkarim

ASKER
It looks like you are building your connection strings differently in button1_click and add1.  Are you sure you are saving the data to the correct database? yes  to Database31.accdb



Am I reading correctly that button1_click is where you are pulling data and add1_click is your "save" button? button1, it just add new row to insert a new data and Add1 mean save the data in the Database31.accdb.

you could try putting a breakpoint on line 64 (cmd.executenonquery...) and check the commandtext property of the cmd (cmd.CommandText).  Copy/paste the commandtext to a new query in Access and see if it runs.  tried to check the command but I did  not see any mistake.
Abdullah Abdulkarim

ASKER
Is this on a Windows 7 machine? If so, then you might be dealing with virtualization issues if you've placed the Access db in the PRogram Files folder. Try temporarily defining a static path like C:\MyDBFolder\MyDatabase.accdb and see if your writes work. If they do, you'll have to change the path to your database to one that isn't virutalized.


If I change it to this path
OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Abdullah\documents\visual studio 2010\Projects\AlbahraniNetwork2\AlbahraniNetwork2\Database31.accdb");
                                            

Open in new window

it will work but I will face problem with creating setup  which says the path is not valid or cannot find the path
UnifiedIS

I understand that it is saving to a database named "Database31.accdb" but it's the rest of the path that is in question.  
This code looks correct but the path in your connection (sc) is suspect:
 sc.Open();
                cmd = new OleDbCommand("insert into AlbahraniNetwork(FirstName,SecondName,LastName,Phone,Cell,Email,Address) values('" + FirstName.Text + "','" + SecondName.Text + "','" + LastName.Text + "','" + Phone.Text + "','" + Cell.Text + "','" + Email.Text + "','" + Address.Text + "')", sc);
                cmd.ExecuteNonQuery();
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Abdullah Abdulkarim

ASKER
Thanks, but do you mean by breakpoints like if statement or while loop. I did not get.

I double check  the path but seems correct. Can you please help me how to do it?
UnifiedIS

Breakpoints in Visual Studio allow you to stop the code at a specific line so you can evaluate status of variables, etc... Click 'F9' on a line to add (or remove) a breakpoint then start the project.  When your code hits the line with the breakpoint, use your immediate window to query the value of your connection object.  In the immediate window, you will type:
? sc.ConnectionString
Compare the path to the data source with where you think the path should be.
Scott McDaniel (EE MVE )

I will face problem with creating setup  which says the path is not valid or cannot find the path
Yes, but it shows that your issue is that you cannot install the Access db to the app path. You should instead install it somewhere like Program Data, or in the User's Documents folder. Those are not virtualized.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Abdullah Abdulkarim

ASKER
I am trying to do what do you told me with breakpoints but  the problem still. I attached the explanation picture.
try
            {
                sc.Open();
                
                cmd = new OleDbCommand("insert into AlbahraniNetwork(FirstName,SecondName,LastName,Phone,Cell,Email,Address) values('" + FirstName.Text + "','" + SecondName.Text + "','" + LastName.Text + "','" + Phone.Text + "','" + Cell.Text + "','" + Email.Text + "','" + Address.Text + "')", sc);
                cmd.ExecuteNonQuery();

                cmd = new OleDbCommand("insert into AlbahraniNetwork(FirstName,SecondName,LastName,Phone,Cell,Email,Address) values('" + FirstName.Text + "','" + SecondName.Text + "','" + LastName.Text + "','" + Phone.Text + "','" + Cell.Text + "','" + Email.Text + "','" + Address.Text + "')", sc.ConnectionString);

                MessageBox.Show(" The name  have been saved  successfully");

            }

            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);



                sc.Close();

            }
        }

Open in new window

issuePicture1.jpg
UnifiedIS

Ok, you got the breakpoint and the code has stopped.  Now you need to use the immediate window to check the data source in your connection string.  As mentioned, virtualized directories are going to give you problems so you should avoid storing the database there.
UnifiedIS

BTW, the immediate window is a tab in the area where you have your Call Stack window.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Abdullah Abdulkarim

ASKER
Okay this my whole project and  this my first one I am try to create. I thought it will be so easy to create program and install it on the computers. I struggled  to figure my problem ,especially with path for the database. Please check it and help my to run it , I could not figure my problem until now. I think it will be easy and clear to the experts to see my problem. I am using visual studio 2010.
this the link  

https://www.dropbox.com/s/drw54dho1a13tl8/AlbahraniNetwork2.rar
Abdullah Abdulkarim

ASKER
what does mean "use the immediate window ""?
UnifiedIS

The Immediate window is a debugging tool within Visual Studio.  While you have the code stopped (with the breakpoint you set), you can query the current values of variables or even change them. In your screenshot, I could see you have it in the bottom right area where you the Call Stack is showing.  You can see the immediate window as a tab at the bottom.

Click there while you have the code running (and stopped at the breakpoint), then type this:
 ? sc.connectionstring and press enter.  You should now see the current value of the connectionstring property of your sc object.
Within that connection string, you should see a value for the data source which is the path to your database that is being updated.  copy and paste that file into notepad or somewhere you can get it after the application is closed.

Now, hit f5 to continue running the application.  Verify that the record was added to the database by opening access.
Then, close your application.  Now see if the database still exists at the path that you saved.
If that path is invalid when the application is not running, you should not put your database in that location.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Abdullah Abdulkarim

ASKER
I got this  from immediate windows

sc.connectionstring?
'AlbahraniNetwork2.Add.sc' is a 'field' but is used like a 'type'


? sc.connectionstring


'System.Data.OleDb.OleDbConnection' does not contain a definition for 'connectionstring' and no extension method 'connectionstring' accepting a first argument of type 'System.Data.OleDb.OleDbConnection' could be found (are you missing a using directive or an assembly reference?)
Abdullah Abdulkarim

ASKER
I click f5 and it says  the data saved but I could not see it when I check it, it is not exist
Abdullah Abdulkarim

ASKER
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Abdullah Abdulkarim

ASKER
I got this path and it works after I put sc.connectionstring but it does not work with setup, I tried to install the program on the other computer but it says the path is not valid  and make sure the path spelled correctly. I double check and everything fine but the still my problem with path .

The path I got is:
//OleDbConnection sc = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Users\\Abdullah\\Documents\\Visual Studio 2010\\Projects\\AlbahraniNetwork2\\AlbahraniNetwork2\\bin\\Debug\\Database31.accdb");
Abdullah Abdulkarim

ASKER
I need the app  work without have problem with path on any computer, How I can make it?
hjgode

Hello Abdullah15

you are still struggling with theDB setup path. Although it is easy to use the application directory to store and use the accdb file, it may not work after an user installation depending on user access rights.

Please look at http://stackoverflow.com/questions/11131168/deploying-a-desktop-application-with-sql-server-express.

Change all your connection string getters to use one common location, the above uses System.Environment.GetFolderPath(System.Environment.SpecialFolder.CommonApplicationData).

regards

Josef
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Abdullah Abdulkarim

ASKER
hey Josef , How are you?
it may not work after an user installation depending on user access rights.

How I can make it work after user installation? I know my app is so simple but if I did not solve my problem I could not improve to make efficiency app and good design. I am keeping search for long time.
ASKER CERTIFIED SOLUTION
hjgode

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
hjgode

One note:

As we are always copying the database file from AlbrahaniNetwork2 project dir changes made to the database made during the program is running are made to the AppData database file only! Changes will not be reflected back to the Project's dir database file!
If you need another solution, you have to copy the database file back from AppData dir to the Project dir or always use the AppData database file (which means you remove the database file from the Project or reference the AppData database file).

Keeping a template database file in Project dir and copying has the advantage, that your user's (and your development) will always start with a fresh database. If you want to check changes, just open the AppData dir database file.
Abdullah Abdulkarim

ASKER
when I run  the app I got Error. This is the picture
Error101.jpg
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
hjgode

The postbuild copy command is not entered correctly.

Please check the postbuild command:
copy "$(ProjectDir)Database31.mdb" "$(APPDATA)"

If you need more assistance you should provide more details. Does the error show when you Run the app from VS2010 or ???

Open a cmd window (DOS prompt) and enter "set" to get a list of environment vars. One line should say APPDATA=xyz. What is xyz and your PC?
Abdullah Abdulkarim

ASKER
I Fixed the path and I did not  do setup , I just try to test if the app add the data to database or not. When I compile it and try to add names , I got message says saved but when I check the data it did not save any thing and also I try to do search to see if the name still on the the app while it is running but I did not see anything. Even I try to do setup with to see if it at lease work with the data manually direct from access but it gives me error  with path the path is not match or incorrect. Can I give you my whole project and you just only work on the add button and make it work with setup the I can get the one you fixed and compare it to what I have to know where exactly my problem.  I know this so simple and it should not take that far but I did not now why I still get problems.
regards,
Abdullah
hjgode

Feel free to post the whole solution (including the setup/deployment project). I will take a look.
Or, you setup Teamviewer and I take a look at your computer directly. I would prefer that, as something maybe special on your PC.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.