Question delete record from dataview and database

[elianeasmar]

Hello.

Dim dv As New DataView(_DataSet.Tables(0))        ' select deleted rows 
        dv.RowStateFilter = DataViewRowState.Deleted
        For _irow As Long = 0 To dv.Table.Rows.Count - 1
            ' if serial is null, that means the row is new and deleted 
            ' so no need to add it to database 
            If Not IsDBNull(dv!serial) Then
                Dim strconnection As String = "Data Source=EASMAR-PC;Initial Catalog=Database Connection;Integrated Security=True;"
                Dim _cn As SqlConnection = New SqlConnection(strconnection)
                _cn.Open()
                Dim cmd As New SqlCommand
                cmd.CommandText = "Delete from tblCustomer where serial= " & txtSerial.Text
            End If
        Next

What am i missing?

I am getting this error:

Conversion from String "serial" to type Integer is not valid
[/FONT]

On this line:

If Not IsDBNull(dv!serial) Then

 

[jmcilhinney]

What you're doing there makes no sense at all. You create a new DataView but then you simply use it to get back the DataTable that you had in the first place. If it's your intention to loop through the records in the DataView then that's what you should be doing; not looping through the DataTable it's a view of.

Perhaps we might have a better chance of being able to help you if you were to tell us exactly what you're trying to achieve rather than expect us to work it out from code that doesn't actually do it.

 

[elianeasmar]

What i am trying to do is,to loop throught the records in the dataview, to view there rowstate.
If the rowstate is deleted(respectevely updated,inserted), i execute the delete query to delete(respectevilly update,insert) the record from database.

The whole point is that the user can delete,insert and update as much as he want.
But in the end of the day, there is a button submit that upply the changes to the database.
I wrote some code. But didn't work for me. I am getting the error in my previous response.

Thank you for your time.

 

[jmcilhinney]

What i am trying to do is,to loop throught the records in the dataview, to view there rowstate.
If the rowstate is deleted(respectevely updated,inserted), i execute the delete query to delete(respectevilly update,insert) the record from database.

But why? The whole point of having the data in a DataTable in the first place is so that you don't have to do that. Just as you use a data adapter to populate a DataTable with a single Fill call, so you save all the changes from a DataTable back to the database with a single Update call. Check out this thread for code examples of the most common ADO.NET scenarios, including yours:

Retrieving and Saving Data in Databases

 

[elianeasmar]

I know. It's much easier. And less line of codes. But i have an assignment. So I am going to try to work it out.
Thank you for your response.
any help would be appreciated.

 

[jmcilhinney]

Well then, as I said, you need to loop through the DataView and not the DataTable. Also, you need to get the identifying field value from the current row, not some TextBox.