CODEWITHSUNDEEP
vb.netvisual-studio-2010
Him_Jalpert
Him_Jalpert

Reputation: 2516

Connection state is closed error when connection is open

I'm getting the following error: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Yet, in my code, the connection state is opened right before this exception is thrown. I don't understand why it says it would be closed??

Code:

Try

conn = New SqlConnection(connectionString)

If i = objId.Count - 1 Then
    cmd = New SqlCommand("Update DmdOD SET note = @note, note1 = @note1, noteBranch = @branch, noteAccountNo = @account," & _
    "noteType = @type, noteSub = @sub, lastUpdatedBy = @user  WHERE no = @no and id=@id", conn)
                        cmd.Parameters.Add(New SqlParameter("@id", objId.Item(i)))
End If

cmd.Parameters.Add(New SqlParameter("@note", ddl.SelectedItem.Text))

If ddlNote.SelectedItem.Text IsNot "" Then
   cmd.Parameters.Add(New SqlParameter("@note1", ddlNote.SelectedItem.Text))
Else
   cmd.Parameters.Add(New SqlParameter("@note1", DBNull.Value))
End If


If txtAccountBox.Text IsNot "" Then
   cmd.Parameters.Add(New SqlParameter("@account", txtAccountBox.Text))
Else
   cmd.Parameters.Add(New SqlParameter("@account", DBNull.Value))
End If

If txtSubBox.Text IsNot "" Then
   cmd.Parameters.Add(New SqlParameter("@sub", txtSubBox.Text))
Else
   cmd.Parameters.Add(New SqlParameter("@sub", DBNull.Value))
End If

cmd.Parameters.Add(New SqlParameter("@no", row.Cells(1).Text))

cmd.Parameters.Add(New SqlParameter("@user", HttpContext.Current.User.Identity.Name.ToString()))

If ddBranchBox.SelectedItem.Text IsNot "" Then
   cmd.Parameters.Add(New SqlParameter("@branch", ddBranchBox.SelectedItem.Text))
Else
   cmd.Parameters.Add(New SqlParameter("@branch", DBNull.Value))
End If

If ddTypeBox.SelectedItem.Text IsNot "" Then
   cmd.Parameters.Add(New SqlParameter("@type", ddTypeBox.SelectedItem.Text))
Else
   cmd.Parameters.Add(New SqlParameter("@type", DBNull.Value))
End If

conn.Open()
   cmd.ExecuteNonQuery() ' Where the exception is being thrown
conn.Close()

   cmd.Parameters.Clear()

   LblErr.ForeColor = Drawing.Color.Green
   LblErr.Text = "Data Saved!"

Catch ex As Exception
   LblErr.ForeColor = Drawing.Color.DarkRed
   LblErr.Text = "Save Button - " & ex.Message
Finally
   conn.Dispose()
End Try

Upvotes: 0

Views: 1894

Answers (2)

Steve
Steve

Reputation: 216333

You have created two times the connection. When you build the SqlCommand you associate that command with the connection created just before, but then you recreate again the connection and this new object is no more the same associated with the command-

After adding the parameters remove this line

  conn = New SqlConnection(connectionString)

However you should always enclose disposable objects like a connection with a using block

Try

  Using conn = New SqlConnection(connectionString)
     .....
     create the command
     create the parameters
     ....
     cmd.ExecuteNonQuery() 

  End Using  ' Here the connection will be closed and disposed
Catch ex As Exception

End Try

As a side note, what happen in your code if

If i <> objId.Count - 1 Then

Upvotes: 1

Cadburry
Cadburry

Reputation: 1864

Your overwrite your instance "conn"

At the beginning of your code you declare "conn"

conn = New SqlConnection(connectionString)

And before open the connection you do that again.. Remove the second assignment.

Upvotes: 1

Related Questions