错误数据转换失败。 [OLE DB状态值（如果已知）= 2] [英] Error data conversion failed. [ OLE DB status value (if known) = 2 ]
问题描述
我正在开发一个Windows窗体应用程序并使用sqlce数据库。当我在c＃中以实际方式创建表时，它是成功的，但是当在其上插入数据时，它会返回那些错误。
当我重新运行调试时执行那些将数据插入我的数据库的代码，它现在是成功的。
我不能创建表，然后在其上插入数据？请帮帮我...
这是我的代码的一部分：
OnButtonClick（）
{
CreateTable（名字）;
AddRecord（名字）;
}
oid createTable（string tableName）
string cmdText =
CREATE TABLE+ tableName +（+
ID int IDENTITY NOT NULL，+
SUB1 int NULL，+
SUB2 int NULL，+
SUB3 int NULL，+
SUB4 int NULL，+
SUB5 int NULL，+
SUB6 int NULL， +
SUB7 int NULL，+
SUB8 int NULL+
）;
int result = ExecuteCommand（cmdText）;
}
void AddRecord（string tableName）
{
string cmdText =INSERT INTO+ tableName + （SUB1，SUB2，SUB3，SUB4，SUB5，SUB6，SUB7，SUB8）+
VALUES（'+
load [0] +'，' +
load [1] +'，'+
load [2] +'，'+
load [ 3] +'，'+
load [4] +'，'+
load [5] +'，'+
load [6] +'，'+
load [7] +'）;
int result = ExecuteCommand（cmdText）;
}
int ExecuteCommand（字符串命令）
{
int result = 0;
使用（con = new SqlCeConnection（conStr））
{
尝试
{
con.Open（）;
cmd = con.CreateCommand（）;
cmd.CommandText = command;
result = cmd.ExecuteNonQuery（ ）;
if（result == -1）
result = 1;
}
catch（异常） ex）
{
Console.WriteLine（ex.Message）;
}
终于
{
con.Close（）;
}
返回结果;
}
}
我尝试过：
我试图重新运行调试并且数据插入成功。但是当我尝试执行它们同一个调试实例时，只创建了表成功。
I am developing a windows form application and uses sqlce database. When i create a table pragmatically in c#, it is a success, but when insert data on it, it return those error.
When i re-run the debug and execute those code that insert data to my database, it is now a success.
Can't i create table and after that insert data on it?? please help me...
This is some part of my code:
OnButtonClick()
{
CreateTable(name);
AddRecord(name);
}
oid createTable(string tableName)
string cmdText =
"CREATE TABLE " + tableName + " (" +
" ID int IDENTITY NOT NULL," +
" SUB1 int NULL," +
" SUB2 int NULL," +
" SUB3 int NULL," +
" SUB4 int NULL," +
" SUB5 int NULL," +
" SUB6 int NULL," +
" SUB7 int NULL," +
" SUB8 int NULL" +
")";
int result = ExecuteCommand(cmdText);
}
void AddRecord(string tableName)
{
string cmdText = "INSERT INTO " + tableName + "(SUB1,SUB2,SUB3,SUB4,SUB5,SUB6,SUB7,SUB8)" +
" VALUES('" +
load[0] + "','" +
load[1] + "','" +
load[2] + "','" +
load[3] + "','" +
load[4] + "','" +
load[5] + "','" +
load[6] + "','" +
load[7] + "')";
int result = ExecuteCommand(cmdText);
}
int ExecuteCommand(string command)
{
int result = 0;
using (con = new SqlCeConnection(conStr))
{
try
{
con.Open();
cmd = con.CreateCommand();
cmd.CommandText = command;
result = cmd.ExecuteNonQuery();
if (result == -1)
result = 1;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
con.Close();
}
return result;
}
}
What I have tried:
I tried to re-run the debug and insertion of data was successful. But when i try to execute them both a the same instance of debug, only creation of table was successful.
推荐答案
不要使用字符串连接来构建SQL -statements。它有几个缺点：
- 偶然或故意注入SQL的风险，导致数据库被破坏。 （谷歌SQL注入）
- 数据类型转换问题。
- 字符串中的引号问题。
- 如果需要可读性差很多参数。
- 对查询执行性能的负面影响。
您应该使用SQL参数：SqlCeParameter Class（System.Data.SqlServerCe） [< a href =https://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceparameter%28v=vs.100%29.aspxtarget =_ blanktitle =New Window> ^ ]
如何使用它的示例： http://www.dotnetperls.com/sqlparameter [ ^ ]（您只需要将SqlCeParameter替换为SqlParameter。 ）
由于您的列类型为int，您不需要配额n在VALUES子句中为它们标记，也许这与你得到的错误有关，但是，老实说，我不确定为什么它会在调试模式下工作。但这只是为了获取信息，因为无论如何，使用SQL参数都无关紧要。我建议您修改代码以使用SQL参数，如果问题仍然存在，请发表评论，我会尝试进一步提供帮助。
Don't use string-concatenation to build your SQL-statements. It has several disadvantages:
- Risk of SQL-injection, by chance or on purpose, leading to destruction of your database. (Google SQL-injection)
- Data type conversion problems.
- Problems with quotation marks in strings.
- Bad readability if you need a lot of parameters.
- Negative impact on query execution performance.
You should use SQL-parameters instead: SqlCeParameter Class (System.Data.SqlServerCe)[^]
Example on how to use it: http://www.dotnetperls.com/sqlparameter[^] (You just need to substitute SqlCeParameter for SqlParameter.)
As your columns are of typeintyou wouldn't need quotation marks for them in the VALUES-clause and maybe that has something to do with the error you're getting, though, to be honest, I'm not sure why it would work then in debug mode. But that's just for info because with SQL-parameters it's irrelevant anyway. I'd suggest you modify your code to use SQL-parameters and if the problem should persist, leave a comment and I'll try to help further.
这篇关于错误数据转换失败。 [OLE DB状态值（如果已知）= 2]的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
