if the input value is in between two values then display the result

Question

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

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.

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

Answer 1

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

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

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.

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

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.

As per Andrew Morton's comment and more in line with what the OP attempted here is a solution that uses 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.

With this in mind:

'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

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.

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.

Answer 2

Hope it Helps...

Dim connetionString As String
Dim cnn As SqlConnection
Dim cmd As SqlCommand
Dim sql As String

connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
sql = "SELECT [Equivalent Value] FROM tblSSS WHERE [FROM]<=" & Val(TextBox1.Text) & " AND [TO]>= " & Val(TextBox1.Text)

cnn = New SqlConnection(connetionString)
Try
    cnn.Open()
    cmd = New SqlCommand(sql, cnn)
    Dim count As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
    cmd.Dispose()
    cnn.Close()

Catch ex As Exception
    MsgBox("Can not open connection ! ")
End Try