Adding new Row to DataSet NULL issue - Page 2

[Mike Askew]

I've been a noob.

The issue is that we are not assigning a table name and so therefore it wouldnt find the table with the old code.

You can assign the table in the dataset a name in the radiobutton code if you want using DataSet.Tables[0].TableName and then the code in the save can be reverted and should work :)

If I remember correctly, for future reference, you can also give tables names when instanciating them directly inside the dataset and so can probably do this on the line where you add the table to the dataset. Try adding a , after the table name and see if any of the overloads intellisense gives (assuming your using visual studio which im 99.9% sure you are) there might be one to assign a table name there.

[sundog1]

Hi Mikey,

That's worked. I added the following code:
cashCustomersTable.TableName = "cashCustomers";

It adds the new user the the grid and also the back-end database! Wh00!

However, As The Customers are linked to the gridview below. If I scroll through my customers and see all there orders then decide to click 'New Customer' and add the details... When clicking the Save Button it error's stating the following problem:

Missing the DataColumn 'QTY' in the DataTable 'cashCustomers' for the SourceColumn 'QTY'.

This looks like its for some reason trying to reference to datagridview table which holds all the orders?

I'm guessing there maybe a way of Clearing the datagridview2 which holds these orders on 'New Account Click action'?

[Mike Askew]

Think there is a .Clear() on the DGV or something like that.

It is also worth noting your not using the datagrid tables again for the other database querying method :p forgot to mention earlier!

[sundog1]

Hi Mikey,

I've tried lots of ways to clear the table DGV and according to a lot of write-ups the easiest way would be to clear the DataSet but that seems to be having no effect what-so-ever.

Code I'm Running is:

       private void btnSaveAcc_Click(object sender, EventArgs e)
        {

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

            cashOrdersDS.Tables[0].Clear();

            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");
        }
    }
}

I've alos attached a screenshot of the error and another screenshot showing the datasource bound to the DGV2.

[sundog1]

Looking into this a bit more I think part of the problem maybe becuase I have a relationship between the 2 tables within the Database. The reason I think this is becuase 'QTY' is the first Column after the standard 'ID' in the cashOrders Table.

[sundog1]

HI Mikey, I'm 100% sure it's something to do with the relationship between the two tables.. I have enclosed a screenshot of Access to show you.

Currently trying to figure out how to break the relationship so I can add a new account without the system trying to look for a value for the QTY field..

There must be a way?!

[Mike Askew]

sorry for the delayed reply I was driving home from work for the last 2 n a bit hours.

Umm datasets dont take note of relationships unless you manually declare them within a dataset's tables using a relationship object and adding it.

It is more likely to be something to do with how the two lists query off each others values.

[sundog1]

HI Mikey,

not a problem about the late reply, I just started to build the other buttons and form parts whilst I tried to figure this issue out.

when you say "something to do with how the two lists query off each others values"

I am guessing you are referring to the SQL Query which has an INNER JOIN inside it?

Code:

       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;
            }

If so, how to I go about countr-acting this so it doesn't go looking for a cashOrders Row?

[Mike Askew]

Hmm I'm not exactly sure to be honest at the moment.

[sundog1]

Hi Mike

Not to worry.. If you or anyone else can think of something that would be awesome.

I'll keep breaking and fixing it until I stumble across something :)