Vb.net listbox index changed shows wrong value from sqlite database

Question

0.00/5 (No votes)

See more:

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:

VB

<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

Posted 11-Apr-17 5:20am

JKCNAIR

Updated 11-Apr-17 10:22am

v2

Add a Solution

Comments

Richard Deeming 11-Apr-17 11:29am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

NightWizzard · Accepted Answer · 2017-04-11T10:22:00

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):

VB

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

Fill the ListBox:

VB

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:

VB

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

   Dim PK As Guid = objItem.ID

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

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

Good luck!