Delete a record from database

Hello everyone,

I'm trying to delete a record from database. I delete within the code and display the results on a grid.

The problem is the record is deleted from the dataset, and I can see it in the grid and in the number of records of the dataset, but not from the database.

Note I get no exception.

If anybody could help me I would very appriciate it.

thanks,
Shuly.

Here is the code:

Dim objConn As OleDb.OleDbConnection
Dim objDS As DataSet
Dim objDeleteCmd As New OleDb.OleDbCommand()
Dim sDeleteSQL As String
Dim objParam As OleDb.OleDbParameter
Dim objDataAdapter As OleDb.OleDbDataAdapter

Private Sub Form1_Load (ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

Const sConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=E:\VbNet_Projects\Add\Grid\DatabaseTry\LocalJournal.mdb;" _
& "Persist Security Info=False"
Dim sSQL As String
sSQL = "SELECT EventId From Journal"

objConn = New OleDb.OleDbConnection(sConnection)
objDataAdapter = New OleDb.OleDbDataAdapter(sSQL, objConn)
objDS = New DataSet()
Dim objDV As DataView

Try
objConn.Open()
Catch myException As System.Exception
Windows.Forms.MessageBox.Show(myException.Message)
End Try

If objConn.State = ConnectionState.Open Then
Try
objDataAdapter.Fill(objDS, "Journal")
objConn.Close()
Catch myexception As Exception
Windows.Forms.MessageBox.Show(myException.Message)
End Try
End If

DataGrid1.SetDataBinding(objDS, "Journal")
TextBox1.Text = CStr(objDS.Tables("Journal").Rows.Count).ToString


sDeleteSQL = "DELETE FROM Journal WHERE EventId = ?"
objDeleteCmd.Connection = objConn
objDeleteCmd.CommandText = sDeleteSQL

objParam = objDeleteCmd.Parameters.Add("@EventId", OleDb.OleDbType.BigInt)
objParam.SourceColumn = "EventId"
objParam.SourceVersion = DataRowVersion.Current

objDataAdapter.DeleteCommand = objDeleteCmd

End Sub


Private Sub btnDelete_Click _Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click

Dim minEvent As Long

Try
minEvent = 1
Dim cRow() As DataRow = objDS.Tables("Journal").Select("[EventId] = " & minEvent)
objDS.Tables("Journal").Rows.Remove(cRow(0))
objDataAdapter.Update(objDS, "Journal")
TextBox1.Text = CStr(objDS.Tables("Journal").Rows.Count).ToString
Catch ex As Exception
Windows.Forms.MessageBox.Show(ex.Message)
End Try

End Sub

You must use DataAdapter.Update(DataSet, table).

For example, if you have the dataAdapter custDA and the data set custDS and the table "Customers", after you make changes to the DS you can update the database like this:

custDA.Update(custDS, "Customers")

You can also hook the events OnRowUpdated and OnRowUpdating. The first one is fired after the update took place while the other one is fired before so you can perform some checks there...

Hi,

I did use this line: objDataAdapter.Update(objDS, "Journal"), but it does not update in the database, I'll try to check the events.

Thanks,
Shuly.

Hi,

Is there any way I can use the Events without using the DataSet wiserd?
All my objects are defind in code, and I can't find those events you talked about.

Thanks,
Shuly.

sDeleteSQL = "DELETE FROM Journal WHERE EventId = ?"


is this the actual line you are using? IF so what is the ? mark for? Remember you have to use single quotes when dealing with strings in Sql....so if you were looking for ? you would have to put

sDeleteSQL = "DELETE FROM Journal WHERE EventId = '?'"

Well, the type of the field is long (or bigInt in vb.net).

Any way I tried it and it didn't help, so that's probebly not the problem.
Maybe something else in the syntax is worng?

Thanks,
Shuly.

Hi, every one and thanks for your answares.

Well it's appear that the problem was in the row: objDS.Tables("Journal").Rows.Remove(cRow(0))

The right way to delete from the recordset (and I don't know why) is: cRow(0).Delete()

Shuly.:)