Multiple table in dataset query

[Microsoft]

Hi everyone,

Before I begin I should point out that I am quite new to programming and
VB.net and am using VS2003 with SQLExpress 2005.

I have two data tables a staff table and an office table, the linking field
is office_id. I have created a form and added a datagrid, when the form
loads I create a new DataAdapter, Connection and DataSet and select the
records from the staff table linking with the office table to display the
office name rather than the ID.

Private Sub frmStaff_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load

Dim dcConn as New OleDB.OleDbConnection("Integrated Security=SSPI;Packet
Size=4096;Data Source='SERVER\INSTANCE';Initial Catalog=Planner;Persist
Security Info=False;Provider="SQLOLEDB.1")

Dim daStaff = New OleDb.OleDbDataAdapter("SELECT a.staff_id,
a.staff_initials, a.staff_name, a.staff_title, a.staff_active, b.office_name
FROM tblStaff a, tblOffice b WHERE a.staff_office_id = b.office_id", dcConn)

Dim dsStaff as New System.Data.DataSet
daStaff.Fill(dsStaff, "tblStaff")

' set the bding context
currManager = CType(Me.BindingContext(dsStaff.Tables("Staff")),
CurrencyManager)

' Set the databindings
DataGrid1.DataSource = dsStaff.Tables("Staff")

....
Here I create all the DataGridTextBoxColumn and set the mapping to the
relevant field, etc.
....

End Sub

' The procedure for saving the changes is:-
Private Sub SaveChanges()
Dim cmdBuilder As New OleDb.OleDbCommandBuilder(daStaff)
Dim lngRetVal As Long

lngRetVal = daStaff.Update(dsStaff, "Staff")
If lngRetVal > 0 Then
dsStaff.AcceptChanges()
End If
End Sub

The form loads ok and the grid is populated, however when I call the update
command on the DataAdapter I get an error saying that I cannot use the
commandbuilder to update multiple tables. So my question is how the hell do
I do this? I prefer to manually code it rather than using the wizards to
really understand it. Plus the wizards don't actually work SQLExpress 2005.
I have read several tutorials but none seem to cover what I want, my MCSD
book I bought doesn't really explain how to do this in real terms.

Any help, pointers, guidance or links to useful articles would be very much
appreciated. I'm completely lost and don'tknow where to go from here.

Thank you in advance for taking the time to read my query!
Cheers
Lee

 

[RobinS]

First of all, be aware that the CommandBuilder is only effective for really
simple updates, like if you have a list of fields with a primary key.

You can *not* do updates using the CommandBuilder if you have a join in
your SQL statement.

You will have to write your own SQL for the update, insert, and delete
commands, so they update only the one table.

Example:

Update tblStaff Set staff_id = ?, staff_initials = ?, staff_name = ?,
staff_title = ?, staff_active = ? WHERE staff_id = ?

and then add parameters for those (in that order) from the data row in your
dataset.

Does that make sense?

Robin S.
-------------------------------------

 

[Microsoft]

Yes I think I undestand where your coming from, effectively I need to create
a new parameterized query for the update and insert command which is then
assigned to the dataadapter.

I have found some articles on doing this so I will have a play around with
it. The other problem I've recognised in doing this is that ideally I need a
combobox in the dataqgrid for the user to select from the fields in the
other table - which isn't supported with the datagrid in 1.1, not sure about
2.0. I will get to this later, for now I will concentrate on getting the
updates working.

Thanks for your help.
Lee

 

[Guest]

Lee,

Here is some information on placing a combobox into a datagrid in .Net 1.1:

http://www.syncfusion.com/FAQ/WindowsForms/FAQ_c44c.aspx#q480q

Also, keep in mind that the code that Robin showed you does not handle
concurrency issues. If your program will be used by multiple users all
hitting a central database then you must deal with concurrency in your SQL
update and delete statements.

Kerry Moorman

 

[Microsoft]

Thanks Kerry for the pointers on the combobox, I will look into this in the
future once I have the basic data stuff sorted. I have come a little bit
unstuck actually, I have modified my SaveChanges proc to reflect the new
Update command and its parameters, but I am a bit lost in actually
performing the update. I have multiple rows that have been modified so do I
have to execute the DataAdapter.Update(x,x) multiple times for each row?

The code I have so far is :-

Private Sub SaveChanges()
Dim UpdateStaffCmd As New OleDb.OleDbCommand
Dim lngRetVal As Long
Dim dsChanges As DataSet
Dim dtTable As DataTable
Dim myDataRow As DataRow

' create a parameterized query to perform the update
UpdateStaffCmd.CommandType = CommandType.Text

' the '?' represent the parameters
UpdateStaffCmd.CommandText = "Update tblStaff Set staff_id = ?,
staff_initials = ?, staff_name = ?, staff_title = ?, staff_active = ?,
staff_office_id = ? WHERE staff_id = ?"

' add the parameters in the same order as indicated the commandtext
UpdateStaffCmd.Parameters.Add("staff_id", OleDb.OleDbType.Integer, 4)
UpdateStaffCmd.Parameters.Add("staff_initials", OleDb.OleDbType.VarChar, 4)
UpdateStaffCmd.Parameters.Add("staff_nane", OleDb.OleDbType.VarChar, 50)
UpdateStaffCmd.Parameters.Add("staff_title", OleDb.OleDbType.VarChar, 50)
UpdateStaffCmd.Parameters.Add("staff_active", OleDb.OleDbType.Binary)

' The datagrid would display LONDON but the ID value is actually 1, so do I
need another datatable to hold this information and use a datarelation
object? this is much harder than the old ADO!!!
UpdateStaffCmd.Parameters.Add("staff_office_id", OleDb.OleDbType.Integer)

' assign the update command to the data adapter
daStaff.UpdateCommand = UpdateStaffCmd

' get the changed datarows and store in new dataset
dsChanges = dsStaff.GetChanges(DataRowState.Modified)

' get the datatable so that I can access the datarow
dtTable = dsChanges.Tables("Staff")

For Each myDataRow In dtTable.Rows
Console.WriteLine(myDataRow.Item)
Next

lngRetVal = daStaff.Update(dsChanges, "Staff")

If lngRetVal > 0 Then
dsStaff.AcceptChanges()
End If

End Sub


Sorry - this probably seems very trivial, but I cannot seem to find a
comprehensive example in any documentation, just bits and pieces which I am
trying to tie together. At least I am learning how to do it properly. I am
reading an article on Data concurrency at the minute, very interesting - not
something I had even considered yet. Oh boy - its going to be a long night.

Thank you all for any assistance.
Lee

 

[RobinS]

When you call Update on the data adapter, it will iterate through the rows.
If a row is marked as Modified, it will run your query defined in the
Update Command object. If it is marked as deleted, it will run the query
defined in the Delete Command object. If it is marked as Added, it will run
the query defined in the Insert Command object.

Hope this helps.

Robin S.
---------------------------