VB.net MS Access troubles

[Mattastic]

I am using an Access db with auto number as the primary key (if that matters). I used this tutorial http://www.homeandlearn.co.uk/NET/nets12p10.html as the bases for my program as I am new to this.

To make this simple; The program works fine, but you must close the program and reopen it before deleting a newly added record or you will get this error: Concurrency violation: the DeleteCommand affected 0 of the expected 1 records. You can delete records fine after restarting the program.

It has to be a simple newbie mistake, but I have been up and down the code comparing it to the tutorial and I can't find anything wrong. Below are the sections of code that are relevent. Any help would be greatly appreciated.

Imports System.Data
Imports System.Data.OleDb

Public Partial Class MainForm
	Public dsPublic As New DataSet
	Public daEmployee As New OleDb.OleDbDataAdapter

	Sub BtnF1SchedClick(ByVal sender As Object, ByVal e As EventArgs)
		fillda()
		F4Sched.F4Initialize()
	End Sub
...
End Class


Public Module Functions1
	Public con As New OleDb.OleDbConnection

	Public Sub Fillda()
		Dim sql As String
		Dim mydir As Reflection.Assembly = _
	               System.Reflection.Assembly.GetExecutingAssembly()
		defaultdir = mydir.Location.Substring(0, InStrRev _
		   (mydir.Location, "\"))
               Con.ConnectionString = "Provider=Microsoft.Jet.OLEDB._
                             4.0;Data Source=" & MainForm.Source
		Con.Open()	

		Sql = "Select * from Schedule Order By ThisDay Desc"
		MainForm.daSched = New OleDb.OleDbDataAdapter(sql, con)
		MainForm.daSched.Fill(MainForm.dsPublic, "Schedule")
	End Sub
End Module	


Public Partial Class F5Day

	Sub BtnF5AddClick(ByVal sender As Object, ByVal e As EventArgs)
	'***********Add Record**********************
		Dim dsNewRow As DataRow
		Dim cb As New OleDb.OleDbCommandBuilder_
                             (MainForm.daSched)
		
		dsNewRow = MainForm.dsPublic.Tables_
                             ("Schedule").NewRow()
		dsNewRow.Item("WhoIs") = lstF5Emp.SelectedIndex
		dsNewRow.Item("StartShift") = txtF5Start.Text
		dsNewRow.Item("EndShift") = txtF5End.Text
		dsNewRow.Item("OnCall") = ckbxOnCall.Checked
		dsNewRow.Item("ThisDay") = ThisDay
		
		MainForm.dsPublic.Tables("Schedule").Rows.Add_
                             (dsNewRow)
		MainForm.daSched.Update(MainForm.dsPublic, "Schedule")
	
		FillSchedule() 'Add new record to the list
	End Sub

	Sub BtnF5RemoveClick(ByVal sender As Object, ByVal e As EventArgs)
	'***********Remove Record*******************
		Dim cb As New OleDb.OleDbCommandBuilder_
                              (MainForm.daSched)
		
		MainForm.dsPublic.Tables("Schedule").Rows(StartRec +_
			lstF5Sched.SelectedIndex).Delete()
		MainForm.daSched.Update(MainForm.dsPublic, "Schedule")
		
		lstF5Sched.Items.Clear() 'Clear the list
		FillSchedule() 'Refresh data
	End Sub

[MaximilianMayrhofer]

The code structure seems a little iffy. I think you might find the ADO.Net tutorial in my sig useful. With the code structure and commands in the tutorial, you won't encounter any of the problems that you mentioned, and it will look cleaner.

[Mattastic]

Yeah, I looked at that doc before I decided to post the question, but I thought it would be an easy fix. I guess not, so I decided to start fresh using the introduction to ADO.NET as the base and slowly build my program onto it.

So after a few hours I am at the same spot. I still get: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." if I attempt to delete a newly added record without restarting the app. I also get the same error if I try to update an exsisting record. Adding to and looking through the records works fine. This is day 3 stuck going no where with this grrrr!!! The error happens at da.Update(ds), so I am guessing that the problem is somewhere in the below code.

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Matt\Documents\SharpDevelop Projects\ADONetTutorial1\ADONetTutorial1\db1.mdb;User Id=admin;Password=;"

        da.SelectCommand = New OleDbCommand("Select ID, Firstname, Lastname, Phone, Addr1, Addr2, City, State, Zip, MustW, PreferW, MustO, PreferO, Days, " & _
        	"Priority, Charge, Bday FROM Employee")
        da.SelectCommand.Connection = conn

        da.UpdateCommand = New OleDbCommand("UPDATE Employee Set Firstname = @Firstname, Lastname = @Lastname, Phone = @Phone, Addr1 = @Addr1, Addr2 = " & _
 			"@Addr2, City = @City, State = @State, Zip = @Zip, MustW = @MustW, PreferW = @PreferW, MustO = @MustOff, Days = @Days, Priority = @Priority, " & _
 			"Charge = @Charge, Bday = @Bday WHERE ID = @ID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 25, "Firstname")
        da.UpdateCommand.Parameters.Add("@LastName", OleDbType.VarChar, 25, "Lastname")
        da.UpdateCommand.Parameters.Add("@Phone", OleDbType.VarChar, 15, "Phone")
        da.UpdateCommand.Parameters.Add("@Addr1", OleDbType.VarChar, 40, "Addr1") 
        da.UpdateCommand.Parameters.Add("@Addr2", OleDbType.VarChar, 40, "Addr2")
        da.UpdateCommand.Parameters.Add("@City", OleDbType.VarChar, 25, "City")
        da.UpdateCommand.Parameters.Add("@State", OleDbType.VarChar, 3, "State")
        da.UpdateCommand.Parameters.Add("@Zip", OleDbType.VarChar, 6, "Zip")
        da.UpdateCommand.Parameters.Add("@MustW", OleDbType.VarChar, 8, "MustW")
        da.UpdateCommand.Parameters.Add("@PreferW", OleDbType.VarChar, 8, "PreferW")
        da.UpdateCommand.Parameters.Add("@MustO", OleDbType.VarChar, 8, "MustO")
        da.UpdateCommand.Parameters.Add("@PreferO)", OleDbType.VarChar, 8, "PreferO")
        da.UpdateCommand.Parameters.Add("@Days", OleDbType.Integer, 5, "Days")
        da.UpdateCommand.Parameters.Add("@Priority", OleDbType.Integer, 5, "Priority")
        da.UpdateCommand.Parameters.Add("@Charge", OleDbType.Boolean, 1, "Charge")
        da.UpdateCommand.Parameters.Add("@Bday", OleDbType.Date, 1, "Bday")
        da.UpdateCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID") '.SourceVersion = DataRowVersion.Original

        da.InsertCommand = New OleDbCommand("INSERT INTO Employee(Firstname, Lastname, Phone, Addr1, Addr2, City, " & _
        	"State, Zip, MustW, PreferW, MustO, PreferO, Days, Priority, Charge, Bday) " & _
        	"VALUES(@Firstname,@Lastname,@Phone,@Addr1,@Addr2,@city,@State,@Zip,@MustW,@PreferW,@MustO,@PreferO,@Days,@Priority,@Charge,@Bday)")
        da.InsertCommand.Connection = conn
  		da.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 25, "Firstname")
        da.InsertCommand.Parameters.Add("@LastName", OleDbType.VarChar, 25, "Lastname")
        da.InsertCommand.Parameters.Add("@Phone", OleDbType.VarChar, 15, "Phone")
        da.InsertCommand.Parameters.Add("@Addr1", OleDbType.VarChar, 40, "Addr1") 
        da.InsertCommand.Parameters.Add("@Addr2", OleDbType.VarChar, 40, "Addr2")
        da.InsertCommand.Parameters.Add("@City", OleDbType.VarChar, 25, "City")
        da.InsertCommand.Parameters.Add("@State", OleDbType.VarChar, 3, "State")
        da.InsertCommand.Parameters.Add("@Zip", OleDbType.VarChar, 6, "Zip")
        da.InsertCommand.Parameters.Add("@MustW", OleDbType.VarChar, 8, "MustW")
        da.InsertCommand.Parameters.Add("@PreferW", OleDbType.VarChar, 8, "PreferW")
        da.InsertCommand.Parameters.Add("@MustO", OleDbType.VarChar, 8, "MustO")
        da.InsertCommand.Parameters.Add("@PreferO)", OleDbType.VarChar, 8, "PreferO")
        da.InsertCommand.Parameters.Add("@Days", OleDbType.Integer, 5, "Days")
        da.InsertCommand.Parameters.Add("@Priority", OleDbType.Integer, 5, "Priority")
        da.InsertCommand.Parameters.Add("@Charge", OleDbType.Boolean, 1, "Charge")
        da.InsertCommand.Parameters.Add("@Bday", OleDbType.Date, 1, "Bday")
        
        da.DeleteCommand = New OleDbCommand("DELETE FROM Employee WHERE ID = @ID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@ID", OleDbType.Integer, 5, "ID")

        da.Fill(ds)

Your help is greatly appretiated.

[MaximilianMayrhofer]

I'm getting the same problems. The problem is in the way the code is handling concurrency. I can't research it now, but i think if you google concurrency violations in ADO.NET it will pull up some useful stuff. Post if you find a solution!

[techgnome]

It's the delete command SQL.... it needs a ID in order to delete from the database... since the newly added rows do not have an ID (since it's not saved to the DB yet) so the delete doesn't work....

Supposedly there's a way to compensate, but I don't use DataAdapters much, so I couldn't tell you what the answer is.

-tg

[Mattastic]

I tried remarking out the update commands and just have it add and delete from the dataset and then do an da.update(ds) when the user closes the program. It works great, you can add and delete and update all day, but when you close the program you still get the same error.

From what I have read you shouldn't get a concurrency error unless two users are trying to edit the same row at the same time. All documentation on this error says to catch the error, but that wouldn't help me.

I'm still looking, I will post a solution if I find anything.

[MaximilianMayrhofer]

I solved the problem my manually inserting an ID into the ds when adding a new record using the following code:

dr("ID") = CInt(ds.Tables(0).Rows(ds.Tables(0).Rows.Count - 2).Item("ID").ToString) + 1

dr represents the datarow that I created to input my new record, and ds represents the dataset that I am using to hold all data.

[RobDog888]

You really shouldnt be specifying an ID in code as its going to create issues with multiusers. Set your ID field as an Autoincrement field so the database will increment it automatically upon the addition of a new row.

When deleting you want to "clear the textboxes by either having them populated with the next or previous record in the dataset or by just setting each textbox to an empty string.

[MaximilianMayrhofer]

hmm yea that makes sense, and then before closing the connection run an sql command that deletes rows that have empty fields.

[Mattastic]

I don't know, I have pretty much given up at this point. After I add a new record (without closing the program), I extract the data from the table and the new record is there. So, if the new record is in the database with a correct ID number why do still need to restart the program to delete the new record. Again, I can't modify records old or new.

I tried setting the ID manually as it will always be 1+ the index, but I still get the same error.

[Mattastic]

Ok I lied, I never give up. I added the following and I can now delete newly added records without restarting the app: da.MissingSchemaAction = MissingSchemaAction.AddWithKey

I still cant update any records though.

[Mattastic]

Ok I seriously don't know. It seems to delete newly added records when it wants to, if not it thows the same error. Modifying an existing record is a definite no go. I've tried to figure it out for the last 10 hours, I'm burnt out on this #$#@###$$%%#!##!$@#$#@%$%@%%@ ( < random cursing). I attached the full code. This is not the full program, it's a skimmed down version. I'm just trying to get one bloody table working right, then modify the full program once the problem is identified. The database is included (no records).