using strongly typed dataset update problem

  • Thread starter Thread starter Scotty
  • Start date Start date
S

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.



'************begin code***************
Imports System
Imports System.Data
Imports system.Data.oledb



Public Class frmPostcodesTypedDS

Inherits System.Windows.Forms.Form

Dim strPath As String = "C:\AADatabases\BeActionData.mdb"
Dim strCnBeActionData As String = "provider=microsoft.jet.oledb.4.0;
Data Source=" & strPath

Dim daActionData As OleDbDataAdapter
'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
Dim cnBeActionData As New OleDbConnection(strCnBeActionData)

Dim dsLandenPostcodes As New DataSet
Dim da1 As New OleDbDataAdapter
Dim da2 As New OleDbDataAdapter

Dim dt1 As New DataTable
Dim dt2 As New DataTable


Private Sub frmPostcodes_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Call LoadPostcodes()
End Sub


Sub LoadPostcodes()

' Dim dsLandenPostcodes As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dsLandenPostcodes.Tables.Add(dt1)
dsLandenPostcodes.Tables.Add(dt2)
Dim da1 As New OleDbDataAdapter("Select * from [tblLanden]",
cnBeActionData)
Dim da2 As New OleDbDataAdapter("Select * from
[tblLandenPostcodes]", cnBeActionData)

da1.FillSchema(dt1, SchemaType.Mapped)
da2.FillSchema(dt2, SchemaType.Mapped)

Dim rel As New DataRelation("FKPostcLanden_Postcodes",
dsLandenPostcodes.Tables(0).Columns("Landcode"),
dsLandenPostcodes.Tables(1).Columns("Landcode"))
dsLandenPostcodes.Relations.Add(rel)

Dim bsPostcodeLanden As New BindingSource
bsPostcodeLanden.DataMember = dsLandenPostcodes.Tables(0).TableName
bsPostcodeLanden.DataSource = dsLandenPostcodes

Dim bsPostcodes As New BindingSource
bsPostcodes.DataSource = bsPostcodeLanden
bsPostcodes.DataMember = "FKPostcLanden_Postcodes"

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

Me.dgvPostcodeLanden.DataSource = bsPostcodeLanden
Me.dgvPostcodes.DataSource = bsPostcodes
End Sub
'************Einde code***************
 
Hi Mark,

Here is a sample I just wrote:

Imports System.Data.OleDb
Public Class Form2
Dim conn As OleDbConnection, da As OleDbDataAdapter, ds As DataSet
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data
Source = db2test.mdb"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandType = CommandType.Text
da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables(0)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
da.UpdateCommand = New OleDbCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.CommandType = CommandType.Text
da.UpdateCommand.CommandText = "Update Table1 Set Field3 = 'aaa'
where ID = 3"
conn.Open()
da.UpdateCommand.ExecuteNonQuery()
'da.Update(ds, "tbl1") '--<<---had to use ExecuteNonQuery since this
didn't run
ds.Clear()
da.Fill(ds, "tbl1")
dgrv1.DataSource = 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.UpdateCommand.ExecuteNonQuery 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 said:
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.



'************begin code***************
Imports System
Imports System.Data
Imports system.Data.oledb



Public Class frmPostcodesTypedDS

Inherits System.Windows.Forms.Form

Dim strPath As String = "C:\AADatabases\BeActionData.mdb"
Dim strCnBeActionData As String = "provider=microsoft.jet.oledb.4.0;
Data Source=" & strPath

Dim daActionData As OleDbDataAdapter
'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
Dim cnBeActionData As New OleDbConnection(strCnBeActionData)

Dim dsLandenPostcodes As New DataSet
Dim da1 As New OleDbDataAdapter
Dim da2 As New OleDbDataAdapter

Dim dt1 As New DataTable
Dim dt2 As New DataTable


Private Sub frmPostcodes_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Call LoadPostcodes()
End Sub


Sub LoadPostcodes()

' Dim dsLandenPostcodes As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dsLandenPostcodes.Tables.Add(dt1)
dsLandenPostcodes.Tables.Add(dt2)
Dim da1 As New OleDbDataAdapter("Select * from [tblLanden]",
cnBeActionData)
Dim da2 As New OleDbDataAdapter("Select * from
[tblLandenPostcodes]", cnBeActionData)

da1.FillSchema(dt1, SchemaType.Mapped)
da2.FillSchema(dt2, SchemaType.Mapped)

Dim rel As New DataRelation("FKPostcLanden_Postcodes",
dsLandenPostcodes.Tables(0).Columns("Landcode"),
dsLandenPostcodes.Tables(1).Columns("Landcode"))
dsLandenPostcodes.Relations.Add(rel)

Dim bsPostcodeLanden As New BindingSource
bsPostcodeLanden.DataMember = dsLandenPostcodes.Tables(0).TableName
bsPostcodeLanden.DataSource = dsLandenPostcodes

Dim bsPostcodes As New BindingSource
bsPostcodes.DataSource = bsPostcodeLanden
bsPostcodes.DataMember = "FKPostcLanden_Postcodes"

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

Me.dgvPostcodeLanden.DataSource = bsPostcodeLanden
Me.dgvPostcodes.DataSource = bsPostcodes
End Sub
'************Einde code***************
Click to expand...
 
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
tblCountryPostcodes
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 said:
Hi Mark,

Here is a sample I just wrote:

Imports System.Data.OleDb
Public Class Form2
Dim conn As OleDbConnection, da As OleDbDataAdapter, ds As DataSet
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
conn = New OleDbConnection
conn.ConnectionString = "provider=microsoft.jet.oledb.4.0; Data
Source = db2test.mdb"
da = New OleDbDataAdapter
ds = New DataSet
da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = conn
da.SelectCommand.CommandType = CommandType.Text
da.SelectCommand.CommandText = "Select * from Table1"
da.Fill(ds, "tbl1")
dgrv1.DataSource = ds.Tables(0)
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
da.UpdateCommand = New OleDbCommand
da.UpdateCommand.Connection = conn
da.UpdateCommand.CommandType = CommandType.Text
da.UpdateCommand.CommandText = "Update Table1 Set Field3 = 'aaa'
where ID = 3"
conn.Open()
da.UpdateCommand.ExecuteNonQuery()
'da.Update(ds, "tbl1") '--<<---had to use ExecuteNonQuery since
this
didn't run
ds.Clear()
da.Fill(ds, "tbl1")
dgrv1.DataSource = 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.UpdateCommand.ExecuteNonQuery 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 said:
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.



'************begin code***************
Imports System
Imports System.Data
Imports system.Data.oledb



Public Class frmPostcodesTypedDS

Inherits System.Windows.Forms.Form

Dim strPath As String = "C:\AADatabases\BeActionData.mdb"
Dim strCnBeActionData As String = "provider=microsoft.jet.oledb.4.0;
Data Source=" & strPath

Dim daActionData As OleDbDataAdapter
'Dim mydtPostcodes As dtPostcodes = New dtPostcodes()
Dim cnBeActionData As New OleDbConnection(strCnBeActionData)

Dim dsLandenPostcodes As New DataSet
Dim da1 As New OleDbDataAdapter
Dim da2 As New OleDbDataAdapter

Dim dt1 As New DataTable
Dim dt2 As New DataTable


Private Sub frmPostcodes_Load(ByVal sender As System.Object, ByVal e
As
System.EventArgs) Handles MyBase.Load
Call LoadPostcodes()
End Sub


Sub LoadPostcodes()

' Dim dsLandenPostcodes As New DataSet
Dim dt1 As New DataTable
Dim dt2 As New DataTable
dsLandenPostcodes.Tables.Add(dt1)
dsLandenPostcodes.Tables.Add(dt2)
Dim da1 As New OleDbDataAdapter("Select * from [tblLanden]",
cnBeActionData)
Dim da2 As New OleDbDataAdapter("Select * from
[tblLandenPostcodes]", cnBeActionData)

da1.FillSchema(dt1, SchemaType.Mapped)
da2.FillSchema(dt2, SchemaType.Mapped)

Dim rel As New DataRelation("FKPostcLanden_Postcodes",
dsLandenPostcodes.Tables(0).Columns("Landcode"),
dsLandenPostcodes.Tables(1).Columns("Landcode"))
dsLandenPostcodes.Relations.Add(rel)

Dim bsPostcodeLanden As New BindingSource
bsPostcodeLanden.DataMember =
dsLandenPostcodes.Tables(0).TableName
bsPostcodeLanden.DataSource = dsLandenPostcodes

Dim bsPostcodes As New BindingSource
bsPostcodes.DataSource = bsPostcodeLanden
bsPostcodes.DataMember = "FKPostcLanden_Postcodes"

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

Me.dgvPostcodeLanden.DataSource = bsPostcodeLanden
Me.dgvPostcodes.DataSource = bsPostcodes
End Sub
'************Einde code***************
Click to expand...
Click to expand...
 
Back
Top