时间戳

首页 > 解决方案 > 无法从数据库中检索数据以用作循环中的变量

vb.net - 无法从数据库中检索数据以用作循环中的变量

问题描述

我正在制作一个正向链接程序，运行循环以找到最终结果时出现了一点错误。

我想从数据库中检索数据作为查询循环的基准，但我想使用的数据没有出现。

Imports MySql.Data.MySqlClient

Public Class frmkonsultasi

    Dim idgejala, idpenyakit, nmpenyakit, konsul As String
    Dim idkonsul As String
    Dim tanggal As Date
    Dim jmlh, total  As Integer


    Private Sub frmkonsultasi_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        koneksi()
        nootkonsul()
        tanggal = Now.Date
        tampil()
    End Sub

    Sub nootkonsul()
        comkonsul = New MySqlCommand("select*from diagnosa order by iddiagnosa desc", cn)
        drkonsul = comkonsul.ExecuteReader
        drkonsul.Read()
        If drkonsul.HasRows Then
            idkonsul = Microsoft.VisualBasic.Right("0000" & Trim(Str(Val(Microsoft.VisualBasic.Right(drkonsul.Item(0), 5)) + 1)), 5)
        Else
            idkonsul = "00001"
        End If
        drkonsul.Close()
    End Sub

    Sub tampil()
        'drkonsul.Close()
        Dim chk As New DataGridViewCheckBoxColumn

        dagejala = New MySql.Data.MySqlClient.MySqlDataAdapter("SELECT * FROM gejala order by idgejala asc", cn)
        dtgejala = New DataTable
        dagejala.Fill(dtgejala)
        gridkonsul.DataSource = dtgejala

        gridkonsul.Columns.Add(chk)

        gridkonsul.Columns(0).HeaderText = "ID GEJALA"
        gridkonsul.Columns(1).HeaderText = "NAMA GEJALA"
        gridkonsul.Columns(2).HeaderText = "PILIH"

        gridkonsul.Columns(0).Width = 100
        gridkonsul.Columns(1).Width = 400
        gridkonsul.Columns(2).Width = 80

        gridkonsul.Columns(0).Visible = False


        gridkonsul.AllowUserToAddRows = False


    End Sub

    Private Sub btnprose_Click(sender As Object, e As EventArgs) Handles btnprose.Click
        If Textnama.Text = "" Then
            MsgBox("Masukan Nama")
            Return
        End If
        TextBox1.Text = idkonsul
        Dim i As Integer
        For i = 0 To gridkonsul.Rows.Count() - 1
            Dim z As Boolean
            z = gridkonsul.Rows(i).Cells(2).Value
            If z = True Then
                idgejala = gridkonsul.Rows(i).Cells(0).Value
                'proses simpan
                comkonsul = New MySqlCommand("insert into ddiagnosa values('" & idkonsul & "','" & idgejala & "')", cn)
                comkonsul.ExecuteNonQuery()
            End If
        Next
        'mencari jumlah gejala
        comkonsul = New MySqlCommand("select count(idgejala) from ddiagnosa where iddiagnosa='" & idkonsul & "'", cn)
        drkonsul = comkonsul.ExecuteReader
        drkonsul.Read()
        If drkonsul.HasRows Then
            jmlh = drkonsul.Item(0)
        End If
        drkonsul.Close()


        comkonsul = New MySqlCommand("select iddiagnosa from ddiagnosa where iddiagnosa='" & idkonsul & "'", cn)
        drkonsul = comkonsul.ExecuteReader
        drkonsul.Read()
        If drkonsul.HasRows Then
            konsul = drkonsul.Item(0)
        End If
        drkonsul.Close()
        'TextBox1.Text = konsul

        comaturan = New MySqlCommand("select idpenyakit,nmpenyakit from vaturan group by nmpenyakit having count(idgejala)='" & jmlh & "'", cn)
        draturan = comaturan.ExecuteReader
        draturan.Read()

        If draturan.HasRows Then
            Do While draturan.Read()
                idpenyakit = draturan.Item(0)
                nmpenyakit = draturan.Item(1)
                draturan.Close()
                total = 0

                'mencari gejala per penyakit
                comaturan2 = New MySqlCommand("select idgejala from vaturan where idpenyakit='" & idpenyakit & "'", cn)
                draturan2 = comaturan2.ExecuteReader
                draturan2.Read()
                If draturan2.HasRows Then
                    Do While draturan2.Read
                        idgejala = draturan2.Item(0)
                        draturan2.Close()

                        comkonsul = New MySqlCommand("select * from ddiagnosa where iddiagnosa='" & idkonsul & "'and idgejala='" & idgejala & "'", cn)
                        drkonsul = comkonsul.ExecuteReader
                        drkonsul.Read()
                        If drkonsul.HasRows Then
                            total = total + 1
                        End If
                        drkonsul.Close()

                        If total = jmlh Then
                            comkonsul = New MySqlCommand("insert into diagnosa values('" & idkonsul & "','" & Format(tanggal, "yyyy-MM-dd") & "','" & Textnama.Text & "','" & nmpenyakit & "')", cn)
                            comkonsul.ExecuteNonQuery()
                            GoTo selesai
                        End If
                    Loop
                    draturan2.NextResult()

                End If
                draturan2.Close()
            Loop
            draturan.NextResult()
            draturan.Close()

            'menyimpan tidak ditemukan
            comkonsul = New MySqlCommand("insert into diagnosa values('" & idkonsul & "','" & Format(tanggal, "yyyy-MM-dd") & "','" & Textnama.Text & "','" & nmpenyakit & "')", cn)
            comkonsul.ExecuteNonQuery()
        Else
            MsgBox("Penyakit Tidak Ditemukan", , "Info")
            Return
        End If


selesai:
        comkonsul = New MySqlCommand("select nmpenyakit from diagnosa where iddiagnosa='" & idkonsul & "'", cn)
        drkonsul = comkonsul.ExecuteReader
        drkonsul.Read()
        If drkonsul.HasRows Then
            MsgBox("Penyakit : " & drkonsul.Item(0))
        End If
        drkonsul.Close()

    End Sub

问题出在这个循环中，我想在下一个查询中使用“idpenyakit”和“nmpenyakit”再次执行循环，但数据为空，因此循环失败。

在我尝试在循环之外检索数据之前可以使用数据，但是在循环中时数据变为空

comaturan = New MySqlCommand("select idpenyakit,nmpenyakit from vaturan group by nmpenyakit having count(idgejala)='" & jmlh & "'", cn)
            draturan = comaturan.ExecuteReader
            draturan.Read()

            If draturan.HasRows Then
                Do While draturan.Read()
                    idpenyakit = draturan.Item(0)
                    nmpenyakit = draturan.Item(1)
                    draturan.Close()
                    total = 0

                    'mencari gejala per penyakit
                    comaturan2 = New MySqlCommand("select idgejala from vaturan where idpenyakit='" & idpenyakit & "'", cn)
                    draturan2 = comaturan2.ExecuteReader
                    draturan2.Read()
                    If draturan2.HasRows Then
                        Do While draturan2.Read
                            idgejala = draturan2.Item(0)
                            draturan2.Close()

对于连接、适配器、数据读取器等，我将其放在模块中。数据库中的每个表都使用自己的数据读取器

Imports MySql.Data.MySqlClient
Module Module1
    Public cn As MySqlConnection

    Public drpenyakit As MySqlDataReader
    Public dapenyakit As MySqlDataAdapter
    Public compenyakit As MySqlCommand
    Public dtpenyakit As DataTable

    Public drgejala As MySqlDataReader
    Public dagejala As MySqlDataAdapter
    Public comgejala As MySqlCommand
    Public dtgejala As DataTable

    Public draturan As MySqlDataReader
    Public daaturan As MySqlDataAdapter
    Public comaturan As MySqlCommand
    Public dtaturan As DataTable

    Public draturan2 As MySqlDataReader
    Public daaturan2 As MySqlDataAdapter
    Public comaturan2 As MySqlCommand
    Public dtaturan2 As DataTable

    Public drkonsul As MySqlDataReader
    Public dakonsul As MySqlDataAdapter
    Public comkonsul As MySqlCommand
    Public dtkonsul As DataTable

    Public drsolusi As MySqlDataReader
    Public dasolusi As MySqlDataAdapter
    Public comsolusi As MySqlCommand
    Public dtsolusi As DataTable

    Sub koneksi()
        Try
            Dim str As String
            str = "server=localhost;user=root;password=;database=karet2;allow user variables=true"
            cn = New MySqlConnection(str)
            If cn.State = ConnectionState.Closed Then
                cn.Open()
            End If
        Catch ex As Exception
            MsgBox("Koneksi Error", MsgBoxStyle.Information, "Info")
        End Try
    End Sub

End Module

我不再知道如何解决这个错误，我请求你帮助解决这个问题

很抱歉这个非常糟糕的英语

标签： vb.net

解决方案

read()通过调用两次，您有效地跳过了第一行。read()删除循环之前的调用。

从中删除阅读器Module1并将它们包装为Using：

comaturan = New MySqlCommand("select idpenyakit, nmpenyakit from vaturan group by nmpenyakit having count(idgejala)='" & jmlh & "'", cn)

Using draturan as MySqlDataReader = comaturan.ExecuteReader()    
    If draturan.HasRows() Then
        Do While draturan.Read()
            idpenyakit = draturan.Item(0)
            nmpenyakit = draturan.Item(1)
            total = 0

            'mencari gejala per penyakit
            comaturan2 = New MySqlCommand("select idgejala from vaturan where idpenyakit='" & idpenyakit & "'", cn)

            Using draturan2 as MySqlDataReader = comaturan2.ExecuteReader()
                If draturan2.HasRows Then
                    Do While draturan2.Read()
                        idgejala = draturan2.Item(0)

                        comkonsul = New MySqlCommand("select * from ddiagnosa where iddiagnosa='" & idkonsul & "'and idgejala='" & idgejala & "'", cn)

                        Using drkonsul As MySqlDataReader = comkonsul.ExecuteReader()
                            If drkonsul.Read() Then
                                total = total + 1
                            End If
                        End Using

                        If total = jmlh Then
                            comkonsul = New MySqlCommand("insert into diagnosa values('" & idkonsul & "','" & Format(tanggal, "yyyy-MM-dd") & "','" & Textnama.Text & "','" & nmpenyakit & "')", cn)
                            comkonsul.ExecuteNonQuery()
                            'GoTo selesai    'Don't use GoTo
                        End If
                    Loop
                End If
            End Using
        Loop

        comkonsul = New MySqlCommand("insert into diagnosa values('" & idkonsul & "','" & Format(tanggal, "yyyy-MM-dd") & "','" & Textnama.Text & "','" & nmpenyakit & "')", cn)
        comkonsul.ExecuteNonQuery()
    Else
        MsgBox("Penyakit Tidak Ditemukan", , "Info")
        Return
    End If
End Using

它也会失败，因为您试图关闭阅读器，同时仍在阅读它。我已经draturan.close()从循环中删除了。comaturan2将需要使用另一个阅读器。

推荐阅读