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:
Works nicely. Loads all data into the datagrid. Now I want to insert a record:
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:
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.
Code:
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:
Code:
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 - wowHere 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:
Code:
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.
Last edited:
