CODEWITHSUNDEEP
vb.netdatatable
DatBrummie
DatBrummie

Reputation: 63

Removing a row from a datatable

I have a program that asks the user a series of questions that are collected from a database and stored in a datatable.

I have a system that chooses two random numbers, one to determine which question and one to determine what order the answers are displayed. I want questions to remain in the datatable until the user gets it correct, and afterwards that question cannot come up again.

My datatable is called DT, and there's a line of code:

DT.Rows.RemoveAt(QNumber)

Which sounds like it should remove the row selected. However I have a question regarding this.

If for example QNumber was 2, and so row 2 was deleted. Would this then move everything from row 3 in to row 2, and then everything from row 4 to row 3 and so on, or would this just make row 2 blank, and so break my code?

Upvotes: 1

Views: 5902

Answers (1)

rheitzman
rheitzman

Reputation: 2297

Since you didn't post any context are some test snippets:

Button1_Click loads a table

Button2_Click "processes" each row and optionally deletes the row.

In your case "process" would mean: display the question and loop until you get an answer.

In your case Button2 code would determine which record to load, probably using .Select() on the datatable that returns the "question" to ask, and eventually delete.

Dim da As SqlDataAdapter
Dim ds As DataSet
Dim dt As DataTable
Dim con As New System.Data.SqlClient.SqlConnection()

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Try
        Using con As New System.Data.SqlClient.SqlConnection()
            con.ConnectionString = "Data Source=APCD03;Initial Catalog=OIStest;Integrated Security=True"
            Dim rdr As Data.SqlClient.SqlDataReader
            con.Open()
            Dim cmd As New SqlCommand("SELECT  [DBLinked] as PK ,[TEBackupDate] FROM [OISTest].[dbo].[_DBLink]", con)
            rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
            dt = New DataTable
            dt.Load(rdr)
            rdr.Close()
        End Using
    Catch ex As Exception
        MsgBox(ex, ex.Message)
    End Try
End Sub

Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    For Each row As DataRow In dt.Rows
        If Not row.RowState = DataRowState.Deleted Then
            If MsgBox("Delete this row, PK: " & row("PK"), vbYesNo) = MsgBoxResult.Yes Then
                row.Delete()
            End If
        End If
    Next
End Sub

Button3 demos selecting a specific row to process and delete:

Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
    Dim rows() As DataRow = dt.Select("PK='test row'")
    If rows.Length = 0 Then
        MsgBox("row was deleted already")
    Else
        MsgBox(rows(0)(0)) ' display PK
        If MsgBox("Delete " & rows(0)(0), vbYesNo) = MsgBoxResult.Yes Then
            rows(0).Delete()
        End If
    End If
End Sub

Note that rows(0) is the first record of the result from the select - not the record index in the overall table.

Upvotes: 1

Related Questions