您所在位置：主题：复制数据表问题
xuyanfeng66
[专家分：0] 发布于 2007-01-21 17:14:00
怎么实现ACCESS数据库中的表的复制啊?其中表的结构都是一样的,字段值分别为Tme(integer),Height(double),Area(double),想复制一个新表中,有两个文本框分别输入原来表的名字和新表的名字,叫Yuanbiao和Xinbiao吧,字段Tme复制后*10取整,该怎么写代码呢,谢谢各位好心人.
Dim SourceTableName As String = Yuanbiao.Text
Dim MyTableName As String = Xinbiao.Text
'创建新表
Dim myconn As New System.Data.OleDb.OleDbConnection
Dim strcnn As String
strcnn = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn.ConnectionString = strcnn
myconn.Open()
Dim MySql As String = "CREATE TABLE " + MyTableName + " (MyMt integer,Area double,Height double) "
Dim mycommand As New OleDb.OleDbCommand
mycommand.CommandText = MySql
Dim myada As New System.Data.OleDb.OleDbDataAdapter(MySql, myconn)
Dim Ds As New DataSet
myada.Fill(Ds, "A")
myconn.Close()
'拷贝数据
Dim myconn1 As New System.Data.OleDb.OleDbConnection
Dim strcnn1 As String
strcnn1 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn1.ConnectionString = strcnn1
myconn1.Open()
Dim MySql1 As String = "select TABLE MyMt,Area,Height from " + SourceTableName + " "
Dim mycommand1 As New OleDb.OleDbCommand
mycommand1.CommandText = MySql1
Dim myada1 As New System.Data.OleDb.OleDbDataAdapter(MySql1, myconn1)
Dim Ds1 As New DataSet
myada1.Fill(Ds1, "AA")
Dim ID, i As Integer
ID = (Ds1.Tables(0).Rows.Count() - 1)
i = 0
Dim temp(ID, 2) As Double
For i = 0 To ID
temp(i, 0) = Int(Ds1.Tables.Item(0).Rows(i).Item(0) * 10 + 0.5)
temp(i, 1) = Ds1.Tables.Item(0).Rows(i).Item(1)
temp(i, 2) = Ds1.Tables.Item(0).Rows(i).Item(2)
Next i
myconn1.Close()
DataGrid1.DataSource = Ds1.Tables("B")
'存储数据
Dim myconn2 As New System.Data.OleDb.OleDbConnection
Dim strcnn2 As String
strcnn2 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn2.ConnectionString = strcnn2
If myconn2.State = ConnectionState.Closed Then
myconn2.Open()
End If
Dim MySql2 As String = "insert into " + MyTableName + "(MyMt,Area,Height) values(@temp(ID, 0),@temp(ID, 1),@temp(ID, 2))"
Dim mycommand2 As New OleDb.OleDbCommand
mycommand2.CommandText = MySql2
Dim myada2 As New System.Data.OleDb.OleDbDataAdapter(MySql2, myconn2)
Dim Ds2 As New DataSet
myada2.Fill(Ds2, "B")
'mycommand2.ExecuteNonQuery()
myconn2.Close()
End Sub
这是我做的程序,前边的两个变量是文本框的NAME,运行是总出错, 到myada1.Fill(Ds1, "AA")时,出现未处理的“System.Data.OleDb.OleDbException”类型的异常出现在 system.data.dll 中,而且数据库中没有数据,只有空表
Dim SourceTableName As String = Yuanbiao.Text
Dim MyTableName As String = Xinbiao.Text
'创建新表
Dim myconn As New System.Data.OleDb.OleDbConnection
Dim strcnn As String
strcnn = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn.ConnectionString = strcnn
myconn.Open()
Dim MySql As String = "CREATE TABLE " + MyTableName + " (MyMt integer,Area double,Height double) "
Dim mycommand As New OleDb.OleDbCommand
mycommand.CommandText = MySql
Dim myada As New System.Data.OleDb.OleDbDataAdapter(MySql, myconn)
Dim Ds As New DataSet
myada.Fill(Ds, "A")
myconn.Close()
'拷贝数据
Dim myconn1 As New System.Data.OleDb.OleDbConnection
Dim strcnn1 As String
strcnn1 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn1.ConnectionString = strcnn1
myconn1.Open()
Dim MySql1 As String = "select TABLE MyMt,Area,Height from " + SourceTableName + " "
Dim mycommand1 As New OleDb.OleDbCommand
mycommand1.CommandText = MySql1
Dim myada1 As New System.Data.OleDb.OleDbDataAdapter(MySql1, myconn1)
Dim Ds1 As New DataSet
myada1.Fill(Ds1, "AA")
Dim ID, i As Integer
ID = (Ds1.Tables(0).Rows.Count() - 1)
i = 0
Dim temp(ID, 2) As Double
For i = 0 To ID
temp(i, 0) = Int(Ds1.Tables.Item(0).Rows(i).Item(0) * 10 + 0.5)
temp(i, 1) = Ds1.Tables.Item(0).Rows(i).Item(1)
temp(i, 2) = Ds1.Tables.Item(0).Rows(i).Item(2)
Next i
myconn1.Close()
DataGrid1.DataSource = Ds1.Tables("B")
'存储数据
Dim myconn2 As New System.Data.OleDb.OleDbConnection
Dim strcnn2 As String
strcnn2 = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Data Source=""newdata.mdb"";Jet OLEDB:Engine Type=5;Provider=""Microsoft.J" & _
"et.OLEDB.4.0"";Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security in" & _
"fo=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=Fa" & _
"lse;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compac" & _
"t=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:G" & _
"lobal Bulk Transactions=1"
myconn2.ConnectionString = strcnn2
If myconn2.State = ConnectionState.Closed Then
myconn2.Open()
End If
Dim MySql2 As String = "insert into " + MyTableName + "(MyMt,Area,Height) values(@temp(ID, 0),@temp(ID, 1),@temp(ID, 2))"
Dim mycommand2 As New OleDb.OleDbCommand
mycommand2.CommandText = MySql2
Dim myada2 As New System.Data.OleDb.OleDbDataAdapter(MySql2, myconn2)
Dim Ds2 As New DataSet
myada2.Fill(Ds2, "B")
'mycommand2.ExecuteNonQuery()
myconn2.Close()
End Sub
这是我做的程序,前边的两个变量是文本框的NAME,运行是总出错, 到myada1.Fill(Ds1, "AA")时,出现未处理的“System.Data.OleDb.OleDbException”类型的异常出现在 system.data.dll 中,而且数据库中没有数据,只有空表