Inserting array into SQL Server database problems

Question

I'm attempting to insert an array into a SQL Server database. 1/4 is copied to the database before I receive the error:

Incorrect syntax near 's'. Unclosed quotation mark after the character string ', ')'.

Here is the button click event -

private void button3_Click(object sender, EventArgs e)
{
   SqlConnection sql = new SqlConnection("server=localhost; uid=xxxx; pwd=xxxx; database=Movies;");
   sql.Open();

   for (int i = 0; i < jointArray.Length; i++)
   {
      SqlCommand command = new SqlCommand("insert into [" + folderName + "] " + "values('" + jointArray[i].ToString() +"', '')", sql);
      command.ExecuteNonQuery();
   }

   sql.Close();
}

The problem is surely linked to the content of the array, can you please give us some information about it ? — krtek
– krtek, Commented Jun 15, 2011 at 11:52
How do you know that 1/4 is executed successfully? Also, as Krtek has mentioned, there would most probably be a function in SqlCommand class to dump the query to output. If you check that it might solve your problem OR paste it in your question. — Ozair Kafray
– Ozair Kafray, Commented Jun 15, 2011 at 11:53
130 movie names are getting copied to the database from which is approx 1/4 of the total from the selected directory — kurupt_89
– kurupt_89, Commented Jun 15, 2011 at 11:54
Have you ever heard of SQL injection attacks?? - don't concatenate together your SQL statements!! Use parametrized queries instead - ALWAYS — marc_s
– marc_s, Commented Jun 15, 2011 at 11:56
Why don't you use a parameterized query? this way you would get rid of the string manipulation that seems to cause problem. — Falanwe
– Falanwe, Commented Jun 15, 2011 at 11:58

CResults · Accepted Answer · 2011-06-15 11:54:19Z

4

I would hazard a guess that a 1/4 of the way through jointArray[i].ToString() contains an apostrophe.

So instead of a SQL query that looks like this..

insert into [MyFolder] values('MyValue')

you end up with something that looks like this

insert into [MyFolder] values('MyValue's')

which causes the error (notice where incorrect syntax near the s is!)

Please consider using parameters with your SQLCommand (look up Parameters.Add and Parameters.AddWithValue).

Using Parameters makes your code

More readable
Easier to debug
Less prone to SQL injection attacks (which recently got Sony Pictures hacked)

answered Jun 15, 2011 at 11:54

CResults

5,1051 gold badge24 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Guffa · Accepted Answer · 2011-06-15 12:53:02Z

The problem is that you don't encode the string properly to be a string literal in SQL. When there is an apostrophe in the string it will break the syntax.

You should use a parameter instead of concatenating the string into the SQL code.

This will also allow you to reuse the same query and command object for all queries, instead of creating a new command for each query. You can also call Prepare on the command before the loop to reduce some of the overhead for each execution.

Example:

private void button3_Click(object sender, EventArgs e) {

  using (SqlConnection sql = new SqlConnection("server=localhost; uid=xxxx; pwd=xxxx; database=Movies;")) {
    sql.Open();

    using (SqlCommand command = new SqlCommand("insert into [" + folderName + "] values(@P, '')", sql) {

      SqlParameter param = command.Parameters.Add("@P", SqlDbType.VarChar, 50);
      command.Prepare();

      for (int i = 0; i < jointArray.Length; i++) {
        param.Value = jointArray[i].ToString();
        command.ExecuteNonQuery();
      }

    }
  }
}

Jonathan Wood · Accepted Answer · 2011-06-15 11:54:50Z

2

Looks like you might have apostrophes in your data, which is messing up your query.

This is a really bad approach. Unless you can guarantee that your data won't have any unexpected characters, you should be using SQL parameters at the very least to ensure they are interpreted correctly.

answered Jun 15, 2011 at 11:54

Jonathan Wood

68.1k86 gold badges308 silver badges542 bronze badges

Comments

Coding Flow · Accepted Answer · 2011-06-15 11:56:56Z

2

It is very likely that one of your array elements contains an apostrophe.

answered Jun 15, 2011 at 11:56

Coding Flow

21.9k6 gold badges68 silver badges84 bronze badges

Comments

Talha Ahmed Khan · Accepted Answer · 2011-06-15 11:53:11Z

1

Checkout for the SQL escape characters like '

answered Jun 15, 2011 at 11:53

Talha Ahmed Khan

15.6k10 gold badges46 silver badges49 bronze badges

Comments

Jeff Sheldon · Accepted Answer · 2011-06-15 12:06:36Z

1

Somewhere in your values you have an unescaped single quote. Prob something like this should quick fix it.

SqlCommand command = new SqlCommand("insert into [" + folderName + "] " + "values('" + jointArray[i].ToString().Replace("'", "''") +"', '')", sql);

EDIT Oops, paste error! Pasted what you had.

edited Jun 15, 2011 at 12:06

answered Jun 15, 2011 at 11:53

Jeff Sheldon

2,1041 gold badge14 silver badges23 bronze badges

Collectives™ on Stack Overflow

Inserting array into SQL Server database problems

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related