如果输入值介于两个值之间，则显示结果 [英] if the input value is in between two values then display the result

问题描述

我有一个包含三列From"、To"和Equivalent Value"的 SQL 表.每个值如下所示:

I have a SQL table with three columns "From","To" and "Equivalent Value". Each value is shown below:

From        To           Equivalent Value
1,001.00    2,000.00     200.00
2,001.00    3,000.00     300.00

现在，如果用户在 textbox1 中输入值1,200.00"，它将向 textbox2 显示结果值200.00"，因为这是From"和To"之间的对应值.

Now if the user enters the value "1,200.00" in textbox1 it will display the result value to textbox2 which is "200.00" because that is the corresponding value of between "From" and "To.

另一个条件，如果用户在 textbox1 中输入值2,500.00"，它将显示值300.00".

Another condition, if the user enters the value "2,500.00" in textbox1 it will display the value "300.00".

到目前为止，我已经尝试过这段代码，但没有成功:

So far, I have tried this code but no luck:

Dim conn As SqlConnection = SQLConn()
Dim da As New SqlDataAdapter
Dim dt As New DataTable

conn.Open()

Dim cmd As New SqlCommand("", conn)
Dim result As String

cmd.CommandText = "SELECT [Equivalent Value] FROM tblSSS"
result = IIf(IsDBNull(cmd.ExecuteScalar), "", cmd.ExecuteScalar)
da.SelectCommand = cmd
dt.Clear()
da.Fill(dt)

If result <> "" Then
    If TextBox1.Text >= dt.Rows(0)(1).ToString() And TextBox1.Text <= dt.Rows(0)(2).ToString() Then
        TextBox2.Text = dt.Rows(0)(3).ToString()

    End If
End If

推荐答案

如果我做对了，我想我会改变一些可能对你有帮助的事情:

If I have got this right I think there are a couple of things I would change which may help you:

1. 使用使用.这将在完成后处理 SQL 对象.
2. 使用 SqlParameters.这将有助于过滤您的数据.
3. 删除SqlDataAdapter 的使用.在这种情况下，我觉得没有必要.
4. IIf 的使用.我将使用替换了 IIf 的 If.

1. Use Using. This will dispose of the SQL objects once finished with.
2. Use SqlParameters. This will help with filtering your data.
3. Remove the use of SqlDataAdapter. In this case I don't feel it's needed.
4. The use of IIf. I will be using If which has replaced IIf.

考虑到这些，我会看这样的:

With these in mind I would look at something like this:

Dim fromValue As Decimal = 0D
Dim toValue As Decimal = 0D

If Decimal.TryParse(TextBox1.Text, fromValue) AndAlso Decimal.TryParse(TextBox1.Text, toValue) Then

    Dim dt As New DataTable

    Using conn As SqlConnection = SQLConn,
          cmd As New SqlCommand("SELECT [Equivalent Value] FROM tblSSS WHERE [From] >= @From AND [To] <= @To", conn)

        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@From", .SqlDbType = SqlDbType.Decimal, .Value = fromValue})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@To", .SqlDbType = SqlDbType.Decimal, .Value = toValue})

        conn.Open()

        dt.Load(cmd.ExecuteReader)

    End Using

    If dt.Rows.Count = 1 Then
        TextBox2.Text = If(IsDBNull(dt.Rows(0).Item("Equivalent Value")), "0", dt.Rows(0).Item("Equivalent Value").ToString)
    End If

End If

注意Decimal.TryParse<的使用/a>:

Note the use of Decimal.TryParse:

将数字的字符串表示形式转换为其十进制等价物.返回值指示转换是成功还是失败.

Converts the string representation of a number to its Decimal equivalent. A return value indicates whether the conversion succeeded or failed.

这是一个假设，即数据库中的From 和To 字段是Decimal.

This is an assumption that the From and To fields in your database are Decimal.

现在解释IIf和If的区别.IIf 执行语句的每一部分，即使它为真，而 If 只执行一部分.我不会详细介绍这里的许多其他人已经这样做了.看看这个答案.

Now to explain the difference between IIf and If. IIf executes each portion of the statement even if it's true whilst If executes only one portion. I won't go into detail as many others on here have done that already. Have a look at this answer.

根据 Andrew Morton 的评论，更符合 OP 在这里尝试的解决方案，使用 ExecuteScaler.

As per Andrew Morton's comment and more in line with what the OP attempted here is a solution that uses ExecuteScaler.

ExecuteScaler 执行查询，并返回查询返回的结果集中第一行的第一列.其他列或行将被忽略.

ExecuteScaler executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

考虑到这一点:

'I reset the value of TextBox2.Text. You may not want to.
TextBox2.Text = ""

Dim fromValue As Decimal = 0D
Dim toValue As Decimal = 0D

If Decimal.TryParse(TextBox1.Text, fromValue) AndAlso Decimal.TryParse(TextBox1.Text, toValue) Then

    Using conn As SqlConnection = SQLConn,
          cmd As New SqlCommand("SELECT [Equivalent Value] FROM tblSSS WHERE [From] >= @From AND [To] <= @To", conn)

        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@From", .SqlDbType = SqlDbType.Decimal, .Value = fromValue})
        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@To", .SqlDbType = SqlDbType.Decimal, .Value = toValue})

        conn.Open()

        Try
            TextBox2.Text = cmd.ExecuteScalar().ToString()
        Catch ex As Exception

        End Try

    End Using

End If

我使用了 ExecuteScaler MSDN 文档中的示例.您可能希望更好地处理 Try Catch 上的异常，而不是让它浪费掉.

I have used the example on the ExecuteScaler MSDN documentation. You might want to look into handling the exception on the Try Catch a little better and not letting it go to waste.

您可能希望将此代码放在 TextBox1.Leave 方法上，或者可能放在 Button.Click 方法上.这完全取决于你.

You may want to place this code on the TextBox1.Leave method or maybe on a Button.Click method. That's totally up to you.

您可能需要进行一些更改，但我认为这将为您提供有关如何继续使用代码的一些想法.

There may a few changes you may need to make however I think this will give you a few ideas on how to move ahead with your code.

这篇关于如果输入值介于两个值之间，则显示结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！