杭州交通信息网保健品发展趋势:VB写入数据库的难题,请高手指教!急!!
来源：百度文库 编辑：杭州交通信息网 时间：2024/05/02 09:51:02
Set Coon = CreateObject("ADODB.Connection") '连接数据库
Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & accpath & ";" '连接数据库的地址，调用 accpath
Coon.Open Connstr
'Set rs = CreateObject("ADODB.recordset")
Dim rs As ADODB.Recordset
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sql = "SELECT sclass FROM SClass where sclass = '" & fenlei & "'"
rs.Open Sql, Coon, adOpenStatic, adLockReadOnly
If Not (rs.BOF Or rs.EOF) Then
MsgBox "它说有数据"
MsgBox sclassid
Exit Sub
End If
我想实现的是: 连接数据库 , 向 sclass 表的 sclass 项查询里面有没有变量 fenlei 的数据 ,如果有,就获取这个已经存在的记录的数据库自动编号的那个 sclassid ,再弹出"它说有数据",再弹出那个自动编号 sclassid ,如果没有,在sclass表的sclass项里面写入一条变量 fenlei ,然后记录自动编号的那个 sclassid ,再弹出"它说没有,但是我写入了",再弹出那个自动编号 sclassid
我怎么写也写不对,请指教...
最后,本人比较菜了... 请尽量回答详细一些,真的感谢你~ 一定加分!
accpath 和 fenlei 都是我自定义的变量
大家可以假设 fenlei 为 测试
抱歉 macker0030 出错
"无效外部过程"
而且,请仔细看下我的要求哦~ 好像没有去取sclassid
你这段代码前面是在ASP里复制过来的吧？asp与VB是不同的。正确的写法应该是这样：
Dim Coon as New ADODB.Connection '连接数据库
Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & accpath & ";" '连接数据库的地址，调用 accpath
Coon.Open Connstr
Dim rs As New ADODB.Recordset
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Sql = "SELECT sclass FROM SClass where sclass = '" & fenlei & "'"
rs.Open Sql, Coon, adOpenStatic, adLockReadOnly
If Not rs.EOF Then
MsgBox "它说有数据"
MsgBox sclassid
Else
rs.addnew '添加新记录
rs!sclass=fenlei
rs.Update '保存
MsgBox "它说没有,但是我写入了"
Msgbox sclassid
End If
用VB6的ADO控件，使用SQL语句可实现两个表格式一样数据不同的数据合并，且能把源数据表和目标数据表主关键词相同的数据更新，并可在目标数据表中添加新的数据。提问者应依据所涉及数据库情况对代码改动。具体代码如下：
第一部分，定义变量
Option Explicit
Dim zh_bm(11, 1500)
Dim zh_bm1(11, 1500)
Dim zsl As Integer
Dim zsl1 As Integer
Dim i As Integer
Dim j As Integer
Dim ii As Integer
Dim jj As Integer
第二部分，将源数据表数据传递给变量
Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db1.mdb;Persist Security Info=False"
Adodc1.RecordSource = "SELECT * FROM mdlk_sj where 销售='N' order by 码单号"
Adodc1.Refresh
DataGrid1.Refresh
zsl = Adodc1.Recordset.RecordCount
Adodc1.Recordset.MoveFirst
For i = 0 To zsl - 1
For j = 0 To 11
zh_bm(j, i) = Adodc1.Recordset(j)
Next j
Adodc1.Recordset.MoveNext
Next i
Adodc1.Recordset.MoveFirst
第三部分，添加与更新目标数据表
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db2.mdb;Persist Security Info=False"
Adodc2.RecordSource = "SELECT * FROM MDLK_SJ1 where 码单号='" & "" & "'" ' order by 码单号"
Adodc2.Refresh
DataGrid2.Refresh
For i = 0 To zsl - 1
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db2.mdb;Persist Security Info=False"
Adodc2.RecordSource = "SELECT * FROM MDLK_SJ1 where 码单号='" & zh_bm(4, i) & "'" ' order by 码单号"
Adodc2.Refresh
DataGrid2.Refresh
zsl1 = Adodc2.Recordset.RecordCount
Text2 = zsl1
If zsl1 = 0 Then
Label2.BackColor = vbGreen
Adodc2.Recordset.AddNew
For j = 0 To 11
Adodc2.Recordset(j) = zh_bm(j, i)
Next j
Adodc2.Recordset.Update
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db2.mdb;Persist Security Info=False"
Adodc2.RecordSource = "SELECT * FROM MDLK_SJ1 where 码单号='" & zh_bm(4, i) & "'" ' order by 码单号"
Adodc2.Refresh
DataGrid2.Refresh
ElseIf zsl1 = 1 Then
Label2.BackColor = vbRed
For j = 0 To 11
Adodc2.Recordset(j) = zh_bm(j, i)
Next j
Adodc2.Recordset.Update
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db2.mdb;Persist Security Info=False"
Adodc2.RecordSource = "SELECT * FROM MDLK_SJ1 where 码单号='" & zh_bm(4, i) & "'" ' order by 码单号"
Adodc2.Refresh
DataGrid2.Refresh
End If
Next i
'以下是目标数据表显示刷新
Adodc2.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\DATA_REF\db2.mdb;Persist Security Info=False"
Adodc2.RecordSource = "SELECT * FROM MDLK_SJ1 order by 码单号"
Adodc2.Refresh
DataGrid2.Refresh
上述代码在VB6调试通过，供参考。