mysql - 如何使用按钮“下一个”和“上一个”从数据库读取数据

Question

Imports System.Data.Odbc
Imports System.Data.SqlClient

Module Module1
    Public con As New odbcConnection
    Public cmd As New OdbcCommand
    Public dr As OdbcDataReader

    Function connect()
        con.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};database=sample;option=0;port=3306;server=localhost;uid=root;pwd=root" local connection
        con.Open()
    End Function

    Function Disconnect()
        con.Close()
    End Function
End Module

我使用此代码从我的数据库检索数据

Private Sub search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
    Disconnect()
    connect()
    Using da As New Odbc.OdbcDataAdapter("Select  model, controlNo, revLvl, dataAq, maker, makerControlNo, revItem, reasonRev, remarks, endorseDate, a, b, c, d, e, frameSize, framProf, fedMark, imgOr, imgPos, txtMark, actual, required from dbstencil.tbldata where controlNo = '" & search.Text & "'and model = '" & searchmodel.Text & "'", con)
        MyDataTable.Clear()
        da.Fill(MyDataTable)
    End Using
    Disconnect()
    CurrentRowNumber = 0
    DisplayRecord()
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
    #Do Code here
End Sub

Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
    #Do Code here
End Sub

Answer 1

使用Dataset或DataTable而不是DataReader。您不需要始终保持连接打开。

这里有一些未经测试的示例代码。

'' declare these variables at form level
Private MyDataTable As New DataTable
Private CurrentRowNumber As Integer

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnsearch.Click
    Connect()
    Using da As New Odbc.OdbcDataAdapter("Select columnA, columnB, columnC, columnD FROM TableName where columnA= '" & search.Text & "'and columnB= '" & searchmodel.Text & "'", con)
        MyDataTable.Clear()
        da.Fill(MyDataTable)
    End Using
    Disconnect()
    CurrentRowNumber = 0
    DisplayRecord()
End Sub

Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
    CurrentRowNumber += 1
    DisplayRecord()
End Sub

Private Sub btnPrev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrev.Click
    CurrentRowNumber -= 1
    DisplayRecord()
End Sub

Private Sub DisplayRecord()
    With MyDataTable.Rows(CurrentRowNumber)
        columnA.Text = .Item("columnA").ToString
        columnB.Text = .Item("columnB").ToString
        columnC.Text = .Item("columnC").ToString
        columnD.Text = .Item("columnD").ToString
    End With
    btnPrev.Enabled = True
    btnNext.Enabled = True
    If CurrentRowNumber = 0 Then btnPrev.Enabled = False
    If CurrentRowNumber = MyDataTable.Rows.Count - 1 Then btnNext.Enabled = False
End Sub