Error when trying to connect web form to SQL database to insert values

Question

I am trying to connect my ASP.NET web form to an SQL database that I've created (the name is User) and add values to the table User. However, there was a runtime error that says 'System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword USER.'' I do not know the problem. Is it syntax? Please help look at my code below. This is the .aspx.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 BMICalc
{  
    public partial class WebForm2 : System.Web.UI.Page  
    {  
        protected void Page_Load(object sender, EventArgs e)  
        {  
           if(IsPostBack)  
            {  
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegisterConnectionString"].ConnectionString);  
                conn.Open();  
                string checkuser = "select count(*) from USER where emailAddress='"+ TextBox3.Text+"'";
                SqlCommand cmd = new SqlCommand(checkuser, conn);  
                int temp = Convert.ToInt32(cmd.ExecuteScalar().ToString());  
  
                if (temp == 1)  
                {  
                    Response.Write("Account Already Exists");  
                }  
  
                conn.Close();  
            }  
               
            }  
  
        protected void Button1_Click(object sender, EventArgs e)  
        {  
            try  
            {  
  
                SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["RegiConnectionString"].ConnectionString);  
                conn.Open();  
                string insertQuery = "insert into User(firstName,lastName,emailAddress,password)values (@firstName,@lastName,@emailAddress,@password)";  
                SqlCommand cmd = new SqlCommand(insertQuery, conn);  
                cmd.Parameters.AddWithValue("@firstName", TextBox1.Text);  
                cmd.Parameters.AddWithValue("@lastName", TextBox2.Text);  
                cmd.Parameters.AddWithValue("@emailAddress", TextBox3.Text);  
                cmd.Parameters.AddWithValue("@password", TextBox4.Text);  
                cmd.ExecuteNonQuery();  
  
                Response.Write("User is successfully registered!");  
  
                conn.Close();  
  
            }  
            catch (Exception ex)  
            {  
                Response.Write("error" + ex.ToString());  
            }  
        }  
    }  
}

Thank you.

that is not a mysql conector that you are using it is a sql server — nbk, Jul 14 '21 at 17:21
put gups between words" User (firstName , lastName , emailAddress , password) values" — Serge, Jul 14 '21 at 17:27
Please clarify whether it's the SELECT or INSERT statement that's causing the error — devlin carnate, Jul 14 '21 at 17:28
The error says 'Exception User-Unhandled. System.Data.SqlClient.SqlException: 'Incorrect syntax near the keyword 'USER'.' @hijinxbassist — Samuel Prajasantosa, Jul 14 '21 at 17:28
Also, it appears you're creating a SELECT statement using an unsanitized text box value. That's how SQL Injection occurs. You should used parameters, not concatenating a string with user inputs — devlin carnate, Jul 14 '21 at 17:29
We know the error. We don't know where in your code it's occurring. — devlin carnate, Jul 14 '21 at 17:30
based on context it looks like its the checkuser since that has the uppercased USER table name. — hijinxbassist, Jul 14 '21 at 17:30
@NealBurns Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\User.mdf;Integrated Security=True — Samuel Prajasantosa, Jul 14 '21 at 17:37
@devlincarnate How do we check which statement is causing the error? — Samuel Prajasantosa, Jul 14 '21 at 17:38

Neal Burns · Answer 1 · 2021-07-14T17:57:42.533

0

The error refers to the keyword 'USER'. You are intending User to be the name of a table, not a keyword. Try escaping it:

insert into [User] (firstName,lastName,emailAddress,password)
values (@firstName,@lastName,@emailAddress,@password)

As Devlin has pointed out, the select statement also has an unescaped USER:

select count(*) from [USER] where emailAddress='"+ TextBox3.Text+"'"

edited Jul 14 '21 at 17:57

answered Jul 14 '21 at 17:36

Neal Burns

839
4
5

1

What about the SELECT statement? – devlin carnate Jul 14 '21 at 17:52

Tu deschizi eu inchid · Answer 2 · 2021-07-14T23:33:25.753

The issue is most likely that one or more of the TextBox values are null or empty. When a value is null or empty it's necessary to set the value to DBNull.Value.

The code below shows how to perform error handling so that error messages are shown as well as how to use parameters. The code has been tested, however the code doesn't encrypt the password-I'll leave that for you to implement. It's important that the password be encrypted prior to storing it in the database.

You'll need to change the value of "connectionStr" for your environment. See Connection Strings for more information. The code below has been tested with SQL Server Express.

Try the following:

VS 2019:

Create a new ASP.NET Web Application (.NET Framework)

Project Name: BMICalc
Click Create
Click Empty
Click Create

Add Web Form

In VS menu, click Project
Select Web Form (name: WebForm2.aspx)

WebForm2.aspx:

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm2.aspx.cs" Inherits="BMICalc.WebForm2" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <asp:Label ID="lblUserId" runat="server" Text="UserId:" style="top: 50px; left: 150px; position: absolute; height: 19px; width: 213px"></asp:Label>
            <asp:TextBox ID="textBoxUserId" runat="server" Height="336px" TextMode="SingleLine" style="top: 48px; left: 354px; position: absolute; height: 22px; width: 225px"></asp:TextBox>

            <asp:Label ID="lblFirstName" runat="server" Text="First Name:" style="top: 90px; left: 150px; position: absolute; height: 19px; width: 213px"></asp:Label>
            <asp:TextBox ID="textBoxFirstName" runat="server" Height="336px" TextMode="SingleLine" style="top: 88px; left: 354px; position: absolute; height: 22px; width: 225px"></asp:TextBox>

             <asp:Label ID="lblLastName" runat="server" Text="Last Name:" style="top: 130px; left: 150px; position: absolute; height: 19px; width: 213px"></asp:Label>
            <asp:TextBox ID="textBoxLastName" runat="server" Height="336px" TextMode="SingleLine" style="top: 128px; left: 354px; position: absolute; height: 22px; width: 225px"></asp:TextBox>

             <asp:Label ID="lblEmailAddress" runat="server" Text="Email Address:" style="top: 170px; left: 150px; position: absolute; height: 19px; width: 213px"></asp:Label>
           <asp:TextBox ID="textBoxEmailAddress" runat="server" Height="336px" TextMode="SingleLine" style="top: 168px; left: 354px; position: absolute; height: 22px; width: 225px"></asp:TextBox>

             <asp:Label ID="lblPassword" runat="server" Text="Password:" style="top: 210px; left: 150px; position: absolute; height: 19px; width: 213px"></asp:Label>
            <asp:TextBox ID="textBoxPassword" runat="server" Height="336px" TextMode="Password" style="top: 208px; left: 354px; position: absolute; height: 22px; width: 225px"></asp:TextBox>
        </div>
        <div>
            <asp:Button ID="btnCreateUser" runat="server" Text="Create User" OnClick="btnCreateUser_Click" style="top: 260px; left: 425px; position: absolute; height: 35px; width: 100px" />
        </div>
        <div>
             <asp:Label ID="lblMsg" runat="server" Text="" style="top: 330px; left: 150px; position: absolute; height: 19px; align-content:center; color:red"></asp:Label>
        </div>
    </form>
</body>
</html>

WebForm2.aspx.cs

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

namespace BMICalc
{
    public partial class WebForm2 : System.Web.UI.Page
    {
        private string connectionStr = String.Format(@"Data Source='.\SQLExpress'; Initial Catalog='BMICalc'; Integrated Security=True; MultipleActiveResultSets=True");
        private string logFilename = string.Empty;

        protected void Page_Load(object sender, EventArgs e)
        {
            string errMsg = string.Empty;
            string folderPath = Path.GetDirectoryName(HttpContext.Current.Server.MapPath("~"));
            logFilename = Path.Combine(folderPath, "log.txt");

            //lblMsg.Text = "logFilename: " + logFilename;

            if (IsPostBack)
            {
                try
                {
                    int rowCount = CheckUser(textBoxEmailAddress.Text);

                    if (rowCount > 0)
                    {
                        lblMsg.Text = "Account already exists.";
                    }
                }
                catch(SqlException ex)
                {
                    errMsg = "Error: (Page_Load - SqlException): " + ex.Message;
                    LogMsg(errMsg);
                    lblMsg.Text = errMsg;

                    //uncommenting the following line may be helpful for debugging purposes
                    //throw ex;
                }
                catch (Exception ex)
                {
                    errMsg = "Error: (Page_Load): " + ex.Message;
                    LogMsg(errMsg);
                    lblMsg.Text = errMsg;

                    //uncommenting the following line may be helpful for debugging purposes
                    //throw ex;
                }
            }
        }

        protected void btnCreateUser_Click(object sender, EventArgs e)
        {
            string errMsg = string.Empty;

            try
            {
                int rowCount = AddUser(textBoxUserId.Text, textBoxFirstName.Text, textBoxLastName.Text, textBoxEmailAddress.Text, textBoxPassword.Text);

                if (rowCount > 0)
                {
                    lblMsg.Text = "User successfully registered.";
                }
                else
                {
                    lblMsg.Text = "Registration failed.";
                }
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                //ToDo: log error message
                errMsg = "Error: (btnCreateUser - SqlException): " + ex.Message;
                LogMsg(errMsg);
                lblMsg.Text = errMsg;

                //uncommenting the following line may be helpful for debugging purposes
                //throw ex; 
            }
            catch (Exception ex)
            {
                //ToDo: log error message
                errMsg = "Error: (btnCreateUser): " + ex.Message;
                LogMsg(errMsg);
                lblMsg.Text = errMsg;

                //uncommenting the following line may be helpful for debugging purposes
                //throw ex;
            }
        }

        private int AddUser(string userId, string firstName, string lastName, string emailAddress, string password)
        {
            int rowCount = 0;

            string sqlText = "insert into [User] (UserId, FirstName, LastName, EmailAddress, Password) values (@userId, @firstName, @lastName, @emailAddress, @password)";

            using (SqlConnection cn = new SqlConnection(connectionStr))
            {
                //open
                cn.Open();

                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    //if a value is null, it's necessary to use DBNull.Value

                    //userId
                    if (!String.IsNullOrEmpty(userId))
                    {
                        cmd.Parameters.Add("@userId", SqlDbType.NVarChar).Value = userId;
                    }
                    else
                    {
                        cmd.Parameters.Add("@userId", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //firstName
                    if (!String.IsNullOrEmpty(firstName))
                    {
                        cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = firstName;
                    }
                    else
                    {
                        cmd.Parameters.Add("@firstName", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //lastName
                    if (!String.IsNullOrEmpty(lastName))
                    {
                        cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = lastName;
                    }
                    else
                    {
                        cmd.Parameters.Add("@lastName", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //emailAddress
                    if (!String.IsNullOrEmpty(emailAddress))
                    {
                        cmd.Parameters.Add("@emailAddress", SqlDbType.NVarChar).Value = emailAddress;
                    }
                    else
                    {
                        cmd.Parameters.Add("@emailAddress", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //password
                    if (!String.IsNullOrEmpty(password))
                    {
                        cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = password;
                    }
                    else
                    {
                        cmd.Parameters.Add("@password", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //execute; returns the number of rows affected
                    rowCount = cmd.ExecuteNonQuery();
                }
            }

            return rowCount;

        }

        private int CheckUser(string emailAddress)
        {
            int rowCount = 0;

            string sqlText = "select count(*) from [User] where emailAddress= @emailAddress";

            using (SqlConnection cn = new SqlConnection(connectionStr))
            {
                //open
                cn.Open();

                using (SqlCommand cmd = new SqlCommand(sqlText, cn))
                {
                    //if a value is null, it's necessary to use DBNull.Value
                    if (!String.IsNullOrEmpty(emailAddress))
                    {
                        cmd.Parameters.Add("@emailAddress", SqlDbType.NVarChar).Value = emailAddress;
                    }
                    else
                    {
                        cmd.Parameters.Add("@emailAddress", SqlDbType.NVarChar).Value = DBNull.Value;
                    }

                    //execute and try to convert
                    Int32.TryParse(cmd.ExecuteScalar().ToString(), out rowCount);
                }
            }

            return rowCount;
        }

        public int ExecuteNonQuery(string sqlText)
        {
            int rowCount = 0;
            using (SqlConnection con = new SqlConnection(connectionStr))
            {
                //open
                con.Open();

                using (SqlCommand cmd = new SqlCommand(sqlText, con))
                {
                    //execute; return num rows affected
                    rowCount = cmd.ExecuteNonQuery();
                }
            }

            return rowCount;
        }

        private void LogMsg(string msg)
        {
            //ToDo: replace the code below with desired code
            //this code is for debugging and it doesn't trim the log
            string formattedMsg = String.Format("{0} {1} {2}", DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"), msg.Replace(System.Environment.NewLine, " "), System.Environment.NewLine);
            File.AppendAllText(logFilename, formattedMsg);
        }
    }
}

Here's the code to create the database table:

CREATE TABLE [dbo].[User]([UserId] [nvarchar](50) Not NULL, 
                          [FirstName] [nvarchar](75) NULL, 
                          [LastName] [nvarchar](75) NULL, 
                          [EmailAddress] [nvarchar](75) NULL, 
                          [Password] [nvarchar](75) NULL,  
                          CONSTRAINT [PK_User_UserId] PRIMARY KEY(UserId));

Resources:

Add IIS 7 AppPool Identities as SQL Server Logons

Error when trying to connect web form to SQL database to insert values

2 Answers2

Linked