We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Find record using dataset vb.net

team2005
team2005 asked
on
Medium Priority
3,535 Views
Last Modified: 2013-12-25
Hi!

Include source code for seartch for records in a dataset.

My dataset is sorted By Firmanavn, and have a listbox with
all records in dataset.

And my dataadapter is olso sorted by Firmanavn.

But DV.Sort=("FirmaID") - Sort data by FirmaID and NOT Firmanavn.

I want to search for FirmaID (Text.box)....

How can i fix this...

Please help ASAP.

Thanks
Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("FirmaID")
        I = DV.Find(txtSearch_txt.Text)
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Comment
Watch Question

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
DataRows[] drFiltered = objLeverandorerDataSet.Tables("qryRSLeverandorer").Select("FirmaID = '" + textbox1.Text + "'");
if drFiltered.Length != 0 then you know you have the records filtered
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:

Author

Commented:
Hi!

Can you please show where to put your source code, in my code.
Thanks...

Tor
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("FirmaID")
       DV.RowFilter = "FirmaId = "' & txtSearch_txt.Text "'"

        If DV.Count > 0 Then
            ...
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Author

Commented:
Hi!

Your code dosent work, changed the code to (Attached code):
But it dosent find any records...?



Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("LeverandorID")
        DV.RowFilter = "LeverandorID = '" & txtSearch_txt.Text & "'"
 
        I = DV.Find(txtSearch_txt.Text)
        
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
why do you need  I = DV.Find(txtSearch_txt.Text) ???

Can you check the following:
Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("LeverandorID")
        DV.RowFilter = "LeverandorID = '" & txtSearch_txt.Text & "'"

Msgbox(dv.Count)

Author

Commented:
Hi!

It's returning 1

Need the Code - > I = DV.Find(txtSearch_txt.Text)
To get the recordnumber for dataset to show on screen.

How do i get the record returnd, to show on screen. ?
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
what ID do you need ? Is it a field in the database ?

Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("LeverandorID")
        DV.RowFilter = "LeverandorID = '" & txtSearch_txt.Text & "'"

      For each dr as DataRowView in dv
           i = dr("ID")
      next

Author

Commented:
Hi!

Have a dataset on my form.
That shows records from qryRSLeverandorer table.

The dataset is sorted bye Firmanavn (String)

Have a textbox, that i am using for search for LeverandorID in table qryRSLeverandorer

Then i want to display this found record on screen.

Hope you anderstand what i meen.

Project manager
CERTIFIED EXPERT
Commented:
In my mind, it's never a good idea to work with a generated order to retrieve the correct record. You should consider using an unique ID which will always be correct for the record, no mather how it's sorted on the screen....

If you really want to use something like your find, you 'll need to loop all your records because If I remember correctly, the find will only work on the key of the table....
Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("LeverandorID")
        DV.RowFilter = "LeverandorID = '" & txtSearch_txt.Text & "'"
        Dim I as integer  = 0
        Dim counter as integer = 0
        For Each drv As DataRowView in dv
           If CType(drv("LeverandorID"), Integer) = Ctype(txtSearch_txt.Text , Integer) then 
              I = counter
           End if
           counter = counter + 1
        Next
        
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
hI!

Now, its not find any record ?

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
what type of column is LeverandorID  ?


(oops, we also need to remove the dv.rowfilter)
Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("LeverandorID")
 
        Dim I as integer  = 0
        Dim counter as integer = 0
        For Each drv As DataRowView in dv
           If Ctype(drv("LeverandorID"), String) = txtSearch_txt.Text then 
              I = counter
           End if
           counter = counter + 1
        Next
        
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Author

Commented:
hI!

LeverandorID is the primery key.

What it must return, is the Position

Example:

Have a record in qryRSLeverandorer that are at position = 10 in the dataset
But the source, now return 83 ???


Author

Commented:
Hi !


Hmmmm, have sorted the dataset to "Firmanavn"
So maby its a sort problem ?
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
Example:

Have a record in qryRSLeverandorer that are at position = 10 in the dataset
But the source, now return 83 ???

--> That's the ristk I already mentioned :)

In my mind, it's never a good idea to work with a generated order to retrieve the correct record. You should consider using an unique ID which will always be correct for the record, no mather how it's sorted on the screen....
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
>> So maby its a sort problem ?

How are your records retrieved in the main form (where you want to show the data ?)

Author

Commented:
Hi!

LeverandorID = unique ID

My dataset and Listbox (Showing ALL records in dataset = Firmanavn - Sorted)
My scrren shows the data for record, selected from listbox.
All this is working....

Have this seek field (textbox), that i want to seek for a record = LeverandorID.
Example : Have a record that i know have LeverandorID = 100

Enter 100 in the textbox, and it must then show this data on screen.
Like i select this record from listbox.

Hope this explane thing a little better now :)

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
I never use a bindingcontext, that's why I didn't understand it completely.

Back to the beginning.
What do you get in your original code for:
I = DV.Find(txtSearch_txt.Text)
or
I = DV.Find(cint(txtSearch_txt.Text))
Dim DV As DataView
        Dim I As Integer
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
--        DV.Sort = ("FirmaID")
        I = DV.Find(txtSearch_txt.Text)
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Author

Commented:
Hi!

If i putt 100 in the textbox.
I = 68

Position 68 = LeverandoID=38

What is going on ?



Author

Commented:
Hi!

Tryed to change  DV.Sort = ("FirmaID")
To : DV.Sort = ("Firmanavn")

So the Listbox = sort on Firmanavn
And Dataset is sort on Firmanavn
And DV.Sort is sort on Firmanavn

Then its working 100%, but i dont want to seartch for Firmanavn
but FirmaID

So the problem is how to Sort on Firmanavn and NOT firmaID

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
I don't see the problem any more.

When you want to search for a position in your dataview, you must be sure that this order is the same as the bindingcontext (in your latest example you said that it's correct).
So what is the issue that is still hanging ? Don't you get the right record ?

Author

Commented:
Hi!

Have fixed it now, the problem was sorting.

I changed -> DV.Sort = ("LeverandorID") to -> DV.Sort = ("Firmanavn")

The included code works..

Thanks for helping me out.
Give you the points.


     Dim DV As DataView
        DV = New DataView(objLeverandorerDataSet.Tables("qryRSLeverandorer"))
        DV.Sort = ("Firmanavn")
        '   DV.RowFilter = "LeverandorID = '" & txtSearch_txt.Text & "'"
        Dim I As Integer = 0
        Dim counter As Integer = 0
        For Each drv As DataRowView In DV
            If CType(drv("LeverandorID"), Integer) = CType(txtSearch_txt.Text, Integer) Then
                I = counter
            End If
            counter = counter + 1
        Next
 
        If I > 0 Then
            Me.BindingContext(objLeverandorerDataSet, "qryRSLeverandorer").Position = I
            Me.objLeverandorerDataSet_PositionChanged()
        Else
            MsgBox("Ingen Leverandør finnes med ID nummer : " & txtSearch_txt.Text, MsgBoxStyle.Information)
        End If

Open in new window

Author

Commented:
Thanks
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.