locked
Finding a Record Or searching for a record in a dataset

    Question

  • Hello

    I am trying to search for records in a dataset using the primary key as the unique field

    I used this code's format, from the Visual Studio 2008 Documentation:

    Dim customersRow As NorthwindDataSet.CustomersRow
    customersRow = NorthwindDataSet1.Customers.FindByCustomerID("ALFKI")


    So I guess the row that is found with the primary key value being ALFKI is assigned to customersRow

    What I tried doing first is since customersRow kind of has the data on the row with the primary key being ALFKI, I guess customersRow.CustomerID = will give the data at the Customer ID column of that record which is ALFKI
    customersRow.CustomerName = will give the data at the Customer Name column of that record

    Hence I tried placing these values in textboxes on the form to act like a search and found record where the search was ALFKI and the found record is in the textboxes in this way:

    Me.textbox1.text = customersRow.customerID
    Me.textbox2.text = customersRow.customerName

    It worked when I compiled it the first time but for some reason it's giving me an error to do with a nullhandled exception...during the debugging time, I placed the cursor on the customersRow at this side of the code:



    customersRow = NorthwindDataSet1.Customers.FindByCustomerID("ALFKI")

    and the display was customersRow = "Nothing" so I guess thats way it may be from, meaning there is no data with that primary key?????? I'm I right or wrong?


    ------------------------------------------------------
    Ok so then this is what came up next, I linked the textboxes to the dataset Customers to display the records in there, and then I wanted to perform this search again so that what I placed in the primary key would be displayed...

    What I want to do is either search or filter out the records from the dataset or database and edit that specific record....


    Any help.... Northwind and Customers are not my example but I'm using them to explain what I'm doing...

    Thanks...

    Monday, August 31, 2009 12:20 PM

Answers

  • Hi DeborahK,

    I'm kind of new to visual basic and infact 'good' programming too as a whole but I found out a solution to my problem after constant 'playing around' to see what would work .

    The is what I was doing: Displaying records through a binding source and I wanted a particular record to be displayed that can be found from the source that the binding source had been linked to, but would be based on a text placed in another textbox and would be picked up by the binding source using the primary key as the 'point of contact'.

    I found a solution in the help of the Visual Studio 2008 Documentation and that was the example I posted earlier:

    Dim customersRow As NorthwindDataSet.CustomersRow
    customersRow = NorthwindDataSet1.Customers.FindByCustomerID("ALFKI")


    Mine was something like this:

    Dim userRow As DBaseDataSet.UserRow
    userRow = DBaseDataSet.Users.FindByuserID(Me.textbox1.text)

    ----------------------------------------------
    The code allows a record to be found based on what is in the bracket where in mine is Me.textbox1.text, meaning it would pick up the text inside textbox1.


    My table: Users   

    And the field I'm checking with, which is the primary key: userID

    Where I wanted the relating to be done was a text box: Me.textbox1

    Variable holding found data: userRow


    So I guess it works by checking the value in Me.textbox1.text and checks for the same value in userID. If there is, it stores the record in userRow else will send a null.

    ----------------------------------------------

    I did the above code but how to let the binding source know that the record that I have found from the code is what I wanted displayed was my problem but some way some how I managed to.

    The code I just had to add was, the data source of the bindingsource should be my variable holding the found record which is userRow, since userRow had all the fields and their data on the record found.


    So the solution was:

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    Dim userRow As DBaseDataSet.UserRow
    userRow = DBaseDataSet.Users.FindByuserID(Me.textbox1.text)

    'plus

    Me.UsersBindingSource.DataSource = userRow

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    And that was it, my records being displayed via textboxes had the records changing straight to the found record based on what primary key value was equal to the one in the textbox1.


    Of course the code has to be placed in a code that runs an event, whether a button being clicked or the form being loaded. So I hope this helps anyone who may come across this problem too..... :)

    Thanks all the same DeboarahK



    I think this can help for anyone trying to work on a search feature on a form. Try it out.
    • Marked as answer by Stripes3 Tuesday, September 01, 2009 11:59 AM
    Tuesday, September 01, 2009 11:58 AM

All replies

  • Yes, it sounds like you don't have a customer in the database with a key of ALFKI. Either that, or the FindByCustomerId is not set up correctly.

    If you are expecting to have the situation where data is not found, you will want an IF around your other code:

    If customersRow isNot nothing then ...


    Hope this helps.

    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Monday, August 31, 2009 2:40 PM
  • Hi DeborahK,

    I'm kind of new to visual basic and infact 'good' programming too as a whole but I found out a solution to my problem after constant 'playing around' to see what would work .

    The is what I was doing: Displaying records through a binding source and I wanted a particular record to be displayed that can be found from the source that the binding source had been linked to, but would be based on a text placed in another textbox and would be picked up by the binding source using the primary key as the 'point of contact'.

    I found a solution in the help of the Visual Studio 2008 Documentation and that was the example I posted earlier:

    Dim customersRow As NorthwindDataSet.CustomersRow
    customersRow = NorthwindDataSet1.Customers.FindByCustomerID("ALFKI")


    Mine was something like this:

    Dim userRow As DBaseDataSet.UserRow
    userRow = DBaseDataSet.Users.FindByuserID(Me.textbox1.text)

    ----------------------------------------------
    The code allows a record to be found based on what is in the bracket where in mine is Me.textbox1.text, meaning it would pick up the text inside textbox1.


    My table: Users   

    And the field I'm checking with, which is the primary key: userID

    Where I wanted the relating to be done was a text box: Me.textbox1

    Variable holding found data: userRow


    So I guess it works by checking the value in Me.textbox1.text and checks for the same value in userID. If there is, it stores the record in userRow else will send a null.

    ----------------------------------------------

    I did the above code but how to let the binding source know that the record that I have found from the code is what I wanted displayed was my problem but some way some how I managed to.

    The code I just had to add was, the data source of the bindingsource should be my variable holding the found record which is userRow, since userRow had all the fields and their data on the record found.


    So the solution was:

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    Dim userRow As DBaseDataSet.UserRow
    userRow = DBaseDataSet.Users.FindByuserID(Me.textbox1.text)

    'plus

    Me.UsersBindingSource.DataSource = userRow

    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

    And that was it, my records being displayed via textboxes had the records changing straight to the found record based on what primary key value was equal to the one in the textbox1.


    Of course the code has to be placed in a code that runs an event, whether a button being clicked or the form being loaded. So I hope this helps anyone who may come across this problem too..... :)

    Thanks all the same DeboarahK



    I think this can help for anyone trying to work on a search feature on a form. Try it out.
    • Marked as answer by Stripes3 Tuesday, September 01, 2009 11:59 AM
    Tuesday, September 01, 2009 11:58 AM