异常信息: Exception:System.Data.Odbc.OdbcException: ERROR [22018] [DataDirect][ODBC Sybas e Wire Protocol driver][SQL Server]Implicit conversion from datatype 'CHAR' to ' DECIMAL' is not allowed. Use the CONVERT function to run this query.ERROR [22018] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Implicit conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f unction to run this query.
同样的代码在vs2005下执行update()出现下面异常:Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo rted against a SelectCommand that does not return any base table information.
to:liujia_0421(SnowLover) 还是不行: Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo rted against a SelectCommand that does not return any base table information.
adapter.update()时出现异常:System.InvalidOperationException: Parameter[5] '' has no default value. Parameter[6] '' has no default value. Parameter[7] '' has no default value. Parameter[13] '' has no default value. Parameter[15] '' has no default value.如何解决?
string select = "select count(*) as col_num from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; int col_num = 0; string sInsert = "Insert " + sTableName +" ("; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) { col_num = System.Convert.ToInt16(reader[col_num]); } select = "select name from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) {
sInsert += reader["name"].ToString() + ",";
} //处理掉最后的',' sInsert = sInsert.Substring(0, sInsert.Length - 1); sInsert += ") VALUES ( "; for (int i = 1; i <= col_num; i++) { sInsert += "?,"; } //处理掉最后的',' sInsert = sInsert.Substring(0, sInsert.Length - 1); //生成最后的insert z (a,b,c,d) values(?,?,?, ?) sInsert += ")"; //生成adapter ole_adapter.InsertCommand = new OleDbCommand(sInsert,Conn_S); select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')"; command = new OleDbCommand(select, Conn_S); reader = command.ExecuteReader(); while (reader.Read()) { string col_name = reader["name"].ToString().Trim(); string col_type = reader["typename"].ToString().Trim(); int length = System.Convert.ToInt32(reader["length"].ToString()); int status = System.Convert.ToInt32(reader["status"].ToString()); OleDbType dbType = OleDbType.Char;
switch (col_type) { case "char": dbType = OleDbType.Char; break; case "varchar": dbType = OleDbType.VarChar; break; case "decimal": dbType = OleDbType.Decimal; break; case "int": dbType = OleDbType.Integer; break; case "smallint": dbType = OleDbType.SmallInt; break; case "datetime": dbType = OleDbType.DBTimeStamp; break;
ds.Tables[0].AcceptChanges();
Console.WriteLine ("Ok");
ds_main.Tables[0].AcceptChanges();
adapter.Update(ds_main,"DT_JCGQZ_LS");
Conn1.Close();
不对吧?出现异常。
Exception:System.Data.Odbc.OdbcException: ERROR [22018] [DataDirect][ODBC Sybas
e Wire Protocol driver][SQL Server]Implicit conversion from datatype 'CHAR' to '
DECIMAL' is not allowed. Use the CONVERT function to run this query.ERROR [22018] [DataDirect][ODBC Sybase Wire Protocol driver][SQL Server]Implicit
conversion from datatype 'CHAR' to 'DECIMAL' is not allowed. Use the CONVERT f
unction to run this query.
OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);adapter.Fill(ds_main,"table1");
ds_main.ReadXml("c:\\1.xml",XmlReadMode.ReadSchema);
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
OdbcDataAdapter adapter = new OdbcDataAdapter("select * from table1",Conn1);
OdbcCommandBuilder builder = new OdbcCommandBuilder(adapter);adapter.Fill(ds_main,"table1");
ds_main.ReadXml("c:\\1.xml",XmlReadMode.ReadSchema);
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
DataTable datatable = ds_main.Tables[0];
ds_main.Tables[0].PrimaryKey = new DataColumn[] {ds_main.Tables[0].Columns[0]};
DataTable datatable = ds_main.Tables[0];
DataRow row = ds_main.Tables[0].Rows[0];
row["col1"] = "xp";
adapter.Update(ds_main,"table1");
ds_main.Tables[0].AcceptChanges();
Conn1.Close();
DataTable datatable = ds_main.Tables[0];
DataRow row = ds_main.Tables[0].Rows[0];
row["col1"] = "xp";
改为
ds_main.Tables["table1"].Rows[0][1]="XP";
试一下
rted against a SelectCommand that does not return any base table information.
Console.WriteLine("table:{0}", ds_main.Tables[0].ToString());
DataTable datatable = ds_main.Tables[0];
ds_main.Tables[0].PrimaryKey = new DataColumn[] {ds_main.Tables[0].Columns[0]};
DataTable datatable = ds_main.Tables[0];
DataRow row = ds_main.Tables[0].Rows[0];
row["col1"] = "xp";这一段改成:
ds_main.Tables["table1"].Rows[0]["col1"]="xp";试下看..
还是不行:
Exception:System.InvalidOperationException: Dynamic SQL generation is not suppo
rted against a SelectCommand that does not return any base table information.
1.只能更新一个表,不能更新两个或两个以上相关联的表
2.表中必须有主键
3.更新的表中字段不能有image类型的
Parameter[6] '' has no default value.
Parameter[7] '' has no default value.
Parameter[13] '' has no default value.
Parameter[15] '' has no default value.如何解决?
int col_num = 0;
string sInsert = "Insert " + sTableName +" (";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
col_num = System.Convert.ToInt16(reader[col_num]); }
select = "select name from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
sInsert += reader["name"].ToString() + ",";
}
//处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1);
sInsert += ") VALUES ( ";
for (int i = 1; i <= col_num; i++)
{
sInsert += "?,";
}
//处理掉最后的','
sInsert = sInsert.Substring(0, sInsert.Length - 1); //生成最后的insert z (a,b,c,d) values(?,?,?, ?)
sInsert += ")"; //生成adapter
ole_adapter.InsertCommand = new OleDbCommand(sInsert,Conn_S); select = "select name ,(select name from systypes where usertype = syscolumns.usertype) as typename ,length,status from syscolumns where id = (select id from sysobjects where name = '" + sTableName + "')";
command = new OleDbCommand(select, Conn_S);
reader = command.ExecuteReader();
while (reader.Read())
{
string col_name = reader["name"].ToString().Trim();
string col_type = reader["typename"].ToString().Trim();
int length = System.Convert.ToInt32(reader["length"].ToString());
int status = System.Convert.ToInt32(reader["status"].ToString());
OleDbType dbType = OleDbType.Char;
switch (col_type)
{
case "char":
dbType = OleDbType.Char;
break;
case "varchar":
dbType = OleDbType.VarChar;
break;
case "decimal":
dbType = OleDbType.Decimal;
break;
case "int":
dbType = OleDbType.Integer;
break;
case "smallint":
dbType = OleDbType.SmallInt;
break;
case "datetime":
dbType = OleDbType.DBTimeStamp;
break;
default:
dbType = OleDbType.Char;
break;
}
ole_adapter.InsertCommand.Parameters.Add("@" + col_name, dbType, length,col_name);
//处理可空的字段
if (status == 8)
{
ole_adapter.InsertCommand.Parameters["@" + col_name].IsNullable = true;
}