搜索
编程论坛 → 开发语言 → 『 VB6论坛 』 → 数据库访问问题？
标题：数据库访问问题？
只看楼主
sncelpl
Rank: 1
等　级：新手上路
帖　子：87
专家分：0
注　册：2006-6-28
结帖率：86.67%
楼主
 已结贴√  问题点数：20 回复次数：8 
数据库访问问题？
咨询下各位大老，VB访问mdb数据库最多多少个啊，我的一个表里有140个字段。
怎么我一点"下一条"程序就卡死了
Set RS = New ADODB.Recordset '新建一个recordset
Set lsRS = New ADODB.Recordset
RS.CursorType = adOpenStatic
RS.CursorLocation = adUseClient
RS.LockType = adLockPessimistic
RS.Open "SELECT * FROM xb  ORDER BY 项目名称 ASC", Con
lsRS.CursorType = adOpenStatic
lsRS.CursorLocation = adUseClient
lsRS.LockType = adLockPessimistic
lsRS.Open "SELECT * FROM xb1", Con
For i = 0 To 136
    Set Text1(i).DataSource = RS
    Text1(i).Locked = True
Next
Text1(0).DataField = "项目名称"
Text1(1).DataField = "名称"
Text1(2).DataField = "型号"
Text1(3).DataField = "制造编号"
Text1(4).DataField = "制造厂家"
Text1(5).DataField = "额定电压G"
Text1(6).DataField = "额定电压D"
Text1(7).DataField = "容量"
Text1(8).DataField = "结线组别"
Text1(9).DataField = "冷却方式"
Text1(10).DataField = "出厂日期"
Text1(11).DataField = "zzg1AB"
Text1(12).DataField = "zzg1AC"
.........
Text1(136).DataField = "J结论"
Set 箱变明细表.DataSource = RS


Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
On Error Resume Next
Select Case Button.Index
Case 1
    RS.MoveFirst
Case 2
    RS.MovePrevious
    If RS.BOF Then
       RS.MoveFirst
    End If
Case 3
    RS.MoveNext
    If RS.EOF Then
       RS.MoveLast
    End If
Case 4
    RS.MoveLast


[此贴子已经被作者于2022-5-20 13:33编辑过]

搜索更多相关主题的帖子: Text1　Case　访问　If　Set　
2022-05-20 13:29
cwa9958
Rank: 10Rank: 10Rank: 10
等　级：贵宾
威　望：67
帖　子：247
专家分：1228
注　册：2006-6-25
第 2 楼
 得分:14 
mdb数据库单个表最多256个字段。
我有个数据库有1050个字段，用了5个表

你卡住可能是别的问题，
在卡住的时候暂停运行，看看停在哪里。

Text1(0).DataField = "项目名称"
Text1(1).DataField = "名称"
Text1(2).DataField = "型号"
Text1(3).DataField = "制造编号"
Text1(4).DataField = "制造厂家"
Text1(5).DataField = "额定电压G"
Text1(6).DataField = "额定电压D"
Text1(7).DataField = "容量"
Text1(8).DataField = "结线组别"
Text1(9).DataField = "冷却方式"
Text1(10).DataField = "出厂日期"
Text1(11).DataField = "zzg1AB"
Text1(12).DataField = "zzg1AC"
.........
Text1(136).DataField = "J结论"

你真厉害，写了137条赋值语句。用循环，或自动绑定就可以。

[此贴子已经被作者于2022-5-20 16:10编辑过]

2022-05-20 16:00
sncelpl
Rank: 1
等　级：新手上路
帖　子：87
专家分：0
注　册：2006-6-28
第 3 楼
 得分:0 
回复 2楼 cwa9958
本人是个业余的,怎么自动绑定啊能举例一个吗

2022-05-20 17:26
sncelpl
Rank: 1
等　级：新手上路
帖　子：87
专家分：0
注　册：2006-6-28
第 4 楼
 得分:0 
回复 2楼 cwa9958
Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
On Error Resume Next
Select Case Button.Index
Case 1
    RS.MoveFirst
Case 2
    RS.MovePrevious
    If RS.BOF Then
        RS.MoveFirst
    End If
Case 3
    RS.MoveNext
    If RS.EOF Then
        RS.MoveLast
    End If
Case 4
    RS.MoveLast
Case 5
    If now_find Then
        Frame2.Visible = False
        Frame3.Visible = True
        Frame4.Visible = False
        Frame5.Visible = False
        Frame6.Visible = False
        Frame7.Visible = False
        Text3.Text = ""
        Text5.Text = ""
        opname.Value = False
        opshao.Value = False
        For i = 1 To 4
            Toolbar1.Buttons(i).Enabled = False
        Next
        Toolbar1.Buttons(6).Enabled = False
        Toolbar1.Buttons(7).Enabled = False
        Toolbar1.Buttons(8).Enabled = False
        Toolbar1.Buttons(5).Caption = "确定"
    Else
        If Text3.Locked = False Then
            RS.Filter = "名称 LIKE ( '*" & Trim(Text3.Text) & "*')"
        ElseIf Text5.Locked = False Then
            RS.Filter = "型号 LIKE ( '*" & Trim(Text5.Text) & "*')"
        Else
            MsgBox ("请写入查询条件")
            Exit Sub
        End If
        If RS.EOF Then
            MsgBox ("无所需查找的资料")
            Exit Sub
        End If
        Label7.Caption = RS.RecordCount
        Frame2.Visible = True
        Frame3.Visible = False
        Frame4.Visible = True
        Frame5.Visible = True
        Frame6.Visible = True
        Frame7.Visible = True
        For i = 1 To 4
            Toolbar1.Buttons(i).Enabled = True
        Next
        Toolbar1.Buttons(6).Enabled = True
        Toolbar1.Buttons(7).Enabled = True
        Toolbar1.Buttons(8).Enabled = True
        Toolbar1.Buttons(5).Caption = "查询"
        Do While lsRS.EOF = False
            lsRS.Delete
            lsRS.MoveNext
        Loop
        Do While RS.EOF = False
            lsRS.AddNew
            For i = 0 To 52
                lsRS.Fields(i) = RS.Fields(i)
            Next
            lsRS.Update
            RS.MoveNext
        Loop
        RS.MoveFirst
    End If
    now_find = Not now_find
    Exit Sub
Case 6
    If now_add Then
        Command1.Visible = True
        old = RS.Bookmark
        RS.AddNew
        Text1(0).SelStart = 1
        Text1(0).SelLength = Len(Text1(0).Text)
        Text1(0).Text = "10kV***箱变新建工程"
        Text1(1).Text = "10kV变压器试验报告"
        Text1(2).Text = "S11-M-315/10"
        Text1(4).Text = "重庆ABB变压器有限公司"
        Text1(10).Text = "2022年4月1日"
        Text1(5).Text = "10kV"
        Text1(6).Text = "0.4kV"
        Text1(7).Text = "315kVA"
        Text1(8).Text = "DYn11"
        Text1(9).Text = "on"
        Text1(45).Text = "2kV耐压60s无异常"
        Text1(46).Text = "100GΩ"
        Text1(47).Text = "100GΩ"
        Text1(44).Text = "200GΩ"
        Text1(43).Text = "200GΩ"
        Text1(53).Text = "28kV耐压60s无异常"
        Text1(39).Text = "RBYQ-变压器直流电阻测试仪"
        Text1(37).Text = "KZTX-6/0.25交流耐压调压控制器及变压器"
        Text1(42).Text = "DM50C电子式绝缘电阻表"
        Text1(52).Text = "2022年4月26日"
        Toolbar1.Buttons(6).Caption = "确定"
        Toolbar1.Buttons(7).Caption = "取消"
        For i = 1 To 5
            Toolbar1.Buttons(i).Enabled = False
        Next
        Toolbar1.Buttons(8).Enabled = False
        Toolbar1.Buttons(9).Enabled = False
        For i = 0 To 136
            Text1(i).Locked = False
        Next
        For i = 0 To 27
            Label8(i).Visible = True
        Next
    Else
        If Text1(0).Text = "" Or Text1(1).Text = "" Or Text1(2).Text = "" Or Text1(3).Text = "" Or Text1(4).Text = "" Or Text1(5).Text = "" Or Text1(6).Text = "" Or Text1(7).Text = "" Or Text1(8).Text = "" Or Text1(9).Text = "" Or Text1(10).Text = "" Or Text1(35).Text = "" Or Text1(11).Text = "" Or Text1(17).Text = "" Or Text1(18).Text = "" Or Text1(19).Text = "" Or Text1(20).Text = "" Or Text1(21).Text = "" Or Text1(22).Text = "" Or Text1(23).Text = "" Or Text1(24).Text = "" Or Text1(25).Text = "" Or Text1(26).Text = "" Or Text1(27).Text = "" Or Text1(28).Text = "" Or Text1(29).Text = "" Or Text1(30).Text = "" Or Text1(31).Text = "" Then
            MsgBox ("请填写带*号项"), vbOKOnly, "警告"
            Exit Sub
        Else
            With RS
                .Fields("项目名称") = Text1(0)
                .Fields("名称") = Text1(1)
                .Fields("型号") = Text1(2)
                .Fields("制造编号") = Text1(3)
                .Fields("制造厂家") = Text1(4)
                .Fields("额定电压G") = Text1(5)
                .Fields("额定电压D") = Text1(6)
                .Fields("容量") = Text1(7)
                .Fields("结线组别") = Text1(8)
                .Fields("冷却方式") = Text1(9)
                .Fields("出厂日期") = Text1(10)
                .Fields("zzg1AB") = Text1(11)
                .Fields("zzg1AC") = Text1(12)
                .Fields("zzg1BC") = Text1(13)
                .Fields("1phl") = Text1(14)
                .Fields("zzg2AB") = Text1(15)
                .Fields("zzg2AC") = Text1(16)
                .Fields("zzg2BC") = Text1(17)
                .Fields("2phl") = Text1(18)
                .Fields("zzg3AB") = Text1(19)
                .Fields("zzg3AC") = Text1(20)
                .Fields("zzg3BC") = Text1(21)
                .Fields("3phl") = Text1(22)
                .Fields("zzg4AB") = Text1(23)
                .Fields("zzg4AC") = Text1(24)
                .Fields("zzg4BC") = Text1(25)
                .Fields("4phl") = Text1(26)
                .Fields("zzg5AB") = Text1(27)
                .Fields("zzg5AC") = Text1(28)
                .Fields("zzg5BC") = Text1(29)
                .Fields("5phl") = Text1(30)
                .Fields("zzda0") = Text1(31)
                .Fields("zzdb0") = Text1(32)
                .Fields("gjl") = Text1(33)
                .Fields("djl") = Text1(34)
                .Fields("zzdc0") = Text1(35)
                .Fields("6phl") = Text1(36)
                .Fields("耐压设备") = Text1(37)
                .Fields("万用表") = Text1(38)
                .Fields("直流电阻仪") = Text1(39)
                .Fields("其它2") = Text1(40)
                .Fields("其它1") = Text1(41)
                .Fields("摇表") = Text1(42)
                .Fields("耐压前绝缘G") = Text1(43)
                .Fields("耐压后绝缘G") = Text1(44)
                .Fields("低压侧交流耐压") = Text1(45)
                .Fields("耐压前绝缘D") = Text1(46)
                .Fields("耐压后绝缘D") = Text1(47)
                .Fields("结论") = Text1(48)
                .Fields("温度") = Text1(49)
                .Fields("湿度") = Text1(50)
                .Fields("试验人员") = Text1(51)
                .Fields("试验日期") = Text1(52)
                .Fields("高压侧交流耐压") = Text1(53)
                .Fields("K设备名称") = Text1(54)
                .Fields("K型号") = Text1(55)
                .Fields("K出厂编号") = Text1(56)
                .Fields("K额定电压") = Text1(57)
                .Fields("K制造厂家") = Text1(58)
                .Fields("K出厂日期") = Text1(59)
                .Fields("K温度") = Text1(60)
                .Fields("K湿度") = Text1(61)
                .Fields("K试验人员") = Text1(62)
                .Fields("K试验日期") = Text1(63)
                .Fields("KSQD绝缘电阻A相") = Text1(64)
                .Fields("KSQD绝缘电阻B相") = Text1(65)
                .Fields("KSQD绝缘电阻C相") = Text1(66)
                .Fields("KSHD绝缘电阻A相") = Text1(67)
                .Fields("KSHD绝缘电阻B相") = Text1(68)
                .Fields("KSHD绝缘电阻C相") = Text1(69)
                .Fields("KSQX绝缘电阻A相") = Text1(70)
                .Fields("KSQX绝缘电阻B相") = Text1(71)
                .Fields("KSQX绝缘电阻C相") = Text1(72)
                .Fields("KSHX绝缘电阻A相") = Text1(73)
                .Fields("KSHX绝缘电阻B相") = Text1(74)
                .Fields("KSHX绝缘电阻C相") = Text1(75)
                .Fields("K回路电阻A相") = Text1(76)
                .Fields("K回路电阻B相") = Text1(77)
                .Fields("K回路电阻C相") = Text1(78)
                .Fields("KD交流耐压A相") = Text1(79)
                .Fields("KD交流耐压B相") = Text1(80)
                .Fields("KD交流耐压C相") = Text1(81)
                .Fields("KS交流耐压A相") = Text1(82)
                .Fields("KS交流耐压B相") = Text1(83)
                .Fields("KS交流耐压C相") = Text1(84)
                .Fields("KJL交流耐压A相") = Text1(85)
                .Fields("KJL交流耐压B相") = Text1(86)
                .Fields("KJL交流耐压C相") = Text1(87)
                .Fields("K仪器仪表1") = Text1(88)
                .Fields("K仪器仪表2") = Text1(89)
                .Fields("K仪器仪表3") = Text1(90)
                .Fields("K结论") = Text1(91)
                .Fields("B设备名称") = Text1(92)
                .Fields("BA型号") = Text1(93)
                .Fields("BA出厂编号") = Text1(94)
                .Fields("BA生产厂家") = Text1(95)
                .Fields("BA出厂日期") = Text1(96)
                .Fields("BB型号") = Text1(97)
                .Fields("BB出厂编号") = Text1(98)
                .Fields("BB生产厂家") = Text1(99)
                .Fields("BB出厂日期") = Text1(100)
                .Fields("BC型号") = Text1(101)
                .Fields("BC出厂编号") = Text1(102)
                .Fields("BC生产厂家") = Text1(103)
                .Fields("BC出厂日期") = Text1(104)
                .Fields("BAU1mA") = Text1(105)
                .Fields("BBU1mA") = Text1(106)
                .Fields("BCU1mA") = Text1(107)
                .Fields("BA泄漏") = Text1(108)
                .Fields("BB泄漏") = Text1(109)
                .Fields("BC泄漏") = Text1(110)
                .Fields("BA试前绝缘") = Text1(111)
                .Fields("BB试前绝缘") = Text1(112)
                .Fields("BC试前绝缘") = Text1(113)
                .Fields("BA试后绝缘") = Text1(114)
                .Fields("BB试后绝缘") = Text1(115)
                .Fields("BC试后绝缘") = Text1(116)
                .Fields("B绝缘电阻仪型号") = Text1(117)
                .Fields("B耐压设备型号") = Text1(118)
                .Fields("B温度") = Text1(119)
                .Fields("B湿度") = Text1(120)
                .Fields("B试验人员") = Text1(121)
                .Fields("B试验日期") = Text1(122)
                .Fields("B结论") = Text1(123)
                .Fields("J接地网名称") = Text1(124)
                .Fields("J环境") = Text1(125)
                .Fields("J接地极") = Text1(126)
                .Fields("J接地带") = Text1(127)
                .Fields("J制作方式") = Text1(128)
                .Fields("J测试方法") = Text1(129)
                .Fields("J测试结果") = Text1(130)
                .Fields("J地阻表型号") = Text1(131)
                .Fields("J温度") = Text1(132)
                .Fields("J湿度") = Text1(133)
                .Fields("J试验人员") = Text1(134)
                .Fields("J试验日期") = Text1(135)
                .Fields("J结论") = Text1(136)
                .Update
                总数.Caption = .RecordCount
            End With
        End If
        Toolbar1.Buttons(6).Caption = "添加"
        Toolbar1.Buttons(7).Caption = "更新"
                        Command1.Visible = False

        For i = 1 To 5
            Toolbar1.Buttons(i).Enabled = True
        Next
        Toolbar1.Buttons(8).Enabled = True
        Toolbar1.Buttons(9).Enabled = True
        For i = 0 To 136
            Text1(i).Locked = True
        Next
        For i = 0 To 27
            Label8(i).Visible = False
        Next
    End If
    now_add = Not now_add
    Exit Sub
Case 7
    If now_add Then
        old = RS.Bookmark
        For i = 0 To 136
            Text1(i).Locked = False
        Next
        Toolbar1.Buttons(6).Caption = "确定"
        Toolbar1.Buttons(7).Caption = "取消"
        For i = 1 To 5
            Toolbar1.Buttons(i).Enabled = False
        Next
        Command1.Visible = True
        For i = 0 To 27
            Label8(i).Visible = True
        Next
        Toolbar1.Buttons(8).Enabled = False
        Toolbar1.Buttons(9).Enabled = False
    Else
        RS.CancelUpdate
        RS.Bookmark = old
        Toolbar1.Buttons(6).Caption = "添加"
        Toolbar1.Buttons(7).Caption = "更新"
        Command1.Visible = False
        For i = 1 To 5
            Toolbar1.Buttons(i).Enabled = True
        Next
        Toolbar1.Buttons(8).Enabled = True
        Toolbar1.Buttons(9).Enabled = True
        For i = 0 To 136
            Text1(i).Locked = True
        Next
        For i = 0 To 27
            Label8(i).Visible = False
        Next
    End If
    now_add = Not now_add
    Exit Sub
Case 8
    If RS.BOF = False Then
        If MsgBox("确定要删除这条数据记录吗？", 36, "注意！") = 6 Then
            RS.Delete
            RS.MoveNext
            If RS.EOF Then
                RS.MoveLast
            End If
        End If
    End If
    总数.Caption = RS.RecordCount
    Exit Sub
Case 9
    If Not now_find Then
        Frame2.Visible = True
        Frame3.Visible = False
        Frame4.Visible = True
        Frame5.Visible = True
        Frame6.Visible = True
        Frame7.Visible = True
        For i = 1 To 4
            Toolbar1.Buttons(i).Enabled = True
        Next
        Toolbar1.Buttons(6).Enabled = True
        Toolbar1.Buttons(7).Enabled = True
        Toolbar1.Buttons(8).Enabled = True
        Toolbar1.Buttons(5).Caption = "查询"
    Else
        Unload Me
    End If
    now_find = True
    Exit Sub
End Select
End Sub


能帮我看下添加没问题更新不行
2022-05-20 18:26
cwa9958
Rank: 10Rank: 10Rank: 10
等　级：贵宾
威　望：67
帖　子：247
专家分：1228
注　册：2006-6-25
第 5 楼
 得分:0 
RS.AddNew位置不对，
RS.AddNew，要马上添加数据，然后Update
 
 Else
            With RS
                .addnew
                .Fields("项目名称") = Text1(0)
                .Fields("名称") = Text1(1)
                .Fields("型号") = Text1(2)
                .Fields("制造编号") = Text1(3)
                .Fields("制造厂家") = Text1(4)
                 .......
           
               .update


[此贴子已经被作者于2022-5-22 17:35编辑过]

2022-05-22 17:23
sncelpl
Rank: 1
等　级：新手上路
帖　子：87
专家分：0
注　册：2006-6-28
第 6 楼
 得分:0 
回复 5楼 cwa9958
我试了下不对点Case 6是新建设点Case 7也是新建应该是old = RS.Bookmark没执行。Case 6是添加，Case 7是更新.可能old = RS.Bookmark位置没放对。

[此贴子已经被作者于2022-5-22 19:36编辑过]

2022-05-22 19:30
cwa9958
Rank: 10Rank: 10Rank: 10
等　级：贵宾
威　望：67
帖　子：247
专家分：1228
注　册：2006-6-25
第 7 楼
 得分:0 
now_add是什么用不清楚，但是addnew以后，就是在数据库末尾添加一条空记录。如果执行了addnew，就要对这条记录赋值，然后保存，就有新数据了。
你说case 7是更新，但是没有在这里看到有更新的代码。
2022-05-23 08:47
sncelpl
Rank: 1
等　级：新手上路
帖　子：87
专家分：0
注　册：2006-6-28
第 8 楼
 得分:0 
回复 7楼 cwa9958
6是添加，7是更新，点添加或点更新6就变成确定7就变成取消，我在网上查了下最大可以定义256个，我成定义了136个，我单个做了一个窗体报定义过多。不知原因了。
2022-05-24 07:48
hmj0745
Rank: 2
等　级：论坛游民
帖　子：52
专家分：83
注　册：2021-11-6
第 9 楼
 得分:0 
回复 4楼 sncelpl
你这个一个过程写这么多代码，看得眼都花了。
关于录入或更新数据库用循环代码会少些，我把一般的对数据库操作写成函数，程序里调用的话，就只要写两三行就可以完成录入更新等一些常规操作。
当然每个人的习惯用法不一样，  我也是初学的，不一定正确哈，权作参考吧。

我的文本框控件名称都是改成和 数据库内字段 名称一致的。这样方便查找，也方便录入修改等操作。

程序中两句
SQL = "select * from " & MyTable & " where 客户编号= '" & 客户编号 & "'"
Call UPmyData(Me, SQL) '// 修改资料

模块中的函数
'03函数名： UPmyData修改记录
'函数功能： 根据窗体控件中的内容修改一条记录到数据库
'返回    ：
'*****************************************************************************************
Function UPmyData(Myform As Form, SQL As String)
On Error GoTo errmsg
Dim ZD As String, Cont As Object
Opendb (StrDB) '// 这一句是我定义的连接数据库的函数，这里就不展开了
Set RS = CreateObject("adodb.recordset") '//创建一个数据集保存数据
RS.Open SQL, CNN, 1, 3 '//数据集保存数据
For i = 0 To RS.Fields.Count - 1'//循环从库里查询出来的表字段
 ZD = RS.Fields(i).Name'// 把字段名称赋值给ZD
  For Each Cont In Myform.Controls '//循环窗体中的所有控件（这里也可以再加个判断限定文本框）
    If Cont.Name = ZD Then'// 如果控件名字等于字段
    RS.Fields(ZD) = Myform.Controls(ZD)'// 把新值赋给这个字段
    GoTo 222
    End If
  Next
222
Next
RS.Update
Call Msgfrm("修改记录成功", 1, Myform)'//自定义的一个对话框
GoTo 111
errmsg:
    MsgBox Err.Description, , "错误报告"
111
Disconnect'// 自定义的一个断开连接释放内存的过程
End Function

下面的这个是录入函数，也贴一下
'02函数名： AddData 添加记录
'函数功能： 从窗体控件中添加一条记录到数据库
'返回    ：
'*****************************************************************************************
Function AddData(StrDB As String, MyTable As String, Myform As Form, str As String)
On Error GoTo errmsg
Dim ZD As String
Opendb (StrDB)
SQL = "SELECT * FROM " & MyTable & ""
Set RS = CreateObject("adodb.recordset") '//创建一个数据集保存数据
RS.Open SQL, CNN, 1, 3 '//数据集保存数据
RS.AddNew
For i = 0 To RS.Fields.Count - 1
ZD = RS.Fields(i).Name
If InStr(str, ZD) > 0 Then GoTo 222 '//如果是指定排除的字段，跳转不用导入
RS.Fields(ZD) = Myform.Controls(ZD)
222
Next
RS.Update
Call Msgfrm("添加保存记录成功", 1, Myform)

GoTo 111
errmsg:
    MsgBox Err.Description, , "错误报告"
111
Disconnect
End Function

[此贴子已经被作者于2022-6-1 19:37编辑过]


不去做永远没有结果！
2022-06-01 19:34



参与讨论请移步原网站贴子：https://bbs.bccn.net/thread-509131-1-1.html




关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有，并保留所有权利。
Powered by Discuz, Processed in 0.113040 second(s), 7 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved