各位看贴的大哥可不可以帮我看看我这条sql语句这样写可不可以? update ke_school_student set cardno = tmpid where company_id='0011' and class_name ='高一(1)班'卡号cardno为表ke_school_student里面的一个字段,tmpid为一个数组,请问我应该如果才能把数组里面的数据按顺序替换cardno这个字段的所有记录如果这样不行,能不能教我这条sql语句应该怎么写?
rstOra.Open "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' " '查询出这个班级的所有卡号 If UBound(tmpid) = rstOra.RecordCount Then '此数组中己包含要替换的新卡号 Do While rstOra.EOF <> True rstOra.Open "update ke_school_student set cardno = '数组????' where company_id='0011' and class_name ='高一(1)班'" rstOra.MoveNext Loop End If请问我这个循环应该怎么写?才能把数组里面的新卡号逐个更新数据表中原来旧的卡号?请各位大哥帮帮忙忙,问题比较急
1.从EXCEL中读 2.向ORACLE中写
Private Sub Command2_Click() CommonDialog1.Filter = "Excel文件|*.xls|所有文件|*.*" CommonDialog1.FilterIndex = 1 CommonDialog1.Action = 1 thefilename = CommonDialog1.FileName If thefilename <> "" Then Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Set oExcel = New Excel.Application oExcel.Visible = True '设置EXCEL可见 'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿 Set oBook = oExcel.Workbooks.Open(thefilename) Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表 oSheet.Activate '激活工作表 For i = 0 To UBound(tmpid) - 1 oSheet.Cells(i + 2, 9) = tmpid(i) Next i oExcel.Visible = True oExcel.UserControl = True
Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Integer Dim str, estr, sql, sstr As String On Error Resume Next Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False" conn.CursorLocation = adUseClient conn.Open rs.CursorLocation = adUseClient rs.ActiveConnection = conn sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' " Set rs = conn.Execute(sql) MsgBox rs.RecordCount & " and " & UBound(tmpid) If UBound(tmpid) = rs.RecordCount Then Do While rs.EOF <> True estr = oSheet.Cells(r + 2, 2) sstr = oSheet.Cells(r + 2, 3) str = tmpid(r) Debug.Print estr & "cardno is: " & str; " sex is: " & sstr sql = "update ke_school_student set cardno = str where student_name = '" + estr + "' and sex = '" + sstr + "' " Set rs = conn.Execute(sql) rs.MoveNext r = r + 1 Loop End If rs.Close sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' " Set rs = conn.Execute(sql) Set DataGrid1.DataSource = rs DataGrid1.Refresh Else Exit Sub End If
End Sub 这样做为什么数据不会更新到表?
Private Sub cmdupdate_Click()
CommonDialog1.Filter = "Excel文件|*.xls|所有文件|*.*" CommonDialog1.FilterIndex = 1 CommonDialog1.Action = 1 thefilename = CommonDialog1.FileName If thefilename <> "" Then Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim x As Integer Set oExcel = New Excel.Application oExcel.Visible = False '设置EXCEL可见 'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿 Set oBook = oExcel.Workbooks.Open(thefilename) Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表 oSheet.Activate '激活工作表 x = oSheet.UsedRange.Rows.Count ' oExcel.Visible = True ' oExcel.UserControl = True
Dim conn As ADODB.Connection Dim rs As ADODB.Recordset Dim r As Integer Dim str, estr, sql, sstr As String On Error Resume Next Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False" conn.CursorLocation = adUseClient conn.Open rs.CursorLocation = adUseClient rs.ActiveConnection = conn sql = "select cardno,student_name, sex,birthday from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' " rs.Open sql, conn, 2, 3 Set DataGrid2.DataSource = rs DataGrid2.Refresh
Debug.Print rs.RecordCount If x - 1 = rs.RecordCount Then For i = 0 To rs.RecordCount - 1 listPrint ("学生姓名: " & oSheet.Cells(i + 2, 2) & " 学生卡号:" & oSheet.Cells(i + 2, 9) & " 处于第" & i + 1 & "个位置") tmp(i) = oSheet.Cells(i + 2, 9) tmp(UBound(tmp)) = tmp(i) ReDim Preserve tmp(UBound(tmp) + 1) Next Else MsgBox "数据表中的数据总数: " & rs.RecordCount & " 不等于excel表中的数据: " & x - 1 & " 请检查校对!!!" End If Debug.Print rs.RecordCount & UBound(tmp) If UBound(tmp) = rs.RecordCount Then '此数组中己包含要替换的新卡号 rs.MoveFirst For i = 0 To rs.RecordCount - 1 rs.Fields(0) = tmp(i) Debug.Print "记录集中的" & rs.Fields(0) & "tmpid数组中的值:" & tmp(i) rs.Update rs.MoveNext Next i End If ' rs.Close sql = "select cardno from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' " rs.Open sql, conn, 1, 1 Set DataGrid1.DataSource = rs DataGrid1.Refresh
oExcel.DisplayAlerts = False '强行退出excel进程 oExcel.Quit Else Exit Sub End If End Sub
update ke_school_student set cardno = tmpid where company_id='0011' and class_name ='高一(1)班'卡号cardno为表ke_school_student里面的一个字段,tmpid为一个数组,请问我应该如果才能把数组里面的数据按顺序替换cardno这个字段的所有记录如果这样不行,能不能教我这条sql语句应该怎么写?
If UBound(tmpid) = rstOra.RecordCount Then '此数组中己包含要替换的新卡号
Do While rstOra.EOF <> True
rstOra.Open "update ke_school_student set cardno = '数组????' where company_id='0011' and class_name ='高一(1)班'"
rstOra.MoveNext
Loop
End If请问我这个循环应该怎么写?才能把数组里面的新卡号逐个更新数据表中原来旧的卡号?请各位大哥帮帮忙忙,问题比较急
2.向ORACLE中写
CommonDialog1.FilterIndex = 1
CommonDialog1.Action = 1
thefilename = CommonDialog1.FileName
If thefilename <> "" Then
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Set oExcel = New Excel.Application
oExcel.Visible = True '设置EXCEL可见
'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿
Set oBook = oExcel.Workbooks.Open(thefilename)
Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表
oSheet.Activate '激活工作表
For i = 0 To UBound(tmpid) - 1
oSheet.Cells(i + 2, 9) = tmpid(i)
Next i
oExcel.Visible = True
oExcel.UserControl = True
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Dim str, estr, sql, sstr As String
On Error Resume Next
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
rs.CursorLocation = adUseClient
rs.ActiveConnection = conn
sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' "
Set rs = conn.Execute(sql)
MsgBox rs.RecordCount & " and " & UBound(tmpid) If UBound(tmpid) = rs.RecordCount Then
Do While rs.EOF <> True
estr = oSheet.Cells(r + 2, 2)
sstr = oSheet.Cells(r + 2, 3)
str = tmpid(r)
Debug.Print estr & "cardno is: " & str; " sex is: " & sstr
sql = "update ke_school_student set cardno = str where student_name = '" + estr + "' and sex = '" + sstr + "' "
Set rs = conn.Execute(sql)
rs.MoveNext
r = r + 1
Loop
End If
rs.Close
sql = "select cardno from ke_school_student where company_id='0011' and class_name ='高一(1)班' "
Set rs = conn.Execute(sql)
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
Else
Exit Sub
End If
End Sub
这样做为什么数据不会更新到表?
CommonDialog1.Filter = "Excel文件|*.xls|所有文件|*.*"
CommonDialog1.FilterIndex = 1
CommonDialog1.Action = 1
thefilename = CommonDialog1.FileName
If thefilename <> "" Then
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim x As Integer
Set oExcel = New Excel.Application
oExcel.Visible = False '设置EXCEL可见
'Set oBook = oExcel.Workbooks.Open(App.Path & "\test.xls") '打开EXCEL工作簿
Set oBook = oExcel.Workbooks.Open(thefilename)
Set oSheet = oBook.Worksheets(1) '打开EXCEL工作表
oSheet.Activate '激活工作表
x = oSheet.UsedRange.Rows.Count
' oExcel.Visible = True
' oExcel.UserControl = True
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Integer
Dim str, estr, sql, sstr As String
On Error Resume Next
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
conn.ConnectionString = "Provider=MSDAORA.1;User ID=kaf;password=kaf;Data Source=kaf;Persist Security Info=False"
conn.CursorLocation = adUseClient
conn.Open
rs.CursorLocation = adUseClient
rs.ActiveConnection = conn
sql = "select cardno,student_name, sex,birthday from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' "
rs.Open sql, conn, 2, 3
Set DataGrid2.DataSource = rs
DataGrid2.Refresh
Debug.Print rs.RecordCount
If x - 1 = rs.RecordCount Then
For i = 0 To rs.RecordCount - 1
listPrint ("学生姓名: " & oSheet.Cells(i + 2, 2) & " 学生卡号:" & oSheet.Cells(i + 2, 9) & " 处于第" & i + 1 & "个位置")
tmp(i) = oSheet.Cells(i + 2, 9)
tmp(UBound(tmp)) = tmp(i)
ReDim Preserve tmp(UBound(tmp) + 1)
Next
Else
MsgBox "数据表中的数据总数: " & rs.RecordCount & " 不等于excel表中的数据: " & x - 1 & " 请检查校对!!!"
End If
Debug.Print rs.RecordCount & UBound(tmp)
If UBound(tmp) = rs.RecordCount Then '此数组中己包含要替换的新卡号
rs.MoveFirst
For i = 0 To rs.RecordCount - 1
rs.Fields(0) = tmp(i)
Debug.Print "记录集中的" & rs.Fields(0) & "tmpid数组中的值:" & tmp(i)
rs.Update
rs.MoveNext
Next i
End If
' rs.Close
sql = "select cardno from ke_school_student where company_id='" + txt_school.Text + "' and class_name ='" + txt_class.Text + "' "
rs.Open sql, conn, 1, 1
Set DataGrid1.DataSource = rs
DataGrid1.Refresh
oExcel.DisplayAlerts = False '强行退出excel进程
oExcel.Quit
Else
Exit Sub
End If
End Sub