C#程式設計向VFP資料庫中插入Numeric型的值
最近做一個C#程式,實作将SQLServer中的資料導入到Visual Foxpro6.0的.dbf資料檔案中。更新Numeric類型字段的值時出現錯誤:
System.Data.Odbc.OdbcException:ERROR [22018] [Microsoft][ODBC Visual FoxPro Driver]Data type mismatch.
原程式類似如下:
//------------------------------------------------------------------------
//到.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"]指派時并不出錯,執行到oodbcDA.Update(dataSet,"table1");時出錯,根源就在于對row["Num"]的指派,實在找不到好的解決辦法。(誰有直接對row["Num"]的指派的好辦法,歡迎賜教)
後來,用SQL語句測試,如:update table1 set Num=10;執行正确,就想用SQL語句insert解決,經測試可行。
SQL-Insert語句如下:
Insert Into table1(DateFrom, Num) Values({^2005-09-10},10)
程式相應的改成如下的了:
//------------------------------------------------------------------
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();
========================================================
其它關于VFP的資訊:
1.VFP-SQL語句
----插入日期值:
insert into 1able1(日期字段) values({^2005-09-10})
----不支援如下語句(insert-select):
insert into table1
select * from table2
2.MS OLE DB Provider for VFP :
3.更多微軟Visual FoxPro資源:
<a href="http://www.microsoft.com/downloads/info.aspx?na=13&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2Fdownloads%2Fbrowse.aspx%3Fdisplaylang%3Den%26productID%3DE794F2FC-0425-40AD-A292-39490679FA65">http://www.microsoft.com/downloads/info.aspx?na=13&p=1&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=&u=%2fdownloads%2fbrowse.aspx%3fdisplaylang%3den%26productID%3dE794F2FC-0425-40AD-A292-39490679FA65</a>
4.記住這個連接配接字元串:
string strOdbcConn = @" DRIVER=Microsoft Visual FoxProDriver;UID=;Collate=Machine;BackgroundFetch=Yes; Exclusive=No;SourceType=DBF;SourceDB=D:\DataDBF\table1.dbf;";
===============================================================
QQ: 23106676