Newbie - Update method not saving with Access database

M

Mike

Hi All;

Learning ADO.Net and did a simple form to see how everything works.

Data is displayed but changes are not updated.

Here is the code:

Imports System.Data.OleDb

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 RichTextBox1 As System.Windows.Forms.RichTextBox
Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents OleDbConnection1 As
System.Data.OleDb.OleDbConnection
Friend WithEvents OleDbDataAdapter1 As
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents ExplanatoryDataSet1 As
CSIComboBox.ExplanatoryDataSet
Friend WithEvents OleDbDataAdapter2 As
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents ExpCodesDataSet1 As CSIComboBox.ExpCodesDataSet1
Friend WithEvents OleDbSelectCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbInsertCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbUpdateCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbDeleteCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents save As System.Windows.Forms.Button
Friend WithEvents Delete As System.Windows.Forms.Button
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.RichTextBox1 = New System.Windows.Forms.RichTextBox
Me.ExplanatoryDataSet1 = New CSIComboBox.ExplanatoryDataSet
Me.ExpCodesDataSet1 = New CSIComboBox.ExpCodesDataSet1
Me.ComboBox1 = New System.Windows.Forms.ComboBox
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDataAdapter2 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDeleteCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbSelectCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand2 = New System.Data.OleDb.OleDbCommand
Me.save = New System.Windows.Forms.Button
Me.Delete = New System.Windows.Forms.Button
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
CType(Me.ExplanatoryDataSet1,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.ExpCodesDataSet1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'RichTextBox1
'
Me.RichTextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_desc"))
Me.RichTextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Tag", Me.ExpCodesDataSet1,
"msp_explanatory_codes.exp_id"))
Me.RichTextBox1.Location = New System.Drawing.Point(40, 88)
Me.RichTextBox1.Name = "RichTextBox1"
Me.RichTextBox1.Size = New System.Drawing.Size(224, 36)
Me.RichTextBox1.TabIndex = 2
Me.RichTextBox1.Text = "RichTextBox1"
'
'ExplanatoryDataSet1
'
Me.ExplanatoryDataSet1.DataSetName = "ExplanatoryDataSet"
Me.ExplanatoryDataSet1.Locale = New
System.Globalization.CultureInfo("en-US")
'
'ExpCodesDataSet1
'
Me.ExpCodesDataSet1.DataSetName = "ExpCodesDataSet1"
Me.ExpCodesDataSet1.Locale = New
System.Globalization.CultureInfo("en-US")
'
'ComboBox1
'
Me.ComboBox1.DataSource =
Me.ExpCodesDataSet1.msp_explanatory_codes
Me.ComboBox1.DisplayMember = "exp_id"
Me.ComboBox1.Location = New System.Drawing.Point(40, 20)
Me.ComboBox1.Name = "ComboBox1"
Me.ComboBox1.Size = New System.Drawing.Size(164, 21)
Me.ComboBox1.TabIndex = 3
Me.ComboBox1.ValueMember = "exp_id"
'
'TextBox1
'
Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_solution"))
Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Tag", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_solution"))
Me.TextBox1.Location = New System.Drawing.Point(44, 156)
Me.TextBox1.Multiline = True
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(220, 80)
Me.TextBox1.TabIndex = 4
Me.TextBox1.Text = "TextBox1"
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=0;Data
Source=""C:\test.db\great_big_test_database.mdb"";Jet OLEDB:Eng" & _
"ine Type=4;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Jet OLE" & _
"DB:SFP=False;persist security info=False;Extended
Properties=;Mode=Share Deny No" & _
"ne;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet O" & _
"LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair" & _
"=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'
'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", "msp_explanatory_codes",
New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("exp_desc", "exp_desc"), New
System.Data.Common.DataColumnMapping("exp_id", "exp_id"), New
System.Data.Common.DataColumnMapping("exp_solution",
"exp_solution")})})
Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
'
'OleDbDataAdapter2
'
Me.OleDbDataAdapter2.DeleteCommand = Me.OleDbDeleteCommand2
Me.OleDbDataAdapter2.InsertCommand = Me.OleDbInsertCommand2
Me.OleDbDataAdapter2.SelectCommand = Me.OleDbSelectCommand2
Me.OleDbDataAdapter2.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "msp_explanatory_codes",
New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("exp_id", "exp_id")})})
Me.OleDbDataAdapter2.UpdateCommand = Me.OleDbUpdateCommand2
'
'OleDbDeleteCommand2
'
Me.OleDbDeleteCommand2.CommandText = "DELETE FROM
msp_explanatory_codes WHERE (exp_id = ?)"
Me.OleDbDeleteCommand2.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbInsertCommand2
'
Me.OleDbInsertCommand2.CommandText = "INSERT INTO
msp_explanatory_codes(exp_id) VALUES (?)"
Me.OleDbInsertCommand2.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
'
'OleDbSelectCommand2
'
Me.OleDbSelectCommand2.CommandText = "SELECT exp_id FROM
msp_explanatory_codes"
Me.OleDbSelectCommand2.Connection = Me.OleDbConnection1
'
'OleDbUpdateCommand2
'
Me.OleDbUpdateCommand2.CommandText = "UPDATE
msp_explanatory_codes SET exp_id = ? WHERE (exp_id = ?)"
Me.OleDbUpdateCommand2.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbUpdateCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
'
'save
'
Me.save.Location = New System.Drawing.Point(28, 256)
Me.save.Name = "save"
Me.save.Size = New System.Drawing.Size(64, 24)
Me.save.TabIndex = 5
Me.save.Text = "Save"
'
'Delete
'
Me.Delete.Location = New System.Drawing.Point(168, 256)
Me.Delete.Name = "Delete"
Me.Delete.Size = New System.Drawing.Size(68, 24)
Me.Delete.TabIndex = 6
Me.Delete.Text = "Delete"
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT exp_desc, exp_id,
exp_solution FROM msp_explanatory_codes WHERE (exp_id = " & _
"?)"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
Me.OleDbSelectCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
msp_explanatory_codes(exp_desc, exp_id, exp_solution) VALUES (?, ?, ?"
& _
")"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180, "exp_desc"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240, "exp_solution"))
'
'OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = "UPDATE
msp_explanatory_codes SET exp_desc = ?, exp_id = ?, exp_solution = ?
WHERE" & _
" (exp_id = ?) AND (exp_desc = ?) AND (exp_solution = ? OR ?
IS NULL AND exp_solu" & _
"tion IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180, "exp_desc"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240, "exp_solution"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_desc", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution1",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM
msp_explanatory_codes WHERE (exp_id = ?) AND (exp_desc = ?) AND (exp_"
& _
"solution = ? OR ? IS NULL AND exp_solution IS NULL)"
Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_desc", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution1",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 294)
Me.Controls.Add(Me.Delete)
Me.Controls.Add(Me.save)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.ComboBox1)
Me.Controls.Add(Me.RichTextBox1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.ExplanatoryDataSet1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.ExpCodesDataSet1,
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.ExpCodesDataSet1.Clear()
Me.OleDbDataAdapter2.Fill(Me.ExpCodesDataSet1)
End Sub

Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e
As System.EventArgs) Handles ComboBox1.TextChanged
Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.Text

Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)
End Sub


Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.SelectedValue

Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)

End Sub

Private Sub save_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles save.Click


MsgBox(Me.ExplanatoryDataSet1.HasChanges.ToString)
result = Me.OleDbDataAdapter1.Update(Me.ExplanatoryDataSet1)
MsgBox(result.ToString)

End Sub


End Class


Thanks!
 
W

William Ryan eMVP

Is it showing that HasChanges is true or false?
Mike said:
Hi All;

Learning ADO.Net and did a simple form to see how everything works.

Data is displayed but changes are not updated.

Here is the code:

Imports System.Data.OleDb

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 RichTextBox1 As System.Windows.Forms.RichTextBox
Friend WithEvents ComboBox1 As System.Windows.Forms.ComboBox
Friend WithEvents TextBox1 As System.Windows.Forms.TextBox
Friend WithEvents OleDbConnection1 As
System.Data.OleDb.OleDbConnection
Friend WithEvents OleDbDataAdapter1 As
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents ExplanatoryDataSet1 As
CSIComboBox.ExplanatoryDataSet
Friend WithEvents OleDbDataAdapter2 As
System.Data.OleDb.OleDbDataAdapter
Friend WithEvents ExpCodesDataSet1 As CSIComboBox.ExpCodesDataSet1
Friend WithEvents OleDbSelectCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbInsertCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbUpdateCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents OleDbDeleteCommand2 As
System.Data.OleDb.OleDbCommand
Friend WithEvents save As System.Windows.Forms.Button
Friend WithEvents Delete As System.Windows.Forms.Button
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.RichTextBox1 = New System.Windows.Forms.RichTextBox
Me.ExplanatoryDataSet1 = New CSIComboBox.ExplanatoryDataSet
Me.ExpCodesDataSet1 = New CSIComboBox.ExpCodesDataSet1
Me.ComboBox1 = New System.Windows.Forms.ComboBox
Me.TextBox1 = New System.Windows.Forms.TextBox
Me.OleDbConnection1 = New System.Data.OleDb.OleDbConnection
Me.OleDbDataAdapter1 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDataAdapter2 = New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDeleteCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbSelectCommand2 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand2 = New System.Data.OleDb.OleDbCommand
Me.save = New System.Windows.Forms.Button
Me.Delete = New System.Windows.Forms.Button
Me.OleDbSelectCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbInsertCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbUpdateCommand1 = New System.Data.OleDb.OleDbCommand
Me.OleDbDeleteCommand1 = New System.Data.OleDb.OleDbCommand
CType(Me.ExplanatoryDataSet1,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.ExpCodesDataSet1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'RichTextBox1
'
Me.RichTextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_desc"))
Me.RichTextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Tag", Me.ExpCodesDataSet1,
"msp_explanatory_codes.exp_id"))
Me.RichTextBox1.Location = New System.Drawing.Point(40, 88)
Me.RichTextBox1.Name = "RichTextBox1"
Me.RichTextBox1.Size = New System.Drawing.Size(224, 36)
Me.RichTextBox1.TabIndex = 2
Me.RichTextBox1.Text = "RichTextBox1"
'
'ExplanatoryDataSet1
'
Me.ExplanatoryDataSet1.DataSetName = "ExplanatoryDataSet"
Me.ExplanatoryDataSet1.Locale = New
System.Globalization.CultureInfo("en-US")
'
'ExpCodesDataSet1
'
Me.ExpCodesDataSet1.DataSetName = "ExpCodesDataSet1"
Me.ExpCodesDataSet1.Locale = New
System.Globalization.CultureInfo("en-US")
'
'ComboBox1
'
Me.ComboBox1.DataSource =
Me.ExpCodesDataSet1.msp_explanatory_codes
Me.ComboBox1.DisplayMember = "exp_id"
Me.ComboBox1.Location = New System.Drawing.Point(40, 20)
Me.ComboBox1.Name = "ComboBox1"
Me.ComboBox1.Size = New System.Drawing.Size(164, 21)
Me.ComboBox1.TabIndex = 3
Me.ComboBox1.ValueMember = "exp_id"
'
'TextBox1
'
Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Text", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_solution"))
Me.TextBox1.DataBindings.Add(New
System.Windows.Forms.Binding("Tag", Me.ExplanatoryDataSet1,
"msp_explanatory_codes.exp_solution"))
Me.TextBox1.Location = New System.Drawing.Point(44, 156)
Me.TextBox1.Multiline = True
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size = New System.Drawing.Size(220, 80)
Me.TextBox1.TabIndex = 4
Me.TextBox1.Text = "TextBox1"
'
'OleDbConnection1
'
Me.OleDbConnection1.ConnectionString = "Jet OLEDB:Global
Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=0;Data
Source=""C:\test.db\great_big_test_database.mdb"";Jet OLEDB:Eng" & _
"ine Type=4;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet
OLEDB:System database=;Jet OLE" & _
"DB:SFP=False;persist security info=False;Extended
Properties=;Mode=Share Deny No" & _
"ne;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System
Database=False;Jet O" & _
"LEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact
Without Replica Repair" & _
"=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1"
'
'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", "msp_explanatory_codes",
New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("exp_desc", "exp_desc"), New
System.Data.Common.DataColumnMapping("exp_id", "exp_id"), New
System.Data.Common.DataColumnMapping("exp_solution",
"exp_solution")})})
Me.OleDbDataAdapter1.UpdateCommand = Me.OleDbUpdateCommand1
'
'OleDbDataAdapter2
'
Me.OleDbDataAdapter2.DeleteCommand = Me.OleDbDeleteCommand2
Me.OleDbDataAdapter2.InsertCommand = Me.OleDbInsertCommand2
Me.OleDbDataAdapter2.SelectCommand = Me.OleDbSelectCommand2
Me.OleDbDataAdapter2.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New
System.Data.Common.DataTableMapping("Table", "msp_explanatory_codes",
New System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("exp_id", "exp_id")})})
Me.OleDbDataAdapter2.UpdateCommand = Me.OleDbUpdateCommand2
'
'OleDbDeleteCommand2
'
Me.OleDbDeleteCommand2.CommandText = "DELETE FROM
msp_explanatory_codes WHERE (exp_id = ?)"
Me.OleDbDeleteCommand2.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbInsertCommand2
'
Me.OleDbInsertCommand2.CommandText = "INSERT INTO
msp_explanatory_codes(exp_id) VALUES (?)"
Me.OleDbInsertCommand2.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
'
'OleDbSelectCommand2
'
Me.OleDbSelectCommand2.CommandText = "SELECT exp_id FROM
msp_explanatory_codes"
Me.OleDbSelectCommand2.Connection = Me.OleDbConnection1
'
'OleDbUpdateCommand2
'
Me.OleDbUpdateCommand2.CommandText = "UPDATE
msp_explanatory_codes SET exp_id = ? WHERE (exp_id = ?)"
Me.OleDbUpdateCommand2.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbUpdateCommand2.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
'
'save
'
Me.save.Location = New System.Drawing.Point(28, 256)
Me.save.Name = "save"
Me.save.Size = New System.Drawing.Size(64, 24)
Me.save.TabIndex = 5
Me.save.Text = "Save"
'
'Delete
'
Me.Delete.Location = New System.Drawing.Point(168, 256)
Me.Delete.Name = "Delete"
Me.Delete.Size = New System.Drawing.Size(68, 24)
Me.Delete.TabIndex = 6
Me.Delete.Text = "Delete"
'
'OleDbSelectCommand1
'
Me.OleDbSelectCommand1.CommandText = "SELECT exp_desc, exp_id,
exp_solution FROM msp_explanatory_codes WHERE (exp_id = " & _
"?)"
Me.OleDbSelectCommand1.Connection = Me.OleDbConnection1
Me.OleDbSelectCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
'
'OleDbInsertCommand1
'
Me.OleDbInsertCommand1.CommandText = "INSERT INTO
msp_explanatory_codes(exp_desc, exp_id, exp_solution) VALUES (?, ?, ?"
& _
")"
Me.OleDbInsertCommand1.Connection = Me.OleDbConnection1
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180, "exp_desc"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbInsertCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240, "exp_solution"))
'
'OleDbUpdateCommand1
'
Me.OleDbUpdateCommand1.CommandText = "UPDATE
msp_explanatory_codes SET exp_desc = ?, exp_id = ?, exp_solution = ?
WHERE" & _
" (exp_id = ?) AND (exp_desc = ?) AND (exp_solution = ? OR ?
IS NULL AND exp_solu" & _
"tion IS NULL)"
Me.OleDbUpdateCommand1.Connection = Me.OleDbConnection1
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180, "exp_desc"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2, "exp_id"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240, "exp_solution"))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_desc", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution1",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
'
'OleDbDeleteCommand1
'
Me.OleDbDeleteCommand1.CommandText = "DELETE FROM
msp_explanatory_codes WHERE (exp_id = ?) AND (exp_desc = ?) AND (exp_"
& _
"solution = ? OR ? IS NULL AND exp_solution IS NULL)"
Me.OleDbDeleteCommand1.Connection = Me.OleDbConnection1
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_id",
System.Data.OleDb.OleDbType.VarWChar, 2,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_id", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_desc",
System.Data.OleDb.OleDbType.VarWChar, 180,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_desc", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New
System.Data.OleDb.OleDbParameter("Original_exp_solution1",
System.Data.OleDb.OleDbType.VarWChar, 240,
System.Data.ParameterDirection.Input, False, CType(0, Byte), CType(0,
Byte), "exp_solution", System.Data.DataRowVersion.Original, Nothing))
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(292, 294)
Me.Controls.Add(Me.Delete)
Me.Controls.Add(Me.save)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.ComboBox1)
Me.Controls.Add(Me.RichTextBox1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.ExplanatoryDataSet1,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.ExpCodesDataSet1,
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.ExpCodesDataSet1.Clear()
Me.OleDbDataAdapter2.Fill(Me.ExpCodesDataSet1)
End Sub

Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e
As System.EventArgs) Handles ComboBox1.TextChanged
Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.Text

Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)
End Sub


Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.SelectedValue

Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)

End Sub

Private Sub save_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles save.Click


MsgBox(Me.ExplanatoryDataSet1.HasChanges.ToString)
result = Me.OleDbDataAdapter1.Update(Me.ExplanatoryDataSet1)
MsgBox(result.ToString)

End Sub


End Class


Thanks!
Click to expand...
 
W

William Ryan eMVP

Ok, that's the problem. If HasChanges is false, calling Update will do
nothing. http://www.knowdotnet.com/articles/efficient_pt4.html

For debugging purposes, I'd ask that you try something. Right before you
call .Update in the Save routine, call me.BindingContext.EndCurrentEdit (I
didn't see a binding context so it looks like it's done with the
designer..If I missed it, then use its name and call EndCurrentEdit on it,
otherwise, Try it on me.BindingContext and see if that fixes it. If so,
it's a temporary fix but will give some insight into what the problme is.
At the moment, we don't know if the Update Command is good or not, but since
the designer created we'll assume it is.

If you call the EndCurrentEdit, HasChanges should be True. Let me know if
it isn't.

Bill
 
T

Taras Pich

William,
Not to hijack the thread, but I have a similar problem. I've tried some of
your suggestions.
Some things to note:
1) EndCurrentEdit is not a member of 'System.Windows.Forms.BindingContext'
2) Debug.assert(dsUsers.HasChange,"Changes found") always returns changes
found regardless if changes were made or not
3) The update command generated reads
Me.OleDbUpdateCommand1.CommandText = "UPDATE UserAccessTBL SET [Password] =
?, Rights = ?, UserName = ? WHERE (UserName = ?) AND ([Password] = ?) AND
(Rights = ?)"

Won't the WHERE clause always be false if you're updating something? If you
take out the 2 ANDs it still doesn't work

TIA,

Hope your response helps both Mike & myself.
 
W

William Ryan eMVP

Taras Pich said:
William,
Not to hijack the thread, but I have a similar problem. I've tried some of
your suggestions.
Some things to note:
1) EndCurrentEdit is not a member of 'System.Windows.Forms.BindingContext'
Click to expand...
It's actually a member of BindingManagerBase which you set to the
BindingContext. So dimming bmb as BindingManagerBase = me.BindingContext is
what I meant. I was in a hurry as I was typing, my apologies for any
confustion.
2) Debug.assert(dsUsers.HasChange,"Changes found") always returns changes
found regardless if changes were made or not
Click to expand...
The way the assertion is structured there, it will fail when there Aren't
changes. The idea of an assertion is that you want to test a precondition
of something. Submitting a DataTable/DataSet with HasChanges = false to a
DataAdapter for .Update is useless. Hence, we'd test the verify that it has
changes. To be consistent, the text should read Changes not Found. If I
wrote the assertion like that, I'm really sorry, that's totally misleading
and I probably need to slow down a bit.

Anyway, since HasChanges is false, nothing is going to happen. For your
problem as well, that's the first problem. There may be an additional
problem with the Update command but we need to get past the first obstacle
first.

3) The update command generated reads
Me.OleDbUpdateCommand1.CommandText = "UPDATE UserAccessTBL SET [Password] =
?, Rights = ?, UserName = ? WHERE (UserName = ?) AND ([Password] = ?) AND
(Rights = ?)"

Won't the WHERE clause always be false if you're updating something? If you
take out the 2 ANDs it still doesn't work
Click to expand...


No unless you are changing those values in the key and even that can be
handled using Original Value.
The fact you have reserved words in your table though is a bit
discomforting. I've seen a LOT of problems with that with Access and
technically, the [] should fix it, but that's going to cause problems at
some point b/c someone is going to forget to escape it.

Right now with both of you, we have a problem in that we don't have any
changes so we can' discern anything about the validity of the Update
(technically you can but there's a lot of potential problems with Updating,
only one of which is syntax).

Anyway, I think the way the bindings are structured, your not moving to the
next position so Rowstate isn't set yet. As such, we need to fire an
endCurrentEdit in order to ensure that the edit is done.

Let me know what happens with this and we'll move on from there.

Bill
 
M

Mike

Thanks Bill!!!

The magic EndCurrentEdit works like a charm!
Here are the updated routines that might help Taras Pich.

Private Sub Form1_Load(ByVal sender As Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Me.ExpCodesDataSet1.Clear()
Me.OleDbDataAdapter2.Fill(Me.ExpCodesDataSet1)
Me.ComboBox1.SelectedIndex = -1
End Sub

Private Sub ComboBox1_TextChanged(ByVal sender As Object, ByVal e
As System.EventArgs) Handles ComboBox1.TextChanged

Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.Text
Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)

End Sub


Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As Object,
ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
If Me.ComboBox1.SelectedIndex >= 0 Then

Me.OleDbDataAdapter1.SelectCommand.Parameters.Item(0).Value =
Me.ComboBox1.SelectedValue
Me.ExplanatoryDataSet1.Clear()
Me.OleDbDataAdapter1.Fill(Me.ExplanatoryDataSet1)
End If

End Sub

Private Sub save_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles save.Click

Dim result As Integer

Me.BindingContext(Me.ExplanatoryDataSet1.msp_explanatory_codes).EndCurrentEdit()
If Me.ExplanatoryDataSet1.msp_explanatory_codes.Rows.Count > 0
Then

Me.OleDbDataAdapter1.Update(Me.ExplanatoryDataSet1.msp_explanatory_codes)
Else

Me.ExplanatoryDataSet1.msp_explanatory_codes.Addmsp_explanatory_codesRow(Me.RichTextBox1.Text,
Me.ComboBox1.Text, Me.TextBox1.Text)

Me.BindingContext(Me.ExplanatoryDataSet1.msp_explanatory_codes).EndCurrentEdit()

Me.OleDbDataAdapter1.Update(Me.ExplanatoryDataSet1.msp_explanatory_codes)

Me.ExpCodesDataSet1.msp_explanatory_codes.Addmsp_explanatory_codesRow(Me.ComboBox1.Text)
End If
Me.ComboBox1.SelectedIndex = -1
Me.ComboBox1.ResetText()
Me.RichTextBox1.ResetText()
Me.TextBox1.ResetText()
Me.ComboBox1.Focus()
End Sub

Private Sub Delete_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles Delete.Click
If Me.ExplanatoryDataSet1.msp_explanatory_codes.Rows.Count > 0
Then

Me.ExplanatoryDataSet1.msp_explanatory_codes.Rows(0).Delete()

Me.BindingContext(Me.ExplanatoryDataSet1.msp_explanatory_codes).EndCurrentEdit()

Me.OleDbDataAdapter1.Update(Me.ExplanatoryDataSet1.msp_explanatory_codes)

Me.ExpCodesDataSet1.msp_explanatory_codes.Removemsp_explanatory_codesRow(Me.ExpCodesDataSet1.msp_explanatory_codes.Rows(Me.ComboBox1.SelectedIndex))
End If
Me.ComboBox1.SelectedIndex = -1
Me.ComboBox1.ResetText()
Me.RichTextBox1.ResetText()
Me.TextBox1.ResetText()
Me.ComboBox1.Focus()
End Sub
End Class

Thanks again!
Now that Jet is going, time to try MSDE......

Taras Pich said:
William,
Not to hijack the thread, but I have a similar problem. I've tried some of
your suggestions.
Some things to note:
1) EndCurrentEdit is not a member of 'System.Windows.Forms.BindingContext'
Click to expand...
It's actually a member of BindingManagerBase which you set to the
BindingContext. So dimming bmb as BindingManagerBase = me.BindingContext is
what I meant. I was in a hurry as I was typing, my apologies for any
confustion.
2) Debug.assert(dsUsers.HasChange,"Changes found") always returns changes
found regardless if changes were made or not
Click to expand...
The way the assertion is structured there, it will fail when there Aren't
changes. The idea of an assertion is that you want to test a precondition
of something. Submitting a DataTable/DataSet with HasChanges = false to a
DataAdapter for .Update is useless. Hence, we'd test the verify that it has
changes. To be consistent, the text should read Changes not Found. If I
wrote the assertion like that, I'm really sorry, that's totally misleading
and I probably need to slow down a bit.

Anyway, since HasChanges is false, nothing is going to happen. For your
problem as well, that's the first problem. There may be an additional
problem with the Update command but we need to get past the first obstacle
first.

3) The update command generated reads
Me.OleDbUpdateCommand1.CommandText = "UPDATE UserAccessTBL SET [Password] =
?, Rights = ?, UserName = ? WHERE (UserName = ?) AND ([Password] = ?) AND
(Rights = ?)"

Won't the WHERE clause always be false if you're updating something? If you
take out the 2 ANDs it still doesn't work
Click to expand...


No unless you are changing those values in the key and even that can be
handled using Original Value.
The fact you have reserved words in your table though is a bit
discomforting. I've seen a LOT of problems with that with Access and
technically, the [] should fix it, but that's going to cause problems at
some point b/c someone is going to forget to escape it.

Right now with both of you, we have a problem in that we don't have any
changes so we can' discern anything about the validity of the Update
(technically you can but there's a lot of potential problems with Updating,
only one of which is syntax).

Anyway, I think the way the bindings are structured, your not moving to the
next position so Rowstate isn't set yet. As such, we need to fire an
endCurrentEdit in order to ensure that the edit is done.

Let me know what happens with this and we'll move on from there.

Bill
TIA,

Hope your response helps both Mike & myself.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

What are OleDbParameter names with 'Original_' prefix? 5
How-to Update Database 4
Concurrency Troubles using Access as Database '?' Appear in SQL 1
DBConcurrencyException when using OleDBTypes DBDate and Double 1
Cant update my database. 5
excess parameters? 1
Database wont update but getting no errors.... 7
VB.NET DataAdapter.Update 2

Top