【IT168技术文档】
最近做一个C#程序,实现将SQLServer中的数据导入到Visual Foxpro6.0的.dbf数据文件中。更新Numeric类型字段的值时出现错误:
System.Data.Odbc.OdbcException:ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Data type mismatch.
原程序类似如下:
程序运行时,在对row["Num"]赋值时并不出错,执行到oodbcDA.Update(dataSet,"table1");时出错,根源就在于//------------------------------------------------------------------------ //到.dbf数据库文件的ODBC连接字符串 string strOdbcConn = @" DRIVER=Microsoft Visual FoxProDriver;UID=;Collate=Machine;BackgroundFetch=Yes; Exclusive=No;SourceType=DBF;SourceDB="+ strFilePath +";"; //获取DataTable string strSQL = "Select * From table1 ; DataSet dataSet = new DataSet(); OdbcDataAdapter odbcDA = new OdbcDataAdapter(strSQL,strOdbcConn); odbcDA.Fill(dataSet,"table1"); DataTable table = dataSet.Tables["table1"]; //向DataTable中添加记录 DataRow row = table.NewRow(); row["DateFrom"] = Convert.ToDateTime("2005-09-10");//日期型字段 row["Num"] = Convert.ToDecimal(10);//Numric(16,0)型字段 table.Rows.Add(row); //更新到数据库中 OdbcCommandBuilder builder = new OdbcCommandBuilder(odbcDA); odbcDA.InsertCommand = builder.GetInsertCommand(); odbcDA.Update(dataSet,"table1"); //----------------------------------------------------------------
对row["Num"]的赋值,实在找不到好的解决办法。
后来,用SQL语句测试,如:update table1 set Num=10;执行正确,就想用SQL语句insert解决,经测试可行。
SQL-Insert语句如下:
Insert Into table1(DateFrom, Num) Values({^2005-09-10},10)
程序相应的改成如下的了:
其它关于VFP的信息://------------------------------------------------------------------ string strOdbcConn = @" DRIVER=Microsoft Visual FoxProDriver;UID=;Collate=Machine;BackgroundFetch=Yes; Exclusive=No;SourceType=DBF;SourceDB="+ strFilePath +";"; OdbcConnection odbcConn = new OdbcConnection(strOdbcConn); string sqlInsert = "Insert Into table1(DateFrom, Num) Values({^2005-09-10},10)"; OdbcCommand odbcComm = new OdbcCommand(sqlInsert,odbcConn); odbcComm.Connection.Open(); odbcComm.ExecuteNonQuery(); odbcConn.Close(); //----------------------------------------------------------------
1.VFP-SQL语句
----插入日期值: insert into 1able1(日期字段) values({^2005-09-10}) ----不支持如下语句(insert-select): insert into table1 select * from table2