.ExecuteNonQuery() sql asp.net error

Question

This is my first time working with sql and asp.net. I am working on a few examples to ensure I have all the basics I need. I was walking though a tutorial and where everything should be working just fine, I am getting an .ExecuteNonQuery() Error. SqlException was unhandled by user code // Incorrect syntax near the keyword 'Table'.

If you have any pointers, let me know. I worked the tutorial twice, I'm sure I'm doing something wrong here. -Thanks

.CS Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

namespace WebSite
{
public partial class _default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

    protected void Page_Load(object sender, EventArgs e)
    {
        con.Open();
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand("insert into Table values('" + txtfName.Text + "','" + txtlName.Text + "','" + txtpNumber.Text + "')", con);
        cmd.ExecuteNonQuery();
        con.Close();
        Label1.Visible = true;
        Label1.Text = "Your DATA has been submitted";
        txtpNumber.Text = "";
        txtlName.Text = "";
        txtfName.Text = "";
    }
  }
}

.aspx File:

<form id="form1" runat="server">
<div class="auto-style1">

    <strong>Insert data into Database<br />
    <br />
    </strong>

</div>
    <table align="center" class="auto-style2">
        <tr>
            <td class="auto-style3">First Name:</td>
            <td class="auto-style4">
                <asp:TextBox ID="txtfName" runat="server" Width="250px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style3">Last Name:</td>
            <td class="auto-style4">
                <asp:TextBox ID="txtlName" runat="server" Width="250px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style3">Phone Number:</td>
            <td class="auto-style4">
                <asp:TextBox ID="txtpNumber" runat="server" Width="250px"></asp:TextBox>
            </td>
        </tr>
        <tr>
            <td class="auto-style3">&nbsp;</td>
            <td class="auto-style4">
                <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Submit" Width="150px" />
            </td>
        </tr>
    </table>
    <br />
    <br />
    <asp:Label ID="Label1" runat="server" ForeColor="#663300" style="text-align: center" Visible="False"></asp:Label>
    <br />
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT * FROM [Table]"></asp:SqlDataSource>
</form>

SQL Database:

CREATE TABLE [dbo].[Table] (
[Id]      INT          IDENTITY (1, 1) NOT NULL,
[fName]   VARCHAR (50) NOT NULL,
[lName]   VARCHAR (50) NOT NULL,
[pNumber] VARCHAR (50) NOT NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

Answer 1

Usually this error message is caused by a single quote present in your input textboxes or by the use of a reserved keyword. Both problems are present in your query. The TABLE word is a reserved keyword for SQL Server and thus you should encapsulate it with square brackets, while for the possible presence of a single quote in the input text the correct approach is to use Parameterized Query like this

SqlCommand cmd = new SqlCommand("insert into [Table] values(@fnam, @lnam, @pNum)", con);
cmd.Parameters.AddWithValue("@fnam", txtfName.Text );
cmd.Parameters.AddWithValue("@lnam", txtlName.Text );
cmd.Parameters.AddWithValue("@pNum", txtpNumber.Text);
cmd.ExecuteNonQuery();

With this approach you shift the work to parse your input text to the framework code and you avoid problems with parsing text and Sql Injection

Also, I suggest to NOT USE a global variable to keep the SqlConnection reference. It is an expensive resource and, if you forget to close and dispose it, you could have a significant impact on the performance and the stability of your application.
For this kind of situations the using statement is all you really need

using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings
                             ["ConnectionString"].ConnectionString));
{
    con.Open();
    SqlCommand cmd = new SqlCommand("insert into [Table] values(@fnam, @lnam, @pNum)", con);
    cmd.Parameters.AddWithValue("@fnam", txtfName.Text );
    cmd.Parameters.AddWithValue("@lnam", txtlName.Text );
    cmd.Parameters.AddWithValue("@pNum", txtpNumber.Text);
    cmd.ExecuteNonQuery();
}

Of course remove the global variable and the open in the Page_Load

Answer 2

Your query is trying to insert into a table called Table. Does that really exist? If not then put the actual table name into the query. If your table really is called Table then I strongly recommend you change it to something less confusing.

Also, stop writing commands by concatenating text now. Learn how to use parameters in order to prevent SQL injection

EDIT

An insert statement uses the format specified in the BOL documents for INSERT, and the examples provided therein. Table is a keyword, so don't use it as a table name. If you have to use a keyword, you need to escape it using square brackets. See BOL: Delimited Identifiers

I still say, don't use "Table" as the name for a table. Make your life easier.

Oh, and write secure code (see the above comment re SQL injection, and how Linked In got hit, and how much it cost them)

Answer 3

Changed 'insert into Table values' to 'insert into [Table] values' and all works fine. Thanks Note to self, stay away from simple names.

Answer 4

SqlConnection conn = new SqlConnection("Data Source=MCTX-ZAFEER\\SQLEXPRESS;Initial Catalog=ZKAbid_Db;Persist Security Info=True;User ID=sa;Password=sa@1234");
    public int checkLogin(Ad_login ad)
    {
        SqlCommand cmd = new SqlCommand("Sp_Login", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@Admin_id", ad.Ad_id);
        cmd.Parameters.AddWithValue("@Password", ad.Ad_Password);
        // cmd.InsertCommand.Connection = connection1;
        SqlParameter objLogin = new SqlParameter();
        objLogin.ParameterName = "@isValid";
        objLogin.SqlDbType = SqlDbType.Bit;
        objLogin.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(objLogin);

        conn.Open();
        cmd.ExecuteNonQuery();
        int res = Convert.ToInt32(objLogin.Value);
        conn.Close();
        return res;
    }

Answer 5

Wherever you are using ExecuteNonQuery() you should catch SqlException or you need to throws from your function.

In the case given above Button1_Click is the function using ExecuteNonQuery() from SqlCommand class.

Now what happens that this function ( ExecuteNonQuery ) has definition to throws SqlException. so you have two option - you can also throws SqlException - or you can put this line in try catch block to handle the Exception.