我的一个库里有两张表,一个是test report ,另一个是supplier,做一个界面选择了相应的report number 就会出现相应的supplier的信息,当然是一一对应的,为了方便,我想能不能用两个ado同时访问Private Sub no_click()
Call openconn
Call openconn1
sqlstr = "SELECT report_number,supplier_name FROM test_report WHERE report_number= '" & no.Text & "'"
Call adors(sqlstr)
If Not (rs.EOF) Then
supplier_nam.Text = rs.Fields("supplier_name")
sqlstr1 = "SELECT *FROM supplier_name WHERE supplier_name='" & supplier_nam.Text & "'"
adors1 (sqlstr1)addr.Text = rs1.Fields("address")
fax.Text = rs1.Fields("fax")
contact_per.Text = rs1("contact_person")email.Text = rs1("e_mail")
End If
Call closeconn
Call closeconn1
End Sub
点击combobox(no)就可以看到对应的信息,不过好像不可行,请大家帮我看看!
Call openconn
Call openconn1
sqlstr = "SELECT report_number,supplier_name FROM test_report WHERE report_number= '" & no.Text & "'"
Call adors(sqlstr)
If Not (rs.EOF) Then
supplier_nam.Text = rs.Fields("supplier_name")
sqlstr1 = "SELECT *FROM supplier_name WHERE supplier_name='" & supplier_nam.Text & "'"
adors1 (sqlstr1)addr.Text = rs1.Fields("address")
fax.Text = rs1.Fields("fax")
contact_per.Text = rs1("contact_person")email.Text = rs1("e_mail")
End If
Call closeconn
Call closeconn1
End Sub
点击combobox(no)就可以看到对应的信息,不过好像不可行,请大家帮我看看!
解决方案 »
- VB中怎么连接Execel呢?
- 关于socket
- hhjjhjhj(大头)及其他高手救命,仍是上次VB访问ACCESS数据库,然后结果转存EXCEL问题.
- 如何在dos下调用vb的exe程序 exe程序隐藏显示 并直接输出内容到改dos窗口 急求?!
- 我想用VB6做一个框架,谁能告诉我方法,提点建议也给分
- 有关拨号上网程序问题:1.怎样获取当前连接的速率2.怎样调用modem设置的属性3.怎样从注册表中屏蔽拨号网络及屏蔽开始菜单中的拨号网络!
- 怎样给手机发短信?
- 请问高手,如何在vb中的webbrowser控件上运行页面中的javascript function?
- 如何用ACCESS的数据库?
- 如何禁止msflexgrid多行选中。
- VB中怎么关闭导入EXCEL表格进程
- 急,递归函数出现提示错误(实时错误'28',堆栈空间溢出)
Call openconn
Call openconn1
sqlstr = "SELECT report_number,supplier_name " & _
"FROM test_report WHERE report_number= '" & no.List(no.listindex) & "'"
Call adors(sqlstr)
If Not (rs.EOF) Then
supplier_nam.Text = rs.Fields("supplier_name")
sqlstr1 = "SELECT *FROM supplier_name " & _
"WHERE supplier_name='" & rs.Fields("supplier_name") & "'"
call adors1(sqlstr1) addr.Text = rs1.Fields("address")
fax.Text = rs1.Fields("fax")
contact_per.Text = rs1("contact_person") email.Text = rs1("e_mail")
End If
Call closeconn
Call closeconn1
End Sub另外:最好判断一下空值null的问题。
Call openconn '这里你肯定用rs做了个全局变量,不好
Call openconn1 '没必要再用一个connection
'而且你的openconn和adors对非常不好
sqlstr = "SELECT report_number,supplier_name FROM test_report WHERE report_number= '" & no.Text & "'"
Call adors(sqlstr)
If Not (rs.EOF) Then
supplier_nam.Text = rs.Fields("supplier_name")
sqlstr1 = "SELECT *FROM supplier_name WHERE supplier_name='" & supplier_nam.Text & "'"
adors1 (sqlstr1)addr.Text = rs1.Fields("address")
fax.Text = rs1.Fields("fax")
contact_per.Text = rs1("contact_person")email.Text = rs1("e_mail")
End If
Call closeconn
Call closeconn1
End Sub给你一个我常用的ado函数,非常好用:'这是数据库文件的连接字符串函数
Public Function ConnectString() As String
Dim DbFile As String
DbFile = App.Path & "\data\data.mdb" '数据库文件路径
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & DbFile & ";"
End Function'这是执行sql语句用的
Public Function ExecuteSQL(ByVal sql As String, Optional Cursorlocation As CursorLocationEnum, Optional ByRef blnReturn As Boolean, Optional blnShowErr As Boolean) As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
Dim sqReturn As Boolean
If (Cursorlocation <> adUseClient) And (Cursorlocation <> adUseServer) Then Cursorlocation = adUseClient
On Error GoTo ExecuteSQL_ErrorHandle
sTokens = Split(sql)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE,DROP", UCase(sTokens(0))) Then
cnn.Execute sql
sqReturn = True
Else
If InStr(UCase(sql), "INTO") Then
'Ex:Select .. Into ..
cnn.Execute sql
sqReturn = True
Else
Set rst = New ADODB.Recordset
rst.Cursorlocation = Cursorlocation '2005-9-12,DataGrid的DataSource需要
rst.Open Trim(sql), cnn, adOpenKeyset, adLockOptimistic
Set ExecuteSQL = rst
sqReturn = True
End If
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
If Not IsMissing(blnReturn) Then blnReturn = sqReturn
Exit Function
ExecuteSQL_ErrorHandle:
sqReturn = False
If Not IsMissing(blnShowErr) Then
If blnShowErr Then MsgBox Err.Description, vbInformation
End If
Resume ExecuteSQL_Exit
End Function下面是你的Combo中的代码:
Private Sub ComboNo_click() '这里Combo的名字改了
Dim sql As String, str As String
Dim rst As ADODB.Recordset
sql = "SELECT report_number,supplier_name FROM test_report WHERE report_number= '" & ComboNo.Text & "'"
Set rst = ExecuteSQL(sql)
If Not rst.EOF Then
supplier_nam.Text = rst.Fields("supplier_name")
sql = "SELECT * FROM supplier_name WHERE supplier_name='" & supplier_nam.Text & "'"
Set rst = ExecuteSQL(sql)
addr.Text = rst.Fields("address")
fax.Text = rst.Fields("fax")
contact_per.Text = rst.Fields("contact_person")
email.Text = rst.Fields("e_mail")
End If
Set rst = Nothing
End Sub