Incorrect syntax near ','.

[mail.sujitkulkarni]

Hi,
I am experiencing irritating error (undiscriptive rather), when I want to insert a single statement into my db.
Following is the code:

protected void Button1_Click(object sender, EventArgs e)
    {
        SqlConnection dataConnection = new SqlConnection();
        dataConnection.ConnectionString =@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True";
        SqlCommand dataCommand = new SqlCommand();
        dataCommand.CommandText=("INSERT INTO citylist(city_id,city_name,latitude,longitude,state) VALUES ((1, 'Port Blair', '11.67 N', '92.76 E', 'Andaman and Nicobar Islands')");

        dataCommand.Connection = dataConnection;    
        dataConnection.Open();

            dataCommand.ExecuteNonQuery();
            dataConnection.Close();

   }

Following is the error:

Incorrect syntax near ','.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near ','.

Source Error:


Line 46:         dataConnection.Open();
Line 47:         
Line 48:             dataCommand.ExecuteNonQuery();
Line 49:             dataConnection.Close();
Line 50:         
Source File: c:\Users\sujit\Documents\Visual Studio 2008\WebSites\WebSite1\Default.aspx.cs    Line: 48 

The similar INSERT statement has been working in one of the other page, but this.
What could be the problem, please?

[urtrivedi]

VALUES ((1, 'Port

there is extra ( after values remove one from it

[mail.sujitkulkarni]

Thanks, it works.
Now, when I've to insert 500 such records, same error still persists.
Here's what I've written for 500 records..

 protected void Button1_Click(object sender, EventArgs e)
    {

        SqlConnection dataConnection = new SqlConnection();
        dataConnection.ConnectionString =@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True";
        SqlCommand dataCommand = new SqlCommand();

        //this is the sql command for 500 records to insert
        //For multiple records I have to put it like VALUES((...),(..)) So double brackets are necessary here. 

        dataCommand.CommandText=(@"INSERT citylist(city_id,city_name,latitude,longitude,state) VALUES ((1, 'Port Blair', '11.67 N', '92.76 E', 'Andaman and Nicobar Islands'), "+
        "(2, 'Adilabad', '19.68 N', '78.53 E', 'Andhra Pradesh'),"+
        "(3, 'Adoni', '15.63 N', '77.28 E', 'Andhra Pradesh'),"+
        "(4, 'Alwal', '17.50 N', '78.54 E', 'Andhra Pradesh'),"+
        ...
        ...
        ...
        "(500, 'Kannur', '11.86 N', '75.35 E', 'Kerala'))");

            dataCommand.Connection = dataConnection;
            dataConnection.Open();

            dataCommand.ExecuteNonQuery();
            dataConnection.Close();


    }

Still it says Incorrect Value Near ','.

[pritaeas]

Are you sure that your database allows to insert multiple records at once? You are using Access? From what I've found that syntax is for single records only.

[mail.sujitkulkarni]

Front end is Visual Studio 2008, and back end is default sql server that comes within the Visual Studio.
And yes, somehow you are getting it right. Even though I make it a 2 line statement rather than 500, still the error pops. What should I do, if the database doesn't allow multiple inserts at once?

[pritaeas]

You can always insert them one by one. There may be ways to do what you want, but I'm not too familiar with SqlExpress' features.

[mail.sujitkulkarni]

Haha gosh, inserting 1000 of such records would make me too unfamiliar with SQLExpress!
Anyway, thanks for the help.

[pritaeas]

Perhaps this is an option.

[adam_k]

or use UNION to insert multiple records at once:

dataCommand.CommandText=(@"INSERT citylist(city_id,city_name,latitude,longitude,state) select 1, 'Port Blair', '11.67 N', '92.76 E', 'Andaman and Nicobar Islands' UNION ALL "+
        "SELECT 2, 'Adilabad', '19.68 N', '78.53 E', 'Andhra Pradesh' UNION ALL "+
        "SELECT 3, 'Adoni', '15.63 N', '77.28 E', 'Andhra Pradesh' UNION ALL "+
        "SELECT 4, 'Alwal', '17.50 N', '78.54 E', 'Andhra Pradesh'"

[BitBlt]

For MS SQL Server, you don't have to include the parens around the entire set of values for it to work.

Your syntax should look like this:

insert into myTable
(col1, col2, col3, col4)
values
(1, 'a', 'v1', 23),
(2, 'b', 'v2', 24),
(3, 'c', 'v3', 25),
(4, 'd', 'v4', 26),
(5, 'e', 'v5', 27)

You just keep tacking on a comma and the next set of values.

Compare with your technique:

insert into myTable
(col1, col2, col3, col4)
values
(                     -- < this is extraneous!
(1, 'a', 'v1', 23),
(2, 'b', 'v2', 24),
(3, 'c', 'v3', 25),
(4, 'd', 'v4', 26),
(5, 'e', 'v5', 27)
)                      -- < this is also extraneous!

The parens surrounding the list of value sets are extraneous.

Hope that helps!

[mail.sujitkulkarni]

Adam K, worthy! Thanks a lot. That really works.