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 6 Years Ago by __avd: Set [code] tags.

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
Dim com As New OleDbCommand(SQL, conn)

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

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 6 Years Ago by Oxiegen: n/a

This article has been dead for over six months. Start a new discussion instead.