我用一个ODBC连接上一个FOXPRO数据库,由于查询后的记录比较多,我原来是用一条一条插入的方法插入到另一个数据库.现在想一次性将其记录导入到一个ACCESS数据库,不知有没有办法.

代码如下
PubConnstr = "provider=MSDASQL.1;user id=admin;password=;driver=microsoft visual foxpro driver;sourcedb="+Pathstr+";sourcetype=dbf;Persist Security Info=False";//用ODBC连接一个FOXPRO数据库.
OdbcConn.ConnectionString = PubConnstr;
OdbcConn.Open();

sqlstr="SELECT * FROM PIAOLST";
OdbcComm.CommandText =sqlstr;
OdbcComm.ExecuteNonQuery();
OdbcData.Fill(TmpDsc,"piaolst");//这里得到并填充数据

for(k = 0; k < TmpDsc.Tables[0].Rows.Count ; k++)//循环插入本地库CURRDAYBUSINFO(ACCESS类型的)
{
sqlstr="INSERT INTO CurrDayBusinfo(num,times,name,linenum,counter1,limt,km,bc,qnum,name1,dayxy,piaodj,chknum,days,recnum,"
+"types,memos,model,fache,facname,facnum,fjsxf,cw,isprntime,fsit,hc,resone,hl)"//zdt,fhj
+"values("+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[0].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[1].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[2].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[3].ToString().Trim()+"'"+","
+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[4].ToString())+","+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[5].ToString())+","+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[6].ToString())+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[7].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[8].ToString().Trim()+"'"+","
+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[9].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[10].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[11].ToString().Trim()+"'"+","+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[12].ToString())+","+"#"+TmpDsc.Tables[0].Rows[k].ItemArray[13].ToString().Trim()+"#"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[14].ToString().Trim()+"'"+","
+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[15].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[16].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[17].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[18].ToString().Trim() +"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[19].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[20].ToString().Trim()+"'"+","
+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[21].ToString())+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[22].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[23].ToString().Trim() +"'"+","+int.Parse(TmpDsc.Tables[0].Rows[k].ItemArray[24].ToString())+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[25].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[26].ToString().Trim()+"'"+","+"'"+TmpDsc.Tables[0].Rows[k].ItemArray[27].ToString().Trim()+"'"+")";
OleDbComm.CommandText =sqlstr;
OleDbComm.ExecuteNonQuery();//第循环一次插入一条记录,这样太慢了.
txt_info.Text="本地数据库插入记录信息:"+k.ToString().Trim()+" 条";
txt_info.Refresh();
}

以上是一条一条插入的方法,太慢了.主要是两种类型的数据库,我现在不清楚的是用ODBC查询到的数据,怎么才能把它一次性插入到一个用OLECONNECTION连接上的ACCESS里,?????????????

不是我不想,要用程序实现这个功能.这是个前题,在这个前题下才能继续讨论下去呀.
这本来就是一个程序里的小部份功能.然道让程序运行到这里,再用手动将这个库转换一下,
然后再让程序运行????

TO jacklee,能否说一下.现在数据记录都在TMPDS(DATASET)里,如何将这些记录全部插入到一个本地ACCESS里,指点一下.谢.

还有一个方法,是否可用SQL语句一次性实现,如：INSERT INTO TABLE1 SELECT * FROM TABLE2；就是在对第一个表进行查询的时侯,就可以对要插入的表进行操作.但那是在同数据库类型的情况下,如同一ACCESS数据库,将表A查询后的记录导入表B,这样是有办法的.现在是两个不同类型的数据库呢.

高手相助.

[此贴子已经被作者于2006-11-15 1:23:36编辑过]

能说一下入口参数吗,我是这样理解的,
dataset DS就是我已经用ODBC查询后的数据集.
strtablename就是被插入的数据库中的某个表.
strConnection 就是被插入数据库的连接;

public DataSetUpdateByDataSet(DataSet ds,string strTblName,string strConnection)
{
SqlConnection conn = new SqlConnection(strConnection)); //这里可能要改为OLECONNECTION吧.因为是ACCESS数据库.

SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand myCommand = new SqlCommand("select * from "+strTblName),(SqlConnection) conn); //请问这里是什么意思呢.???? 这里好像是从STRCONNECTION连接里查询某个表.这样就不对.因为我要从DATASET往另一个OLECONNECTION连接里拷贝记录.
myAdapter.SelectCommand = myCommand;
SqlCommandBuilder myCommandBuilder = new SqlCommandBuilder(myAdapter);

[此贴子已经被作者于2006-11-14 11:49:04编辑过]

以下是一个定时器过程,我想每次将一个FOXPRO数据库查询到DATASET里,然后再更新一个CONNECTION连接上的ACCESS数据库,库中的表名为CURRDAYBUSINFO
private void Timer_Scan_Tick(object sender, System.EventArgs e)
{
string PubConnstr,Sqlstr;
Timer_Scan.Enabled = false ;//关闭定时器,以免没有执行完再次进入过程
Txt_Info.Text = "进入扫描过程";
Txt_Info.Refresh();
Timer_Scan.Enabled = false;
QueryCount = QueryCount + 1;
OdbcConnection OdbcConn = new OdbcConnection() ;
OdbcDataAdapter OdbcData = new OdbcDataAdapter();
OdbcCommand OdbcComm = new OdbcCommand();

DataSet TmpDs = new DataSet();
Pathstr = Application.StartupPath ;
PubConnstr = "provider=MSDASQL.1;user id=admin;password=;driver=microsoft visual foxpro driver;sourcedb="+Pathstr+";sourcetype=dbf;Persist Security Info=False";
OdbcConn.ConnectionString = PubConnstr;
OdbcConn.Open();
Txt_Info.Text = "打开数据库正常...";
Txt_Info.Refresh();

Sqlstr = "Select * From Piaolst where days > {^2006-10-10}";
OdbcComm.CommandText = Sqlstr;
OdbcComm.Connection = OdbcConn;
OdbcComm.ExecuteNonQuery();
Txt_Info.Text = "查询数据库正常...";
Txt_Info.Refresh();

OdbcData.SelectCommand = OdbcComm;
OdbcData.Fill(TmpDs,"Piaolst");
Txt_Count.Text = TmpDs.Tables[0].Rows.Count.ToString();
Timer_Scan.Enabled = true;
Txt_Info.Text = "查询次数:"+ QueryCount.ToString();
Txt_Info.Refresh();

//-------------------------------------------------------------------------------------------------
OleDbConnection OleConn = new OleDbConnection();
OleDbDataAdapter OleData = new OleDbDataAdapter();
OleDbCommand OleComm = new OleDbCommand();

OleConn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;persist security info=false ;data source="+Application.StartupPath+"\\CurrDayBusinfo.mdb";
OleConn.Open();
OleComm.CommandText = "Select * from CurrDayBusinfo";
OleComm.Connection = OleConn;
OleDbCommandBuilder OleBuilder =new OleDbCommandBuilder(OleData);
//----------------------------------------------------------------------------------------
try

{

lock(this) //处理并发情况(分布式情况)

{

OleData.Update(TmpDs,"CurrDayBusinfo");//想通过这里来将TMPDS的数据更新到currdaybusinfo

}

}
catch(Exception err)
{

OleConn.Close();
//throw new BusinessException(err);
}

//-------------------------------------------------------------------------------------------------
Timer_Scan.Enabled = true ;//再次打开定时器,让程序重新进入这个过程,
panel1.Refresh();
OdbcConn.Close();

}


以上程序无效,不能更新TMPDS里的数据到ACCESS数据库CURRDAYBUFINFO里的表CURRDAYBUSINFO

没有办法了吗.

哪位朋友帮我看看..........