using strongly typed dataset update problem

[Scotty]

I get stuck to write an update, insert and delete command, i am looking for
some help to start

Whats the best way to update 2 tables toe the database (Access)
below my code used to load my data.(2 tables)
Do someone has a good sample code to help me?

Many thanks in advance,
Marc.



'************be gin code*********** ****
Imports System
Imports System.Data
Imports system.Data.ole db



Public Class frmPostcodesTyp edDS

Inherits System.Windows. Forms.Form

Dim strPath As String = "C:\AADatabases \BeActionData.m db"
Dim strCnBeActionDa ta As String = "provider=micro soft.jet.oledb. 4.0;
Data Source=" & strPath

Dim daActionData As OleDbDataAdapte r
'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
Dim cnBeActionData As New OleDbConnection (strCnBeActionD ata)

Dim dsLandenPostcod es As New DataSet
Dim da1 As New OleDbDataAdapte r
Dim da2 As New OleDbDataAdapte r

Dim dt1 As New DataTable
Dim dt2 As New DataTable


Private Sub frmPostcodes_Lo ad(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Call LoadPostcodes()
End Sub


Sub LoadPostcodes()

' Dim dsLandenPostcod es As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dsLandenPostcod es.Tables.Add(d t1)
dsLandenPostcod es.Tables.Add(d t2)
Dim da1 As New OleDbDataAdapte r("Select * from [tblLanden]",
cnBeActionData)
Dim da2 As New OleDbDataAdapte r("Select * from
[tblLandenPostco des]", cnBeActionData)

da1.FillSchema( dt1, SchemaType.Mapp ed)
da2.FillSchema( dt2, SchemaType.Mapp ed)

Dim rel As New DataRelation("F KPostcLanden_Po stcodes",
dsLandenPostcod es.Tables(0).Co lumns("Landcode "),
dsLandenPostcod es.Tables(1).Co lumns("Landcode "))
dsLandenPostcod es.Relations.Ad d(rel)

Dim bsPostcodeLande n As New BindingSource
bsPostcodeLande n.DataMember = dsLandenPostcod es.Tables(0).Ta bleName
bsPostcodeLande n.DataSource = dsLandenPostcod es

Dim bsPostcodes As New BindingSource
bsPostcodes.Dat aSource = bsPostcodeLande n
bsPostcodes.Dat aMember = "FKPostcLanden_ Postcodes"

da1.Fill(dt1)
da2.Fill(dt2)

Me.dgvPostcodeL anden.DataSourc e = bsPostcodeLande n
Me.dgvPostcodes .DataSource = bsPostcodes
End Sub
'************Ei nde code*********** ****

[Rich]

RE: using strongly typed dataset update problem

Hi Mark,

Here is a sample I just wrote:

Imports System.Data.Ole Db
Public Class Form2
Dim conn As OleDbConnection , da As OleDbDataAdapte r, ds As DataSet
Private Sub Form2_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
conn = New OleDbConnection
conn.Connection String = "provider=micro soft.jet.oledb. 4.0; Data
Source = db2test.mdb"
da = New OleDbDataAdapte r
ds = New DataSet
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandType = CommandType.Tex t
da.SelectComman d.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
dgrv1.DataSourc e = ds.Tables(0)
End Sub

Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
da.UpdateComman d = New OleDbCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandType = CommandType.Tex t
da.UpdateComman d.CommandText = "Update Table1 Set Field3 = 'aaa'
where ID = 3"
conn.Open()
da.UpdateComman d.ExecuteNonQue ry()
'da.Update(ds, "tbl1") '--<<---had to use ExecuteNonQuery since this
didn't run
ds.Clear()
da.Fill(ds, "tbl1")
dgrv1.DataSourc e = ds.Tables(0)
conn.Close()
MessageBox.Show ("Updated")
End Sub
End Class

the first routine loads data from a table in an Access mdb. The second
routine updates the table. The only thing is that I couldn't get the
da.Update thing to work (like it does with sql server). So I used
da.UpdateComman d.ExecuteNonQue ry which did work. The only thing is that you
have to open and close the connection yourself where usually the dataAdapter
takes care of that automatically.

Rich


"Scotty" wrote:

I get stuck to write an update, insert and delete command, i am looking for
some help to start
>
Whats the best way to update 2 tables toe the database (Access)
below my code used to load my data.(2 tables)
Do someone has a good sample code to help me?
>
Many thanks in advance,
Marc.
>
>
>
'************be gin code*********** ****
Imports System
Imports System.Data
Imports system.Data.ole db
>
>
>
Public Class frmPostcodesTyp edDS
>
Inherits System.Windows. Forms.Form
>
Dim strPath As String = "C:\AADatabases \BeActionData.m db"
Dim strCnBeActionDa ta As String = "provider=micro soft.jet.oledb. 4.0;
Data Source=" & strPath
>
Dim daActionData As OleDbDataAdapte r
'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
Dim cnBeActionData As New OleDbConnection (strCnBeActionD ata)
>
Dim dsLandenPostcod es As New DataSet
Dim da1 As New OleDbDataAdapte r
Dim da2 As New OleDbDataAdapte r
>
Dim dt1 As New DataTable
Dim dt2 As New DataTable
>
>
Private Sub frmPostcodes_Lo ad(ByVal sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
Call LoadPostcodes()
End Sub
>
>
Sub LoadPostcodes()
>
' Dim dsLandenPostcod es As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dsLandenPostcod es.Tables.Add(d t1)
dsLandenPostcod es.Tables.Add(d t2)
Dim da1 As New OleDbDataAdapte r("Select * from [tblLanden]",
cnBeActionData)
Dim da2 As New OleDbDataAdapte r("Select * from
[tblLandenPostco des]", cnBeActionData)
>
da1.FillSchema( dt1, SchemaType.Mapp ed)
da2.FillSchema( dt2, SchemaType.Mapp ed)
>
Dim rel As New DataRelation("F KPostcLanden_Po stcodes",
dsLandenPostcod es.Tables(0).Co lumns("Landcode "),
dsLandenPostcod es.Tables(1).Co lumns("Landcode "))
dsLandenPostcod es.Relations.Ad d(rel)
>
Dim bsPostcodeLande n As New BindingSource
bsPostcodeLande n.DataMember = dsLandenPostcod es.Tables(0).Ta bleName
bsPostcodeLande n.DataSource = dsLandenPostcod es
>
Dim bsPostcodes As New BindingSource
bsPostcodes.Dat aSource = bsPostcodeLande n
bsPostcodes.Dat aMember = "FKPostcLanden_ Postcodes"
>
da1.Fill(dt1)
da2.Fill(dt2)
>
Me.dgvPostcodeL anden.DataSourc e = bsPostcodeLande n
Me.dgvPostcodes .DataSource = bsPostcodes
End Sub
'************Ei nde code*********** ****
>
>
>

[Scotty]

Re: using strongly typed dataset update problem

Hi Rich,

Thanks for your answer
I have tested your sample and works fine,
But this is not realy my question,

As sample i have a database with 2 tables like tblCountry and
tblCountryPostc odes
If you like I send you this evening the Access database and the sample
programm (vbnet2005) as sample to use?

I like to fill in new items the form textbox and datagridview to update
them.

Many thanks,

Marc,
Belgium



"Rich" <Rich@discussio ns.microsoft.co mschreef in bericht
news:A8DCB593-6877-4EAD-AECE-5C864C0C879F@mi crosoft.com...

Hi Mark,
>
Here is a sample I just wrote:
>
Imports System.Data.Ole Db
Public Class Form2
Dim conn As OleDbConnection , da As OleDbDataAdapte r, ds As DataSet
Private Sub Form2_Load(ByVa l sender As System.Object, ByVal e As
System.EventArg s) Handles MyBase.Load
conn = New OleDbConnection
conn.Connection String = "provider=micro soft.jet.oledb. 4.0; Data
Source = db2test.mdb"
da = New OleDbDataAdapte r
ds = New DataSet
da.SelectComman d = New OleDbCommand
da.SelectComman d.Connection = conn
da.SelectComman d.CommandType = CommandType.Tex t
da.SelectComman d.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
dgrv1.DataSourc e = ds.Tables(0)
End Sub
>
Private Sub Button1_Click(B yVal sender As System.Object, ByVal e As
System.EventArg s) Handles Button1.Click
da.UpdateComman d = New OleDbCommand
da.UpdateComman d.Connection = conn
da.UpdateComman d.CommandType = CommandType.Tex t
da.UpdateComman d.CommandText = "Update Table1 Set Field3 = 'aaa'
where ID = 3"
conn.Open()
da.UpdateComman d.ExecuteNonQue ry()
'da.Update(ds, "tbl1") '--<<---had to use ExecuteNonQuery since
this
didn't run
ds.Clear()
da.Fill(ds, "tbl1")
dgrv1.DataSourc e = ds.Tables(0)
conn.Close()
MessageBox.Show ("Updated")
End Sub
End Class
>
the first routine loads data from a table in an Access mdb. The second
routine updates the table. The only thing is that I couldn't get the
da.Update thing to work (like it does with sql server). So I used
da.UpdateComman d.ExecuteNonQue ry which did work. The only thing is that
you
have to open and close the connection yourself where usually the
dataAdapter
takes care of that automatically.
>
Rich
>
>
"Scotty" wrote:
>

>I get stuck to write an update, insert and delete command, i am looking
>for
>some help to start
>>
>Whats the best way to update 2 tables toe the database (Access)
>below my code used to load my data.(2 tables)
>Do someone has a good sample code to help me?
>>
>Many thanks in advance,
>Marc.
>>
>>
>>
>'************b egin code*********** ****
>Imports System
>Imports System.Data
>Imports system.Data.ole db
>>
>>
>>
>Public Class frmPostcodesTyp edDS
>>
> Inherits System.Windows. Forms.Form
>>
> Dim strPath As String = "C:\AADatabases \BeActionData.m db"
> Dim strCnBeActionDa ta As String = "provider=micro soft.jet.oledb. 4.0;
>Data Source=" & strPath
>>
> Dim daActionData As OleDbDataAdapte r
> 'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
> Dim cnBeActionData As New OleDbConnection (strCnBeActionD ata)
>>
> Dim dsLandenPostcod es As New DataSet
> Dim da1 As New OleDbDataAdapte r
> Dim da2 As New OleDbDataAdapte r
>>
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
>>
>>
> Private Sub frmPostcodes_Lo ad(ByVal sender As System.Object, ByVal e
>As
>System.EventAr gs) Handles MyBase.Load
> Call LoadPostcodes()
> End Sub
>>
>>
>Sub LoadPostcodes()
>>
> ' Dim dsLandenPostcod es As New DataSet
> Dim dt1 As New DataTable
> Dim dt2 As New DataTable
> dsLandenPostcod es.Tables.Add(d t1)
> dsLandenPostcod es.Tables.Add(d t2)
> Dim da1 As New OleDbDataAdapte r("Select * from [tblLanden]",
>cnBeActionData )
> Dim da2 As New OleDbDataAdapte r("Select * from
>[tblLandenPostco des]", cnBeActionData)
>>
> da1.FillSchema( dt1, SchemaType.Mapp ed)
> da2.FillSchema( dt2, SchemaType.Mapp ed)
>>
> Dim rel As New DataRelation("F KPostcLanden_Po stcodes",
>dsLandenPostco des.Tables(0).C olumns("Landcod e"),
>dsLandenPostco des.Tables(1).C olumns("Landcod e"))
> dsLandenPostcod es.Relations.Ad d(rel)
>>
> Dim bsPostcodeLande n As New BindingSource
> bsPostcodeLande n.DataMember =
>dsLandenPostco des.Tables(0).T ableName
> bsPostcodeLande n.DataSource = dsLandenPostcod es
>>
> Dim bsPostcodes As New BindingSource
> bsPostcodes.Dat aSource = bsPostcodeLande n
> bsPostcodes.Dat aMember = "FKPostcLanden_ Postcodes"
>>
> da1.Fill(dt1)
> da2.Fill(dt2)
>>
> Me.dgvPostcodeL anden.DataSourc e = bsPostcodeLande n
> Me.dgvPostcodes .DataSource = bsPostcodes
> End Sub
>'************E inde code*********** ****
>>
>>
>>