虚警：SqlCommand，SqlParameter和单引号

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.

问题是,如果idx =“that”,则SQL无法说出语法错误。显然,问题在于引用。但是一些谷歌搜索显示使用参数是使用引号的方式。如果type是TEXT而不是VARCHAR,那么SqlParameter运行良好。

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?

更新:我尝试在SQL Management Studio中手动编辑此字段,并且它不允许在VARCHAR字段中使用单引号。这在SQL中是正常的吗?

4 个解决方案

#1

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.

我怀疑问题是你的表名中的引号,或者idx听起来更像是数字类型的名称而不是字符类型。

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

根据您的更新,我建议您检查管理工作室中表格的额外限制。

#2

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

说实话,我从来没有遇到过SqlParameters的问题。但请尝试以下方法:

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.

这应该适合您并且正确编码。

#3

If you are sure its a quotes prob then,

如果你确定它是一个引号概率那么,

C# code:

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

would solve the problem.

会解决问题。

#4

Sorry.

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

问题出在SQL触发器的代码中。删除后,一切正常。

#1