database query

[brews]

Whenever I query my database and the db returns more than one record such as a search on lastname might do, the query returns the right number of records but it is three of the same record. Tried to convert a vb6 file/program to vs 2008.

Here is what I have so far:
The Query

        Dim cm As New List(Of CMember)
        If Not dataMgr.Find(sName, cm) Then
                MessageBox.Show("Could not find '" & sName & "'.", "No Record", MessageBoxButtons.OK)
                Exit Sub
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        Return False
    End Function

            For Each Me.nMember In cm
                With frmSearch.lstDisplay
                    .Items.Add(IIf(nMember.MaidenName <> "", _
                            nMember.LastName & ", " & nMember.FirstName & " (" _
                            & nMember.MaidenName & ")", nMember.LastName & ", " _
                            & nMember.FirstName))
                End With
            Next nMember


    Public Function Find(ByVal sSearch As String, ByVal cm As List(Of CMember)) As Boolean
        da = New OleDbDataAdapter
        Dim dr As DataRow
        ds = New DataSet
        Dim table As New DataTable
        If cn.State = ConnectionState.Closed Then cn.Open()
        cmd = New OleDbCommand("SELECT * FROM Members" & _
                               " WHERE LastName Like'" & sSearch & "%' OR " & _
                               " MaidenName LIKE'" & sSearch & "%'", cn)
        da.SelectCommand = cmd
        da.Fill(ds, "Members")
        Try
            nMember = New CMember
            For Each dr In ds.Tables("Members").Rows

                With nMember
                    .MemberID = CInt(dr("MemberID"))
                    .LastName = dr("Lastname").ToString
                    .FirstName = dr("FirstName").ToString
                    cm.Add(nMember)
                    cn.Close()
                End With
            Next
            If nMember.LastName <> "" Then
                Return True
            End If

            For Each Me.nMember In cm 'checking here shows 3 of the same record.
                With frmSearch.lstDisplay
                    .Items.Add(IIf(nMember.MaidenName <> "", _
                            nMember.LastName & ", " & nMember.FirstName & " (" _
                            & nMember.MaidenName & ")", nMember.LastName & ", " _
                            & nMember.FirstName))
                End With
            Next nMember

Thanks for the help.

 

[SkipVought]

hi,

Your question seems to have nothing to do with .net or VB6 or any other programming language. It is a question regarding a query in your database, that reflects the data that is in your Menbers table.

I would assign a string for your SQL code and then print the string contents to see exactly what SQL you are executing.

I would run that sql string directly in your database management system query editor, to observe exactly what is returned. If you still do not get the results you expect, then you must change your SQL code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[brews]

Thanks for the reply. The problem was the placement of the instantiation of cMember. It should be inside the For each loop.