CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Question find nearest value database access

    hello all..
    great community with great people..

    I have problems with the communication between VB.net and database access 2003.

    How do I find closest/nearest value or on the form 1 (value.text) with data on form 2 (value of datagridview (access)).
    Matching results are stored in the form of 1 (result.text) .. stored in the form 1 (result.text) ..
    I attachment a picture of sample form but without program code..

    I am sorry, If I am seeking help in simple thing or not able to explain it enough.

    thank you very much.
    Name:  2.JPG
Views: 1425
Size:  45.1 KB

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: find nearest value database access

    You would have to loop through your datagrid and check each value in the given column and compare to the target value until you either find an exact match or tested them all. If the grid is sorted on that column then you could avoid testing them all with the proper logic.

    I would suggest creating a public function on form2 that takes an input parameter and uses that to search the grid then returns an array to pass the data back to the other form.
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Jan 2013
    Posts
    6

    Re: find nearest value database access

    Thanks @DataMiser
    Always use tags when posting code.
    the code is not visible in this reply
    Last edited by Jamil Nur M; February 7th, 2013 at 03:09 PM.

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: find nearest value database access

    There was no code in my reply... that part about code tags is in my signature just to remind people to use the code tags when they post code.
    Always use [code][/code] tags when posting code.

  5. #5
    Join Date
    Jan 2013
    Posts
    6

    Re: find nearest value database access

    I'm sorry...

    this is my simple code, this is not in accordance with what is desired because the code is to look exactly the same value, not the closest value

    code:
    Code:
        Private Sub TextBox4_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox4.TextChanged
            conn.Open()
            cmd = New OleDbCommand("select*from data1 where value='" & TextBox4.Text & "'", conn)
            rd = cmd.ExecuteReader
            rd.Read()
            If rd.HasRows Then
                TextBox3.Text = rd.Item("data")
            End If
            conn.Close()
        End Sub
    how about this code, I get this code from another article but I do not know to apply. Where I have to put it :
    Code:
    Select Top 1 * From data1 Order By ABS(Value-@SearchValue), Value
    I have put in a few places but it always comes up a warning message
    or maybe you have another solution,please help
    Thanks
    Last edited by Jamil Nur M; February 8th, 2013 at 09:48 PM.

  6. #6
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: find nearest value database access

    You need to have a temp variable to store your result and another to hold what you are searching for.
    You then would loop through your grid checking each item against your search value and your temp var to see if the current item is closer to your search value than what is in temp var. If it is closer then replace the value in temp var with the current value from the grid and continue moving through the grid until you get to the end.

    If the grid is sorted on that column then you could stop sooner. Once you get close then if you see the next value is farther away that the one you have then you would know that you have the one that would be closest. If it is not sorted then there is no way to tell until you test them all.

    The exception would be if one were an exact match you would accept that one and exit the loop since it can't possibly be any closer.
    Always use [code][/code] tags when posting code.

  7. #7
    Join Date
    Jan 2013
    Posts
    6

    Re: find nearest value database access

    thank you very much for your attention and solutions...
    forgive me if I was too hard to understand
    I am new in the database and vb

    is it possible the code looks like this?
    Code:
    Dim mileAge = Int32.Parse(Miles.Text) 
    Dim rate = 0
    For Each row In _test_2DataSet.test
        If mileAge <= row.MILESTO Then
            rate = row.TRUCKLOADRATE
            Exit For
        End If
    Next
    If rate <> 0 Then
        TxtRate.Text = rate.ToString
    End If

  8. #8
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: find nearest value database access

    It would appear that the code you posted is not checking to see if the value is close just if it is < or = and then when it is it quits looking. You need to make it a bit more complex than that checking each value and comparing the difference between that value and your search value and then comparing that difference to the closest you have found so far then only when you have found one that is = to the search value or checked them all can you know that you have the one that is closest
    Always use [code][/code] tags when posting code.

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

Featured