VB.Net/access database/jet 4.0 won't update data to database

[aaron-79]

I've got a really simple database with an even simpler user interface designed to just view and edit data. The problem is that when I run the solution everything works as it is supposed to, but when I close the solution and then restart, all my record data changes are not saved to the database. Its like the record changes are temparily saved to the dataset and not saved to the database, and when the applicaiton is closed the changes to records in the dataset go back to their previous state. If you want to look at the entire application then just email me its only 130K zipped.

Heres the code that I have for the menuitem Update:

PrivateSub MenuItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuUpdate.Click

txtDateModified.Text = Now 'used to monitor updates - fills a form textbox that is bound to a database field
Call EditState(False) 'editstate locks or unlocks form textboxes
Call Calculations() 'a few math calculations repeatly performed on textboxes

Dim pdsInsertedRows, pdsModifiedRows, pdsDeletedRows As DataSet

pdsDeletedRows = DsWidgets1.GetChanges(DataRowState.Deleted)
pdsInsertedRows = DsWidgets1.GetChanges(DataRowState.Added)
pdsModifiedRows = DsWidgets1.GetChanges(DataRowState.Modified)

IfNot pdsInsertedRows IsNothingThen
OleDbDataAdapter1.Update(pdsInsertedRows)
EndIf

IfNot pdsModifiedRows IsNothingThen
OleDbDataAdapter1.Update(pdsModifiedRows)
EndIf
IfNot pdsDeletedRows IsNothingThen
OleDbDataAdapter1.Update(pdsDeletedRows)
EndIf

Me.BindingContext(DsWidgets1, "tblWidgets").EndCurrentEdit()

DsWidgets1.AcceptChanges()

EndSub


I'm not sure what the problem is, maybe theres another way to update the database. I recreated the database and recoded a new identical application and have the gotten the same results, it won't save the updates. If anyone wants see the entire thing just email. This entire application is just for learning purposes which is why it is so small. The goal was to add a field by which I could monitor changes to the database through, the datemodified field, and then to be able to sort the database by this field later on to find the changed fields and use them to update a master database. An example of the practical use would be different salespeople using the same database with everyone adding different clients to the database. There would be a need to see all the different changes made and then to update the master database and then redistribute it.

 

[ManicCW]

Just like you said: "Its like the record changes are temparily saved to the dataset and not saved to the database..."

.net uses daset as virtual database, so you need to send back results to real database. Use direct INSERT INTO statement or Dataadapter.

 

[aaron-79]

Thanks for the quick reply, I was afraid that I wasn't specific or clear enough in my post. I'm relatively new to VB.Net or programming for that matter, but I learn really fast. You wouldn't happen to have an example of the insert into command. Is the SQL code generated by Microsoft Access the same as that used in VB.Net, or does the data adapter change code? The reason that I ask is ACcess has a code generator in SQL view. Anyways thanks in advance. If you have any tutorial links or code example links that would be great. Thanks in advance.

Aaron

 

[ManicCW]

something like:

[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] cn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Friend[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] OleDbCommand[/SIZE]

[SIZE=2][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] InsertRecord()
cn = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbConnection(ConfigurationSettings.AppSettings("DatabaseString").ToString)
cmd = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand
cmd.Connection = cn
cmd.CommandText = "INSERT INTO Images " & _
"(Name, Date, Title, Description, Album, HasThumb, InGallery) " & _
"VALUES (?, ?, ?, ?, ?, ?, ?)"
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Name", OleDbType.VarChar))
cmd.Parameters("Name").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtImageName.Text.Trim
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Date", OleDbType.Date))
cmd.Parameters("Date").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Date[/COLOR][/SIZE][SIZE=2].Now
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Title", OleDbType.VarChar))
cmd.Parameters("Title").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtTitle.Text.Trim
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Description", OleDbType.VarChar))
cmd.Parameters("Description").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].txtDescription.Text.Trim
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("Album", OleDbType.VarChar))
cmd.Parameters("Album").Value = [/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].cmbAlbum.SelectedValue
cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("HasThumb", OleDbType.Boolean))
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].chkThumb.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]cmd.Parameters("HasThumb").Value = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]cmd.Parameters("HasThumb").Value = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]cmd.Parameters.Add([/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbParameter("InGallery", OleDbType.Boolean))
[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].chkInGallery.Checked = [/SIZE][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]cmd.Parameters("InGallery").Value = [/SIZE][SIZE=2][COLOR=#0000ff]True
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]cmd.Parameters("InGallery").Value = [/SIZE][SIZE=2][COLOR=#0000ff]False
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2]cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]

[/COLOR][/SIZE][/SIZE]

 

[aaron-79]

I'm getting closer

I've been working with the code example that you gave me, and I changed the type of command from an insert to an UPDATE, but I'm not sure if the syntax is correct. I do have the entire project zipped if you want to look at it. I'm getting a 'Data type mismatch in criteria expression error' followed by a 'The changes you requested were not successful because they would create duplicate values in the index, primary key, or relationship' error.

Private Sub UpdateRecord()
Dim DateModified As Date
Dim PartNumber As Short
Dim Description As String
Dim QuantityOnHand As Short
Dim Cost, SalesPrice As Double
Dim cn As New OleDb.OleDbConnection
Dim cmd As New OleDb.OleDbCommand

DateModified = Me.txtDateModified.Text 'Matching variables to Textboxes
PartNumber = Me.txtPartNumber.Text
Description = Me.txtDescription.Text
QuantityOnHand = Me.txtQuantityOnHand.Text
Cost = Me.txtCost.Text
SalesPrice = Me.txtSalesPrice.Text

cn = New OleDb.OleDbConnection("Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDBatabase L" & _
"ocking Mode=1;Jet OLEDBatabase Password=;Data Source=" & Application.StartupPath & "\Factory.mdb;Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:" & _
"Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:System d" & _
"atabase=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB" & _
":New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDBon't C" & _
"opy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User " & _
"ID=Admin;Jet OLEDB:Encrypt Database=False".ToString)

cmd.Connection = cn
cmd.CommandText = "UPDATE tblParts SET [fldPartNumber] = '" & PartNumber & "', [fldDescription] = '" & Description & "', [fldSalesPrice] = '" & SalesPrice & "', [fldCost] = '" & Cost & "', [fldQuantityOnHand] = '" & QuantityOnHand & "', [fldDateModified] = '" & DateModified & "' WHERE [RecordNumber]= '" & Current & "'" 'Current is a public variable for the current record

cmd.Parameters.Add(New OleDb.OleDbParameter("fldDateModified", OleDb.OleDbType.Date))
cmd.Parameters("fldDateModified").Value = Date.Now

cmd.Parameters.Add(New OleDb.OleDbParameter("fldPartNumber", OleDb.OleDbType.VarChar))
cmd.Parameters("fldPartNumber").Value = Me.txtPartNumber.Text.Trim

cmd.Parameters.Add(New OleDb.OleDbParameter("fldDescription", OleDb.OleDbType.String))
cmd.Parameters("fldDescription").Value = Me.txtDescription.Text.Trim

cmd.Parameters.Add(New OleDb.OleDbParameter("fldQuantityOnHand", OleDb.OleDbType.VarChar))
cmd.Parameters("fldQuantityOnHand").Value = Me.txtQuantityOnHand.Text.Trim

cmd.Parameters.Add(New OleDb.OleDbParameter("fldCost", OleDb.OleDbType.VarChar))
cmd.Parameters("fldCost").Value = Me.txtCost.Text.Trim

cmd.Parameters.Add(New OleDb.OleDbParameter("fldSalesPrice", OleDb.OleDbType.VarChar))
cmd.Parameters("fldSalesPrice").Value = Me.txtSalesPrice.Text.Trim

cmd.Parameters.Add(New OleDb.OleDbParameter("RecordNumber", OleDb.OleDbType.VarChar)) 'Not sure if this extra parameter is needed or not

Try
cn.Open()
cmd.ExecuteNonQuery()
Catch oleEx As OleDb.OleDbException
MessageBox.Show(oleEx.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
cn.Close()

End Sub

 

[ManicCW]

you declared Cost and SalesPrice as double and in parameter like varchar (that is the first error you are getting). the second error tells me that you have a unique column in database table and you are inserting duplicate (i belive it's error in recordnumber.

I allways use (?) as parameter in string because it's easy to maintain code like that, just be carefull to add parameters in order as it is in query string.

PS: use code block when posting code!

 

[aaron-79]

I'm not getting any errors but I'm back to where I started, with not being able to save my changes back to the database. I went ahead and recreated a new database as well to make sure that it wasn't the problem, but no luck. I'm starting to pull my hair out accompanied with a twitch, like Tweak from south park.

Private Sub UpdateRecord()
Dim DateModified As Date

Dim PartNumber As Integer

Dim Description As String

Dim QuantityOnHand As Integer

Dim Cost, SalesPrice As Double

Dim cn As New OleDb.OleDbConnection
Dim cmd As New OleDb.OleDbCommand
DateModified = Me.txtDateModified.Text 'Matching variables to Textboxes

PartNumber = Me.txtPartNumber.Text
Description = Me.txtDescription.Text
QuantityOnHand = Me.txtQuantityOnHand.Text
Cost = Me.txtCost.Text
SalesPrice = Me.txtSalesPrice.Text
cn = New OleDb.OleDbConnection(strConnectionString)
cmd.Connection = cn
cmd.CommandText = "usp_UpdatetblParts"
cmd.CommandType = CommandType.StoredProcedure 'Procedure is stored in Database as Query

'usp_UpdatetblParts =

'UPDATE tblParts SET fldPartNumber = PartNumber, fldDescription = & _

' Description, fldSalesPrice = SalesPrice, fldCost = Cost, fldQuantityOnHand & _

' = QuantityOnHand, fldDateModified = DateModified & _

' WHERE (fldRecordNumber=Current);


cmd.Parameters.Add(New OleDb.OleDbParameter("fldPartNumber", OleDb.OleDbType.VarChar)) 'Parameter Order Matches Query Order

cmd.Parameters("fldPartNumber").Value = Me.txtPartNumber.Text.Trim
cmd.Parameters.Add(New OleDb.OleDbParameter("fldDescription", OleDb.OleDbType.VarChar))
cmd.Parameters("fldDescription").Value = Me.txtDescription.Text.Trim
cmd.Parameters.Add(New OleDb.OleDbParameter("fldSalesPrice", OleDb.OleDbType.Double))
cmd.Parameters("fldSalesPrice").Value = Me.txtSalesPrice.Text.Trim
cmd.Parameters.Add(New OleDb.OleDbParameter("fldCost", OleDb.OleDbType.Double))
cmd.Parameters("fldCost").Value = Me.txtCost.Text.Trim
cmd.Parameters.Add(New OleDb.OleDbParameter("fldQuantityOnHand", OleDb.OleDbType.VarChar))
cmd.Parameters("fldQuantityOnHand").Value = Me.txtQuantityOnHand.Text.Trim
cmd.Parameters.Add(New OleDb.OleDbParameter("fldDateModified", OleDb.OleDbType.Date))
cmd.Parameters("fldDateModified").Value = Date.Now
cmd.Parameters.Add(New OleDb.OleDbParameter("RecordNumber", OleDb.OleDbType.VarChar))
cmd.Parameters("RecordNumber").Value = Current

Try

cn.Open()
cmd.ExecuteNonQuery()
cn.Close()
Catch oleEx As OleDb.OleDbException
MessageBox.Show(oleEx.Message)
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

'CleanUp

cn.Dispose()
cn = Nothing

cmd.Dispose()
cmd = Nothing



End Sub

 

[aaron-79]

I got the application to work using code similiar to my first post. The txtDateModified textbox shows the current date/time but it only updates to the database as the current date with the time of 12:00 AM. The time is stuck on 12:00 AM. I was using date for fldDateModified data type in the database but then I switched to string. I'm using string as well for the variable on the application side. Any ideas?