asked on

DataGridview Delete and update.

I got my datagridview to work, I now can query the DB, and insert data to it. Now I need to know how can I delete and update a datagridview.

If I select on the side the row, and press delete how can I remove it from the DB?
also if I make some changes on the datagridview fields how can I update the DB?

ASKER CERTIFIED SOLUTION

vbturbo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ennio

ASKER

I'm getting this error when doing the delete. Am I missing something?

Here is the error code

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Agenda.data.deleteData(Int32 number) in C:\Documents and Settings\Ennio\My Documents\Visual Studio 2005\Projects\Agenda\Agenda\data.vb:line 58

here is my code:

Public Sub deleteData(ByVal number As Integer)
Try
cmdDelete.CommandText = "DELET tbl_agenda where id = " & myDataSet.Tables("tbl_agenda").Rows(number).Item(0)
cmdDelete.Connection = myConn
myAdapter.DeleteCommand = cmdDelete

myDataSet.Tables("tbl_agenda").Rows(number).Delete()
myAdapter.Update(myDataSet)

Catch ex As Exception
Debug.Write(ex.ToString)
End Try

End Sub

Ennio

ASKER

I fixed the SQL statement I the data was not removed from the DB

here is the error msg

A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
System.Data.OleDb.OleDbException: Syntax error (missing operator) in query expression 'tbl_agenda where id = 1'.
at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet)
at Agenda.data.deleteData(Int32 number) in C:\Documents and Settings\Ennio\My Documents\Visual Studio 2005\Projects\Agenda\Agenda\data.vb:line 58

Ennio

ASKER

got it to work... missed the E on the delete and the FROM

:)

vbturbo

Hi

And glad you got it

Here delete from the database

cmdDelete.CommandText = "DELET tbl_agenda where id = " & myDataSet.Tables("tbl_agenda").Rows(number).Item(0)
cmdDelete.Connection = myConn
myAdapter.DeleteCommand = cmdDelete

then delete from the dataset ! THAT just is ok
myDataSet.Tables("tbl_agenda").Rows(number).Delete()
Then you Delete one more time the same record in the database
myAdapter.Update(myDataSet)

Your myAdapter has -----insert update delete and select statements implicit + all the connection data ,

so you dont even have to write con.open and con.close---just myAdapter.Update(myDataSet)

then you dont need this code

cmdDelete.CommandText = "DELET tbl_agenda where id = " & myDataSet.Tables("tbl_agenda").Rows(number).Item(0)
cmdDelete.Connection = myConn
myAdapter.DeleteCommand = cmdDelete

try only this

Public Sub deleteData(ByVal number As Integer)
Dim cb As New OleDb.OleDbCommandBuilder(myAdapter)

Try
myDataSet.Tables("tbl_agenda").Rows(number).Delete()
myAdapter.Update(myDataSet)

Catch ex As Exception
Debug.Write(ex.ToString)
End Try

End Sub

vbturbo

vbturbo

will have the same effect