非常感谢您对我提出问题的答复,非常非常感谢
我先前问的问题如果是新打开的数据库,我相信没有问题,但如果DATAGRID1的数据是刚刚从EXCEL文件导入的,那那段代码该如何修改。导入EXCEL(这是您的功劳)
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
Dim oRS As New ADODB.Recordset
oConn.CursorLocation = adUseClient
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;""" oRS.Open "Select * from [Sheet1$]", oConn, 3, 2 Set DataGrid1.DataSource = oRSDATAGRID1导入后台的SQL代码,非常非常感谢您
这段代码如何根据上段代码修改?
Dim oRS1 As New ADODB.RecordsetPrivate Sub Form1_Load()
Dim oCN1 As New ADODB.Connection
oCN1.CursorLocation = adUseClient
'连接显示数据的数据库
oCN1.Open "......"
oRS1.Open "某个要显示的表", oCN1, adOpenDynamic, adLockOptimistic, adCmdTable
Set DataGrid1.DataSource = oRS1 '显示数据到DataGrid1
End SubPrivate Sub Command11_Click()
Dim cn2 As New ADODB.Connection
Dim rsTemp As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
cn2.Open "Provider=SQLOLEDB.1;User ID=sa;pwd=y6;Initial Catalog=Hr_Osp;Data Source=SqlSerP"
'Record1是后台数据库的表
rs2.Open "Record1", cn2, adOpenDynamic, adLockBatchOptimistic, adCmdTable '循环插入数据
oRS1.MoveFirst
While Not oRS1.EOF
rsTemp.Open "select top 1 * from Record1 where Rtrim(AA)+Rtrim(BB)='" + RTrim(oRS1("AA")) + oRS1("BB") + "'", cn, adOpenDynamic, adLockReadOnly
'判断记录没有重复则插入表中
If rsTemp.EOF Then
rs2.AddNew
For i = 0 To rs2.Fields.Count - 1
rs2(i) = rs(i)
Next
End If
rs.MoveNext
Wend
rs2.UpdateBatch
End Sub
我先前问的问题如果是新打开的数据库,我相信没有问题,但如果DATAGRID1的数据是刚刚从EXCEL文件导入的,那那段代码该如何修改。导入EXCEL(这是您的功劳)
Dim oConn As Object
Set oConn = CreateObject("ADODB.Connection")
Dim oRS As New ADODB.Recordset
oConn.CursorLocation = adUseClient
oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;""" oRS.Open "Select * from [Sheet1$]", oConn, 3, 2 Set DataGrid1.DataSource = oRSDATAGRID1导入后台的SQL代码,非常非常感谢您
这段代码如何根据上段代码修改?
Dim oRS1 As New ADODB.RecordsetPrivate Sub Form1_Load()
Dim oCN1 As New ADODB.Connection
oCN1.CursorLocation = adUseClient
'连接显示数据的数据库
oCN1.Open "......"
oRS1.Open "某个要显示的表", oCN1, adOpenDynamic, adLockOptimistic, adCmdTable
Set DataGrid1.DataSource = oRS1 '显示数据到DataGrid1
End SubPrivate Sub Command11_Click()
Dim cn2 As New ADODB.Connection
Dim rsTemp As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset
cn2.Open "Provider=SQLOLEDB.1;User ID=sa;pwd=y6;Initial Catalog=Hr_Osp;Data Source=SqlSerP"
'Record1是后台数据库的表
rs2.Open "Record1", cn2, adOpenDynamic, adLockBatchOptimistic, adCmdTable '循环插入数据
oRS1.MoveFirst
While Not oRS1.EOF
rsTemp.Open "select top 1 * from Record1 where Rtrim(AA)+Rtrim(BB)='" + RTrim(oRS1("AA")) + oRS1("BB") + "'", cn, adOpenDynamic, adLockReadOnly
'判断记录没有重复则插入表中
If rsTemp.EOF Then
rs2.AddNew
For i = 0 To rs2.Fields.Count - 1
rs2(i) = rs(i)
Next
End If
rs.MoveNext
Wend
rs2.UpdateBatch
End Sub
Private Sub Form1_Load()
Dim oCN1 As New ADODB.Connection
oCN1.CursorLocation = adUseClient
'连接显示数据的数据库
oCN1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Book1.xls;" & _
"Extended Properties=""Excel 8.0;"""
oRS1.Open "某个要显示的表", oCN1, adOpenDynamic, adLockOptimistic, adCmdTable
Set DataGrid1.DataSource = oRS1 '显示数据到DataGrid1
End SubPrivate Sub Command11_Click()
Dim cn2 As New ADODB.Connection
Dim rsTemp As New ADODB.Recordset
Dim rs2 As New ADODB.Recordset '打开目标数据库的连接,准备导入数据
cn2.Open "Provider=SQLOLEDB.1;User ID=sa;pwd=y6;Initial Catalog=Hr_Osp;Data Source=SqlSerP" '循环插入数据
oRS1.MoveFirst
While Not oRS1.EOF
rsTemp.Open "select top 1 * from Record1 where Rtrim(AA)+Rtrim(BB)='" + RTrim(oRS1("AA")) + oRS1("BB") + "'", cn, adOpenDynamic, adLockReadOnly
'判断记录没有重复则插入表中
If rsTemp.EOF Then
cn.Execute("insert into Record1(AA,BB,CC) values('" & oRS1("AA") & "','" & oRS1("BB") & "','" & oRS1("CC") & "')")
rs.MoveNext
Wend
End Sub