Results 1 to 23 of 23

Thread: [RESOLVED] Display specific row from a table

  1. #1

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Resolved [RESOLVED] Display specific row from a table

    I have a form that displays data from a specific table from a data set. I want the form to display the data from a specific row in that table. I have manipulated the table adapter, the data set and the table in every way I can think of to no avail. How do I get the table to display that specific row?

  2. #2
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Can you please explain your set up further and show us you code ?

  3. #3

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    Sure. I have a form that has text boxes to display a set of fields on the form. I can change the data in the fields and save the data if I desire. In the load sub I use the following code to fill the form:

    Me.TblFMFileMasterBaseTableAdapter.Fill(Me._MasterBase_03DataSet.tblFMFileMasterBase)

    This puts the data from the first row into the form. What I want to do is display a specific row instead of the first row.

    Also, I use this form elsewhere and have a binding navigator for that usage. However, for this usage I have set the visible property for the binding navigator to false, since I only want the one record to display.
    Last edited by gwboolean; Feb 26th, 2015 at 12:37 PM. Reason: Left out

  4. #4
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Display specific row from a table

    If you have a BindingSource bound to the DataTable then you'd set the Filter property to the correct filter. The correct filter all depends on the column name, how you wish to compare the data, and the value being compared. For example, if I had the following DataTable:
    ID First Name Last Name
    1 John Doe
    2 Jane Doe
    3 David Day
    4 John McIlhinney
    5 Jacob Roman

    And if I wanted to return all rows where the last name column is Doe then the BindingSource's filter would be:
    Code:
    BindingSource.Filter = "Last Name = 'Doe'"
    Notice the single quotes around the search value, this is important.
    Last edited by dday9; Feb 26th, 2015 at 12:45 PM. Reason: Forgot the ending "
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  5. #5
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Code:
     Me._MasterBase_03DataSet.tblFMFileMasterBase.rows(0).item ("yourcolumnname").value
    The above will give you a value for each specified column from your dataset by replacing your column name with the name of the column you wish to get the value from

    Then simply set your text box. Text property equal to this value

  6. #6

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    I used both methods suggested above and got an unhandled exception error. I assume that the value in the quotes (both methods) is the table column name and not the text box name? I already have the value for the row held in a variable so all I want to do is plug that value into the code and display that record. I apparently am still not getting it.

  7. #7
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Can you show the line in which the exception occurs?

  8. #8
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Display specific row from a table

    I used both methods suggested above and got an unhandled exception error.
    You would have to change "BindingSource" with the actual name of the BindingSource.

    I assume that the value in the quotes (both methods) is the table column name and not the text box name?
    In my example, the value of the filter should be in the following format:
    Code:
    Column_Name Comparison Value
    So if you had a TextBox that was representing your value then the code would need to be tweeked:
    Code:
    BindingSource.Filter = String.Format("{0} = '{1}'", "ColumnName", TextBox.Text)
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  9. #9

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    Sure. I tried both of the following (individually) and in each case they threw the same exception. The second one, seems to me, that it would provide the value of the row, which I already have. What I want is to take the ROWNUM value and use it to display the record in that row instead of the first record in the table.

    Me.TblFMFileMasterBaseBindingSource.Filter = "intFileID = ROWNUM"

    Me._MasterBase_03DataSet.tblFMFileMasterBase.Rows(ROWNUM).Item("intFileID").Value()

    I am about to take a shot at the binding source filter

  10. #10
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Your code takes the value your giving and gives it back to you ? You've filtered by the itnfileid column and then returned the value of that column so we you find your exception you will still not have exactly what you want I would presume ?

  11. #11
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    However your error is that you have included your variable within the speech marks so it won't take the value of your variable it will treat it as a string which cannot be used to filter an numeric field which is the cause of your exception

  12. #12

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    I don't think so. What I have when the form loads is the first record on display. What I want to display is record at ROWNUM

  13. #13
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Use the last code snippet from dday this should stop your exception occurring

  14. #14

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    OK, Here is what I did

    In the form load sub I have the following code:

    Me.TblFMFileMasterBaseTableAdapter.Fill(Me._MasterBase_03DataSet.tblFMFileMasterBase)
    Me.TblFMFileMasterBaseBindingSource.Filter = "intFileID" = ROWNUM


    And here is the exception that is thrown on the second line above:

    An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

    Additional information: Conversion from string "intFileID" to type 'Double' is not valid.


    intFileID is an integer field from the table and ROWNUM is an integer variable set to a specific integer value. Where does type double come into this?

  15. #15
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Use the last code snippet from dday this should stop your exception occurring

  16. #16

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    OK, here is what I did with the code

    Me.TblFMFileMasterBaseBindingSource.Filter = String.Format("{0} = {1}", "intFileID", txtFileID.Text)


    What I got was the record from the first row is displayed. intFileID is the field in the table and txtFileID is the text box on the form. I then changed the values in the squiggle parenthesis, but got an error for my effort. Where in there do I define the row that I want to display the record for?

  17. #17
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Where it says rows() you place the row number in between the brackets

  18. #18

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    ok, i placed the following code snippets in the form load event

    Me.TblFMFileMasterBaseTableAdapter.Fill(Me._MasterBase_03DataSet.tblFMFileMasterBase)
    Me.TblFMFileMasterBaseBindingSource.Filter = "intFileID = 5"


    So then the data for record number 5 is displayed. However, if I use the integer variable ROWNUM in the place of the integer value, I get the following exception:

    'System.Data.EvaluateException' occurred in System.Data.dll

    Additional information: Cannot find column [ROWNUM].


    Now why will the integer variable ROWNUM work instead of an actual integer value? I am getting a headache.

  19. #19
    Hyperactive Member
    Join Date
    Sep 2014
    Posts
    404

    Re: Display specific row from a table

    Where it says rows() you place the row number in between the brackets

  20. #20
    Super Moderator dday9's Avatar
    Join Date
    Mar 2011
    Location
    South Louisiana
    Posts
    11,715

    Re: Display specific row from a table

    Take a look at this example and see if you're able to get a better understanding of how the filter property works:
    Code:
    Public Class Form1
    
    	Dim bs As BindingSource
    
    	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
    		'Create the DataTable and add 3 columns
    		'ID: Int32
    		'First_Name: String
    		'Last_Name: String
    		Dim dt As DataTable = New DataTable("MyTable")
    		dt.Columns.AddRange({New DataColumn("ID", GetType(Integer)), New DataColumn("First_Name"), New DataColumn("Last_Name")})
    
    		'Create 3 new DataRows from the DataTable
    		'Set their cell values
    		'Add them to the DataTable
    		Dim row1, row2, row3 As DataRow
    		row1 = dt.NewRow()
    		row1(0) = 1
    		row1(1) = "John"
    		row1(2) = "Doe"
    		dt.Rows.Add(row1)
    
    		row2 = dt.NewRow()
    		row1(0) = 2
    		row1(1) = "Jane"
    		row1(2) = "Doe"
    		dt.Rows.Add(row2)
    
    		row3 = dt.NewRow()
    		row1(0) = 3
    		row1(1) = "David"
    		row1(2) = "Day"
    		dt.Rows.Add(row3)
    
    		'Create a BindingSource and set it's DataSource as the DataTable
    		bs = New BindingSource With {.DataSource = dt}
    
    		'Create a DataGridView and set it's DataSource to the BindingSource
    		Dim dgv As DataGridView = New DataGridView With {.DataSource = bs, .Dock = Fill}
    
    		'Create a TextBox. This will be the value we want to filter.
    		Dim txtFilter As TextBox = New TextBox With {.Dock = Top}
    		
    		'Add the Controls to the Form
    		Me.Controls.AddRange({txtFilter, dgv})
    		
    		'Generate the TextChanged event for the TextBox
    		AddHandler txtFilter.TextChanged, AddressOf txtFilter_TextChanged
    		
    	End Sub
    
    	Private Sub txtFilter_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
    		'Get the TextBox
    		Dim txtFilter As TextBox = DirectCast(sender, TextBox)
    		
    		'Check if the value is not empty
    		If Not String.IsNullOrWhiteSpace(txtFilter.Text) Then
    			'Set the filter to the text and compare it to the Last_Name columns
    			bs.Filter = String.Format("Last_Name = '{0}'", txtFilter.Text)
    		End If
    
    	End Sub
    
    End Class
    "Code is like humor. When you have to explain it, it is bad." - Cory House
    VbLessons | Code Tags | Sword of Fury - Jameram

  21. #21

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    You have me confused Leary. It does not say rows() in there anywhere. Which code snippet are you referring to?

    I used the one in the form load event from DDay that goes:

    Me.TblFMFileMasterBaseBindingSource.Filter = "intFileID = 5"

    and it opened the form displaying the record from row #5. However, when I substituted the integer variable ROWNUM for the integer value 5 I got the following error:

    System.Data.EvaluateException' occurred in System.Data.dll

    Additional information: Cannot find column [ROWNUM].

    So the only question is how can I get the number that I want into that code snippet since I cannot seem to use the integer variable ROWNUM

  22. #22
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,289

    Re: Display specific row from a table

    Quote Originally Posted by gwboolean View Post
    ok, i placed the following code snippets in the form load event

    Me.TblFMFileMasterBaseTableAdapter.Fill(Me._MasterBase_03DataSet.tblFMFileMasterBase)
    Me.TblFMFileMasterBaseBindingSource.Filter = "intFileID = 5"


    So then the data for record number 5 is displayed. However, if I use the integer variable ROWNUM in the place of the integer value, I get the following exception:

    'System.Data.EvaluateException' occurred in System.Data.dll

    Additional information: Cannot find column [ROWNUM].


    Now why will the integer variable ROWNUM work instead of an actual integer value? I am getting a headache.
    If you know the index of the row you want to display, simply set the bindingsource.position property. For example, if you want to display row#5 which has the index of 4 (because indexes are zero-based), you'd do:
    Code:
    Me.TblFMFileMasterBaseBindingSource.Position= 4
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  23. #23

    Thread Starter
    Fanatic Member
    Join Date
    Dec 2011
    Location
    Oregon City, Oregon
    Posts
    703

    Re: Display specific row from a table

    Perfect! I did have to reduce the variable by 1, but that gets the job done. Obviously I need to learn how to explain myself and what I am trying to do better in the future. Thanks a lot!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width