How-to Update Database

Shanon Swafford · Oct 14, 2003

Hey guys,

I have a VB.NET application that I am trying to make update an Access
Database. I have had success inserting rows but can not for the life of me
figure out how to update and existing row.

In the following code I have 2 text boxes bound to a dataset
(dataset11.Customers) when I change the value in the first box and hit
update, the value of the second box changes to match which makes me believe
that I am changing the dataset and all I need to do is "save" that to the
database.

But I haven't had any luck.

Can someone help me fill in the blanks on how to update CustomerID = "220"
to "Shanon"?

Thanks and please let me know if you have any questions.
Shanon

To email directly, take the "g"s out of my address.

Public Class Form1

Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents Label1 As System.Windows.Forms.Label

Friend WithEvents TextBox1 As System.Windows.Forms.TextBox

Friend WithEvents OleDbDataAdapter1 As
System.Data.OleDb.OleDbDataAdapter

Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection

Friend WithEvents DataSet11 As TestDatabase.DataSet1

Friend WithEvents TextBox2 As System.Windows.Forms.TextBox

Friend WithEvents Label2 As System.Windows.Forms.Label

Friend WithEvents btnUpdate As System.Windows.Forms.Button

Friend WithEvents btnLoad As System.Windows.Forms.Button

<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

Me.Label1 = New System.Windows.Forms.Label

Me.TextBox1 = New System.Windows.Forms.TextBox

Me.DataSet11 = New TestDatabase.DataSet1

Me.btnUpdate = New System.Windows.Forms.Button

Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter

Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection

Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand

Me.TextBox2 = New System.Windows.Forms.TextBox

Me.btnLoad = New System.Windows.Forms.Button

Me.Label2 = New System.Windows.Forms.Label

CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'

'Label1

'

Me.Label1.Location = New System.Drawing.Point(32, 40)

Me.Label1.Name = "Label1"

Me.Label1.Size = New System.Drawing.Size(100, 40)

Me.Label1.TabIndex = 0

Me.Label1.Text = "Make Changes Here"

'

'TextBox1

'

Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DataSet11,
"Customers.Company_Name"))

Me.TextBox1.Location = New System.Drawing.Point(136, 40)

Me.TextBox1.Name = "TextBox1"

Me.TextBox1.TabIndex = 1

Me.TextBox1.Text = "TextBox1"

'

'DataSet11

'

Me.DataSet11.DataSetName = "DataSet1"

Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")

'

'btnUpdate

'

Me.btnUpdate.Location = New System.Drawing.Point(264, 40)

Me.btnUpdate.Name = "btnUpdate"

Me.btnUpdate.Size = New System.Drawing.Size(88, 23)

Me.btnUpdate.TabIndex = 2

Me.btnUpdate.Text = "Save Changes"

'

'OleDbDataAdapter1

'

Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1

Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1

Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1

Me.OleDbDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Customers", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New
System.Data.Common.DataColumnMapping("Company_Name", "Company_Name")})})

Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1

'

'OleDbDeleteCommand1

'

Me.OleDbDeleteCommand1.CommandText = "DELETE FROM Customers WHERE
(CustomerID = ?) AND (Company_Name = ? OR ? IS NULL A" & _

"ND Company_Name IS NULL)"

Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"CustomerID", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

'

'OleDbConnection1

'

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB

atabase L" & _

"ocking Mode=1;Jet OLEDB

atabase Password=;Data
Source=""C:\Data\SacEnergy\Sac_Or" & _

"ders.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
Transactions=1" & _

";Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=Fal" & _

"se;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database
Password=;Je" & _

"t OLEDB:Create System Database=False;Jet OLEDB

on't Copy Locale on
Compact=Fals" & _

"e;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet
OLEDB:Encrypt" & _

" Database=False"

'

'OleDbInsertCommand1

'

Me.OleDbInsertCommand1.CommandText = "INSERT INTO
Customers(Company_Name) VALUES (?)"

Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1

Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company_Name"))

'

'OleDbSelectCommand1

'

Me.OleDbSelectCommand1.CommandText = "SELECT CustomerID,
Company_Name FROM Customers WHERE (CustomerID = 220)"

Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1

'

'OleDbUpdateCommand1

'

Me.OleDbUpdateCommand1.CommandText = "UPDATE Customers SET
Company_Name = ? WHERE (CustomerID = ?) AND (Company_Name = " & _

"? OR ? IS NULL AND Company_Name IS NULL)"

Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company_Name"))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"CustomerID", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

'

'TextBox2

'

Me.TextBox2.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DataSet11,
"Customers.Company_Name"))

Me.TextBox2.Location = New System.Drawing.Point(136, 120)

Me.TextBox2.Name = "TextBox2"

Me.TextBox2.TabIndex = 3

Me.TextBox2.Text = "TextBox2"

'

'btnLoad

'

Me.btnLoad.Location = New System.Drawing.Point(368, 40)

Me.btnLoad.Name = "btnLoad"

Me.btnLoad.RightToLeft = System.Windows.Forms.RightToLeft.No

Me.btnLoad.TabIndex = 4

Me.btnLoad.Text = "Load Table"

'

'Label2

'

Me.Label2.Location = New System.Drawing.Point(24, 120)

Me.Label2.Name = "Label2"

Me.Label2.Size = New System.Drawing.Size(100, 48)

Me.Label2.TabIndex = 5

Me.Label2.Text = "Dataset11 Customer Name Value"

'

'Form1

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(592, 266)

Me.Controls.Add(Me.Label2)

Me.Controls.Add(Me.btnLoad)

Me.Controls.Add(Me.TextBox2)

Me.Controls.Add(Me.btnUpdate)

Me.Controls.Add(Me.TextBox1)

Me.Controls.Add(Me.Label1)

Me.Name = "Form1"

Me.Text = "Form1"

CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load

TextBox1.Text = ""

TextBox2.Text = ""

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

End Sub

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click

DataSet11.Clear()

OleDbDataAdapter1.Fill(DataSet11, "Customers")

End Sub

End Class

Greg · Oct 15, 2003

You are always in an edit mode when you initially databind. You need to
call EndCurrentEdit on your BindingContext. Doing this will create a
'cache' of modified DataRows. You can hand the modified rows off to the
DataAdapter.Update method as a DataTable.

Shanon Swafford · Oct 15, 2003

Shanon Swafford said:
Hey guys,

I have a VB.NET application that I am trying to make update an Access
Database. I have had success inserting rows but can not for the life of me
figure out how to update and existing row.

In the following code I have 2 text boxes bound to a dataset
(dataset11.Customers) when I change the value in the first box and hit
update, the value of the second box changes to match which makes me believe
that I am changing the dataset and all I need to do is "save" that to the
database.

But I haven't had any luck.

Can someone help me fill in the blanks on how to update CustomerID = "220"
to "Shanon"?

Thanks and please let me know if you have any questions.
Shanon

To email directly, take the "g"s out of my address.

Public Class Form1

Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents Label1 As System.Windows.Forms.Label

Friend WithEvents TextBox1 As System.Windows.Forms.TextBox

Friend WithEvents OleDbDataAdapter1 As
System.Data.OleDb.OleDbDataAdapter

Friend WithEvents OleDbSelectCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbInsertCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbUpdateCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbDeleteCommand1 As System.Data.OleDb.OleDbCommand

Friend WithEvents OleDbConnection1 As System.Data.OleDb.OleDbConnection

Friend WithEvents DataSet11 As TestDatabase.DataSet1

Friend WithEvents TextBox2 As System.Windows.Forms.TextBox

Friend WithEvents Label2 As System.Windows.Forms.Label

Friend WithEvents btnUpdate As System.Windows.Forms.Button

Friend WithEvents btnLoad As System.Windows.Forms.Button

<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()

Me.Label1 = New System.Windows.Forms.Label

Me.TextBox1 = New System.Windows.Forms.TextBox

Me.DataSet11 = New TestDatabase.DataSet1

Me.btnUpdate = New System.Windows.Forms.Button

Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter

Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection

Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand

Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand

Me.TextBox2 = New System.Windows.Forms.TextBox

Me.btnLoad = New System.Windows.Forms.Button

Me.Label2 = New System.Windows.Forms.Label

CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginInit()

Me.SuspendLayout()

'

'Label1

'

Me.Label1.Location = New System.Drawing.Point(32, 40)

Me.Label1.Name = "Label1"

Me.Label1.Size = New System.Drawing.Size(100, 40)

Me.Label1.TabIndex = 0

Me.Label1.Text = "Make Changes Here"

'

'TextBox1

'

Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DataSet11,
"Customers.Company_Name"))

Me.TextBox1.Location = New System.Drawing.Point(136, 40)

Me.TextBox1.Name = "TextBox1"

Me.TextBox1.TabIndex = 1

Me.TextBox1.Text = "TextBox1"

'

'DataSet11

'

Me.DataSet11.DataSetName = "DataSet1"

Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")

'

'btnUpdate

'

Me.btnUpdate.Location = New System.Drawing.Point(264, 40)

Me.btnUpdate.Name = "btnUpdate"

Me.btnUpdate.Size = New System.Drawing.Size(88, 23)

Me.btnUpdate.TabIndex = 2

Me.btnUpdate.Text = "Save Changes"

'

'OleDbDataAdapter1

'

Me.OleDbDataAdapter1.DeleteCommand = Me.OleDbDeleteCommand1

Me.OleDbDataAdapter1.InsertCommand = Me.OleDbInsertCommand1

Me.OleDbDataAdapter1.SelectCommand = Me.OleDbSelectCommand1

Me.OleDbDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Customers", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("CustomerID", "CustomerID"), New
System.Data.Common.DataColumnMapping("Company_Name", "Company_Name")})})

Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1

'

'OleDbDeleteCommand1

'

Me.OleDbDeleteCommand1.CommandText = "DELETE FROM Customers WHERE
(CustomerID = ?) AND (Company_Name = ? OR ? IS NULL A" & _

"ND Company_Name IS NULL)"

Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"CustomerID", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

'

'OleDbConnection1

'

Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global Partial
Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _

"ocking Mode=1;Jet OLEDBatabase Password=;Data
Source=""C:\Data\SacEnergy\Sac_Or" & _

"ders.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk
Transactions=1" & _

";Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System
database=;Jet OLEDB:SFP=Fal" & _

"se;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database
Password=;Je" & _

"t OLEDB:Create System Database=False;Jet OLEDBon't Copy Locale on
Compact=Fals" & _

"e;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet
OLEDB:Encrypt" & _

" Database=False"

'

'OleDbInsertCommand1

'

Me.OleDbInsertCommand1.CommandText = "INSERT INTO
Customers(Company_Name) VALUES (?)"

Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1

Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company_Name"))

'

'OleDbSelectCommand1

'

Me.OleDbSelectCommand1.CommandText = "SELECT CustomerID,
Company_Name FROM Customers WHERE (CustomerID = 220)"

Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1

'

'OleDbUpdateCommand1

'

Me.OleDbUpdateCommand1.CommandText = "UPDATE Customers SET
Company_Name = ? WHERE (CustomerID = ?) AND (Company_Name = " & _

"? OR ? IS NULL AND Company_Name IS NULL)"

Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50, "Company_Name"))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_CustomerID",
System.Data.OleDb.OleDbType.Integer, 0,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"CustomerID", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_Company_Name1",
System.Data.OleDb.OleDbType.VarWChar, 50,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"Company_Name", System.Data.DataRowVersion.Original, Nothing))

'

'TextBox2

'

Me.TextBox2.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.DataSet11,
"Customers.Company_Name"))

Me.TextBox2.Location = New System.Drawing.Point(136, 120)

Me.TextBox2.Name = "TextBox2"

Me.TextBox2.TabIndex = 3

Me.TextBox2.Text = "TextBox2"

'

'btnLoad

'

Me.btnLoad.Location = New System.Drawing.Point(368, 40)

Me.btnLoad.Name = "btnLoad"

Me.btnLoad.RightToLeft = System.Windows.Forms.RightToLeft.No

Me.btnLoad.TabIndex = 4

Me.btnLoad.Text = "Load Table"

'

'Label2

'

Me.Label2.Location = New System.Drawing.Point(24, 120)

Me.Label2.Name = "Label2"

Me.Label2.Size = New System.Drawing.Size(100, 48)

Me.Label2.TabIndex = 5

Me.Label2.Text = "Dataset11 Customer Name Value"

'

'Form1

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(592, 266)

Me.Controls.Add(Me.Label2)

Me.Controls.Add(Me.btnLoad)

Me.Controls.Add(Me.TextBox2)

Me.Controls.Add(Me.btnUpdate)

Me.Controls.Add(Me.TextBox1)

Me.Controls.Add(Me.Label1)

Me.Name = "Form1"

Me.Text = "Form1"

CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit()

Me.ResumeLayout(False)

End Sub

#End Region

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load

TextBox1.Text = ""

TextBox2.Text = ""

End Sub

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

End Sub

Private Sub btnLoad_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnLoad.Click

DataSet11.Clear()

OleDbDataAdapter1.Fill(DataSet11, "Customers")

End Sub

End Class

I got it to work by putting the following in the update button event
procedure.

But is there a better way?

Last, with ASP you are supposed to close your connections. Should I doing
something like this in VB.NET? The only method I can find with a close is
OleDbConnection1.Close().

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnUpdate.Click

Dim tblCustomer As DataTable

tblCustomer = DataSet11.Tables("Customers")

Dim drCurrent As DataRow

drCurrent = tblCustomer.NewRow()

drCurrent = tblCustomer.Rows.Find("220")

drCurrent.BeginEdit()

drCurrent.EndEdit()

MsgBox("Record edited successfully")

OleDbDataAdapter1.Update(DataSet11, "Customers")

MsgBox("SQL Server updated successfully" & Chr(13) & "Check Server
explorer to see changes")

End Sub

Greg · Oct 15, 2003

I am confused...are you adding a new row or updating an existing row? You
are calling NewRow but your original post described an update.

Shanon Swafford · Oct 15, 2003

Greg said:
I am confused...are you adding a new row or updating an existing row? You
are calling NewRow but your original post described an update.

Updating an existing row. The NewRow is in the temporary table
"tblCustomer" but when the Update command is called, I think that somewhere
behind the scenes tblCustomer containing one row is compared Dataset11
containing one row and the database is updated with the new values. I tried
taking the beginedit and endedit out and the table isn't
updated.

It works with updating 1 or many of the fields that I have databound to
the dataset.

I found "Microsoft Knowledge Base Article - 308055" and got the procedure
from there.

Thanks for your help.
Shanon