找到最接近的号码数据库访 [英] find closest number database access

问题描述

你好..

很棒的社区和很棒的人..



我在VB.net和数据库访问之间的通信有问题2003.



我如何在表格2上找到最接近/最接近的值或表格1（value.text）上的

数据（ datagridview（access）的值。

匹配结果以1（result.text）的形式存储..存储在表单1（result.text）中..





对不起，如果我在简单的事情上寻求帮助或者无法解释它。



非常感谢。

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.

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

推荐答案

其他文章的代码可以像这样使用：

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

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

这里发生了什么... < br $>
... @searchvalue 是命令的参数。

...在SQL中，您根据值和 @searchvalue 按升序排列并仅选择第一条记录 - 即与 @searchvalue 的最小差异或最接近的数字。

...如果有两个等于 @searchvalue 的记录，那么值中值最高的行将被退回。如果你想要它是最低的那么从订单中删除，值。



这个可能写的是这样的......

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 ...

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

但是使用参数比使用字符串连接更好（有很多解释为什么在google-land中出来）



注意 - 此解决方案仅在值是您的问题标题的数字列（任何字段大小）时才有效，并且上面的文本中可能存在输入错误。

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.

这篇关于找到最接近的号码数据库访的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！