Simple SQLCE application

[bufer24]

A am trying to make a simple database application just to learn SQLCE. I can not get the simplest things to work: adding and deleting records. Here´s how it looks now:

 

Conn = New SqlCeConnection(CS) 'set the connection
Conn.Open() 'and open it

'the database has 1 table called Invoices and 2 columns. First column called ID (identity with auto increment set to true) is the primary key. Second is a string (nvarchar) column called InvoiceNo. 

TA = New SqlCeDataAdapter("SELECT * FROM Invoices", Conn) 'set the data adapter

CB = New SqlCeCommandBuilder(TA)

Dim DSF as New Dataset 'a blank dataset
TA.Fill(DSF, "Invoices") 'filling the dataset

DSF.Tables("Invoices").PrimaryKey = New DataColumn() {DSF.Tables("Invoices").Columns("ID")} 'I need a primary key to easily search for records in dataset by ID

'there is a DataGridView called DG on the form
DG.DataSource = DSF
DGFaktury.DataMember = "Invoices"

 

Works nicely. Loads all data into the datagrid. Now I want to insert a record:

 

Dim R As DataRow = DSF.Tables("Invoices").NewRow
R.Item("InvoiceNo") = "027"
DSF.Tables("Invoices").Rows.Add(R)
TA.Update(DSF, "Invoices")

'so far so good I can get the new row into the database. But in my datagrid I see only the new InvoiceNo, but not the new ID. Of course the dataset doesn´t know what ID was assigned to the new row in the database, so I have to get it somehow. So here´s what I did.

Dim cmd As New SqlCeCommand("SELECT ID FROM Invoices WHERE (ID = @@IDENTITY)", Conn)
Dim id As Integer = CInt(cmd.ExecuteScalar())
R.Item("ID") = id 'now I see the ID of newly added record - wow

 

Here comes the problem: there is a Delete button on my form. When I click it, and the newly added row in the datagrid is selected, it executes this:

Dim ID As Integer = DG.SelectedRows(0).Cells("ID").Value 'retrieves the ID
DSF.Tables("Invoices").Rows.Find(ID).Delete() 'finds a row in the dataset and deletes it
TA.Update(DSF, "Invoices")

 

I ended up with an exception: DBConcurrencyException, DeleteCommand affected 0 of 1 expected rows. After inserting the new row, the dataset changed when I changed the ID, so it thinks it has to update the record while deleting it at the same time and that´s what is causing the error. Is that right? There´s no problem while deleting older records.

 

Am I going the right direction? Is there another way to get these simple operations (insert, delete, update) to work? I found tutorials dealing with wieving of data only, but not inserting, updating or deleting.

Edited November 29, 2011 by bufer24

[bufer24]

I figured it out my self. I put Dataset.AcceptChanges before Dataadapter.Fill and it worked :)