Re: VB.NET DataAdapter.Update

Tech-Archive recommends: Fix windows errors by optimizing your registry

Don't mean to be rude, but why are you posting a VB.NET question on the C#
newsgroup?

There are at least 4 newsgroups that would be far more appropriate for your
question:

m.p.d.framework.windowsforms
m.p.d.framework.adonet
m.p.d.languages.vb
m.p.d.languages.vb.data

Pete

"Joe Fetters via .NET 247" <anonymous@xxxxxxxxxxxxx> wrote in message
news:%23f$0uzEPFHA.508@xxxxxxxxxxxxxxxxxxxxxxx
Have googled and read the VS.NET documentation can't seem to get the answer
to the following.

Environment:
Framework 1.1
VB.NET WinForm
Access database
Using all automagic tools (DataAdapter Wizard with generated Dataset and
generated Select, Update, Insert and Delete commands, controls bound to
DataSet using the Advanced Databinding properties)

Issue:
Do I have to set parameters before I issue the DataSet.Update(Dataset,
[Table])? Again, my goal is minimal coding and let VS.NET generate
everything for me. I'm hoping there's an advance data property I need to
set on each control to bind it to the dataset. Currently the Text property
on the Textboxes is bound to the dataset and the Checked properties are set
on the CheckBoxes.

When I issue the command w/out setting any values it generates a "Additional
information: Concurrency violation: the UpdateCommand affected 0 records."
error.

Thanks for your time in advance,

Joe

VB.NET Code
*******************
Public Class Form2
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 OrgConnection As System.Data.OleDb.OleDbConnection
Friend WithEvents OrganizationAllowDelete As
System.Windows.Forms.CheckBox
Friend WithEvents btnSave As System.Windows.Forms.Button
Friend WithEvents OrgAdapter As System.Data.OleDb.OleDbDataAdapter
Friend WithEvents btnPrevious As System.Windows.Forms.Button
Friend WithEvents btnNext As System.Windows.Forms.Button
Friend WithEvents OrgDataSet As WindowsApplication3.OrgDataSet
Friend WithEvents OrganizationID As System.Windows.Forms.TextBox
Friend WithEvents OrganizationName As System.Windows.Forms.TextBox
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
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.btnSave = New System.Windows.Forms.Button
Me.OrganizationID = New System.Windows.Forms.TextBox
Me.OrgDataSet = New WindowsApplication3.OrgDataSet
Me.OrganizationName = New System.Windows.Forms.TextBox
Me.OrgConnection = New System.Data.OleDb.OleDbConnection
Me.OrgAdapter = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
Me.OrganizationAllowDelete = New System.Windows.Forms.CheckBox
Me.btnPrevious = New System.Windows.Forms.Button
Me.btnNext = New System.Windows.Forms.Button
CType(Me.OrgDataSet,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'btnSave
'
Me.btnSave.Location = New System.Drawing.Point(256, 88)
Me.btnSave.Name = "btnSave"
Me.btnSave.TabIndex = 0
Me.btnSave.Text = "Save"
'
'OrganizationID
'
Me.OrganizationID.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.OrgDataSet,
"Organizations.OrganizationID"))
Me.OrganizationID.Location = New System.Drawing.Point(0, 8)
Me.OrganizationID.Name = "OrganizationID"
Me.OrganizationID.Size = New System.Drawing.Size(350, 20)
Me.OrganizationID.TabIndex = 1
Me.OrganizationID.Text = "OrganizationID"
'
'OrgDataSet
'
Me.OrgDataSet.DataSetName = "OrgDataSet"
Me.OrgDataSet.Locale = New System.Globalization.CultureInfo("en-US")
'
'OrganizationName
'
Me.OrganizationName.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.OrgDataSet,
"Organizations.OrganizationName"))
Me.OrganizationName.Location = New System.Drawing.Point(0, 32)
Me.OrganizationName.Name = "OrganizationName"
Me.OrganizationName.Size = New System.Drawing.Size(350, 20)
Me.OrganizationName.TabIndex = 2
Me.OrganizationName.Text = "OrganizationName"
'
'OrgConnection
'
Me.OrgConnection.ConnectionString = "Jet OLEDB:Global Partial Bulk
Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source="{PathTo}\"
& _
"Your.mdb;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Globa" & _
"l Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System databa" & _
"se=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny
None;Jet OLEDB:New " & _
"Database Password=;Jet OLEDB:Create System Database=False;Jet
OLEDB:Don't Copy L" & _
"ocale on Compact=False;Jet OLEDB:Compact Without Replica
Repair=False;User ID=Ad" & _
"min;Jet OLEDB:Encrypt Database=False"
'
'OrgAdapter
'
Me.OrgAdapter.DeleteCommand = Me.OleDbDeleteCommand1
Me.OrgAdapter.InsertCommand = Me.OleDbInsertCommand1
Me.OrgAdapter.MissingSchemaAction =
System.Data.MissingSchemaAction.AddWithKey
Me.OrgAdapter.SelectCommand = Me.OleDbSelectCommand1
Me.OrgAdapter.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "Organizations", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("LastModifiedDate",
"LastModifiedDate"), New
System.Data.Common.DataColumnMapping("LastModifiedUser",
"LastModifiedUser"), New
System.Data.Common.DataColumnMapping("OrganizationAbbrv",
"OrganizationAbbrv"), New
System.Data.Common.DataColumnMapping("OrganizationAllowDelete",
"OrganizationAllowDelete"), New
System.Data.Common.DataColumnMapping("OrganizationDescription",
"OrganizationDescription"), New
System.Data.Common.DataColumnMapping("OrganizationID", "OrganizationID"),
New System.Data.Common.DataColumnMapping("OrganizationIsRoot",
"OrganizationIsRoot"), New
System.Data.Common.DataColumnMapping("OrganizationName",
"OrganizationName"), New
System.Data.Common.DataColumnMapping("OrganizationNode",
"OrganizationNode")})})
Me.OrgAdapter.UpdateCommand = Me.OleDbUpdateCommand1
'
'OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM Organizations
WHERE (OrganizationID = ?) AND (LastModifiedDate = ?) A" & _
"ND (LastModifiedUser = ?) AND (OrganizationAbbrv = ? OR ? IS NULL
AND Organizati" & _
"onAbbrv IS NULL) AND (OrganizationAllowDelete = ?) AND
(OrganizationIsRoot = ?) " & _
"AND (OrganizationName = ?) AND (OrganizationNode = ?)"
Me.OleDbDeleteCommand1.Connection = Me.OrgConnection
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationID",
System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "OrganizationID",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastModifiedDate",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastModifiedDate",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastModifiedUser",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LastModifiedUser", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAbbrv",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationAbbrv", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAbbrv1",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationAbbrv", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAllowDelete",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationAllowDelete", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationIsRoot",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationIsRoot", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationName",
System.Data.OleDb.OleDbType.VarWChar, 100,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationName", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationNode",
System.Data.OleDb.OleDbType.VarWChar, 100,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationNode", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
Organizations(LastModifiedDate, LastModifiedUser, OrganizationAbbrv, " & _
"OrganizationAllowDelete, OrganizationDescription, OrganizationID,
OrganizationIs" & _
"Root, OrganizationName, OrganizationNode) VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?)"
Me.OleDbInsertCommand1.Connection = Me.OrgConnection
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastModifiedDate",
System.Data.OleDb.OleDbType.DBDate, 0, "LastModifiedDate"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastModifiedUser",
System.Data.OleDb.OleDbType.VarWChar, 30, "LastModifiedUser"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationAbbrv",
System.Data.OleDb.OleDbType.VarWChar, 10, "OrganizationAbbrv"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationAllowDelete",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationAllowDelete", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationDescription",
System.Data.OleDb.OleDbType.VarWChar, 0, "OrganizationDescription"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationID",
System.Data.OleDb.OleDbType.Guid, 0, "OrganizationID"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationIsRoot",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationIsRoot", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationName",
System.Data.OleDb.OleDbType.VarWChar, 100, "OrganizationName"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationNode",
System.Data.OleDb.OleDbType.VarWChar, 100, "OrganizationNode"))
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT LastModifiedDate,
LastModifiedUser, OrganizationAbbrv, OrganizationAllowDe" & _
"lete, OrganizationDescription, OrganizationID, OrganizationIsRoot,
OrganizationN" & _
"ame, OrganizationNode FROM Organizations"
Me.OleDbSelectCommand1.Connection = Me.OrgConnection
'
'OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = "UPDATE Organizations SET
LastModifiedDate = ?, LastModifiedUser = ?, Organization" & _
"Abbrv = ?, OrganizationAllowDelete = ?, OrganizationDescription =
?, Organizatio" & _
"nID = ?, OrganizationIsRoot = ?, OrganizationName = ?,
OrganizationNode = ? WHER" & _
"E (OrganizationID = ?) AND (LastModifiedDate = ?) AND
(LastModifiedUser = ?) AND" & _
" (OrganizationAbbrv = ? OR ? IS NULL AND OrganizationAbbrv IS NULL)
AND (Organiz" & _
"ationAllowDelete = ?) AND (OrganizationIsRoot = ?) AND
(OrganizationName = ?) AN" & _
"D (OrganizationNode = ?)"
Me.OleDbUpdateCommand1.Connection = Me.OrgConnection
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastModifiedDate",
System.Data.OleDb.OleDbType.DBDate, 0, "LastModifiedDate"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("LastModifiedUser",
System.Data.OleDb.OleDbType.VarWChar, 30, "LastModifiedUser"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationAbbrv",
System.Data.OleDb.OleDbType.VarWChar, 10, "OrganizationAbbrv"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationAllowDelete",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationAllowDelete", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationDescription",
System.Data.OleDb.OleDbType.VarWChar, 0, "OrganizationDescription"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationID",
System.Data.OleDb.OleDbType.Guid, 0, "OrganizationID"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationIsRoot",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationIsRoot", System.Data.DataRowVersion.Current, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationName",
System.Data.OleDb.OleDbType.VarWChar, 100, "OrganizationName"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("OrganizationNode",
System.Data.OleDb.OleDbType.VarWChar, 100, "OrganizationNode"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationID",
System.Data.OleDb.OleDbType.Guid, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "OrganizationID",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastModifiedDate",
System.Data.OleDb.OleDbType.DBDate, 0, System.Data.ParameterDirection.Input,
False, CType(0, Byte), CType(0, Byte), "LastModifiedDate",
System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_LastModifiedUser",
System.Data.OleDb.OleDbType.VarWChar, 30,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"LastModifiedUser", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAbbrv",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationAbbrv", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAbbrv1",
System.Data.OleDb.OleDbType.VarWChar, 10,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationAbbrv", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationAllowDelete",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationAllowDelete", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationIsRoot",
System.Data.OleDb.OleDbType.Decimal, 0,
System.Data.ParameterDirection.Input, False, CType(1, Byte), CType(0, Byte),
"OrganizationIsRoot", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationName",
System.Data.OleDb.OleDbType.VarWChar, 100,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationName", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_OrganizationNode",
System.Data.OleDb.OleDbType.VarWChar, 100,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0, Byte),
"OrganizationNode", System.Data.DataRowVersion.Original, Nothing))
'
'OrganizationAllowDelete
'
Me.OrganizationAllowDelete.DataBindings.Add(New
System.Windows.Forms.Binding("Checked", Me.OrgDataSet,
"Organizations.OrganizationAllowDelete"))
Me.OrganizationAllowDelete.Location = New System.Drawing.Point(88,
56)
Me.OrganizationAllowDelete.Name = "OrganizationAllowDelete"
Me.OrganizationAllowDelete.Size = New System.Drawing.Size(168, 24)
Me.OrganizationAllowDelete.TabIndex = 9
Me.OrganizationAllowDelete.Text = "OrganizationAllowDelete"
'
'btnPrevious
'
Me.btnPrevious.Location = New System.Drawing.Point(16, 88)
Me.btnPrevious.Name = "btnPrevious"
Me.btnPrevious.TabIndex = 11
Me.btnPrevious.Text = "Previous"
'
'btnNext
'
Me.btnNext.Location = New System.Drawing.Point(128, 88)
Me.btnNext.Name = "btnNext"
Me.btnNext.TabIndex = 12
Me.btnNext.Text = "Next"
'
'Form2
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(352, 117)
Me.Controls.Add(Me.btnNext)
Me.Controls.Add(Me.btnPrevious)
Me.Controls.Add(Me.OrganizationAllowDelete)
Me.Controls.Add(Me.OrganizationName)
Me.Controls.Add(Me.OrganizationID)
Me.Controls.Add(Me.btnSave)
Me.Name = "Form2"
Me.Text = "Form1"
CType(Me.OrgDataSet,
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
Me.OrgAdapter.Fill(Me.OrgDataSet)
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNext.Click
MoveRecord(1)
End Sub

Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnPrevious.Click
MoveRecord(-1)
End Sub
Private Sub MoveRecord(ByVal Count As Integer)
Me.BindingContext(Me.OrgDataSet, "Organizations").Position += Count
End Sub

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSave.Click
Me.OrgAdapter.Update(Me.OrgDataSet, "Organizations")
End Sub
End Class

--------------------------------
From: Joe Fetters

-----------------------
Posted by a user from .NET 247 (http://www.dotnet247.com/)

<Id>txqPPDwRE0mPrb+Ji1A9Kg==</Id>


.

Quantcast