OleDB w Access - INSERT data does not get saved to .MDB?

csharp , db Tunisia
  • 12 years ago

    I need to know how to save the .MDB database after making an INSERT call. 

    I have an Access database in a .MDB file and am using OleDb to access it. I am able to retrieve data from the table (that I initially populated using the MS Access program), and INSERT data to the table programmatically, but when I close the app and restart, the data I have added is not there.

    I can start the app, click the "Write Tags" button, I will see all the tags I initially put in the Tags table. If I add tags, then click "Write Tags" I see all the tags, plus the ones I've added. When I restart the app, I don't see any of the tags I have added, though, and they don't show up in the .MDB when I view the table in MS Access.

    Thanks for any help.
     

    CODE: 

    this.oleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\\Database.mdb";

    private void btnWriteTags_Click(object sender, EventArgs e)
          {
             OleDbDataReader dataReaderTags = null;
             OleDbCommand commandTags = new OleDbCommand("SELECT * FROM Tags", oleDbConnection1);

             oleDbConnection1.Open();
             try
             {
                dataReaderTags = commandTags.ExecuteReader();


                while (dataReaderTags.Read())
                {
                   Console.WriteLine("Tag: " + dataReaderTags["Tag"]);
                }
             }
             finally
             {
                if (dataReaderTags != null)
                {
                   dataReaderTags.Close();
                }
                if (oleDbConnection1 != null)
                {
                   oleDbConnection1.Close();
                }
             }       
          }

    private void AddTag(string tag)

          {
             UpdateTags();

             if (!tags.Contains(tag))
             {
                try
                {
                   oleDbConnection1.Open();

                   OleDbCommand insertCommand = new OleDbCommand(
                      "INSERT INTO Tags (Tag) values (@TagValue)", oleDbConnection1);

                   OleDbParameter insertParameter = new OleDbParameter();
                   insertParameter.ParameterName = "@TagValue";
                   insertParameter.Value = tag;

                   insertCommand.Parameters.Add(insertParameter);

                   insertCommand.ExecuteNonQuery();
                }
                finally
                {
                   if (oleDbConnection1 != null)
                   {
                      oleDbConnection1.Close();
                   }
                }
             }
             else
             {
                MessageBox.Show("Tags database table already contains tag: " + tag);
             }
          }


    private void UpdateTags()
          {
             OleDbDataReader dataReaderTags = null;
             OleDbCommand commandTags = new OleDbCommand("SELECT * FROM Tags", oleDbConnection1);

             oleDbConnection1.Open();
             try
             {
                dataReaderTags = commandTags.ExecuteReader();

                tags.Clear();
                while (dataReaderTags.Read())
                {
                   tags.Add(dataReaderTags["Tag"].ToString());
                }
             }
             finally
             {
                if (dataReaderTags != null)
                {
                   dataReaderTags.Close();
                }
                if (oleDbConnection1 != null)
                {
                   oleDbConnection1.Close();
                }
             }
          }


  • 12 years ago

     Hi,

     

    Try this code, I have used TextBoxes to enter the values from the user:

    ______________________________________________________________________________________________________________

     

        Dim da As New OleDbDataAdapter
        Dim cn As New OleDbConnection
        Dim ds As New DataSet
        Public dr As DataRow
        'Public cb As OleDbCommandBuilder
        Dim cm As CurrencyManager
        Dim cmd As New OleDbCommand



        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

            cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Nwind.mdb;Persist Security Info=False"
            da = New OleDbDataAdapter("select * from Shippers", cn)
            cb = New OleDbCommandBuilder(da)
            da.Fill(ds)

            DataGridView1.DataSource = ds.Tables(0)

            cm = CType(BindingContext(DataGridView1.DataSource), CurrencyManager)
            TextBox1.DataBindings.Add("text", ds.Tables(0), ds.Tables(0).Columns(0).ColumnName)
            TextBox2.DataBindings.Add("text", ds.Tables(0), ds.Tables(0).Columns(1).ColumnName)
            TextBox3.DataBindings.Add("text", ds.Tables(0), ds.Tables(0).Columns(2).ColumnName)

            dr = ds.Tables(0).NewRow

        End Sub

    'Code for inserting a new DataRow

        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
           
            cn.Open()

            dr.Item(0) = TextBox1.Text
            dr.Item(1) = TextBox2.Text
            dr.Item(2) = TextBox3.Text
            ds.Tables(0).Rows.Add(dr)

            da.Update(ds.Tables(0))
            da.Update(ds)
            ds.AcceptChanges()

            cn.Close()

        End Sub

     

     Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

                 cmd.CommandText = "update Shippers set ShipperID=  " & TextBox1.Text & ",CompanyName=" & TextBox2.Text & ",Phone=" & TextBox3.Text & ";"

            da.Update(ds)

        End Sub

    ______________________________________________________________________________________________________________

     

     Hope this helps.

    ______________________________________________________________________________________________________________

    Regards,

    Vinay

    ComponentOne LLC.

    www.componentone.com

     

  • 12 years ago

    Where exactly in your code are you calling AddTag to do the insert into the table? 

     

  • 12 years ago

    Hey guys -- I figured out the isssue. I had the .mdb under source control (it was checked out, but still wouldn't get updated). I removed it from source control and the database saves automatically. 

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“My definition of an expert in any field is a person who knows enough about what's really going on to be scared.” - P. J. Plauger