Why I'm getting Incorrect syntax near ')' error?

Question

I'm trying to create a registration page using C# on Visual Basic 2012. When I debug I get 0 errors, but when I try to register an account I get the following error.

"Incorrect syntax near ')'"

If I try to create an account with an existing username it says that username already exist. So I'm able to connect to the SQL server, but I'm not sure where I went wrong.

This registration page should create accounts in my DB DNMembership> Table> Accounts

Here is my code I'm working with.

{
    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegDNMembershipConnectionString"].ConnectionString);
    con.Open();
    string insCmd = "Insert into Accounts (AccountName, Passphrase, EmailAddress, FullName, Country)";
    SqlCommand insertUser = new SqlCommand(insCmd, con);
    insertUser.Parameters.AddWithValue("@AccountName", TextBoxUN.Text);
    insertUser.Parameters.AddWithValue("@Passphrase", TextBoxPass.Text);
    insertUser.Parameters.AddWithValue("@EmailAddress", TextBoxEA.Text);
    insertUser.Parameters.AddWithValue("@FullName", TextBoxFN.Text);
    insertUser.Parameters.AddWithValue("@Country", DropDownListCountry.SelectedItem.ToString());

    try
    {
        insertUser.ExecuteNonQuery();
        con.Close();
        Response.Redirect("Login.aspx");
    }
    catch(Exception er)
    {
        Response.Write("<b>Something Really Bad Happened... Please Try Again.< /br></b>");
        Response.Write(er.Message);
}

What did I do wrong?

You need to master SQL. It is only executed on runtime. You won't get any compiler error for SQL syntax in a string. — Francis, Jan 22 '14 at 14:38

Soner Gönül · Accepted Answer · 2014-01-22T15:07:26.403

Looks like you forget to add VALUES part in your INSERT command.

VALUES

Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or in the table. The value list must be enclosed in parentheses.

Change your sql query like;

string insCmd = @"Insert into Accounts (AccountName, Passphrase, EmailAddress, FullName, Country)
                 VALUES(@AccountName, @Passphrase, @EmailAddress, @FullName, @Country)";

And use using statement to dispose your SqlConnection and SqlCommand like;

using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["RegDNMembershipConnectionString"].ConnectionString))
{    
   using(SqlCommand insertUser = new...)
   {
     //Your code..
   }
}

score 3 · Answer 2 · answered Jan 22 '14 at 14:35

You haven't specified any parameters in your SQL, or a VALUES section - you're saying "I want to insert into these fields..." but not what you want to insert. It should be something like:

string insCmd =
   "Insert into Accounts (AccountName, Passphrase, EmailAddress, FullName, Country) "
 + "Values (@AccountName, @Passphrase, @EmailAddress, @FullName, @Country");

score 1 · Answer 3 · answered Jan 22 '14 at 14:35

1

You need to change the SQL statement:

string insCmd = "Insert into Accounts (AccountName, Passphrase, EmailAddress, FullName, Country) VALUES (@AccountName,@Passphrase,@EmailAddress,@FullName,@Country)";

answered Jan 22 '14 at 14:35

ekad

14,436
26
44
46

score 1 · Answer 4 · answered Jan 22 '14 at 14:36

1

You are missing part of Insert statement

INSERT INTO table (col1, col2) VALUES (@col1, @col2)

Or if you want to insert all values into columns in order they are in table

INSERT INTO table VALUES (@col1, @col2)

answered Jan 22 '14 at 14:36

cichy

10,464
4
26
36

score 0 · Answer 5 · edited May 23 '17 at 12:06

There is several alternatives for INSERT command in SQL Server.

Specify COLUMNS and after that specify VALUES
- SQL Syntax - INSERT INTO TABLE(AccountName, Passphrase, EmailAddress, FullName, Country) VALUES ('AccountName', 'Passphrase', 'EmailAddress', 'FullName', 'Country')
- C# string insCmd = "INSERT INTO TABLE(AccountName, Passphrase, EmailAddress, FullName, Country) VALUES (@AccountName, @Passphrase, @EmailAddress, @FullName, @Country)"
If you are sure about the order of columns you can skip specifying columns, this can be risky in case you screw up order of VALUES you will insert values into wrong columns
- SQL Sytanx - INSERT INTO TABLE VALUES ('AccountName', 'Passphrase', 'EmailAddress', 'FullName', 'Country')
- C# string insCmd = "INSERT INTO TABLE VALUES (@AccountName, @Passphrase, @EmailAddress, @FullName, @Country)"

Good resources to read would be

W3School - http://www.w3schools.com/sql/sql_insert.asp
Technet - http://technet.microsoft.com/en-us/library/dd776381(v=sql.105).aspx

Alternative to INSERT INTO TABLE you can call stored procedures from C# that inserts into table. Use of stored procedures can help you reduce ad-hoc queries, help prevent SQL injection, reduce network traffic, add additional validation server side. Your code will look as follows.

SqlCommand cmd  = new SqlCommand("usp_InsertIntoAccount", con);
con.Open();

cmd.CommandType = CommandType.StoredProcedure;  

cmd.Parameters.Add(new SqlParameter("@AccountName", TextBoxUN.Text));
cmd.Parameters.Add(new SqlParameter("@Passphrase", TextBoxPass.Text));
cmd.Parameters.Add(new SqlParameter("@EmailAddress", TextBoxEA.Text));
cmd.Parameters.Add(new SqlParameter("@FullName", TextBoxFN.Text));
cmd.Parameters.Add(new SqlParameter("@Country", DropDownListCountry.SelectedItem.ToString()));

try
{
    cmd.ExecuteNonQuery();
    con.Close();
    Response.Redirect("Login.aspx");
}
catch(Exception er)
{
    Response.Write("<b>Something Really Bad Happened... Please Try Again.< /br></b>");
    Response.Write(er.Message);
}

Additional resources are listed on answer at the following questions How to execute a stored procedure within C# program

Why I'm getting Incorrect syntax near ')' error?

5 Answers5