Hi Guys...

I seem to be having an issue when trying to create a new row in a Dataset. Below is the following Connection Details to the Access DB.

        private void radioButton3_CheckedChanged(object sender, EventArgs e)
        {
            cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
            cashCustomerDS = new DataSet();
            cashcTable = new DataTable();
            cashCustomerDS.Tables.Add(cashcTable);


            cashCustom.Open();
            //cashCustom.Close();

            string sql = "SELECT * FROM cashCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(sql, cashCustom);
            cashDA.Fill(cashcTable);


            foreach (DataRow myRow in cashcTable.Rows)
            {
                listView1.Items.Add(myRow[0].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[1].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[4].ToString());

                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[2].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[3].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[5].ToString());
                listView1.Items[listView1.Items.Count - 1].SubItems.Add(myRow[6].ToString());

            }

As you can see from above it connects to the database and Selects all from cashCustomers and lists the items out in a list view.

Now... Here is the code for the Save button.

      private void button1_Click(object sender, EventArgs e)
        {
            if (cashCustomerDS != null && cashCustomerDS.Tables.Count > 0)
            {
                **DataRow dRow = cashCustomerDS.Tables["orders.cashCustomers"].NewRow();**

                dRow[1] = txtAccRef.Text;
                dRow[2] = txtName.Text;
                dRow[3] = txtAddr1.Text;
                dRow[4] = txtAddr2.Text;
                dRow[5] = txtTown.Text;
                dRow[6] = txtCounty.Text;
                dRow[7] = txtTown.Text;


                cashCustomerDS.Tables["cashCustomers"].Rows.Add(dRow);

                cashDA.Update(cashCustomerDS, "cashCustomers");

                MessageBox.Show("Cash Account Added");

        }

Data should be pulled from the Linked txt boxes placed on a new row in the dataset and then using the dataAdapter the cashCustomers table should be updated.

Once I input new details into said text boxes the syste bugs out on the high-lighted row above stating the following:

NullReferenceException was Unhandled
Object reference not set to an instance of an object

I'm guessing its because the system believes the Dataset is blank?

any helkp would be great. Thanks in advance.

Mark.

Its not to do with the fact the table your making the new row from is referred to as orders.cashCustomers is it?

Your then adding the row to cashCustomers.

Edited 4 Years Ago by Mike Askew

Hi Mikey,

I'm afraid not, that was test to see if it needed the name of the datbase aswell but still not working..

I dont know if the Dataset gets empited once it fills a Data table? Is that where I could be going wrong?

Shouldnt do, might be worth dropping a break point in the save method and having a look at what the dataset actually contains.

Hmm.. your right...

I added an ELSE clause which should show a message box.

When setting a break point on the IF statement it and then "Stepping into" it jumps straight to the "Nothing in here" message box"

If thats the case and the DS is empty, what is the best way/easiest way to re-populate it?

Edited 4 Years Ago by sundog1: spelling.

Ahh Hold up... I might be wrong.

If I select the radio button in debug mode which populates the listview with data from the DS and then rung the "stepping into" it tries to add the row but comes up with the error. I'm guessing this means that there is data in the DS.

The reason I think this is becuase if i run the debug and do not click the 'radio button' to populate the listview and then run "stepping into" it jumps direct to my message box stating "there is nothing here".

I hope that makes sense. :S

Kind of makes sense.

Yes you would have to run the listview populate before you could save because this is where all the tables are added.

Until this is done the dataset would appear null.

That the right understanding?

Correct Mikey

However, When all that is done, this is when I get the NULL message. Which Does not really make any sense.

Hi Mikey,

I've uploaded a screenshot for this isue to show you where I think the Null value could be causing a problem.

Attachments DaniWeb1.PNG 72.42 KB

dRow would be null as its not past the instanciation line (the one its exceptioning on).

I believe you will find the dataset table your doing the .NewRow() on to instanciate the dRow will be the null object.

Hi Mikey,

So you think the cashCustomersDS.Tables["cashCustomers"] no longer exists? in the casCustomerDS?

if thats so, how come it read the IF statment and follows through without reading the IF statement and then Jumping to the 'ELSE' statement.

I completely missed the If statement sorry.

I happen to be using that identical line structure in the code im working on currently and declaring the datarow and instanciating with the .NewRow() works fine for me.

In the watch window look at cashCustomerDS.Tables[0] see what it contains.

Hi Mikey,

Ok.. For some reason I had 2

private void listView1_SelectedIndexChanged(object sender, EventArgs e)

I pasted it into the other 1 and it worked fine! I now have the comment box appearing telling me the tag number! Wh00!

Thats another step closer! :)

God knows what I have 2 Private voices exactly the same! :S

Edited 4 Years Ago by sundog1: oops.

Ahhh Mikey... This is the wrong thread..

The other ones fixed.. this is still an issue :(

Hi Mike,

On this Issue I set the Debugger at the 'Save Button' position and stepped through bit by bit. As soon as the program reached the following code:

DataRow dRow = cashCustomerDS.Tables["cashCustomers"].NewRow();

The 'Watch' Box was completly empty. and the next step brought up the error. So I'm guessing for some reason the Dataset at this point has no data?

Thanks again for your help on this.

Hi Mikey,

Even though I have now re-written this thing from the ground up using Gridview instead of list view and made some great advances I'm still having the same problem with the bloody Save button and that infernal Dataset line!

Below is the new code: (I even added the connection the database AGAIN to try and make sure it got filled!)

      private void btnSaveAcc_Click(object sender, EventArgs e)
        {
            //System.Data.OleDb.OleDbCommandBuilder cbp;
            //cbp = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

            //String cashCustomerSQL = "SELECT * FROM cashCustomers";
            //cashDA = new System.Data.OleDb.OleDbDataAdapter(cashCustomerSQL, cashCustom);
            //cashDA.Fill(cashCustomersTable);

            cashCustomersDS.Tables[0].Rows.Add();
            dataGridView1.DataSource = cashCustomersDS;
            dRow = cashCustomersDS.Tables["cashCustomers"].NewRow();
            dRow[0] = txtBoxAccRef.Text;
            dRow[1] = txtBoxName.Text;
            dRow[2] = txtBoxAddr1.Text;
            dRow[3] = txtBoxAddr2.Text;
            dRow[4] = txtBoxTown.Text;
            dRow[5] = txtBoxPostCode.Text;

            cashCustomersDS.Tables["cashCustomers"].Rows.Add(dRow);




            MessageBox.Show("Added");

Screenshot of Error is included. I think I've used the watch window correctly and its showing nowt. Diddly Squat even when I make it watch something.

Attachments Capture9.PNG 40.04 KB

Can you screenshot the watch window at line 10 of the above code block.

In the watch window type cashCustomersDS.Tables.Count (will be a one line entry) and also cashCustomerDS.Tables[0].Rows.Count (again a one line response).

Hi Mikey,

It seems to me that the DataSet is Null??

and Does not exisit in current context?

Attachments Capture10.PNG 64.32 KB

Yup confirms what I thought it would be.

The dataset is completely null at time of call.

It doesnt exist in the current context because there your looking for an exact object which obviously doesnt exist as the dataset it is within is null :)

Hmm... So I guess I have to re-fill the dataset?

by calling a connection to the database again?

Hmm it depends upon the execution order.

Im guessing the DataSet is filled at a specific action? In which case you could technically fill the DataSet on form load and store it as a form level variable so that it can be accessed by any method?

This would mean it would be filled when you go to use it as form level variables arent disposed of until the form is closed (or you tell it to dispose of itself).

Good morning Mikey,

I thought it might be useful to see my NEWBIE CODE in all its glory to give you some idea on what it looks like, hopefully from there we would be able to see the problem with the 'Save Button' not working..

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;

namespace CardIndex_v2_2012
{
    public partial class Form1 : Form
        {

        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();

        //Access Database Adapater Information//

        System.Data.OleDb.OleDbConnection cashCustom = new System.Data.OleDb.OleDbConnection();
        System.Data.OleDb.OleDbDataAdapter cashDA = new System.Data.OleDb.OleDbDataAdapter();
        System.Data.OleDb.OleDbConnection con;

        DataSet cashCustomersDS;
        DataTable cashCustomersTable;

        DataSet cashOrdersDS;
        DataTable cashOrdersTable;

        DataSet printedCustomersDS;
        DataTable printedOrdersTable;



        public Form1()
        {
            InitializeComponent();

            //Opens Line 50 Connection//
            adoConn.Open("SageLine50v17", "Manager", "", 0);

                        adoRS = adoConn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, null, System.Reflection.Missing.Value);

            while (!(adoRS.EOF))
            {
                comboBox1.Items.Add(adoRS.Fields["TABLE_NAME"].Value.ToString());

                adoRS.MoveNext();
            }

            adoRS.Close();

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Code Below Disables all Txt Boxes from Being Selected until a new account is ready to be inputted//
            txtBoxAccRef.Enabled = false;
            txtBoxAddr1.Enabled = false;
            txtBoxAddr2.Enabled = false;
            txtBoxCounty.Enabled = false;
            txtBoxName.Enabled = false;
            txtBoxPostCode.Enabled = false;
            txtBoxTown.Enabled = false;

            //Access Database Connection Infomation//
            con = new System.Data.OleDb.OleDbConnection();
            con.ConnectionString = "Microsoft.Jet.OLEDB.4.0;DSN=SageLine50v 12";
            con.Open();

            //Tests Connection on Form load//
            MessageBox.Show("Connection to Database Open");


        }

        private void radioBtnCash_CheckedChanged(object sender, EventArgs e)
        {
            //Following Code loads CashCustomers data Table from Database and puts into dataGridView1//
            cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
            cashCustomersDS = new DataSet();
            cashCustomersTable = new DataTable();
            cashCustomersDS.Tables.Add(cashCustomersTable);

            cashCustom.Open();
            cashCustom.Close();


            String cashCustomerSQL = "SELECT * FROM cashCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(cashCustomerSQL, cashCustom);
            cashDA.Fill(cashCustomersTable);

            dataGridView1.DataSource = cashCustomersTable;
            dataGridView1.Columns["CashAddress5"].Visible = false;
            dataGridView1.Columns["CashAddress3"].Visible = false;
            dataGridView1.Columns["CashAddress2"].Visible = false;
            dataGridView1.Columns["CashAddress1"].Visible = false;

        }

        private void radioGroupBox_Enter(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            //Checks to see which radio button is checked before running the SQL Scripts to populate the datagridviews//
            if (radioBtnCash.Checked == true)
            {

                //Below selects data from the Current Selected Row, finds out the cell value an populates the current text box//
                txtBoxAccRef.Text = dataGridView1.SelectedCells[0].Value.ToString();
                txtBoxName.Text = dataGridView1.SelectedCells[1].Value.ToString();
                txtBoxAddr1.Text = dataGridView1.SelectedCells[2].Value.ToString();
                txtBoxAddr2.Text = dataGridView1.SelectedCells[3].Value.ToString();
                txtBoxTown.Text = dataGridView1.SelectedCells[4].Value.ToString();
                txtBoxCounty.Text = dataGridView1.SelectedCells[5].Value.ToString();
                txtBoxPostCode.Text = dataGridView1.SelectedCells[6].Value.ToString();

                //////////Following code selects the row in the first grid, selects the ID, Stores it and runs SQL to match ID to populate 2nd dataGrid////////////

                //Selects the Row ID and stores in a INT Variable//
                DataGridViewRow row = dataGridView1.SelectedRows[0];
                Int64 CustomerID = Convert.ToInt64(row.Cells[0].Value);

                //Reconnects to the database//
                System.Data.OleDb.OleDbCommandBuilder cb;
                cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

                //Organises the DataSets and Datatables//
                cashOrdersDS = new DataSet();
                cashOrdersTable = new DataTable();
                cashOrdersDS.Tables.Add(cashOrdersTable);

                //Opens the Connection//
                cashCustom.Open();
                cashCustom.Close();

                //Takes the Stored SQL IN the String Vairable and uses the DataAdapter to run SQL on database connection//
                String cashOrdersSQL = "SELECT QTY, Description, Supplier, Date, Cost, Sell from cashOrders INNER JOIN cashCustomers ON cashOrders.cashAccountRef_FKID=cashCustomers.CashAccRef WHERE cashCustomers.CashAccRef = " + CustomerID.ToString();
                cashDA = new System.Data.OleDb.OleDbDataAdapter(cashOrdersSQL, cashCustom);

                //Fills the Datatable with all the requires data//
                cashDA.Fill(cashOrdersTable);

                //Populates the datagridview2//
                dataGridView2.DataSource = cashOrdersTable;
            }

            else

            {
                //Below selects data from the Current Selected Row, finds out the cell value an populates the current text box//
                txtBoxAccRef.Text = dataGridView1.SelectedCells[0].Value.ToString();
                txtBoxName.Text = dataGridView1.SelectedCells[1].Value.ToString();
                txtBoxAddr1.Text = dataGridView1.SelectedCells[2].Value.ToString();
                txtBoxAddr2.Text = dataGridView1.SelectedCells[3].Value.ToString();
                txtBoxTown.Text = dataGridView1.SelectedCells[4].Value.ToString();
                txtBoxCounty.Text = dataGridView1.SelectedCells[5].Value.ToString();
                txtBoxPostCode.Text = dataGridView1.SelectedCells[6].Value.ToString();

                DataGridViewRow row = dataGridView1.SelectedRows[0];
                Int64 CustomerID = Convert.ToInt64(row.Cells[0].Value);

                System.Data.OleDb.OleDbCommandBuilder cb;
                cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

                cashOrdersDS = new DataSet();
                cashOrdersTable = new DataTable();
                cashOrdersDS.Tables.Add(cashOrdersTable);

                cashCustom.Open();
                cashCustom.Close();

                String PrintedOrdersSQL = "SELECT QTY, Description, Supplier, Date, Cost, Sell from cashOrders INNER JOIN PrintedCustomers ON cashOrders.cashAccountRef_FKID=PrintedCustomers.PrintedAccRef WHERE PrintedCustomers.PrintedAccRef = " + CustomerID.ToString();
                cashDA = new System.Data.OleDb.OleDbDataAdapter(PrintedOrdersSQL, cashCustom);
                cashDA.Fill(cashOrdersTable);


                dataGridView2.DataSource = cashOrdersTable;
            }


        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {      
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {


        }

        private void radioBtnPrinted_CheckedChanged(object sender, EventArgs e)
        {
            //Following Code loads PrintedCustomers data Table from Database and puts into dataGridView1//
            System.Data.OleDb.OleDbCommandBuilder cbp;
            cbp = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

            printedCustomersDS = new DataSet();
            printedOrdersTable = new DataTable();
            printedCustomersDS.Tables.Add(printedOrdersTable);

            cashCustom.Open();
            cashCustom.Close();

            String printedCustomersSQL = "SELECT * FROM PrintedCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(printedCustomersSQL, cashCustom);
            cashDA.Fill(printedOrdersTable);

            dataGridView1.DataSource = printedOrdersTable;
            dataGridView1.Columns["PrintedAddress5"].Visible = false;
            dataGridView1.Columns["PrintedAddress3"].Visible = false;
            dataGridView1.Columns["PrintedAddress2"].Visible = false;
            dataGridView1.Columns["PrintedAddress1"].Visible = false;



        }

        private void btnEditAcc_Click(object sender, EventArgs e)
        {
            //Enables to Text Boxes for any Edits that are needed//
            txtBoxAccRef.Enabled = true;
            txtBoxAddr1.Enabled = true;
            txtBoxAddr2.Enabled = true;
            txtBoxCounty.Enabled = true;
            txtBoxName.Enabled = true;
            txtBoxPostCode.Enabled = true;
            txtBoxTown.Enabled = true;
        }

        private void btnNewAcc_Click(object sender, EventArgs e)
        {
            //Enables Text Boxes//
            txtBoxAccRef.Enabled = true;
            txtBoxAddr1.Enabled = true;
            txtBoxAddr2.Enabled = true;
            txtBoxCounty.Enabled = true;
            txtBoxName.Enabled = true;
            txtBoxPostCode.Enabled = true;
            txtBoxTown.Enabled = true;

            //Clears Text Boxes//
            txtBoxAccRef.Clear();
            txtBoxAddr1.Clear();
            txtBoxAddr2.Clear();
            txtBoxCounty.Clear();
            txtBoxName.Clear();
            txtBoxPostCode.Clear();
            txtBoxTown.Clear();
        }

        private void btnSaveAcc_Click(object sender, EventArgs e)
        {

            DataRow DR = cashCustomersTable.NewRow();

            DR["CashAccRef"] = Int64.Parse(txtBoxAccRef.Text);
            DR["CashName"] = txtBoxName.Text.ToString();
            DR["CashAddress1"] = txtBoxAddr1.Text.ToString();
            DR["CashAddress2"] = txtBoxAddr2.Text.ToString();
            DR["CashTown"] = txtBoxTown.Text.ToString();
            DR["CashAddress3"] = txtBoxCounty.Text.ToString();
            DR["CashAddress5"] = txtBoxPostCode.Text.ToString();

            cashCustom.Open();

            cashCustomersDS.Tables["cashCustomers"].Rows.Add(DR);

            cashCustom.Close();

            MessageBox.Show("Added");

Ha-ha.. Oops! I didnt see your reply before I posted all of that...

Sorry about that.

The Dataset I beleive gets filled when selecting the 'Cash Radio' Button.

Code Below:

 private void radioBtnCash_CheckedChanged(object sender, EventArgs e)
{
//Following Code loads CashCustomers data Table from Database and puts into dataGridView1//
cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
cashCustomersDS = new DataSet();
cashCustomersTable = new DataTable();
cashCustomersDS.Tables.Add(cashCustomersTable);
cashCustom.Open();
cashCustom.Close();
String cashCustomerSQL = "SELECT * FROM cashCustomers";
cashDA = new System.Data.OleDb.OleDbDataAdapter(cashCustomerSQL, cashCustom);
cashDA.Fill(cashCustomersTable);
dataGridView1.DataSource = cashCustomersTable;
dataGridView1.Columns["CashAddress5"].Visible = false;
dataGridView1.Columns["CashAddress3"].Visible = false;
dataGridView1.Columns["CashAddress2"].Visible = false;
dataGridView1.Columns["CashAddress1"].Visible = false;
}

G'morning,

Ok while im reading the code, points to note for improvement:

  • Form1 Class

    • You can use one dataset to store multiple tables, thus eliminating the need for three to be declared, unless you have other reasons for doing this.
  • Form1_Load

    • You do not shut the connection after testing it to the database.
  • radioBtnCash_CheckedChanged

    • Your opening and closing the cashCustom connection and then running the SQL query for some reason (surprised this hasn't errored for you, run the SQL on an open connection to the DB, this could explain the empty dataset later on when its expected to be full?).

    • You add the cashCustomerTable to the dataset and then continue using it instead of your new table in the dataset to store data. Not much point adding it to the dataset if it wont be referred to, which it is then referred to in the save method where you'll be adding a row to a table with no data in (even when we did get the SQL to work).

  • btnSaveAcc_Click

    • Your referring to the cashCustomersTable and not the table in the dataset to create the row, you can do it from the dataset table also.

    • When adding a row to a table in the dataset, this will not push it through to the database and so the open connection is not required for that line. Instead you will then need to call a method afterwards from the dataadapter which is .Update(*DataSetNameForUpdate*, *"NameOfTableInDatasetToUpdate"*)

From the layout of the code it seems you cannot call the update method before activating the radioBtnCash_CheckedChanged method, is this intentional (obviously I don't know fully how you use the program) else you can move the code in that method to a new one and simply call the method from the Form1_Load and this will ensure the dataset is always populated.

You could even just keep the databind to datagrid in the radio button method so that the data does not appear until its changed even though the data is already pulled down. This may cause issue later if you work with live changing data while the program is running as obviously you'd then need to update the data frequently through new methods.

Hopefully that might help a bit, give me a shout if anything is unclear!

Edit: missed the second post while writing this but doesnt have any effect on whats written above (spent forever writing it haha xD)

Edited 4 Years Ago by Mike Askew

HI Mikey,

First of all thank you for you long in-depth reply. I'm always wanting to lear how to adjust my code and try to make it better and eaiser for people to understand. I have taken those points on board.

I've taken out the cashCustom.Close(); option as requested and looking through the data I had lots and lots of OPEN & Close so have removed most of them. The only one's I have left are the Open connection and close connection on the radioBtnCash.

I have adjusted the Save Button method to use the Command Builder and as reconnection to the database and have also adjusted the DataRow Line which now calls the 'CashCustomerDS.Tables["cashCustomers"].NewRow(); -- Is that right or have I got it wrong?

When stepping through the system still seems to bug out on this line saying it's null. (I have attached a screenshot for you to see)

I have closed up most of the methods that I dont think are causing and issue to make the code shorter below:

Code:

namespace CardIndex_v2_2012
{
    public partial class Form1 : Form
        {

        private ADODB.Connection adoConn = new ADODB.Connection();
        private ADODB.Recordset adoRS = new ADODB.Recordset();

        //Access Database Adapater Information//

        System.Data.OleDb.OleDbConnection cashCustom = new System.Data.OleDb.OleDbConnection();
        System.Data.OleDb.OleDbDataAdapter cashDA = new System.Data.OleDb.OleDbDataAdapter();

        //Data Sets and Data Tables//
        DataSet cashCustomersDS;
        DataTable cashCustomersTable;

        DataSet cashOrdersDS;
        DataTable cashOrdersTable;

        DataSet printedCustomersDS;
        DataTable printedOrdersTable;



        public Form1()
        {
            InitializeComponent();

            //Opens Line 50 Connection//
            adoConn.Open("SageLine50v17", "Manager", "", 0);

                        adoRS = adoConn.OpenSchema(ADODB.SchemaEnum.adSchemaTables, null, System.Reflection.Missing.Value);

            while (!(adoRS.EOF))
            {
                comboBox1.Items.Add(adoRS.Fields["TABLE_NAME"].Value.ToString());

                adoRS.MoveNext();
            }

            adoRS.Close();

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            //Code Below Disables all Txt Boxes from Being Selected until a new account is ready to be inputted//
            txtBoxAccRef.Enabled = false;
            txtBoxAddr1.Enabled = false;
            txtBoxAddr2.Enabled = false;
            txtBoxCounty.Enabled = false;
            txtBoxName.Enabled = false;
            txtBoxPostCode.Enabled = false;
            txtBoxTown.Enabled = false;
        }

        private void radioBtnCash_CheckedChanged(object sender, EventArgs e)
        {
            //Following Code loads CashCustomers data Table from Database and puts into dataGridView1//
            cashCustom.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Orders.mdb";
            cashCustomersDS = new DataSet();
            cashCustomersTable = new DataTable();
            cashCustomersDS.Tables.Add(cashCustomersTable);

            cashCustom.Open();

            String cashCustomerSQL = "SELECT * FROM cashCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(cashCustomerSQL, cashCustom);
            cashDA.Fill(cashCustomersTable);

            dataGridView1.DataSource = cashCustomersTable;
            dataGridView1.Columns["CashAddress5"].Visible = false;
            dataGridView1.Columns["CashAddress3"].Visible = false;
            dataGridView1.Columns["CashAddress2"].Visible = false;
            dataGridView1.Columns["CashAddress1"].Visible = false;

            cashCustom.Close();

        }

        private void radioGroupBox_Enter(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {

            //Checks to see which radio button is checked before running the SQL Scripts to populate the datagridviews//
            if (radioBtnCash.Checked == true)
            {

                //Below selects data from the Current Selected Row, finds out the cell value an populates the current text box//
                txtBoxAccRef.Text = dataGridView1.SelectedCells[0].Value.ToString();
                txtBoxName.Text = dataGridView1.SelectedCells[1].Value.ToString();
                txtBoxAddr1.Text = dataGridView1.SelectedCells[2].Value.ToString();
                txtBoxAddr2.Text = dataGridView1.SelectedCells[3].Value.ToString();
                txtBoxTown.Text = dataGridView1.SelectedCells[4].Value.ToString();
                txtBoxCounty.Text = dataGridView1.SelectedCells[5].Value.ToString();
                txtBoxPostCode.Text = dataGridView1.SelectedCells[6].Value.ToString();

                //////////Following code selects the row in the first grid, selects the ID, Stores it and runs SQL to match ID to populate 2nd dataGrid////////////

                //Selects the Row ID and stores in a INT Variable//
                DataGridViewRow row = dataGridView1.SelectedRows[0];
                Int64 CustomerID = Convert.ToInt64(row.Cells[0].Value);

                //Reconnects to the database//
                System.Data.OleDb.OleDbCommandBuilder cb;
                cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

                //Organises the DataSets and Datatables//
                cashOrdersDS = new DataSet();
                cashOrdersTable = new DataTable();
                cashOrdersDS.Tables.Add(cashOrdersTable);

                //Opens the Connection//
                //cashCustom.Open();
                //cashCustom.Close();

                //Takes the Stored SQL IN the String Vairable and uses the DataAdapter to run SQL on database connection//
                String cashOrdersSQL = "SELECT QTY, Description, Supplier, Date, Cost, Sell from cashOrders INNER JOIN cashCustomers ON cashOrders.cashAccountRef_FKID=cashCustomers.CashAccRef WHERE cashCustomers.CashAccRef = " + CustomerID.ToString();
                cashDA = new System.Data.OleDb.OleDbDataAdapter(cashOrdersSQL, cashCustom);

                //Fills the Datatable with all the requires data//
                cashDA.Fill(cashOrdersTable);

                //Populates the datagridview2//
                dataGridView2.DataSource = cashOrdersTable;
            }

            else

            {
                //Below selects data from the Current Selected Row, finds out the cell value an populates the current text box//
                txtBoxAccRef.Text = dataGridView1.SelectedCells[0].Value.ToString();
                txtBoxName.Text = dataGridView1.SelectedCells[1].Value.ToString();
                txtBoxAddr1.Text = dataGridView1.SelectedCells[2].Value.ToString();
                txtBoxAddr2.Text = dataGridView1.SelectedCells[3].Value.ToString();
                txtBoxTown.Text = dataGridView1.SelectedCells[4].Value.ToString();
                txtBoxCounty.Text = dataGridView1.SelectedCells[5].Value.ToString();
                txtBoxPostCode.Text = dataGridView1.SelectedCells[6].Value.ToString();

                DataGridViewRow row = dataGridView1.SelectedRows[0];
                Int64 CustomerID = Convert.ToInt64(row.Cells[0].Value);

                System.Data.OleDb.OleDbCommandBuilder cb;
                cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

                cashOrdersDS = new DataSet();
                cashOrdersTable = new DataTable();
                cashOrdersDS.Tables.Add(cashOrdersTable);

                //cashCustom.Open();
                //cashCustom.Close();

                String PrintedOrdersSQL = "SELECT QTY, Description, Supplier, Date, Cost, Sell from cashOrders INNER JOIN PrintedCustomers ON cashOrders.cashAccountRef_FKID=PrintedCustomers.PrintedAccRef WHERE PrintedCustomers.PrintedAccRef = " + CustomerID.ToString();
                cashDA = new System.Data.OleDb.OleDbDataAdapter(PrintedOrdersSQL, cashCustom);
                cashDA.Fill(cashOrdersTable);


                dataGridView2.DataSource = cashOrdersTable;
            }


        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {      
        }

        private void dataGridView1_RowEnter(object sender, DataGridViewCellEventArgs e)
        {


        }

        private void radioBtnPrinted_CheckedChanged(object sender, EventArgs e)
        {
            //Following Code loads PrintedCustomers data Table from Database and puts into dataGridView1//
            System.Data.OleDb.OleDbCommandBuilder cbp;
            cbp = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

            printedCustomersDS = new DataSet();
            printedOrdersTable = new DataTable();
            printedCustomersDS.Tables.Add(printedOrdersTable);

            //cashCustom.Open();
            //cashCustom.Close();

            String printedCustomersSQL = "SELECT * FROM PrintedCustomers";
            cashDA = new System.Data.OleDb.OleDbDataAdapter(printedCustomersSQL, cashCustom);
            cashDA.Fill(printedOrdersTable);

            dataGridView1.DataSource = printedOrdersTable;
            dataGridView1.Columns["PrintedAddress5"].Visible = false;
            dataGridView1.Columns["PrintedAddress3"].Visible = false;
            dataGridView1.Columns["PrintedAddress2"].Visible = false;
            dataGridView1.Columns["PrintedAddress1"].Visible = false;



        }

        private void btnEditAcc_Click(object sender, EventArgs e)
        {
            //Enables to Text Boxes for any Edits that are needed//
            txtBoxAccRef.Enabled = true;
            txtBoxAddr1.Enabled = true;
            txtBoxAddr2.Enabled = true;
            txtBoxCounty.Enabled = true;
            txtBoxName.Enabled = true;
            txtBoxPostCode.Enabled = true;
            txtBoxTown.Enabled = true;
        }

        private void btnNewAcc_Click(object sender, EventArgs e)
        {
            //Enables Text Boxes//
            txtBoxAccRef.Enabled = true;
            txtBoxAddr1.Enabled = true;
            txtBoxAddr2.Enabled = true;
            txtBoxCounty.Enabled = true;
            txtBoxName.Enabled = true;
            txtBoxPostCode.Enabled = true;
            txtBoxTown.Enabled = true;

            //Clears Text Boxes//
            txtBoxAccRef.Clear();
            txtBoxAddr1.Clear();
            txtBoxAddr2.Clear();
            txtBoxCounty.Clear();
            txtBoxName.Clear();
            txtBoxPostCode.Clear();
            txtBoxTown.Clear();
        }

        private void btnSaveAcc_Click(object sender, EventArgs e)
        {

            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

            DataRow DR = cashCustomersDS.Tables["cashCustomers"].NewRow();

            DR["CashAccRef"] = Int64.Parse(txtBoxAccRef.Text);
            DR["CashName"] = txtBoxName.Text.ToString();
            DR["CashAddress1"] = txtBoxAddr1.Text.ToString();
            DR["CashAddress2"] = txtBoxAddr2.Text.ToString();
            DR["CashTown"] = txtBoxTown.Text.ToString();
            DR["CashAddress3"] = txtBoxCounty.Text.ToString();
            DR["CashAddress5"] = txtBoxPostCode.Text.ToString();




            cashCustomersDS.Tables["cashCustomers"].Rows.Add(DR);

            cashDA.Update(cashCustomersDS, "cashCustomers");

            MessageBox.Show("Added");
        }
    }
}
Attachments Capture10.PNG 53.18 KB

With regards to the connection open/close.

The DataSet.Fill() method automatically will open and close the connection with underlying code.

However if you do ever manually open the connection always remember to close it after the database related code has run. (you will need this once you want to update the database when we finally resolve this null dataset issue :D)

Do you always make the radiobutton code to populate the dataset run before trying to run the save method?

Hi Mike, Thanks for the INFO on the DataSet.Fill() issue.. I will keep that in mind.

I think I've made some progess that may help us out here.

The following line:
DataRow DR = cashCustomersDS.Tables["cashCustomers"].NewRow();

I changed to this:
DataRow DR = cashCustomersDS.Tables[0].NewRow();

**and this piece of code: **
cashCustomersDS.Tables["cashCustomers"].Rows.Add(DR);

to this:
cashCustomersDS.Tables[0].Rows.Add(DR);

It somehow populated the Gridview when I clicked Save! Wh00!

However, when stepping through the Debug when it got to this line of code:

cashDA.Update(cashCustomersDS, "cashCustomers");

It came up with the following error. (See Screenshot).

It seems to think It can't find it? but its happy to update the dataGridView with new data??

Code I amended it below:

        private void btnSaveAcc_Click(object sender, EventArgs e)
        {

            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);

            DataRow DR = cashCustomersDS.Tables[0].NewRow();

            DR["CashAccRef"] = Int64.Parse(txtBoxAccRef.Text);
            DR["CashName"] = txtBoxName.Text.ToString();
            DR["CashAddress1"] = txtBoxAddr1.Text.ToString();
            DR["CashAddress2"] = txtBoxAddr2.Text.ToString();
            DR["CashTown"] = txtBoxTown.Text.ToString();
            DR["CashAddress3"] = txtBoxCounty.Text.ToString();
            DR["CashAddress5"] = txtBoxPostCode.Text.ToString();




            cashCustomersDS.Tables[0].Rows.Add(DR);

            cashDA.Update(cashCustomersDS, "cashCustomers");

            MessageBox.Show("Added");
        }
    }
}

One again, thanks for your help so far I really couldn't of got this far without your help :)

Attachments Capture11.PNG 47.89 KB
This article has been dead for over six months. Start a new discussion instead.