Re: Multiple table in dataset query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: Multiple table in dataset query
    ... If a row is marked as Modified, it will run your query defined in the ... Update Command object. ... Dim UpdateStaffCmd As New OleDb.OleDbCommand ... Private Sub frmStaff_Load(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.adonet)
  • 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)
  • Create a cutom datagrid with its datasource preset and dataset prefilled.
    ... Creating a DataGrid with a prefilled dataset ... Dim SQLConn_MYReadOnly As ... 'This call is required by the Web Form Designer. ... Private Sub Page_Init(ByVal sender As System.Object, ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Button in a Repeater in a Datagrid
    ... in a repeater in a datagrid. ... Protected WithEvents OleDbDataAdapter1 As ... Private Sub Page_Init(ByVal sender As System.Object, ... Dim da As New OleDb.OleDbDataAdapter ...
    (microsoft.public.dotnet.framework.aspnet.webcontrols)
  • Re: Problem with a datagrid inside a datalist
    ... > this event when my datagrid is inside a DataList. ... > Dim SqlStr As String ... > Private Sub DataList1_ItemDataBound(ByVal sender As Object, ... > Public Function GetServicesAs DataSet ...
    (microsoft.public.dotnet.framework.aspnet)