Getting an error "System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'."

Question

I really need help with this error, I am new to C# so it may or may not be an obvious one.

The error is

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

It appears at:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient; //allows a secure link between the button on add user and the database

namespace inventory_management_coding
{
    public partial class Add_New_User : Form
    {
        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=C:\Users\Sarwan\Desktop\computer science coding coursework\inventory management coding\Inventory.mdf;Integrated Security=True"); // this is a connection string so it sets the variable con with the database file that is exactly in that file location

        public Add_New_User()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            int i = 0;

            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "select * from Registration where username = '" + textBox3.Text + "'"; //this gets information from my database. Textbox 3 is the username textbox
            cmd.ExecuteNonQuery(); // This is used for executing queries that do not return any data. 

            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(cmd); // allows access to the database 
            da.Fill(dt);
            i = Convert.ToInt32(dt.Rows.Count.ToString());

            if (i == 0) //allows us to pass through sub query 
            {
                SqlCommand cmd1 = con.CreateCommand();
                cmd1.CommandType = CommandType.Text;
                cmd1.CommandText = "Insert into registeration ('"+ textBox1.Text  +"','"+ textBox2.Text  +"','"+  textBox3.Text  +"','"+  textBox4.Text  +"','"+  textBox5.Text  +"')"; //This allows data to be entere into the database. Text box 1,2,3,4,5 are linked to firstname, lastname etc
                cmd1.ExecuteNonQuery();

                textBox1.Text = ""; textBox2.Text = ""; 
                textBox3.Text = ""; textBox4.Text = ""; 
                textBox5.Text = ""; // these allow the parameters to be passed through

                MessageBox.Show("user record inserted successfully");
            }
            else
            {
                MessageBox.Show("This username already exists, please choose another"); // this would be an invalid statement for choosing a same username. They must be Unique!
            }
        }

        private void Add_New_User_Load(object sender, EventArgs e)
        {
            if (con.State == ConnectionState.Open) //this section of code is vital in all areas to allow the program to automatically connect to the database. Con is the linking variable. 
            {
                con.Close();
            }

            con.Open();
        }
    }
}

I really need help on this, it says that the syntax is ')' but I am unable to fond the correct solution. It occurs on this line:

cmd1.ExecuteNonQuery();

You should discard and ignore whatever tutorial or learning source is teaching you to compose SQL that way. It has never been the correct way to do so in .NET. The error you are getting is just one of the milder issues with it. — Ňɏssa Pøngjǣrdenlarp, Mar 24 '20 at 21:47

willman · Answer 1 · 2020-03-25T10:40:50.477

Your commandText for insert a row is missing some information.

You currently have

cmd1.CommandText = "Insert into registeration ('"+ textBox1.Text  +"','"+ textBox2.Text  +"','"+  textBox3.Text  +"','"+  textBox4.Text  +"','"+  textBox5.Text  +"')"; //This allows data to be inputted in the database. Text box 1,2,3,4,5 are linked to firstnale, lastname etc

but the syntax of the Insert into command is

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

You skipped the specifying of the names of the columns you are inserting values for. You haven't given us enough info to know what your column names are, but your line of code should look something like:

//This allows data to be inputted in the database. Text box 1,2,3,4,5 are linked to firstnale, lastname etc
cmd1.CommandText = "Insert into registeration (column1, column2, column3, column4, column5) VALUES ('"+ textBox1.Text  +"','"+ textBox2.Text  +"','"+  textBox3.Text  +"','"+  textBox4.Text  +"','"+  textBox5.Text  +"')";

where column1, column2, column3, column4, column5 are replaced with the actual names of your columns.

When you find a SQL exception occurring in your C# code, trying building your string in a separate variable, that you then pass to CommandText so you can step through the code and debug, and potentially cut & paste the entire concatenated string into a query window so you can run and debug the SQL separately from your app.

As a side note, you need to do some research into SQL Injection attacks, because by building your SQL commands directly from user input boxes, you are very much opening yourself up to this vulnerability.

Getting an error "System.Data.SqlClient.SqlException: 'Incorrect syntax near ')'."

1 Answers1