Not able to search for a entry with duplicate values

Question

Hi i have a database where my duplicated data (serial no) are entered. Like

Serial Nos  Chalan-no

Abcd1234    VS/12-13/T-S/187

Abcd1234    VS/12-13/T-S/187

Xyz11234    VS/12-13/T-S/130

Xyz11234    VS/12-13/T-S/174

In this if I search for abcd1234 it will give me chalans-no VS/12-13/T-S/187 for both entries. It's OK, but if I search for xyz11234 it gives me chalans-no VS/12-13/T-S/130 only, not xyz11234 & VS/12-13/T-S/174 as it checks for serial no and gives its first occurrence. But what if I want xyz11234 & VS/12-13/T-S/174?

I am Using following code to search for Serial No and retrieve chalan_no for that:---

   Private Sub cmbSn_no_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmbSn_no.SelectedIndexChanged
    'code to fill all details in text boxes when a Sr.No is selected in combobox

    Dim strSelVen As String = ("SELECT * FROM Duplicate_srno where sr_no= @srno")
    Dim comm_SelVen As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSelVen, cnnOLEDB)
    comm_SelVen.Parameters.AddWithValue("@srno", cmbSn_no.Text)
    cnnOLEDB.Open()

    Dim dr As OleDb.OleDbDataReader = comm_SelVen.ExecuteReader

    If dr.Read = True Then

        cmbSn_no.Text = dr("Sr_no")
        cmbChal_no.Text = dr("chalan_no")

    End If
    cnnOLEDB.Close()
    strsrno = cmbSn_no.Text
    strchlno = cmbChal_no.Text
End Sub

Please suggest me what to do.

score 1 · Accepted Answer · answered Apr 15 '13 at 11:01

1

Your code If dr.Read = True Then ... End If only does the .Read once, so you only retrieve one row. Try something like Do While dr.read ... Loop.

answered Apr 15 '13 at 11:01

Gord Thompson

116,920
32
215
418

Thank You @Gord.. Its working Partly...if I have abcd1234---VS/12-13/T-S/186 and abcd1234---VS/12-13/T-S/187....it moves from VS/12-13/T-S/186 to VS/12-13/T-S/187 but not reverse.... If i select the second one and then go back to first one its not working...Code :--- ` Do While dr.Read = True cmbSn_no.Text = dr("Sr_no") cmbChal_no.Text = dr("chalan_no") Loop ` Please resolve my problem.. Thank You... – Harabati Apr 16 '13 at 10:00
@Harabati It looks like you're trying to add items to a combo box list. If so, then you'll need to use the appropriate `.method` to accomplish that. In VBA that would be `cmb_Sn_no.AddItem`; I'm sure it is something quite similar in VB.NET. (Right now you're probably just overwriting the `cmbSn_no.Text` property on each iteration of the loop.) – Gord Thompson Apr 16 '13 at 10:14

Felix C · Answer 2 · 2013-04-15T10:26:28.020

0

I assume you have following class:

Public Class Data
    Public Property SerialNumber As String
    Public Property ChalanNumber As String
End Class

The Table displaying the values in your question is using a List(Of Data):

Dim myList As New List(Of Data)()

Then the operation should be:

Dim selectedData = myList.Where(Function(e) e.SerialNumber = "xyz11234")

edited Apr 15 '13 at 10:26

answered Apr 15 '13 at 08:16

Felix C

1,755
5
26
40

Thank you @Felix. I am using the above code,..... I have just edited my post and added the code i am using..please check and suggest me .. Thank you – Harabati Apr 15 '13 at 09:06
I am sorry, don't know how to work with `OleDbDataReader`. But I guess your problem is, that you just display the first result of your query. You have to get a collection back from your SQL command. Maybe this link will help you: http://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt – Felix C Apr 15 '13 at 10:29

score 0 · Answer 3 · answered Apr 15 '13 at 18:07

0

I haven't used an OleDBDataReader but if it's looks like it's only getting a single reader. Try the looping method.

Is there a way to read the entire table at once? In the SQL world it would be SqlDataAdapter.fill.

answered Apr 15 '13 at 18:07

Jeff B

535
1
6
15

Not able to search for a entry with duplicate values

3 Answers3