CODEWITHSUNDEEP
c#sqlsql-server-2005
Tamas
Tamas

Reputation: 35

How can I insert a value into a table where the column contains special character?

I would like to insert a record into an MsSql data table. It is an easy thing, but the target table contains special naming columns like: <"mz,x",int> Unfortunately I cannot rename the column, because I do not own the database. My code fails with exception:

using (SqlConnection conn = GetSqlConnection())
{
            SqlCommand cmd = CreateNewCmd(conn);
            cmd.CommandText = "INSERT INTO test VALUES(@mz,x)";
            cmd.Parameters.Add("@mz,x", System.Data.SqlDbType.Int).Value = 1;
            

            cmd.ExecuteNonQuery();
}

The exception is:

Incorrect syntax near ','.

Must declare the scalar variable "@mz".

The problem is of course with the comma. How could I protect the command in my sql, or how can I insert a simple record into this table?

Thank you for your help!

Upvotes: 1

Views: 779

Answers (2)

user12031933
user12031933

Reputation:

I don't know the SQL syntax you try to use but you can write:

using (SqlConnection conn = GetSqlConnection())
{
  var cmd = CreateNewCmd(conn);
  cmd.CommandText = "INSERT INTO test VALUES (?,?,?)";
  cmd.Parameters.Add("@value1", System.Data.SqlDbType.Int).Value = 1;
  cmd.Parameters.Add("@value2", System.Data.SqlDbType.Int).Value = 1;
  cmd.Parameters.Add("@value3", System.Data.SqlDbType.Int).Value = 1;
  cmd.ExecuteNonQuery();
}

Supposing that the table test has 3 columns for the sample, we add a row with all the cells so we don't need to name the columns as you tried to do in the values predicate that should be the parameter name (here we use ? instead to avoid some incompatibilities).

Ideally the request should be:

INSERT INTO test (column1Name, column2Name, column3Name) VALUES (?,?,?)

You can name the parameters if the database support that and there is no problem between it and the ADO.NET provider:

INSERT INTO test (column1Name, column2Name, column3Name) VALUES (@value1, @value2, @value3)

If you got an exception, use ? like the .NET Framework does.

For your column special name, I don't know but you may try the @AnkitDas answer.

Upvotes: 1

Ankit Das
Ankit Das

Reputation: 650

You may want to wrap your column name in square brackets: [mz,x]

And i dont think you need to declare a variable as "@mz,x". Instead just simply declare it as mz for using it in the C# code.

using (SqlConnection conn = GetSqlConnection())
{
            SqlCommand cmd = CreateNewCmd(conn);
            cmd.CommandText = "INSERT INTO test([mz,x]) VALUES(@mz)";
            cmd.Parameters.Add("@mz", System.Data.SqlDbType.Int).Value = 1;
            

            cmd.ExecuteNonQuery();
}

Upvotes: 1

Related Questions