System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

Question

I am trying to make a simple web that gets your your username,email, etc... and stores it in a sql database (usersDB.mdf) the table name is(users)
But I am getting a error saying

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

here is my table:

CREATE TABLE [dbo].[users] (
    [uname]  NCHAR (50)  NOT NULL,
    [pass]   NCHAR (50)  NOT NULL,
    [fName]  NCHAR (50)  NOT NULL,
    [lName]  NCHAR (50)  NOT NULL,
    [YBorn]  INT         NOT NULL,
    [phone]  INT         NULL,
    [prefix] NCHAR (10)  NOT NULL,
    [Email]  NCHAR (100) NOT NULL,
    [Gender] NCHAR (50)  NOT NULL,
    [Admin]  NCHAR (3)   NULL,
    PRIMARY KEY CLUSTERED ([uname] ASC)
);

here is my client side code:

<%@ Page Title="" Language="C#" MasterPageFile="~/Site1.Master" AutoEventWireup="true" CodeBehind="registration.aspx.cs" Inherits="web.registration" %>


    


   
    
                Register
        

        

        
        

        
            
            userName
            
        

        

        

        
            
            Password
             
        
         
            
            verification password
        
        

        

        
            
            First Name
             <%----%>
        
        

        

        
            
            Last Name
             <%----%>
        
        

        

        
            
            Email
             
        
        

        

        Year of Born: 
        
         
        

        

        
        
        
            Gender:

            
                

                Male
                

            
            
                Female

            
            
                Nonbinary

            
            
                Transgender

            
            
                Other

            

            
        
        

        

        prefix: 
        

        phone: 
        
         
        

        

        
        

        
        
        

        

        
        already have an account? login
        
        

        

    <%=sqlMsg %>
    <%=msg %>

Here is my server side 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;

namespace web
{
    public partial class registration : System.Web.UI.Page
    {
        public string st = "";
        public string msg = "";
        public string sqlMsg = "";
        public string formMsg = "";
        public string sqlSelect;
        protected void Page_Load(object sender, EventArgs e)
        {

            string fileName = "usersDB.mdf";
            string tableName = "users"; 

            if (Request.Form["submit"] != null)
            {
                string uname = Request.Form["uname"];
                string pass = Request.Form["pass"];
                string fName = Request.Form["fName"];
                string lName = Request.Form["lName"];
                string YBorn = Request.Form["YBorn"];
                if (YBorn is null)
                { YBorn = "0"; }
                string phone = Request.Form["phone"];
                if (phone is null)
                { phone = "0"; }
                //int pn = int.Parse(phone);
                string prefix = Request.Form["prefix"];
                string Email = Request.Form["Email"];
                string Gender = Request.Form["Gender"];
                string Admin = Request.Form["Admin"];
                if (Admin is null)
                    Admin = "NO";
                sqlSelect = $"SELECT * FROM {tableName} WHERE uname = '{uname}'";

                if (Helper.IsExist(fileName, sqlSelect))
                {
                    msg = "User name is already taken";
                    sqlMsg = sqlSelect; 
                }
                else
                {
                    string sqlInsert =$"INSERT INTO {tableName}";
                    sqlInsert += $"VALUES ('{uname}','{pass}','{fName}','";
                    sqlInsert +=$"{lName}','{YBorn}','";
                    sqlInsert +=$"{phone}','{prefix}','";
                    sqlInsert +=$"{Email}','{Gender}','{Admin}')";
                    sqlMsg = sqlInsert;
                    Helper.DoQuery(fileName, sqlInsert);
                    msg = "!account created successfully!";
                    Session["uname"] = uname;

                }
            }
            

        }
        
    }
}

Answer

Bad things usually happen when creating an SQL statement with string concatenation without parameters.

The following is a basic example

Placing data operations in its own class is recommended
Using Parameters via .Add over .AddWithValue is best
I excluded error handling for clarity, the Open method and ExecuteScalar should be in a try/catch
Once ExecuteScalar has executed (in this case for SQL-Server) we assign the primary key to the entity customer
Note the CommandText is using C# 11 raw string literals, if using an earlier version of C# use @""

internal class DataOperations
{
    public static void AddCustomer(Customer customer)
    {

        using var cn = new SqlConnection("TODO");

        // insert new record, get new primary key
        using var cmd = new SqlCommand
        {
            Connection = cn,
            CommandText =
                """
                    INSERT INTO dbo.Customer (CompanyName, ContactName, ContactTypeIdentifier, GenderIdentifier) 
                    VALUES (@CompanyName,  @ContactName,  @ContactTypeIdentifier,  @GenderIdentifier);
                    SELECT CAST(scope_identity() AS int);
                    """
        };


        cmd.Parameters.Add("@CompanyName", SqlDbType.NChar).Value =
            customer.CompanyName;

        cmd.Parameters.Add("@ContactName", SqlDbType.NChar).Value =
            customer.ContactName;

        cmd.Parameters.Add("@ContactTypeIdentifier", SqlDbType.Int).Value =
            customer.ContactTypeIdentifier;

        cmd.Parameters.Add("@GenderIdentifier", SqlDbType.Int).Value =
            customer.GenderIdentifier;

        cn.Open();

        customer.Identifier = Convert.ToInt32(cmd.ExecuteScalar());
    }
}

// belongs in its own file
internal class Customer
{
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public int ContactTypeIdentifier { get; set; }
    public int GenderIdentifier { get; set; }
    public int Identifier { get; set; }
}

System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'.'

1 answer