Home Asp.net Ado.net – SqlCommand ExecuteNonQuery Example in Asp.net C# Vb.net

Ado.net – SqlCommand ExecuteNonQuery Example in Asp.net C# Vb.net

24
0
Ado.net - SqlCommand ExecuteNonQuery example in asp.net c# vb.net
Ado.net - SqlCommand ExecuteNonQuery example in asp.net c# vb.net

In my previous tutorials, I’d explained about ExecuteReader, ExecuteScalar and Difference between ExecuteReader, ExecuteNonQuery, ExecuteScalar and other more cracking tutorials on Asp.net here.

Now here in this tutorial, I’ll explain Ado.net SQL Command, ExecuteNonQuery function in detail with example code.

The Ado.net Sql Command ExecuteNonQuery returns the number of row(s) affected while performing sql operations like INSERT, UPDATE, DELETE etc.

ExecuteNonQuery return -1 if there is no row affected, otherwise return int value indicating number of rows/columns affected while performing sql operation.

We can use ExecuteNonQuery only with SET, CREATE, INSERT, UPDATE, DELETE statements.

To explain further about executenonquery example, we need to create one database table to read data and bind retrieved result to asp.net gridview, so simply execute following script to sql query editor to create database table and then add few records manually or download from below complete example with script.

Here is the script to create “SubjectDetails” table:

CREATE TABLE [dbo].[SubjectDetails]
(
    [SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
    [SubjectName] [nvarchar](100) NULL
)

Ado.net SqlCommand ExecuteNonQuery Example in .NET – [.aspx]

Following is the complete HTML Markup for your .aspx page:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Ado.net - ExecuteNonQuery Example in C# .NET</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <table>
        <tr>
            <td colspan="3">
                <h4>SqlCommand ExecuteNonQuery in ado.net example</h4>
            </td>
        </tr>
        <tr>
            <td colspan="3">
                <asp:GridView ID="gvSubjectDetails" runat="server"
                    AutoGenerateColumns="false" DataKeyNames="SubjectID">
                    <HeaderStyle Font-Bold="true" BackColor="#ff6600"
                        BorderColor="#f5f5f5" ForeColor="White" Height="30" />
                    <Columns>
                        <asp:BoundField DataField="SubjectID"
                            HeaderText="Subject Id" ItemStyle-Width="150px" />
                        <asp:BoundField DataField="SubjectName"
                            HeaderText="Subject Name" ItemStyle-Width="200px" />
                    </Columns>
                </asp:GridView>
            </td>
        </tr>
        <tr>
            <td colspan="3">&nbsp;</td>
        </tr>
        <tr>
            <td colspan="3">
                <asp:Button ID="btnInsert" runat="server" Text="Insert Record"
                    OnClick="btnInsert_Click" />
            </td>
        </tr>
        <tr>
            <td colspan="3">&nbsp;</td>
        </tr>
        <tr>
            <td>
                <b>ExecuteNonQuery Returns:</b>
            </td>
            <td colspan="2">
                <asp:Label ID="lblReturnValue" runat="server"></asp:Label>
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>
Note: Required namespace for ExecuteNonQuery() is System.Data.SqlClient.

Now check the below sample code snippet that show you a simple executenonquery example:

Ado.net SqlCommand ExecuteNonQuery Example – [C#]

//specify your connection string here..
string strConn = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB";

protected void Page_Load(object sender, EventArgs e) {
    if (!IsPostBack) {
        BindGridviewFileData();
    }
}

//button click event to get the scalar value
protected void btnInsert_Click(object sender, EventArgs e) {
    try {
        using (SqlConnection sqlConn = new SqlConnection(strConn)) {
            using (SqlCommand sqlCmd = new SqlCommand()) {
                sqlCmd.CommandText = "INSERT INTO SubjectDetails VALUES ('jQuery')";
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();

                //here execute non-query will return no of records value in integer
                int retValue = sqlCmd.ExecuteNonQuery();
                if (retValue > 0) {
                    lblReturnValue.Text = retValue + " record(s) inserted!";

                    //record(s) inserted so rebind fresh data
                    BindGridviewFileData();
                } else {
                    lblReturnValue.Text = "No record(s) inserted!";
                }
                sqlConn.Close();
            }
        }
    } catch { }
}

//bind subject details to gridview
private void BindGridviewFileData() {
    try {
        using (SqlConnection sqlConn = new SqlConnection(strConn)) {
            using (SqlCommand sqlCmd = new SqlCommand()) {
                sqlCmd.CommandText = "SELECT * FROM SubjectDetails";
                sqlCmd.Connection = sqlConn;
                sqlConn.Open();
                gvSubjectDetails.DataSource = sqlCmd.ExecuteReader();
                gvSubjectDetails.DataBind();
                sqlConn.Close();
            }
        }
    } catch { }
}

Ado.net SqlCommand ExecuteNonQuery Example – [Vb.net]

'specify your connection string here..'
Dim strConn As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB"

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not IsPostBack Then
        BindGridviewFileData()
    End If
End Sub

'button click event to get the scalar value'
Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As EventArgs)
    Try
        Using sqlConn As New SqlConnection(strConn)
            Using sqlCmd As New SqlCommand()
                sqlCmd.CommandText = "INSERT INTO SubjectDetails VALUES ('jQuery')"
                sqlCmd.Connection = sqlConn
                sqlConn.Open()

                'here execute non-query will return no of records value in integer'
                Dim retValue As Integer = sqlCmd.ExecuteNonQuery()
                If retValue > 0 Then
                    lblReturnValue.Text = retValue & " record(s) inserted!"

                    'record(s) inserted so rebind fresh data'
                    BindGridviewFileData()
                Else
                    lblReturnValue.Text = "No record(s) inserted!"
                End If
                sqlConn.Close()
            End Using
        End Using
    Catch
    End Try
End Sub

'bind subject details to gridview'
Private Sub BindGridviewFileData()
    Try
        Using sqlConn As New SqlConnection(strConn)
            Using sqlCmd As New SqlCommand()
                sqlCmd.CommandText = "SELECT * FROM SubjectDetails"
                sqlCmd.Connection = sqlConn
                sqlConn.Open()
                gvSubjectDetails.DataSource = sqlCmd.ExecuteReader()
                gvSubjectDetails.DataBind()
                sqlConn.Close()
            End Using
        End Using
    Catch
    End Try
End Sub

Example Result

Ado.net - SqlCommand ExecuteNonQuery example in asp.net c# vb.net
Ado.net – SqlCommand ExecuteNonQuery example in asp.net c# vb.net

Download Example

Git Repo

That’s it, this is simplest way to understand ado.net sqlcommand executenonquery in .net using c# or vb.net.

Let me know if you’ve any questions or doubts about this tutorial by writing down your queries in comment below. I would be happy to provide you my feedback as soon as possible.

Happy coding!

Previous articleAdo.net – SqlCommand ExecuteScalar Example in Asp.net C# Vb.net
Next articleAdo.net – SqlCommand ExecuteReader Example in Asp.net C# Vb.net
Hi there, I am Mayank, the man behind Technical Mack. I started AspnetO with a motive to educate people on various programming languages ranging from beginners to expert level. Through this blog, I aim to provide more insightful content.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

seven − three =