Current item cannot be removed from the list because there is no current item-CodePudding

I wrote this code to delete the selected record:

Dim Str As String = "DELETE FROM TableName WHERE ID=" & IDTextBox.Text & ""
        Dim cmd As New SqlCommand
        If Sqlcon.State = ConnectionState.Open Then Sqlcon.Close()
        Sqlcon.Open()
        With cmd
            .Connection = Sqlcon
            .CommandType = CommandType.Text
            .CommandText = Str
            .ExecuteNonQuery()
            .Dispose()
        End With
        Sqlcon.Close()

But I get this Error: (Current item cannot be removed from the list because there is no current item)

I couldn't find the problem!

CodePudding user response：

You need to validate the input in the text box to be sure it is a number. (I guessed that the ID field is a number) TryParse will return True or False and if True it fills the second parameter with the number.

It is a good idea to separate your database code from your user interface code.

Both Commands and Connections need to be disposed so they should be declared locally in a Using block.You should never have to check ConnectionState because the connection is always in the method where it is used. Wherever the connection is declared elsewhere, delete it.

You can pass the CommandText and Connection directly to the constructor of the Command. CommandType.Text is the default so you don't have explicitly assign it.

To avoid sql injection and assure the correct type is presented to the server ALWAYS use Parameters.

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim Id As Integer
    If Integer.TryParse(IDTextBox.Text, Id) Then
        DeleteRecord(Id)
    Else
        MessageBox.Show("Please enter a valid ID")
    End If
End Sub

Private OPConStr As String = "Your connection string."

Private Sub DeleteRecord(id As Integer)
    Dim Str As String = "DELETE FROM TableName WHERE ID= @ID;"
    Using SqlCon As New SqlConnection(OPConStr),
            cmd As New SqlCommand(Str, SqlCon)
        cmd.Parameters.Add("@ID", SqlDbType.Int).Value = id
        SqlCon.Open()
        cmd.ExecuteNonQuery()
    End Using 'disposes the command and closes and disposes the connection
End Sub