Re: Multiple table in dataset query

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.
---------------------------
"Microsoft" <lee.clements.nospam@xxxxxxxxxxxxxxx> wrote in message
news:eG0LfsiRHHA.1860@xxxxxxxxxxxxxxxxxxxxxxx
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



"Kerry Moorman" <KerryMoorman@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9DC8D049-F072-44C4-8271-A92CFADBA983@xxxxxxxxxxxxxxxx
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" wrote:

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


"RobinS" <RobinS@xxxxxxxxxxxxxxx> wrote in message
news:DIednU8aQKZ3h1zYnZ2dnUVZ_r-onZ2d@xxxxxxxxxxxxxx
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" <lee.clements.nospam@xxxxxxxxxxxxxxx> wrote in message
news:%2380X%23$VRHHA.4448@xxxxxxxxxxxxxxxxxxxxxxx
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










.

Relevant Pages

  • Re: access 2003
    ... Dim ctl As Control ... Dim rs As Recordset ... This sets the query definitions for choosing data to create an invoice using ... Event on combo box: Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Listbox filtering on a form
    ... you can select from a query instead of a table. ... Dim varItem As Variant ... Private Sub List200_AfterUpdate ... Dim cnt, i As Integer ...
    (microsoft.public.access.formscoding)
  • Re: Multiple table in dataset query
    ... Thanks Kerry for the pointers on the combobox, I will look into this in the ... Update command and its parameters, but I am a bit lost in actually ... Dim UpdateStaffCmd As New OleDb.OleDbCommand ... Private Sub frmStaff_Load(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: access 2003
    ... I've noticed is that it seems you should be using a parameter query to ... Dim db As DAO.Database ... then the recordset contains no records: ... Private Sub ChooseCust_AfterUpdate ...
    (microsoft.public.access.conversion)
  • Re: Updated query via VBA
    ... >The SQL of the update query is ... >Private Sub Update_Click ... > Dim qdf As DAO.QueryDef ... > Dim strOffice As String ...
    (microsoft.public.access.modulesdaovba)