Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> Visual Basic >> Visual Basic General

Update isn't updating

tattoo wrote:

There is something very basic that I am missing here. When I change the data in a field it isn't getting updated back to the Access Db. I'm using Access 2000 and VB.Net 2003.

To try and troubleshoot where I'm going wrong I have put together a very minor application and selected just one field from the Db table to update. Below is all the code I'm using.

I've tried reading the forums and the MSDN help and I seem to get multiple, different descriptions on how to update but nothing is working.

any help is appreciated. I have tried binding the field to the Db and tried without binding as well.


PublicClass Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
PublicSubNew()
MyBase.New()
'This call is required by the Windows Form Designer.

InitializeComponent()
'Add any initialization after the InitializeComponent() call

EndSub

'Form overrides dispose to clean up the component list.

ProtectedOverloadsOverridesSub Dispose(ByVal disposingAsBoolean)
If disposingThen

IfNot (componentsIsNothing)Then

components.Dispose()
EndIf

EndIf

MyBase.Dispose(disposing)
EndSub

'Required by the Windows Form Designer

Private componentsAs System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form Designer

FriendWithEvents btnNextAs System.Windows.Forms.Button
FriendWithEvents btnPrevAs System.Windows.Forms.Button
FriendWithEvents lblNavLocationAs System.Windows.Forms.Label
FriendWithEvents TextBox1As System.Windows.Forms.TextBox
FriendWithEvents Label1As System.Windows.Forms.Label
FriendWithEvents btnQuitAs System.Windows.Forms.Button
FriendWithEvents btnUpdateAs System.Windows.Forms.Button
FriendWithEvents Adapt1As System.Data.OleDb.OleDbDataAdapter
FriendWithEvents OleDbSelectCommand1As System.Data.OleDb.OleDbCommand
FriendWithEvents OleDbInsertCommand1As System.Data.OleDb.OleDbCommand
FriendWithEvents OleDbUpdateCommand1As System.Data.OleDb.OleDbCommand
FriendWithEvents OleDbDeleteCommand1As System.Data.OleDb.OleDbCommand
FriendWithEvents Conn1As System.Data.OleDb.OleDbConnection
FriendWithEvents DataSet1As Udate2.DataSet1
FriendWithEvents StatusBar1As System.Windows.Forms.StatusBar
FriendWithEvents TextBox2As System.Windows.Forms.TextBox
FriendWithEvents Label2As System.Windows.Forms.Label
<System.Diagnostics.DebuggerStepThrough()>PrivateSub InitializeComponent()
Me.btnNext =New System.Windows.Forms.Button
Me.btnPrev =New System.Windows.Forms.Button
Me.lblNavLocation =New System.Windows.Forms.Label
Me.TextBox1 =New System.Windows.Forms.TextBox
Me.DataSet1 =New Udate2.DataSet1
Me.Label1 =New System.Windows.Forms.Label
Me.btnQuit =New System.Windows.Forms.Button
Me.btnUpdate =New System.Windows.Forms.Button
Me.Adapt1 =New System.Data.OleDb.OleDbDataAdapter
Me.OleDbDeleteCommand1 =New System.Data.OleDb.OleDbCommand
Me.Conn1 =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.StatusBar1 =New System.Windows.Forms.StatusBar
Me.TextBox2 =New System.Windows.Forms.TextBox
Me.Label2 =New System.Windows.Forms.Label
CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'

'btnNext

'

Me.btnNext.Location =New System.Drawing.Point(360, 208)
Me.btnNext.Name = "btnNext"
Me.btnNext.Size =New System.Drawing.Size(72, 40)
Me.btnNext.TabIndex = 0
Me.btnNext.Text = ">"
'

'btnPrev

'

Me.btnPrev.Location =New System.Drawing.Point(184, 208)
Me.btnPrev.Name = "btnPrev"
Me.btnPrev.Size =New System.Drawing.Size(72, 40)
Me.btnPrev.TabIndex = 1
Me.btnPrev.Text = "<"
'

'lblNavLocation

'

Me.lblNavLocation.Font =New System.Drawing.Font("Microsoft Sans Serif", 10.2!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,CType(0,Byte))
Me.lblNavLocation.Location =New System.Drawing.Point(256, 208)
Me.lblNavLocation.Name = "lblNavLocation"
Me.lblNavLocation.Size =New System.Drawing.Size(104, 40)
Me.lblNavLocation.TabIndex = 26
Me.lblNavLocation.TextAlign = System.Drawing.ContentAlignment.MiddleCenter
'

'TextBox1

'

Me.TextBox1.DataBindings.Add(New System.Windows.Forms.Binding("Text",Me.DataSet1, "Vehicle Info.Department"))
Me.TextBox1.Font =New System.Drawing.Font("Microsoft Sans Serif", 10.2!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,CType(0,Byte))
Me.TextBox1.Location =New System.Drawing.Point(216, 96)
Me.TextBox1.Name = "TextBox1"
Me.TextBox1.Size =New System.Drawing.Size(144, 27)
Me.TextBox1.TabIndex = 27
Me.TextBox1.Text = ""
'

'DataSet1

'

Me.DataSet1.DataSetName = "DataSet1"
Me.DataSet1.Locale =New System.Globalization.CultureInfo("en-US")
'

'Label1

'

Me.Label1.Font =New System.Drawing.Font("Microsoft Sans Serif", 10.2!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,CType(0,Byte))
Me.Label1.Location =New System.Drawing.Point(96, 96)
Me.Label1.Name = "Label1"
Me.Label1.Size =New System.Drawing.Size(104, 32)
Me.Label1.TabIndex = 28
Me.Label1.Text = "Department"
'

'btnQuit

'

Me.btnQuit.Location =New System.Drawing.Point(320, 272)
Me.btnQuit.Name = "btnQuit"
Me.btnQuit.Size =New System.Drawing.Size(112, 24)
Me.btnQuit.TabIndex = 29
Me.btnQuit.Text = "Quit"
'

'btnUpdate

'

Me.btnUpdate.Location =New System.Drawing.Point(184, 272)
Me.btnUpdate.Name = "btnUpdate"
Me.btnUpdate.Size =New System.Drawing.Size(112, 24)
Me.btnUpdate.TabIndex = 30
Me.btnUpdate.Text = "Update"
'

'Adapt1

'

Me.Adapt1.DeleteCommand =Me.OleDbDeleteCommand1
Me.Adapt1.InsertCommand =Me.OleDbInsertCommand1
Me.Adapt1.SelectCommand =Me.OleDbSelectCommand1
Me.Adapt1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "Vehicle Info",New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("Department", "Department"),New System.Data.Common.DataColumnMapping("Vehicle ID #", "Vehicle ID #")})})
Me.Adapt1.UpdateCommand =Me.OleDbUpdateCommand1
'

'OleDbDeleteCommand1

'

Me.OleDbDeleteCommand1.CommandText = "DELETE FROM [Vehicle Info] WHERE ([Vehicle ID #] = ?)AND (Department = ?OR ?IS" & _
" NULLAND DepartmentIS NULL)"
Me.OleDbDeleteCommand1.Connection =Me.Conn1
Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Vehicle_ID__", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Vehicle ID #", System.Data.DataRowVersion.Original,Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Department", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Department", System.Data.DataRowVersion.Original,Nothing))
Me.OleDbDeleteCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Department1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Department", System.Data.DataRowVersion.Original,Nothing))
'

'Conn1

'

Me.Conn1.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=""C:\Documentsand Setting" & _
"s\Kevin\Desktop\SB\SB47.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=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Da" & _
"tabase Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Loc" & _
"ale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admi" & _
"n;Jet OLEDB:Encrypt Database=False"
'

'OleDbInsertCommand1

'

Me.OleDbInsertCommand1.CommandText = "INSERT INTO [Vehicle Info] (Department, [Vehicle ID #]) VALUES (?, ?)"
Me.OleDbInsertCommand1.Connection =Me.Conn1
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Department", System.Data.OleDb.OleDbType.VarWChar, 50, "Department"))
Me.OleDbInsertCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Vehicle_ID__", System.Data.OleDb.OleDbType.SmallInt, 0, "Vehicle ID #"))
'

'OleDbSelectCommand1

'

Me.OleDbSelectCommand1.CommandText = "SELECT Department, [Vehicle ID #] FROM [Vehicle Info]"
Me.OleDbSelectCommand1.Connection =Me.Conn1
'

'OleDbUpdateCommand1

'

Me.OleDbUpdateCommand1.CommandText = "UPDATE [Vehicle Info]SET Department = ?, [Vehicle ID #] = ? WHERE ([Vehicle ID #" & _
"] = ?)AND (Department = ?OR ?IS NULLAND DepartmentIS NULL)"
Me.OleDbUpdateCommand1.Connection =Me.Conn1
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Department", System.Data.OleDb.OleDbType.VarWChar, 50, "Department"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Vehicle_ID__", System.Data.OleDb.OleDbType.SmallInt, 0, "Vehicle ID #"))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Vehicle_ID__", System.Data.OleDb.OleDbType.SmallInt, 0, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Vehicle ID #", System.Data.DataRowVersion.Original,Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Department", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Department", System.Data.DataRowVersion.Original,Nothing))
Me.OleDbUpdateCommand1.Parameters.Add(New System.Data.OleDb.OleDbParameter("Original_Department1", System.Data.OleDb.OleDbType.VarWChar, 50, System.Data.ParameterDirection.Input,False,CType(0,Byte),CType(0,Byte), "Department", System.Data.DataRowVersion.Original,Nothing))
'

'StatusBar1

'

Me.StatusBar1.Location =New System.Drawing.Point(0, 362)
Me.StatusBar1.Name = "StatusBar1"
Me.StatusBar1.Size =New System.Drawing.Size(592, 22)
Me.StatusBar1.TabIndex = 31
'

'TextBox2

'

Me.TextBox2.DataBindings.Add(New System.Windows.Forms.Binding("Text",Me.DataSet1, "Vehicle Info.Vehicle ID #"))
Me.TextBox2.Font =New System.Drawing.Font("Microsoft Sans Serif", 10.2!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,CType(0,Byte))
Me.TextBox2.Location =New System.Drawing.Point(216, 24)
Me.TextBox2.Name = "TextBox2"
Me.TextBox2.ReadOnly =True

Me.TextBox2.Size =New System.Drawing.Size(144, 27)
Me.TextBox2.TabIndex = 32
Me.TextBox2.Text = ""
'

'Label2

'

Me.Label2.Font =New System.Drawing.Font("Microsoft Sans Serif", 10.2!, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point,CType(0,Byte))
Me.Label2.Location =New System.Drawing.Point(159, 27)
Me.Label2.Name = "Label2"
Me.Label2.Size =New System.Drawing.Size(40, 24)
Me.Label2.TabIndex = 33
Me.Label2.Text = "VIN"
'

'Form1

'

Me.AutoScaleBaseSize =New System.Drawing.Size(6, 15)
Me.ClientSize =New System.Drawing.Size(592, 384)
Me.Controls.Add(Me.Label2)
Me.Controls.Add(Me.TextBox2)
Me.Controls.Add(Me.StatusBar1)
Me.Controls.Add(Me.btnUpdate)
Me.Controls.Add(Me.btnQuit)
Me.Controls.Add(Me.Label1)
Me.Controls.Add(Me.TextBox1)
Me.Controls.Add(Me.lblNavLocation)
Me.Controls.Add(Me.btnPrev)
Me.Controls.Add(Me.btnNext)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataSet1, System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)
EndSub

#EndRegion

PrivateSub btnPrev_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnPrev.Click
BindingContext(DataSet1, "Vehicle Info").Position = (BindingContext(DataSet1, "Vehicle Info").Position - 1)
position_Changed()
EndSub

PrivateSub btnNext_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnNext.Click
BindingContext(DataSet1, "Vehicle Info").Position = (BindingContext(DataSet1, "Vehicle Info").Position + 1)
position_Changed()
EndSub

PrivateSub position_Changed()
lblNavLocation.Text = (((BindingContext(DataSet1, "Vehicle Info").Position + 1).ToString + " of ") _
+ BindingContext(DataSet1, "Vehicle Info").Count.ToString)
EndSub

PrivateSub btnQuit_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnQuit.Click
Application.Exit()
EndSub

PrivateSub Form1_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load
Adapt1.Fill(DataSet1, "Vehicle Info")
EndSub

PrivateSub btnUpdate_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btnUpdate.Click
Try

Adapt1.Update(DataSet1, "Vehicle Info")
StatusBar1.Text = "Record Update"
Catch

MsgBox("Can't Update Record", MsgBoxStyle.OKOnly)
EndTry

EndSub
EndClass


the data field I am updating is a character field 50 (Char), There is also one other field I used from the Db which is an integer, Fixed format, needed because it is the Key field.

the command text for my Update command is as follows, this was created when I generated the Adapter from the toolbox.

UPDATE [Vehicle Info]
SET Department = ?, [Vehicle ID #] = ?
WHERE ([Vehicle ID #] = ?) AND (Department = ? OR
? IS NULL AND Department IS NULL)

The parameter collection editor shows the parameter list as follows.

Direction OleDbType Size Srce Col. Src Ver.

0 Department Input VarWChar 50 Department Current

1 Vehicle_ID_ Input SmallInt 0 Vehicle ID # Current

2 Orig_Vehicle_ID_ Input SmallInt 0 Vehicle ID # Original

3 Orig_Department Input VarWChar 50 Department Original

4 Orig_Deparment1 Input VarWChar 50 Department Oiginal

I have written these types of Apps before and not had this problem....

This should be very simple. can anyone figure out why the data doesn't update ?

The app, runs through the update statement fine as if it worked but the data never changes.

I'd pull my hair out if I had any........

Thanks in advance

tattoo

Hi tattoo,

Your coding is updating the database!

[52182 byte] By [ReaSoftwareEngineering] at [2007-12-24]
«« SQL Query
»» Data Access Page
# 1

At some point you have to have your coding actually modify the data in your dataset; or else you will not notice a change because you are simply updating the database with original date you pulled out. There are multiple ways of doing this; the primary way used is to retrieve the data in a datarow, datatable, dataview, datagrid, etc. Edit the data(row/table, etc). Update the database with the modified data(row/table, etc), which is NOW contained in your dataset, using your table adapter.

This MSDN link will provide good detail and coding example on how to do this:

http://msdn2.microsoft.com/en-us/library/tat996zc.aspx

Once you have modified the dataset to contain the new data then your current update procedure will work; this is the reason you are not getting an error. The error will only produce if something is actually failing.

Thank you,

James

ReaSoftwareEngineering at 2007-8-31 > top of Msdn Tech,Visual Basic,Visual Basic General...
# 2

Thanks for your response James,

I guess my next question is, If I am using Databinding and I loaded all the records into my dataset using the Adapter.Fill method , when I change data in the textbox isn't that in effect changing it in the dataset ?

My textbox is bound to the field in the dataset. Perhaps I'm not grabbing the concept here but, in the link you sent me to above, it seems to me that that is explaining how to update data that I'm not seeing. Theoretically if my text box is bound to the field in the dataset I would think that I have that data in hand, so to speak, and I'm wondering why I would have to use the Findby method. I think the fact that I have cursored through using my navigation buttons and am looking at the data I want to edit means I have already found my data and the Findby method would seem to add additional overhead to my app.

In another app I have created where I wasn't using Databinding I created a Subroutine to Capture field Values before doing the update.

[code]

Private Sub Capture_Screen()

StatusDS.Tables("Status").Rows(BindingContext(StatusDS, "Status").Position).Item("Statcode") = txtStatCode.Text

StatusDS.Tables("Status").Rows(BindingContext(StatusDS, "Status").Position).Item("Allowinput") = txtAllowInput.Text

End Sub

[/code]

Am I way off the mark ? Do I make any sense ?

I am relatively new to .Net programming so please have patience.

Regards

tattoo

tattoo at 2007-8-31 > top of Msdn Tech,Visual Basic,Visual Basic General...
# 3

Hi tattoo,

Don't worry about being new to things; we all are/were....seems every few years I am new to the programming syntax the MSFT decided to use.

The best way to tell if your dataset is or isn't being updated is to place a breakpoint (press F9) at the point of the line that calls the Adapter's update method. Once your program breaks at this point you can hover the mouse over the dataset variable and select 'DataTable Visualizer'. This should be in the tooltip that shows when you hover over; you might see a magnifying glass followed by { } that contain your 'projectname.datasetname.tablename', this magnifying glass will pull up the 'DataTable Visualizer'. Once you get into this viewer then you can easily see if the dataset contains the updated information or not.

These above directions are for VB.Net (2005); I am not sure if VB.Net (2003) holds the same capabilities, but most definitely worth a try.

Right now the objective I would focus on is to figure out if the dataset is or isn't updated; not use theories, as life rarely works out as it should. I only urge for you to proceed this route because you are not receiving any type of error on your catch statement, in which most cases this is means the Try part is working fine; however, remember that is in theory also. It's best to eliminate each possibility one at a time.

I hope this helps.

Thank you,

James

ReaSoftwareEngineering at 2007-8-31 > top of Msdn Tech,Visual Basic,Visual Basic General...
# 4

Thanks James,

That feature doesn't appear to be available in V2003.

I did find out what the problem was though. Right before the Update statement I added the following line.

BindingContext(FleetDSAll, "Vehicle Info").EndCurrentEdit()

I'm not quite sure what EndCurrentEdit means but it seems to work..

Thanks for taking the time to look at this problem.

tattoo

tattoo at 2007-8-31 > top of Msdn Tech,Visual Basic,Visual Basic General...
# 5

Hi tattoo,

That is a great find in resolving the problem. I had always assumed that using the keyword EndCurrentEdit was used in conjunction with BeginEdit; as I further investigate the usage I see that is not the case.

It does appear that the EndCurrentEdit method is basically telling the dataset to commit the changes you made to the data-bound controls. NOW that you have a modified DataSet you are noticing the changes in the database; because they were updated. Which, to go in full circle, you were using the Update method correctly. Funny to find that a problem caused earlier can mislead to thinking a problem occurs somewhere else.

I am glad to see you figured out the reason the DataSet wasn't getting updated.

I hope your project is coming along well.

Thank you,

James

ReaSoftwareEngineering at 2007-8-31 > top of Msdn Tech,Visual Basic,Visual Basic General...
Visual Basic
Visual Basic General

Visual Basic Hot Topic

Visual Basic New Topic

Visual Basic

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified