Deleting a row from a Grid and Saving back to database. - Page 2

[Mike Askew]

G'dammit, back to playing in visual studio till something works :D

[Mike Askew]

Oh Oh Oh OH.

I think it is not submitting the changes to the database. When you switch account selection you re-run all the SQL queries for population and so therefore the dataset would revert to its old data if the database was not updated.

Can you update a row and then check in the database whether it changes before you move off the row. See if what I expect it to be is right.

[sundog1]

Good morning Mike,

I've just amended the Description field of a Item, Updated it and the code ran saying 'updated'.

Checked the .MDB file to see if changes were there and they were not.

[Mike Askew]

Yeah I suspected that would be the case.

Try the following with the same test:

private void btnOrderEdit_Click(object sender, EventArgs e)
        {
            int i, j;
            i = dataGridView2.CurrentCell.RowIndex;
            j = dataGridView2.CurrentRow.Index;
            System.Data.OleDb.OleDbCommandBuilder cb;
            cb = new System.Data.OleDb.OleDbCommandBuilder(cashDA);
            /// ###########################################################
            /// Changed so it edits directly into the DGV which in turn should edit the dataset behind it, change #13
            /// ###########################################################
            dataGridView2.SelectedRows[0].Cells["cashQTY"].Value = Int64.Parse(txtOrderQTY.Text);
            dataGridView2.SelectedRows[0].Cells["cashDescription"].Value = txtBoxDescrip.Text.ToString();
            dataGridView2.SelectedRows[0].Cells["cashSupplier"].Value = txtBoxSupplier.Text.ToString();
            dataGridView2.SelectedRows[0].Cells["cashDate"].Value = dateTimePicker1.Value.ToString();
            dataGridView2.SelectedRows[0].Cells["cashCost"].Value = Convert.ToDouble(txtBoxCost.Text);
            dataGridView2.SelectedRows[0].Cells["cashSell"].Value = Convert.ToDouble(txtBoxSell.Text);
            dataGridView2.SelectedRows[0].Cells["cashAccountRef_FKID"].Value = Int64.Parse(textBox1.Text);
            dataGridView2.EndEdit();
            /// ###########################################################
            /// Added connection open and close around the update call, change #9
            /// ###########################################################
            cashCustom.Open();
            cashDA.Update(cashOrdersDS, "cashOrders");
            cashCustom.Close();
            MessageBox.Show("Data Updated");
        }

[sundog1]

Hi Mikey,

Sorry for the late reply. Sophos has just crippled our Network. urgh!...

Anyhows.. that change you added I can see the was the 'endupdate();'

that has not worked either :( checked the backend .mdb and no change still.

[Mike Askew]

No worries,

Ah the joy of external companies.

Hmm if you add a debug point to line 22, and then look at cashOrdersDS.Tables["cashOrders"].Rows in watch and navigate to the data in list form (takes a bit of exploring, generally found in non public members, see if the changes make it into the dataset.

[sundog1]

HI Mikey,

Hmm.. I could be reading this wrong but it looks like it collects info and squirts it into cashOrders table but with only 3 Rows??? the only grid I have with 3 rows in the listview above which holds the Account Name,Ref,Town?

I've attached a Screenshot for you to have a look at.

[Mike Askew]

In the "Non-Public Members" section of the Rows bit you were in you can access the actual row data.

Have a deeper dig and see what you find.

[sundog1]

Ah-HAH!

I've found the data.. and the 251 was the amendment I made. So data is being pulled through to the dataset I guess then?

Attaced Screenshot.

[Mike Askew]

Wooo!

So now we know it is something between going to the database from the dataset, that is the issue.

What if we change cashDA.Update(cashOrdersDS, "cashOrders"); to cashDA.Update(cashOrdersDS);

[sundog1]

Hi Mikey.. Yeah.. its asking for the datatable.

I've tried ["Cashorders"] and [0] which don't error but also don't update the backend datatbase either.

I've attached SS.

[Mike Askew]

Have you tried cashOrdersDS.Tables["cashOrders"]? (think you have but i misunderstood the above post)

[sundog1]

Yeah, tried that to.

[Mike Askew]

Hummm I'm stumped.

[sundog1]

Me to.. heh :)

I'm half tempted to start the whole project again from scratch becasue I've learnt so much from you so thanks for that so far.

Would you have any suggestions on how to start again correctly crossing the T's and Dotting the I's as such?

Or do you think it's worth struggling with. There must be a basic explanation somewhere in the vast mess of my code to why it's not working I suppose :S

[Mike Askew]

Hmm there are definately pros to starting again in the fact it will remove all the coding niggles ive corrected if they were missed, ie a stray table or something.

and I dont mind helping with new issues.

Cons I suppose would just be the time invested and how long it would take to re-write.

The reason is most likely a simple one but they are always absolute f##kers to track down, I've spent a good half a day tracking down an error before which was caused by a comma in the wrong place -.-'

[sundog1]

Ok, Lets shelve that problem for a moment and come back to it..

The other issue I have is the fact that when I'm clicking through the differnt account and viewing order lines. Then I decide I wish to create a new 'Customers' I click 'New Cash Account' Fill out the details and then click 'Save'.

I get the error shown in Screenshot.

[Mike Askew]

Ok we shall, sorry for the delay came into work and was in a meeting within 15 mins that I wasnt expecting to be in!

Hmm there is column in the database called cashQTY which does not exist in the dataset table (stating the obvious).

Maybe it is trying to map the wrong table in database?

[sundog1]

Good morning Mikey,

Sorry - I was off Ill on Friday and then was on Holiday Monday so only come back into the Office today.

I think your right.. the cashQTY Column is actually in a different table within the Database.

To give you an idea of how the tables look and the database I had attached some screenshots for you to look at.

My guess on this is that it has something to possibly do with the fact that the SQL Script uses a Join to link the tables customers to the orders table.

and as the 'OrderQTY' is the 1st Column on the ordersTable I'm guessing I may have to somehow run a different SQL Script just to updatate the 'cashCustomers' table seperate? - That Make sense ?? :s

[sundog1]

Hi Mikey,

Going along the lines above I have come up with this INSERT SQL Command but just wondering where I could possibly fit it into the new 'SAVE ACCOUNT'.

     string newAccSQL = "INSERT INTO cashCustomers INSERT INTO cashCustomers (CashAccRef, CashName, CashAddress1, CashAddress2, CashAddress3, CashAddress5, ) " +
                " VALUES('" + Int64.Parse(txtBoxAccRef.Text) + "', '" + txtBoxName.Text + "','" + txtBoxAddr1.Text + "' ,'" + txtBoxAddr2.Text + "' , '" + txtBoxTown.Text + "' , '" + txtBoxCounty.Text + "' , '" + txtBoxPostCode.Text + ")";

[Mike Askew]

Morning,

Sorry had the week off work so wasnt really watching the forum as much, nor did I have access to an IDE.

I believe your correct with the new SQL statement though, you would need to update that table alone and not any others.