Lỗi executenonquery requires an open and available connection c năm 2024

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Show

SqlCommand.ExecuteNonQuery Method

  • Reference

Definition

Executes a Transact-SQL statement against the connection and returns the number of rows affected.

public:
 override int ExecuteNonQuery();
public:
 virtual int ExecuteNonQuery();
public override int ExecuteNonQuery ();
public int ExecuteNonQuery ();
override this.ExecuteNonQuery : unit -> int
abstract member ExecuteNonQuery : unit -> int
override this.ExecuteNonQuery : unit -> int
Public Overrides Function ExecuteNonQuery () As Integer
Public Function ExecuteNonQuery () As Integer

Returns

The number of rows affected.

Implements

Exceptions

An exception occurred while executing the command against a locked row. This exception is not generated when you are using Microsoft .NET Framework version 1.0.

-or-

A timeout occurred during a streaming operation. For more information about streaming, see SqlClient Streaming Support.

Examples

The following example creates a SqlCommand and then executes it using ExecuteNonQuery. The example is passed a string that is a Transact-SQL statement (such as UPDATE, INSERT, or DELETE) and a string to use to connect to the data source.

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}
Public Sub CreateCommand(ByVal queryString As String, _
  ByVal connectionString As String)
    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(queryString, connection)
        command.Connection.Open()
        command.ExecuteNonQuery()
    End Using
End Sub

Remarks

You can use the ExecuteNonQuery to perform catalog operations (for example, querying the structure of a database or creating database objects such as tables), or to change the data in a database without using a DataSet by executing UPDATE, INSERT, or DELETE statements.

Although the ExecuteNonQuery returns no rows, any output parameters or return values mapped to parameters are populated with data.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. For all other types of statements, the return value is -1.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers.

When SET NOCOUNT ON is set on the connection (before or as part of executing the command, or as part of a trigger initiated by the execution of the command) the rows affected by individual statements stop contributing to the count of rows affected that is returned by this method.

If no statements are detected that contribute to the count, the return value is -1. If a rollback occurs, the return value is also -1.

Applies to

See also

  • Retrieving and Modifying Data in ADO.NET
  • SQL Server and ADO.NET
  • ADO.NET Overview

Collaborate with us on GitHub

The source for this content can be found on GitHub, where you can also create and review issues and pull requests. For more information, see our contributor guide.

I am new to .net and created a simple form in vb with a small table in sql. I have a datagrid on the page as well as the simple form. When i enter my info in the simple form, I get different errors, which I have been able to fix, except this one:

"ExecuteNonQuery requires an open and available Connection. The connection's current state is closed. " Can someone tell me what I am doing incorrect?

  1. How easy is it to swtich to c# for this app?

\===========Current Code Behind================ Imports System.Data Imports System.Data.SqlClient

Public Class records Inherits System.Web.UI.Page

+Web Form Designer Generated Code

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load 'bind recordListing datagrid here, if no records, do not show grid recordListing.DataBind() recordListing.Visible = recordListing.Items.Count > 0

End Sub

Private Sub submit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles submit.Click 'place condition, if user selects submit, insert, update, or delete data End Sub

Private Sub AddNewRecord_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles AddNewRecord.Click 'create connection string Dim Conn As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString")) Dim myCommand As New SqlCommand("INSERT INTO classicRecords (artist,recordName,releaseDate,comments,submitDate) VALUES ('" & artist.Text & "','" & recordName.Text & "','" & releaseDate.Text & "','" & comments.Text & "','" & Now() & "')", Conn)

myCommand.Connection = recordConnection Conn.Open() myCommand.ExecuteNonQuery() Conn.Close()

messageOutput.Text = "Thank you for adding " & artist.Text & "!"

End Sub

End Class

\===========Webconfig file:========

Thanks in advance...