Populate ListView In VB.Net

Question

Code to show the data in two tables display in ListView (CODE WORKS PERFECTLY):

#Region "FillListView"
    Sub FillListview()
        LV.Items.Clear()
        myqry = "SELECT AccResult.StudNo,AccResult.CNumber,AccResult.FirstName,AccResult.LastName,AccResult.YrandSec,Exercises.Exer1,Exercises.Exer2,Exercises.Exer3,Exercises.Exer4,Exercises.Exer5 from AccResult INNER JOIN Exercises ON AccResult.StudNo = Exercises.StudNo ORDER BY AccResult.FirstName,AccResult.YrandSec Asc;"

        mycmd = New OleDbCommand(myqry, con)
        con.Open()
        mydr = mycmd.ExecuteReader
        While mydr.Read
            With LV
                .Items.Add(mydr("StudNo"))
                With .Items(.Items.Count - 1).SubItems
                    .Add(mydr("CNumber"))
                    .Add(mydr("FirstName"))
                    .Add(mydr("LastName"))
                    .Add(mydr("YrandSec"))
                    .Add(mydr("Exer1"))
                    .Add(mydr("Exer2"))
                    .Add(mydr("Exer3"))
                    .Add(mydr("Exer4"))
                    .Add(mydr("Exer5"))
                End With
            End With
        End While
        con.Close()
    End Sub
#End Region

Code for Populate ListView (ERROR):

Public Sub PopulateListView()
        Me.LV.Items.Clear()
        Dim OleDr As OleDb.OleDbDataReader
        OleDr = OleDa.SelectCommand.ExecuteReader() <-----< ERROR: The specified field '[StudNo]' could refer to more than one table listed in the FROM clause of your SQL statement.

        Do While OleDr.Read()
            Dim Item As New ListViewItem
            Item.Text = IIf(OleDr.IsDBNull(0), "", OleDr.Item(0))

            For shtCntr = 1 To OleDr.FieldCount() - 1
                If Not OleDr.IsDBNull(shtCntr) Then
                    Item.SubItems.Add(OleDr.Item("CNumber"))
                    Item.SubItems.Add(OleDr.Item("FirstName"))
                    Item.SubItems.Add(OleDr.Item("LastName"))
                    Item.SubItems.Add(OleDr.Item("YrandSec"))
                    Item.SubItems.Add(OleDr.Item("Exer1"))
                    Item.SubItems.Add(OleDr.Item("Exer2"))
                    Item.SubItems.Add(OleDr.Item("Exer3"))
                    Item.SubItems.Add(OleDr.Item("Exer4"))
                    Item.SubItems.Add(OleDr.Item("Exer5"))
                Else
                    Item.SubItems.Add("")

                End If
            Next shtCntr
            Me.LV.Items.Add(Item)
        Loop
    End Sub

Code for Search:

Private Sub BSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BSearch.Click
        If txtSearch.Text = "" Then
               MsgBox("Please enter keyword to search...", MsgBoxStyle.Information, "Keyword to search...")
            txtSearch.Focus()
            Exit Sub
        End If
        Call OpenConnection()
         With OleDa
            Call Initialized()
            .SelectCommand = New OleDb.OleDbCommand()
            .SelectCommand.CommandText = "SELECT * FROM [AccResult],[Exercises] WHERE StudNo Like '%%" & txtSearch.Text & "%%' or [YrandSec] Like '%%" & txtSearch.Text & "%%' or [LastName] Like '%%" & txtSearch.Text & "%%'" & _
        "Or [FirstName] Like '%%" & txtSearch.Text & "%%' or [Exer1] Like '%%" & txtSearch.Text & "%%' or [Exer2] Like '%%" & txtSearch.Text & "%%' or [Exer3] Like '%%" & txtSearch.Text & "%%'" & _
        "Or [Exer4] Like '%%" & txtSearch.Text & "%%' or [Exer5] Like '%%" & txtSearch.Text & "%%' ORDER By YrandSec, LastName ASC"
            .SelectCommand.Connection = OleCn

            Call PopulateListView()

             If Me.LV.Items.Count >= 1 Then
                 MsgBox(Me.LV.Items.Count & " Record(s) found for " & "( " & Me.txtSearch.Text & " )", MsgBoxStyle.OkOnly, "Record(s) found...")
            Else
                 MsgBox("No record(s) found for " & "( " & Me.txtSearch.Text & " )" & " , please try again... ", MsgBoxStyle.Critical, "No record found...")
                txtSearch.Focus()
                  txtSearch.SelectAll()
            End If
        End With
        Call CloseConnection()

    End Sub

How to populate listview after searching data in two data tables. It gives me error after searching. Thank You in advance.

Answer 1

It seems that both tables AccResult and Exercises contain a field named StudNo and when you refer to that field in the where statement the database cannot decide for you which field you are referring to.

To remove the problem prefix the field name StudNo with the name of the table like you have done in the code that works

Said that, please consider that your query, using string concatenation, is doomed to failure.
If a simple single quote is present in your search term the code will fail with a syntax error.
And then there is the big problem of Sql Injection

With OleDa
    Dim searchTerm = "%" & txtSearch.Text & "%"
    Call Initialized()
    .SelectCommand = New OleDb.OleDbCommand()
    .SelectCommand.CommandText = "SELECT * FROM AccResult INNER JOIN Exercises " & _ 
                                 "ON AccResult.StudNo = Exercises.StudNo " & _
                                 "WHERE AccResult.StudNo Like ? " & _ 
                                 "or [YrandSec] Like ? " & _
                                 "or [LastName] Like ? " & _
                                 "Or [FirstName] Like ? " & _ 
                                 "or [Exer1] Like ? " & _
                                 "or [Exer2] Like ? " & _
                                 "or [Exer3] Like ? " & _
                                 "Or [Exer4] Like ? " & _
                                 "or [Exer5] Like ? " & _ 
                                 "ORDER By YrandSec, LastName ASC"
    .SelectCommand.Parameters.AddWithValue("@p1", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p2", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p3", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p4", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p5", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p6", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p7", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p8", searchTerm)
    .SelectCommand.Parameters.AddWithValue("@p9", searchTerm)
    .SelectCommand.Connection = OleCn
    .......

In this version I have removed the ugly string concatenation and used a parameterized query. The command is more readable and the join of the wildcard char is done just one time. Unfortunately, OleDb cannot recognize the parameters by their names and so we need to add a parameter (@pX) for every placeholder (?) present in the query text