更改的Vb.net列表框索引显示sqlite数据库中的错误值 [英] Vb.net listbox index changed shows wrong value from sqlite database

问题描述

大家好，所有

我正在编写一个vb.net程序，它使用sq lite作为数据库。我的问题是表单加载它时显示从特定列（我的情况下的问题）的所有值从数据库到列表框。然后，如果点击任何项目（列表框索引更改），则数据库（答案）中的相应值将显示在富文本框中。这可以按预期工作。

当用户在文本框中键入内容并单击搜索按钮时，预计会根据文本框文本将数据库中的问题显示为同一列表框中的标记列。这也很好....问题开始时

列表框索引现在改变...如果搜索结果只是列表框中的一个项目，索引将为零，富文本框显示第一个项目数据库中包含列表框项的对应值。

请帮我解决这个问题。

我的代码在这里供您考虑： -



我的尝试：

Hi, all
I amcoding a vb.net program, which using sq lite as database. My problem is when form loading it displaying all values from particular column ( questions in my case) from database to listbox. Then if clicks on any item ( listbox index change), the corresponding value from database ( answer) displays in rich text box. Upto this works as expected.
when user types something in textbox and click search button, it is expected to show the questions from database according to textbox text as tag column in database to the same listbox. this also works fine....The problem begins when
listbox index changes now...if the search result is only one item in listbox, the index will be zero and rich textbox shows the first item of database insted of corresponding value of listbox item.
please help me to resolve this problem.
My code is here for your kind consideration:-

What I have tried:

<pre>
Sub showData()'''this shows questions when form loads
        connect()
        Dim da As New SQLiteDataAdapter("select * from elect", connection)
        'Dim dt As New DataTable
        Dim ds As New DataSet
        da.Fill(ds, "elect")
        Dim mySelectQuery As String = "select * from elect"
        Dim sqConnection As New SQLiteConnection(connection)
        Dim sqCommand As New SQLiteCommand(mySelectQuery, sqConnection)
        'sqConnection.Open()
        Try
            Dim sqReader As SQLiteDataReader = sqCommand.ExecuteReader()

            ' Always call Read before accessing data.
            Do While sqReader.Read()
                Dim sName = sqReader.Item("question")

                ListBox1.Items.Add(sName)
            Loop

            ' always call Close when done reading.
            sqReader.Close()

            ' Close the connection when done with it.
        Finally
            connection.Close()
        End Try


    End Sub

  Public Sub NavigateRecords()
        page1.Clear()''page is rich text box
       

        connect()
        Dim da As New SQLiteDataAdapter("select * from elect", connection)
        'Dim dt As New DataTable
        Dim ds As New DataSet
        da.Fill(ds, "elect")
       
        Dim mySelectQuery As String
     
        mySelectQuery = "select * from elect"

        Dim sqConnection As New SQLiteConnection(connection)
        Dim sqCommand As New SQLiteCommand(mySelectQuery, sqConnection)
        Dim num As Integer = Me.inc = Conversions.ToInteger(ListBox1.SelectedIndices.ToString)
        MsgBox(num)
        Me.inc = Conversions.ToInteger(ListBox1.SelectedIndex.ToString)
        If (Me.inc > -1) Then
          
                Dim ans As String = Conversions.ToString(ds.Tables.Item("elect").Rows.Item(Me.inc).Item(2))

                page1.Text = (ans)
 End If
  End Sub

  
Private Sub search()
        connect()
        Dim da As New SQLiteDataAdapter("select * from elect", connection)
        'Dim dt As New DataTable
        Dim ds As New DataSet
        da.Fill(ds, "elect")
        Dim mySelectQuery As String = ("select * from elect WHERE tag like'%" & txtSearch.Text & "%' ")
        Dim sqConnection As New SQLiteConnection(connection)
        Dim sqCommand As New SQLiteCommand(mySelectQuery, sqConnection)
        Try
            Dim sqReader As SQLiteDataReader = sqCommand.ExecuteReader()

            ' Always call Read before accessing data.
            Do While sqReader.Read()
                Dim sName = sqReader.Item("question")

                ListBox1.Items.Add(sName)
            Loop
            If ListBox1.Items.Count = 0 Then
                MsgBox("Nothing Found ")
                
                showData()
      

               


            End If
            ' always call Close when done reading.
            sqReader.Close()

            ' Close the connection when done with it.
        Finally
            connection.Close()
        End Try
    End Sub



 Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles ListBox1.SelectedIndexChanged
        page1.Visible = True
        
        ListBox1.Visible = False
       
        Label1.Visible = False
        
            NavigateRecords()



    End Sub

推荐答案

您的解决方案完全解耦了列表框项从数据库表中，您无法通过选定的索引标识所需的数据库记录。更好：创建一个简单的类，其中包含主键和用于在列表框中显示的字段。覆盖该类的ToString方法以返回diplay文本。然后为表中的每个记录创建该类的实例，并将此对象作为项添加到列表框中。制作selectioin时，将selecteditem强制转换为类类型并读取主键。然后在数据库表中查询该键，该键始终返回一个匹配的记录。这将解决您的所有问题。一个简单的例子，假设您的主键是Guid（对于int将ID属性类型更改为Integer）：

Your solution completely discouples the listbox items from the database table so you can't identifiy the required database record by selected index. Better: create a simple class that holds the primary key and the field used for display in the listbox. Overwrite the ToString method of that class to return the diplay text. Then create an instance of that class for every record in your table and add this object as item to your listbox. When selectioin is made, cast the selecteditem back to the class type and read the primary key. Then query your database table for that key which always return exactly the one matching record. This will solve all your problems. A quick example, assuming your primary key is Guid (for int change the ID property type to Integer):

Private Class clsListBoxItem
    Public Property ID As Guid = Nothing
    Private Name As String = String.Empty

    Public Sub New(ByVal PK As Guid, ByVal DisplayName As String)
        ID = PK
        Name = DisplayName
    End Sub

    Public Override Function ToString() As String
       Return Name
    End Function
End Class

填写ListBox：

Fill the ListBox:

MyListBox.Items.Clear

   'Fill the reader object here
  
   If Reader.HasRows
      Do While Reader.Read()
         Dim objItem As New clsListBoxItem(Reader.Item("ID"), _ Reader.Item("Question")

         MyListBox.Itms.Add(objItem)
      Loop
   End If

'获取所选项目：

'Get the selected item:

Dim objItem As clsListBoxItem = TryCast(MyListBox.SelectedItem, clsListBoxItem)

   Dim PK As Guid = objItem.ID

   Dim strSELECTCmd = "SELECT * FROM elect WHERE (ID = @ID)

'在这里设置SQL参数并执行查询....



祝你好运！

'Set SQL parameter here and execute the query....

Good luck!

这篇关于更改的Vb.net列表框索引显示sqlite数据库中的错误值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！