False Alarm: SqlCommand, SqlParameter and single quotes

Question

Hello! I'm trying to fix single quote bug in the code:

std::string Index;

connection->Open();
String^ sTableName = gcnew String(TableName.c_str());
String^ insertstring = String::Format("INSERT INTO {0} (idx, rec, date) VALUES (@idx,    @rec, getdate())", sTableName);
SqlCommand^ command = gcnew SqlCommand(insertstring, connection);
String^ idx = gcnew String(Index.c_str());
command->Parameters->Add("@idx", SqlDbType::VarChar)->Value = idx;

The bug is that if idx="that's", the SQL fails saying that there is a syntax error. Obviously, the problem is in the quote. But some googling shows that using parameters is the way to work with quotes. And SqlParameter works well, if type is TEXT and not VARCHAR.

There are any solutions other than manually doubling number of quote symbols in the string?

Update: I tried to manually edit this field in SQL Management Studio and it didn't allow single quotes in VARCHAR field. It this normal in SQL?

Answer 1

If you are sure its a quotes prob then,

C# code:

idx = idx.Replace("'", "''");

would solve the problem.

Answer 2

To be honest I have never had a problem with SqlParameters. But try the following:

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlparametercollection.addwithvalue.aspx

command->Parameters->AddWithValue("@idx", idx);

This should work and encode correctly for you.

Answer 3

I suspect the problem is either a quote getting in your table name, or that idx sounds more like the name of a number type than a character type.

---

Based on your update, I suggest you check for extra constraints on the table in management studio.

Answer 4

Sorry.

The problem was in SQL trigger's code. After removing them, all worked fine.