using strongly typed dataset update problem

4 Oct 2006 8:39 PM

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***************

4 Oct 2006 10:25 PM

Rich

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

Show quoteHide quote

"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.
>
>
>
> '************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***************
>
>
>

5 Oct 2006 6:12 AM

Scotty

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

Show quoteHide quote

"Rich" <R***@discussions.microsoft.com> schreef in bericht
news:A8DCB593-6877-4EAD-AECE-5C864C0C879F@microsoft.com...
> 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" 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.
>>
>>
>>
>> '************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***************
>>
>>
>>