Member Avatar for ogimy

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 kvprajapati because: Set [code] tags.
  • 2 Contributors
  • 3 Replies
  • 238 Views
  • 7 Hours Discussion Span
  • Latest Post Latest Post by Oxiegen

Dani AI

Generated

Short answer: the command is being executed with no open connection, and the SQL string itself is malformed (extra comma before WHERE and the UserName value needs proper quoting). As already noted, that WHERE clause must be corrected, and as asked, the SQL must be declared before it is used and supplied to a Command that has an open Connection.

Practical fixes (in order of importance):

  • Remove the stray comma before WHERE and stop building SQL by concatenation. Use parameterized SQL so quotes and embedded apostrophes are handled safely.
  • Ensure the Command has a Connection assigned and that the Connection is Open before calling ExecuteNonQuery (or use a Using block so the connection is reliably closed).
  • Match parameter types to the database (numeric fields should be passed as numbers, not quoted strings).
  • For OleDb use positional parameters (?) and add parameters in the same order.

Example pattern to follow (parameterized, Using pattern — not the same code shown earlier):

Dim cs As String = "<your connection string>"
Using cn As New OleDb.OleDbConnection(cs)
  Using cmd As New OleDb.OleDbCommand("UPDATE addresses SET PinCode=?, Occupation=?, Balance=?, Age=? WHERE UserName=?", cn)
    cmd.Parameters.Add("p1", OleDb.OleDbType.VarChar).Value = txtPinCode.Text
    cmd.Parameters.Add("p2", OleDb.OleDbType.VarChar).Value = txtOccupation.Text
    cmd.Parameters.Add("p3", OleDb.OleDbType.Decimal).Value = Decimal.Parse(txtBalance.Text)
    cmd.Parameters.Add("p4", OleDb.OleDbType.Integer).Value = Integer.Parse(txtAge.Text)
    cmd.Parameters.Add("p5", OleDb.OleDbType.VarChar).Value = txtUserName.Text
    cn.Open()
    If cmd.ExecuteNonQuery() > 0 Then txtStatus.Text &= vbCrLf & "Query Successful"
  End Using
End Using

Quick debugging tips: set a breakpoint and inspect the command's Connection (and its State) before ExecuteNonQuery; log the final SQL/parameter values (but not sensitive connection details); verify the connection string and field names/types. Using parameters will also eliminate SQL injection risks and the need for manual quoting.

Recommended Answers

All 3 Replies

Member Avatar for Oxiegen

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

why SQL above is not declare?
How to declare it?
Member Avatar for Oxiegen

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 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.