Problem wit bindingSource and dbase *.dbf files

[piotr_kast]

I have general problem with bindingSource control In VS 2005 C# professional.

In project I created new Form and:
1. added a BindingSource component named bindingSource1 and DataGridView control named dataGridView1.
2. binding bindingSource1 to data (DataSource), and the DataGridView controls to bindingSource1.
3. in bindingSource1 set properties DataMember to data.
4. adding a BindingNavigator control named bindingNavigator1 to my form.
5. seting the BindingSource property for bindingNavigator1 to bindingSource1.

Program shows data from dataset and I tried delete records. I removed record using button delete from binding navigator. I dataset all working fine but binding source won’t commit any changes in dBase files. I try either Microsoft dBase driver or the newest Visual Fox Pro driver.
I must bind dataSource to the *.dbf data (dbase driver - clipper files).

Can you explain to me what I’m doing wrong? I searched many programmers solution on the Web and I did’t find any answer and this problem drives me crazy.

[williamrojas78]

i am not sure, but i don't think the binding source will do the update to the database, it will just bind the data to the controls.

You will have to update the database using an adapter, etc after the changes to the dataset have been made.

Regards

[piotr_kast]

This I can figured myself. I don't want this type of answer. I think that you even
not to try find the solution. I need a sample how to do it.

I tried this approach what you suggested. Unfortunatelly I still didn't find the
answer to my problem. In this case program says that have no valid delete command.

My question is: how to do valid delete command which when I delete row of data (record) in dataset by the binding navigator it will delete also related record from opened by form clipper database.

Actually my code look like this:

private void InitializeComponent()
        {
            this.components = new System.ComponentModel.Container();
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Form1));
            this.dataGridView1 = new System.Windows.Forms.DataGridView();
            this.binfakturyBindingSource = new System.Windows.Forms.BindingSource(this.components);
            this.dataSet1 = new MyDBF.DataSet1();
            this.bindingNavigator1 = new System.Windows.Forms.BindingNavigator(this.components);
            this.bindingNavigatorAddNewItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorCountItem = new System.Windows.Forms.ToolStripLabel();
            this.toobindingNavigatorDeleteItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorMoveFirstItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorMovePreviousItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorSeparator = new System.Windows.Forms.ToolStripSeparator();
            this.bindingNavigatorPositionItem = new System.Windows.Forms.ToolStripTextBox();
            this.bindingNavigatorSeparator1 = new System.Windows.Forms.ToolStripSeparator();
            this.bindingNavigatorMoveNextItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorMoveLastItem = new System.Windows.Forms.ToolStripButton();
            this.bindingNavigatorSeparator2 = new System.Windows.Forms.ToolStripSeparator();
            this.toolStripSave = new System.Windows.Forms.ToolStripButton();
            this.fakturyTableAdapter = new MyDBF.DataSet1TableAdapters.fakturyTableAdapter();
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.binfakturyBindingSource)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).BeginInit();
            ((System.ComponentModel.ISupportInitialize)(this.bindingNavigator1)).BeginInit();
            this.bindingNavigator1.SuspendLayout();
            this.SuspendLayout();
            // 
            // dataGridView1
            // 
            this.dataGridView1.ColumnHeadersHeightSizeMode = System.Windows.Forms.DataGridViewColumnHeadersHeightSizeMode.AutoSize;
            this.dataGridView1.Location = new System.Drawing.Point(12, 73);
            this.dataGridView1.Name = "dataGridView1";
            this.dataGridView1.Size = new System.Drawing.Size(544, 150);
            this.dataGridView1.TabIndex = 0;
            this.dataGridView1.UserDeletedRow += new System.Windows.Forms.DataGridViewRowEventHandler(this.Form1_Update);
            // 
            // binfakturyBindingSource
            // 
            this.binfakturyBindingSource.DataMember = "faktury";
            this.binfakturyBindingSource.DataSource = this.dataSet1;
            // 
            // dataSet1
            // 
            this.dataSet1.DataSetName = "DataSet1";
            this.dataSet1.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
            // 
            // bindingNavigator1
            // 
            this.bindingNavigator1.AddNewItem = this.bindingNavigatorAddNewItem;
            this.bindingNavigator1.BindingSource = this.binfakturyBindingSource;
            this.bindingNavigator1.CountItem = this.bindingNavigatorCountItem;
            this.bindingNavigator1.DeleteItem = this.toobindingNavigatorDeleteItem;
            this.bindingNavigator1.Items.AddRange(new System.Windows.Forms.ToolStripItem[] {
            this.bindingNavigatorMoveFirstItem,
            this.bindingNavigatorMovePreviousItem,
            this.bindingNavigatorSeparator,
            this.bindingNavigatorPositionItem,
            this.bindingNavigatorCountItem,
            this.bindingNavigatorSeparator1,
            this.bindingNavigatorMoveNextItem,
            this.bindingNavigatorMoveLastItem,
            this.bindingNavigatorSeparator2,
            this.bindingNavigatorAddNewItem,
            this.toobindingNavigatorDeleteItem,
            this.toolStripSave});
            this.bindingNavigator1.Location = new System.Drawing.Point(0, 0);
            this.bindingNavigator1.MoveFirstItem = this.bindingNavigatorMoveFirstItem;
            this.bindingNavigator1.MoveLastItem = this.bindingNavigatorMoveLastItem;
            this.bindingNavigator1.MoveNextItem = this.bindingNavigatorMoveNextItem;
            this.bindingNavigator1.MovePreviousItem = this.bindingNavigatorMovePreviousItem;
            this.bindingNavigator1.Name = "bindingNavigator1";
            this.bindingNavigator1.PositionItem = this.bindingNavigatorPositionItem;
            this.bindingNavigator1.Size = new System.Drawing.Size(571, 25);
            this.bindingNavigator1.TabIndex = 1;
            this.bindingNavigator1.Text = "bindingNavigator1";
            // 
            // bindingNavigatorAddNewItem
            // 
            this.bindingNavigatorAddNewItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.bindingNavigatorAddNewItem.Image = ((System.Drawing.Image)(resources.GetObject("bindingNavigatorAddNewItem.Image")));
            this.bindingNavigatorAddNewItem.Name = "bindingNavigatorAddNewItem";
            this.bindingNavigatorAddNewItem.RightToLeftAutoMirrorImage = true;
            this.bindingNavigatorAddNewItem.Size = new System.Drawing.Size(23, 22);
            this.bindingNavigatorAddNewItem.Text = "Add new";
            this.bindingNavigatorAddNewItem.Visible = false;
            // 
            // bindingNavigatorCountItem
            // 
            this.bindingNavigatorCountItem.Name = "bindingNavigatorCountItem";
            this.bindingNavigatorCountItem.Size = new System.Drawing.Size(36, 22);
            this.bindingNavigatorCountItem.Text = "of {0}";
            this.bindingNavigatorCountItem.ToolTipText = "Total number of items";
            // 
            // toobindingNavigatorDeleteItem
            // 
            this.toobindingNavigatorDeleteItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.toobindingNavigatorDeleteItem.Image = ((System.Drawing.Image)(resources.GetObject("toobindingNavigatorDeleteItem.Image")));
            this.toobindingNavigatorDeleteItem.Name = "toobindingNavigatorDeleteItem";
            this.toobindingNavigatorDeleteItem.RightToLeftAutoMirrorImage = true;
            this.toobindingNavigatorDeleteItem.Size = new System.Drawing.Size(23, 22);
            this.toobindingNavigatorDeleteItem.Text = "Delete";
            // 
            // bindingNavigatorMoveFirstItem
            // 
            this.bindingNavigatorMoveFirstItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.bindingNavigatorMoveFirstItem.Image = ((System.Drawing.Image)(resources.GetObject("bindingNavigatorMoveFirstItem.Image")));
            this.bindingNavigatorMoveFirstItem.Name = "bindingNavigatorMoveFirstItem";
            this.bindingNavigatorMoveFirstItem.RightToLeftAutoMirrorImage = true;
            this.bindingNavigatorMoveFirstItem.Size = new System.Drawing.Size(23, 22);
            this.bindingNavigatorMoveFirstItem.Text = "Move first";
            // 
            // bindingNavigatorMovePreviousItem
            // 
            this.bindingNavigatorMovePreviousItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.bindingNavigatorMovePreviousItem.Image = ((System.Drawing.Image)(resources.GetObject("bindingNavigatorMovePreviousItem.Image")));
            this.bindingNavigatorMovePreviousItem.Name = "bindingNavigatorMovePreviousItem";
            this.bindingNavigatorMovePreviousItem.RightToLeftAutoMirrorImage = true;
            this.bindingNavigatorMovePreviousItem.Size = new System.Drawing.Size(23, 22);
            this.bindingNavigatorMovePreviousItem.Text = "Move previous";
            // 
            // bindingNavigatorSeparator
            // 
            this.bindingNavigatorSeparator.Name = "bindingNavigatorSeparator";
            this.bindingNavigatorSeparator.Size = new System.Drawing.Size(6, 25);
            // 
            // bindingNavigatorPositionItem
            // 
            this.bindingNavigatorPositionItem.AccessibleName = "Position";
            this.bindingNavigatorPositionItem.AutoSize = false;
            this.bindingNavigatorPositionItem.Name = "bindingNavigatorPositionItem";
            this.bindingNavigatorPositionItem.Size = new System.Drawing.Size(50, 21);
            this.bindingNavigatorPositionItem.Text = "0";
            this.bindingNavigatorPositionItem.ToolTipText = "Current position";
            // 
            // bindingNavigatorSeparator1
            // 
            this.bindingNavigatorSeparator1.Name = "bindingNavigatorSeparator1";
            this.bindingNavigatorSeparator1.Size = new System.Drawing.Size(6, 25);
            // 
            // bindingNavigatorMoveNextItem
            // 
            this.bindingNavigatorMoveNextItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.bindingNavigatorMoveNextItem.Image = ((System.Drawing.Image)(resources.GetObject("bindingNavigatorMoveNextItem.Image")));
            this.bindingNavigatorMoveNextItem.Name = "bindingNavigatorMoveNextItem";
            this.bindingNavigatorMoveNextItem.RightToLeftAutoMirrorImage = true;
            this.bindingNavigatorMoveNextItem.Size = new System.Drawing.Size(23, 22);
            this.bindingNavigatorMoveNextItem.Text = "Move next";
            // 
            // bindingNavigatorMoveLastItem
            // 
            this.bindingNavigatorMoveLastItem.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.Image;
            this.bindingNavigatorMoveLastItem.Image = ((System.Drawing.Image)(resources.GetObject("bindingNavigatorMoveLastItem.Image")));
            this.bindingNavigatorMoveLastItem.Name = "bindingNavigatorMoveLastItem";
            this.bindingNavigatorMoveLastItem.RightToLeftAutoMirrorImage = true;
            this.bindingNavigatorMoveLastItem.Size = new System.Drawing.Size(23, 22);
            this.bindingNavigatorMoveLastItem.Text = "Move last";
            // 
            // bindingNavigatorSeparator2
            // 
            this.bindingNavigatorSeparator2.Name = "bindingNavigatorSeparator2";
            this.bindingNavigatorSeparator2.Size = new System.Drawing.Size(6, 25);
            // 
            // toolStripSave
            // 
            this.toolStripSave.DisplayStyle = System.Windows.Forms.ToolStripItemDisplayStyle.None;
            this.toolStripSave.ImageTransparentColor = System.Drawing.Color.Magenta;
            this.toolStripSave.Name = "toolStripSave";
            this.toolStripSave.Size = new System.Drawing.Size(23, 22);
            this.toolStripSave.Text = "Save";
            this.toolStripSave.Click += new System.EventHandler(this.toolStripSave_Click);
            // 
            // fakturyTableAdapter
            // 
            this.fakturyTableAdapter.ClearBeforeFill = true;
            // 
            // Form1
            // 
            this.AutoScaleDimensions = new System.Drawing.SizeF(6F, 13F);
            this.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font;
            this.ClientSize = new System.Drawing.Size(571, 273);
            this.Controls.Add(this.bindingNavigator1);
            this.Controls.Add(this.dataGridView1);
            this.Name = "Form1";
            this.Text = "Form1";
            this.Load += new System.EventHandler(this.Form1_Load);
            ((System.ComponentModel.ISupportInitialize)(this.dataGridView1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.binfakturyBindingSource)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.dataSet1)).EndInit();
            ((System.ComponentModel.ISupportInitialize)(this.bindingNavigator1)).EndInit();
            this.bindingNavigator1.ResumeLayout(false);
            this.bindingNavigator1.PerformLayout();
            this.ResumeLayout(false);
            this.PerformLayout();

        }

        #endregion

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

namespace MyDBF
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'dataSet1.faktury' table. You can move, or remove it, as needed.
            this.fakturyTableAdapter.Fill(this.dataSet1.faktury);

        }

        private void toolStripSave_Click(object sender, EventArgs e)
        {
            try
            {
                this.Validate();
                this.binfakturyBindingSource.EndEdit();
                this.fakturyTableAdapter.Update(this.dataSet1.faktury);
                MessageBox.Show("Update successful");
            }
            catch (System.Exception ex)
            {
                MessageBox.Show("Update failed");
            }
        }

        private void Form1_Update(object sender, DataGridViewRowEventArgs e)
        {
            this.fakturyTableAdapter.Update(this.dataSet1.faktury);
        }
    }
}

[williamrojas78]

If you can figure it out yourself how come you have not? And you are right, i am not trying to find the solution - because i dont need it; the one that needs it is you.

but here is the solution anyways, and i dont think you research that much.

this is for a sqlce database, but it should be the smae. you are basically missing the commands to maniuplate the database. use the command builder to do that.

SqlCeCommandBuilder builder = new SqlCeCommandBuilder(adapter);
                adapter.UpdateCommand = builder.GetUpdateCommand();
                adapter.InsertCommand = builder.GetInsertCommand();
                adapter.DeleteCommand = builder.GetDeleteCommand();

[piotr_kast]

OK. When manually:
1. Set Properies bindingSource.Filter to specific record from database
2. In Solution Explorer set Dataset generated by properties bindingSource.DataSource
3. In properties dataAdapter.DeleteCommand.CommandText set SQLString "DELETE from [name of my base]" by Query Builder
4. Click button "Execute Query" in Query Builder

I get desire efect. I want do this programatically. In filter I had two parameters which I get from the record which I had to delete. I am newbe in SQL and I need step-by step instruction how to do it by code. So far I tried:

OdbcConnection conn = new OdbcConnection();
            conn.ConnectionString = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\Faktury\Zbiory";
            conn.Open();

            mycomm = new SqlCommand();

            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter();
                SqlCommand command1 = new SqlCommand(command_delete);

                bindingSource1.Filter = command;
                adapter.DeleteCommand = command1;

                adapter.DeleteCommand.ExecuteNonQuery();
            }
            catch (Exception xcp)
            {
                //	Catch any errors and show the error message
                MessageBox.Show(xcp.ToString());
            }
            finally
            {
                //	Close the connection
                conn.Close();
            }

When program executes:

adapter.DeleteCommand.ExecuteNonQuery();

I received a message:
ExecuteNonQuery: Property Connection has not been initialized.

What I'm doing wrong? I don't fully understand what is going on behind the scenes of the program.

[piotr_kast]

Finally, I find the solution for my problem:

private void toobindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {
            string connectionString = @"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=c:\Faktury\Zbiory";
            OdbcConnection thisConnection = new OdbcConnection(connectionString);
            
            thisConnection.Open();
            
            OdbcCommand cmd = new OdbcCommand();
            cmd.Connection = thisConnection;
            cmd.CommandText = command_delete;
            
            //close the connection after executing the command
            OdbcDataReader rd = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            
            //what is happening here, why are you closing the reader just after opening it
            rd.Close();
        }

I think that maybe ususefull for someone!