C# SQL ID field Datagrid and DataSource

OK I am a newbie when it comes to lots of .Net so if you answer go slowwww. I have an auto incrementing ID field in a SQL database.

This is all displayed to the user in a datagrid control. I add two or three new rows and AcceptChanges.

At this point I my ID field displayed on the grid bound tot he SQL ID field is out of Sync. What the#@.^%#$&*

In fact it looks like the data grid placed in its own numbers by doing some sort of Maxvalue on the column.

HELP!

Bob

Chicago_Bob:

In fact it looks like the data grid placed in its own numbers by doing some sort of Maxvalue on the column.

Can you explain more about what's the exact problem you're facing? How does the indentity column look like now? Do you mean the new rows got wrong IDs (not the maxID+1/+2)?

|||

I did some digging last night to find I am not alone in this problem this is a thing that might be addressed by

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconretrievingidentityorautonumbervalues.asp

I am not sure yet but to tell you the truth this bites. The ID column beucase the dataset is disconected does not stay in sync with the

ID column. SQL can be on number 999 and the ID will be MAX ID of the record set + 1. YIKES!!! And the error is only know when you

try to update the record set. It will reply with a Concurency error. DOH! It took a while to relate the 2 together. Who would have thought..

Anyway the dataset the DataGrid and therefore the results of the DataGrid are not correct when you accept changes and you have a SQL table with an autoincrement field. NOT GOOD.. There is nothing anywhere that spells this out when you read about the DataSet or DatGrid.

In fact web searching turns up little if anything at all. Guess thats what I guess for using those pretty tools in .NET eh?

Bob

|||

Rant mode.

I thought this was a 3 maybe 4 hour job. MultiLine copy and Paste addition to the DataGrid control. I already had the code to do a one line copy and paste. So what a little loop code and I was out of there.. That would be normal. But what in using RAD tools is normal? I did finally get my multirow copy and paste to work but it took quite a bit of playing "twist the code" and it was not pleasant. Here is a few of the issues that I hit like a brick wall.

I use a SQL autoincrement ID field so multiple users can be making changes etc. WELL bad idea with the DataGrid.. First the ID sync is not in sync with SQL server. You have to cusomize your autogenerated code to do insert to work with a Stored Proc so you can get a value back which is the magical ID. How intuitive that was. I saw no mention of this anywhere in the Grid docs. The error is special as well "Concerency error " (which makes total sense to eveyrone here I am sure but not to me) when I did not have my ID's displayed. So after 1 day of fishing on the Net I get some info about how to do this with a data set. Its the link above. It does work butWARNING if you even look at your XSD and check some properties and save the OLD code I had for insert whacks the new. So after retyping again I am afraid to go back and even breath on that stuff..

Now onto the multiple other hurdles. If you have a bound Grid control youcan not simply add a row. WHY? Cause its bound the compiler tells me, But thats why I bound it! I bound it so I can use the Grid to manipulate the dataset. I was stumped for an aswer until I saw that you can get the last row of the Grid by usingdataGridView1.NewRowIndex; COOL I fill in the data and do the Accept and update and .... Nothing. The data is added to the Grid but not to SQL. BUMMER. After playing around for several hours with this idea and that I got a new idea.I need to add a row so I figure you add a record to the dataset. Yeah that should do it. Great three lines of code to add the row. I change my paste function to place data into each data field in the new data row and do an update and accept. I query SQL Server using Enterpirse manager and see the data is there but the grid? It shows nothing. Empty...?

Isnt the Grid suppossed to update with the new data when I update the dataset and accept the data with adapter. Guess NOT.. I thought they were connected. Oh well I go back to the old code and add data to the grid and add a new row this caused me to get 2 rows for every one that I added. Yeah that was FUN but I figured out why that was happening. The index to the Grids row would update as soon as you add row a row to the dataset. This would start my new row below my old row and I would 2 records in SQL and one would be blank etc. Bad Stuff. Here is the original snipit.

instEditsDataSet1.EQ_IntranetReportData.Rows.Add(myRow);

row = dataGridView1.NewRowIndex;

// add a row to the data set.

dataGridView1.CurrentCell = dataGridView1[0, row];

------ BUT if you switchit around -----

// add a row to the data set.

row = dataGridView1.NewRowIndex;

instEditsDataSet1.EQ_IntranetReportData.Rows.Add(myRow);

dataGridView1.CurrentCell = dataGridView1[0, row];

all is good. This will get you a place to start adding data from the copy.

GEE I thought these were supposed to be connected? But they are a pain So I add new data to the DataRow and simultaneously add data to the Grids Cells on that row. Do my update and accept and it works.. EXCEPT when you use a filter. Then it all goes bad.

So you must take save all filters and sorts then put em back later.

All in all 3 normal days.. Now that was what I call a RAD tool.

Rant Mode off.