dran1979 dran1979 - 3 months ago 17
Vb.net Question

Update *. SDF database file using VB.NET

I have a desktop application created in Visual Studio and SQL Server Compact Edition 3.5. I can insert a record without error. My problem is after running the update without error, there's no changes in the database after.

Here's my update code:

Dim Cmd As SqlCeCommand
Try
If Con.State = ConnectionState.Closed Then Con.Open()
Cmd = New SqlCeCommand("Update [Meds] SET [Brand] =@Brand,[GenericName]=@GenericName,[Dosage]=@Dosage,[Form]=@Form,[StockDose]=@StockDose,[Supplier]=@Supplier,[Manufacturer]=@Manufacturer,[Quantity]=@Quantity,[PerUnit1]=@PerUnit1,[ExpiryDate]=@ExpiryDate,[SellingPrice]=@SellingPrice WHERE [PerUnit2] =@PerUnit2", Con)
Cmd.Parameters.Add(New SqlCeParameter("@Brand", SqlDbType.NVarChar)).Value = txtBrand.Text
Cmd.Parameters.Add(New SqlCeParameter("@GenericName", SqlDbType.NVarChar)).Value = txtGenericName.Text
Cmd.Parameters.Add(New SqlCeParameter("@Dosage", SqlDbType.NVarChar)).Value = txtDosage.Text
Cmd.Parameters.Add(New SqlCeParameter("@Form", SqlDbType.NVarChar)).Value = cbForm.SelectedText
Cmd.Parameters.Add(New SqlCeParameter("@StockDose", SqlDbType.NVarChar)).Value = txtStockDose.Text
Cmd.Parameters.Add(New SqlCeParameter("@Supplier", SqlDbType.NVarChar)).Value = txtSupplier.Text
Cmd.Parameters.Add(New SqlCeParameter("@Manufacturer", SqlDbType.NVarChar)).Value = txtManufacturer.Text
Cmd.Parameters.Add(New SqlCeParameter("@Quantity", SqlDbType.NVarChar)).Value = txtQty.Text
Cmd.Parameters.Add(New SqlCeParameter("@PerUnit1", SqlDbType.NVarChar)).Value = cbPerUnit1M.SelectedText
Cmd.Parameters.Add(New SqlCeParameter("@ExpiryDate", SqlDbType.DateTime)).Value = dtpExpiry.Value
Cmd.Parameters.Add(New SqlCeParameter("@SellingPrice", SqlDbType.NVarChar)).Value = txtPrice.Text
Cmd.Parameters.Add(New SqlCeParameter("@PerUnit2", SqlDbType.NVarChar)).Value = cbPerUnit2M.SelectedText
Cmd.ExecuteNonQuery()
MsgBox("Items already UPDATED!")
List.Show()
Me.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
Con.Close()

Answer

Try adding:

Dim modified = Cmd.ExecuteNonQuery()
If modified = 0 Then Throw New Exception("Nothing was updated")

If the modified value = 0 then something is wrong with your WHERE clause and you're not matching the records.

If the modified value <> 0 then the update query ran and matched something. So the problem must be with the values that you're updating, or however you are checking these values.