0

i have make this program to update database.but it have something error.
pop up show this message "An unhandled exception of type 'System.InvalidOperationException' occurred in system.data.dll

Additional information: ExecuteNonQuery requires an open and available Connection. The connection's current state is Closed.
"

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
Try

If txtUserName.Text <> "" Then


OleDbDataAdapter1.UpdateCommand.CommandText = _
"UPDATE addresses SET PinCode= '" & txtPinCode.Text & "' , " & _
"Occupation= '" & txtOccupation.Text & "' , " & _
"Balance='" & txtBalance.Text & "' , " & _
"Age='" & txtAge.Text & "' , " & _
"WHERE UserName=" & txtUserName.Text & " ; "

txtStatus.Text &= vbCrLf & "Sending query: " & _
OleDbDataAdapter1.UpdateCommand.CommandText & _
vbCrLf


txtStatus.Text &= vbCrLf & "Query Successful" & _
vbCrLf

OleDbDataAdapter1.UpdateCommand. _
ExecuteNonQuery()

Else
txtStatus.Text &= vbCrLf & _
"You may only update an existing record. "

End If

Catch exception As System.Data.OleDb.OleDbException
Console.WriteLine(exception.StackTrace)
txtStatus.Text &= exception.ToString

End Try
End Sub

Edited by __avd: Set [code] tags.

2
Contributors
3
Replies
4
Views
6 Years
Discussion Span
Last Post by Oxiegen
0

Do this instead:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
Dim conn As New OleDbConnection("<connection string>")
Try
   If txtUserName.Text <> "" Then

   Dim SQL As String = "UPDATE addresses SET PinCode= '" & txtPinCode.Text & "' , " & _
"Occupation= '" & txtOccupation.Text & "' , " & _
"Balance='" & txtBalance.Text & "' , " & _
"Age='" & txtAge.Text & "' , " & _
"WHERE UserName=" & txtUserName.Text & "'"

   txtStatus.Text &= vbCrLf & "Sending query: " & SQL

   Dim hasErrors As Boolean = False
   conn.Open()
   Dim com As New OleDbCommand(SQL, conn)
   If com.ExecuteNonQuery = 0 Then
      hasErrors = True
   End If
   conn.Close()

   If Not hasErrors Then
      txtStatus.Text &= vbCrLf & "Query Successful" & vbCrLf
   Else
      txtStatus.Text &= vbCrLf & You may only update an existing record. "
   End If

Catch exception As System.Data.OleDb.OleDbException
   If conn.State = ConnectionState.Open Then
      conn.Close()
   End If
   Console.WriteLine(exception.StackTrace)
   txtStatus.Text &= exception.ToString
End Try
End Sub
0
Dim com As New OleDbCommand(SQL, conn)

why SQL above is not declare?
How to declare it?
0

Look further up, almost at the top.
SQL is declared.

However, I noticed an error in the SQL string.
Replace "WHERE UserName=" & txtUserName.Text & "'" with "WHERE UserName='" & txtUserName.Text & "'"

Edited by Oxiegen: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.