473,327 Members | 2,012 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

using strongly typed dataset update problem

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***************
Oct 4 '06 #1
2 1854
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***************
Oct 4 '06 #2
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" <Ri**@discussions.microsoft.comschreef in bericht
news:A8**********************************@microsof t.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***************

Oct 5 '06 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: theWizK | last post by:
Hello all. I have noticed that when I generate a strongly-typed dataset from an xml schema that the DataTables that are generated have their constructors marked as internal. What this means is...
1
by: Job Lot | last post by:
I am confused how strongly typed dataset is different from un-typed dataset. Is there any good link explaining pros and cons of both? Which one should be used preferably?
0
by: Uchiha Jax | last post by:
When using a strongly typed dataset (generated from the Visual Studio IDE from an XSD file) and databinding I get a really odd error when binding to both a combox and a datetimepicker. I bind...
2
by: David | last post by:
I have been developing applications with Java for quite a while but I am new to .NET development. I am trying to learn the ".NET way" of creating Strongly Typed Objects from a database. The...
3
by: sck10 | last post by:
Hello, I am creating a form for users to enter information about a lab and the members of the lab. I have one form (FormView) that they use to enter information about that lab. The keyvalue is...
1
by: Jim | last post by:
Using VS 2005. I have bound fields on a form bound to a dataset(s) and I want to add various data elements to fields in the DB that are not comming from bound fields. This is what I have resorted...
2
by: Scotty | last post by:
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...
5
by: Monty M. | last post by:
Hello; I was wondering if anyone can assist me with this problem. Here are the tools I am using: Language: C# Database: MS SQL Server 2000 Application: Visual Studio 2005 1. I have a...
6
by: Dhananjay | last post by:
hello everyone i have got a problem i want to design business layer, data access layer , presentation layer for asp.net using C#.net , can anyone help me to solve this problem. i want some...
12
by: Simon | last post by:
Hi all, I'm having a baffling problem with a windows service that I'm working on. Basically, I am using a typed dataset to insert a large number of rows into an SQL Server 2005 database. But...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.