CODEWITHSUNDEEP
vb.netsqlitesql-update
Milosz Laksa
Milosz Laksa

Reputation: 59

System.InvalidOperationException: 'Operation is not valid due to the current state of the object.' on a update query in vb.net

Hello I am trying to do an update query on my database however I end up with this error message

System.InvalidOperationException: 'Operation is not valid due to the current state of the object.'

Here is the code for my search query which I then use in the update

Private Function SearchData(Fname As String, ID As Int32) As DataTable
    Dim dt As New DataTable
    Dim ds As New DataSet

    Dim ssql As String = "SELECT * FROM customers WHERE fname LIKE @Fname OR CustomerID = @ID"
    Using con As New SQLiteConnection(ConStr),
     cmd As New SQLiteCommand(ssql, con)
        con.Open()
        cmd.Parameters.Add("@Fname", DbType.String).Value = $"%{Fname}%"
        cmd.Parameters.Add("@ID", DbType.Int32).Value = ID
        dt.Load(cmd.ExecuteReader)
        Dim da As New SQLiteDataAdapter(cmd)
        da.Fill(ds, "customers")
        dt = ds.Tables(0)
        If dt.Rows.Count > 0 Then
            ToTextbox(dt)
        End If
    End Using
    Return dt
End Function

This is the ToTextBox function

 Public Sub ToTextbox(ByVal newdt)

    txtFName.Text = newdt.Rows(0)(1).ToString()
    txtLName.Text = newdt.Rows(0)(2).ToString()
    mtxtContactNumber.Text = newdt.rows(0)(3).ToString()
    txtAddress.Text = newdt.rows(0)(4).ToString()
    txtTown.Text = newdt.rows(0)(5).ToString()
    txtPostCode.Text = newdt.rows(0)(6).ToString()



End Sub

And the update function

 Public Function updateguest(FirstName As String, ID As Integer) As Integer
    Dim Result As Integer
    Dim usql As String = "UPDATE Customers SET fname = @fname WHERE CustomerID = @ID;"
    Using con As New SQLiteConnection(ConStr),
        cmd As New SQLiteCommand(usql, con)
        con.Open()
        cmd.Parameters.Add("@fname", DbType.String).Value = FirstName
        cmd.Parameters.Add("@ID", DbType.Int32).Value = ID
        con.Open()
        Result = cmd.ExecuteNonQuery
        con.Close()
    End Using
    Return Result
End Function

And the Update button

    Private Sub IbtnUpdate_Click(sender As Object, e As EventArgs) Handles ibtnUpdate.Click
    Try
        Dim Result = updateguest(txtFName.Text, CInt(txtSearchID.Text))
        If Result > 0 Then
            MsgBox("New RECORD HAS BEEN UPDATED!")
        Else
            MsgBox("NO RECORD HAS BEEN UPDATDD!")
        End If
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

End Sub

Upvotes: 1

Views: 1532

Answers (1)

Mary
Mary

Reputation: 15081

I believe you update problems are solved in comments.

You still have too much stuff in your DataAccess code. Just return the DataTable and use it in the user interface code.

Private Function SearchData(Fname As String, ID As Int32) As DataTable
    Dim dt As New DataTable
    Dim ssql As String = "SELECT * FROM customers WHERE fname LIKE @Fname OR CustomerID = @ID"
    Using con As New SQLiteConnection(ConStr),
            cmd As New SQLiteCommand(ssql, con)
        con.Open()
        cmd.Parameters.Add("@Fname", DbType.String).Value = $"%{Fname}%"
        cmd.Parameters.Add("@ID", DbType.Int32).Value = ID
        dt.Load(cmd.ExecuteReader)
    End Using
    Return dt
End Function

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim dt = SearchData(FirstNameTextBox.Text, CInt(IDTextBox.Text))
    If dt.Rows.Count > 0 Then
        ToTextbox(dt)
    End If
End Sub

Public Sub ToTextbox(ByVal newdt As DataTable)
    txtFName.Text = newdt.Rows(0)(1).ToString()
    txtLName.Text = newdt.Rows(0)(2).ToString()
    mtxtContactNumber.Text = newdt.Rows(0)(3).ToString()
    txtAddress.Text = newdt.Rows(0)(4).ToString()
    txtTown.Text = newdt.Rows(0)(5).ToString()
    txtPostCode.Text = newdt.Rows(0)(6).ToString()
End Sub

Upvotes: 1

Related Questions