find closest number database access

Question

1.00/5 (1 vote)

See more:

hello..
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 am sorry, If I am seeking help in simple thing or not able to explain it enough.

thank you very much.

[Edit - added code from comment]

VB

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
    conn.Open()
    cmd = New OleDbCommand("select*from table1 where value ='" & TextBox1.Text & "'", conn)
    rd = cmd.ExecuteReader
    rd.Read()
    If rd.HasRows Then
        TextBox2.Text = rd.Item("data")
    End If
    conn.Close()
End Sub

Posted 7-Feb-13 9:22am

Juragan minyak

Updated 9-Feb-13 12:38pm

CHill60

v2

Add a Solution

Comments

tiggerc 8-Feb-13 6:15am

Can you provide some code, I don't follow what you want.

Juragan minyak 8-Feb-13 21:17pm

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:

Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox1.TextChanged
conn.Open()
cmd = New OleDbCommand("select*from table1 where value ='" & TextBox1.Text & "'", conn)
rd = cmd.ExecuteReader
rd.Read()
If rd.HasRows Then
TextBox2.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 :
Select Top 1 * From MyTable Order By ABS(Value-@SearchValue), Value

or maybe you have another solution,please help

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2013-02-09T14:28:00

The code from the other article can be used like this:

SQL

cmd = New OleDbCommand("select top 1 * from table1 order by abs(value - @searchvalue), value", conn)
cmd.Parameters.Add(New OleDbParameter("@searchvalue", TextBox1.Text))
rd = cmd.ExecuteReader

What's happening here...
...The @searchvalue is a Parameter to the Command.
...In the SQL you're ordering the data to be returned based on the smallest difference between value and @searchvalue in ascending order and selecting only the first record - i.e. the smallest difference or closest number to @searchvalue.
...If there are two records equidistant from @searchvalue then the row with the highest value in value will be returned. If you want it to be the lowest then remove the , value from the order by.

This could have been written something like this ...

SQL

cmd = New OleDbCommand("select top 1 * from table1 order by abs(value - " & TextBox1.Text & ", value", conn)
rd = cmd.ExecuteReader

But it is always better to use Parameters than string concatenation (there are plenty of explanations of why out in google-land)

Note - this solution only works if value is a Number column (any Field Size) as per your question title, and there may be typing errors in the text above.