record appending with datagridview

[divjoy]

Hi,

I'm using vb.net 2010 with sql server 2008 express with ado.net to access the tables and store in a dataset.
I have a master detail form with a datagridview. The datagridview allows users to add as many new rows to the grid as they like and I know how to then to commit the new rows back to the database.

But I have a problem in mydatagrid view I have 2 hidden columns, 1 is the primary key of the details table, which I can ignore as the database creates this when the new rows are added and the second is the Client ID of the master form that also needs to be stored in the details table back in the database.

How do I got about adding the ClientID to the dataset for each new row added to the datagirdview before committing the 'changes' back to the database?

Regards

 

[jmcilhinney]

You need to have a DataRelation defined in your DataSet that mimics the foreign key in the database. Your DataTables should be configured to auto-generate the IDs just like the database and then those values can be used in code until the final values are generated by the database. When you add child records, you just use the ID from the parent record as normal. You configure your data adapters to refresh the DataTable with the final IDs generated by the database and you configure the DataRelation to cascade on update. Then, when you save the parent record, the ID from the database gets pushed to the parent DataTable and from there to the child DataTable. You can then save the child records as they now contain the correct parent ID. Here's a quick example of how everything should be configured:

Dim data As New DataSet
Dim parentTable = data.Tables.Add("Parent")
Dim childTable = data.Tables.Add("Child")

'Add the key columns.
Dim parentPrimaryKeyColumn = parentTable.Columns.Add("ParentId", GetType(Integer))
Dim childPrimaryKeyColumn = childTable.Columns.Add("ChildId", GetType(Integer))
Dim childForeignKeyKeyColumn = childTable.Columns.Add("ParentId", GetType(Integer))

'Add the remaining columns.
parentTable.Columns.Add("ParentName", GetType(String))
childTable.Columns.Add("ChildName", GetType(String))

'Auto-generate IDs in both tables.
parentPrimaryKeyColumn.AutoIncrement = True
childPrimaryKeyColumn.AutoIncrement = True

'Create the foreign key and configure to cascade on update, which will push the correct ParentId value from the parent table to the child table.
data.Relations.Add("ParentChild", parentPrimaryKeyColumn, childForeignKeyKeyColumn).ChildKeyConstraint.UpdateRule = Rule.Cascade

'Write the SQL code to insert new records and retrieve the auto-generated IDs.
Dim parentInsertStatement = <sql>
                                INSERT INTO Parent (ParentId, ParentName) VALUES (@ParentId, @ParentName);
                                SELECT ParentId = SCOPE_IDENTITY()
                            </sql>
Dim childInsertStatement = <sql>
                                INSERT INTO Child (ChildId, ParentId, ChildName) VALUES (@ChildId, @ParentId, @ChildName);
                                SELECT ChildId = SCOPE_IDENTITY()
                            </sql>

Dim connection As New SqlConnection("connection string here")
Dim parentInsertCommand As New SqlCommand(parentInsertStatement.Value, connection)
Dim childInsertCommand As New SqlCommand(childInsertStatement.Value, connection)
Dim parentAdapter As New SqlDataAdapter("SELECT * FROM Parent", connection) With {.InsertCommand = parentInsertCommand}
Dim childAdapter As New SqlDataAdapter("SELECT * FROM Child", connection) With {.InsertCommand = childInsertCommand}

'Retrieve existing data.
parentAdapter.Fill(parentTable)
childAdapter.Fill(childTable)

'NOTE: Edit data here.

'Add new parent row, which will generate temporary ID.
Dim parentRow = parentTable.Rows.Add(Nothing, "Parent1")

'Add new child row, using temporary parent ID to create relationship.
childTable.Rows.Add(Nothing, parentRow("ParentId"), "Child1")

'Save parent changes.
parentAdapter.Update(parentTable)

'NOTE: All new parent rows have now been updated to contain the final IDs from the database in place of the temporary IDs generated by the DataTable.
'NOTE: The related child records have also been updated with the final parent IDs the database to maintain referencial integrity in both the DataTable and database.

'Save child changes.
childAdapter.Update(childTable)

 

[divjoy]

Thanks for thats, its excellent example code, which I will be using, my scenario is a little different.
In my dataset i have the parent table that is just one record, the client and does not need to be saved back to the db. Then I have all the clients services (the child table) in the dataset which can be changed and new services added.
A datagridview and datataset.update does this beautifully but that the Foreign key ClientID, in the Services table ( child) is hidde, as it would be stupid to show the clientid repeating on the datsgrid when its obvious from the screen who the client is.
So in this scenario i want the user to add a new row to the datagrid and I add the known clientID by code to the new row to the dataset.

 

[jmcilhinney]

Oh dear, I just wasted my time on an example that you didn't need because I misinterpreted the thrust of the question. Oh well, it sounds like you may get some use out of that example anyway so it may not be for nought.

Now to the ACTUAL question. What you should be able to do is simply assign the ID from the one row of the parent table to the DefaultValue property of the DataColumn that contains the foreign key in the child table. That way, whenever you add a new DataRow to the child table, that parent ID value will be inserted into that column automatically. I've never tried but, as far as I'm aware, changing that property value mid stream is no issue; any existing rows will be unchanged but any rows added afterwards will use the new value.

 

[divjoy]

Hi, Thanks again, yes it does sound simple when you describe it like that.
I suppose what I was concerned about was addressing a column that is hidden in the datgridview, but maybe being hidden has no effect other than not being seen! I can still set its default value! brilliant yet simple...

I will give it ago.

 

[jmcilhinney]

Hi, Thanks again, yes it does sound simple when you describe it like that.
I suppose what I was concerned about was addressing a column that is hidden in the datgridview, but maybe being hidden has no effect other than not being seen! I can still set its default value! brilliant yet simple...

I will give it ago.

The fact that the grid has no column for that data or its column is hidden only means that the user can't see it. It still exists in the source DataTable and it's there that you should be working with the data in code anyway, so you still have exactly the same access to it as you normally would.

 

[divjoy]

This is what I need to know how do I take the data the user enters into a new partial record in the datsgridview add a ClientID and store in datatable?

 

[jmcilhinney]

This is what I need to know how do I take the data the user enters into a new partial record in the datsgridview add a ClientID and store in datatable?

I thought that we'd already established that: set the DefaultValue for the appropriate DataColumn and it will be set automatically. If you're really determined to set it manually then you can use the Current property of your BindingSource (if you're not binding the DataTable to the DataGridView via a BindingSource then you should change that now) to get the corresponding DataRowView. Just like the underlying DataRow, you can set the desired field by name or ordinal.

 

[divjoy]

Ok thanks again, just need to see a nice worked example