Re: Multiple table in dataset query

Tech-Archive recommends: Speed Up your PC by fixing your registry

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: 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: Run a Stored Procedure from Excel
    ... You also want to find out if you have a rsDW variable defined. ... Your code had a semicolon between the 2nd command and the ... Dim cnnDW As ADODB.Connection ... Set cnnDW = New ADODB.Connection ...
    (microsoft.public.excel.programming)
  • Re: Frustrated with learning add command code...
    ... Add the following code to the command button ... Private Sub CommandButton1_Click ... Dim wb as Workbook ... 'find first empty row in database ...
    (microsoft.public.excel.programming)
  • Re: Client-Side User Confirmation Within DataGrid
    ... the delete command and i have a problem: ... Private Sub DataGrid1_ItemDataBound(ByVal sender As Object, ... Dim control As WebControl ... Dim matrans As OdbcTransaction = cnn.BeginTransaction ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Using CommandBuilder to update an Access table from a DataGrid
    ... update command properly. ... Private Sub Form1_Load(ByVal sender As Object, ... Dim myDA = New OleDbDataAdapter("SELECT * FROM ...
    (microsoft.public.dotnet.framework.adonet)