CODEWITHSUNDEEP
vb.net
Muhammad Zahid
Muhammad Zahid

Reputation: 597

how to resolve "an expression of boolean type specified in a context where a condition is excepted near number " error in vb.net

Imports System.Data
Imports System.Data.SqlClient


Public Class Form2
    Private Sub button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim cmd As SqlCommand
        Dim conn As SqlConnection
        Dim dr2 As SqlDataReader

        Dim sql = "SELECT room number,date,ddate FROM date WHERE room number = '" & nametxt.Text & "' AND date >= '" & DateTimePicker1.Text & "'AND ddate <= '" & DateTimePicker1.Text & "'OR Room number = '" & nametxt.Text & "'AND date = '" & DateTi    mePicker1.Text & "' "
        conn = New SqlConnection("Data Source=zahid\sqlexpress;Initial Catalog=test;Integrated Security=True")
        conn.Open()

        cmd = New SqlCommand(sql, conn)


        Try
            dr2 = cmd.ExecuteReader
            If dr2.Read = True Then
                MessageBox.Show("room not available...")
            Else
                MessageBox.Show("Login Successful...")
            End If
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        If conn.State <> ConnectionState.Closed Then
            conn.Close()
        End If

    End Sub
End Class

Upvotes: 1

Views: 329

Answers (1)

Steve
Steve

Reputation: 216313

When you have a field with spaces in its name you should enclose it in square brackets

Dim sql = "SELECT [room number,date,ddate FROM date WHERE [room number] = " & _
         "'" & nametxt.Text & "' AND date >= '" & DateTimePicker1.Text & _
         "'AND ddate <= '" & DateTimePicker1.Text & "'OR [Room number] = '" & _ 
         "nametxt.Text & "'AND date = '" & DateTimePicker1.Text & "' "

Said that you should absolutely remove the string concatenation and use a parameterized query. I put also a parenthesys to better group the logical conditions

Dim sql = "SELECT [room number],date,ddate FROM date WHERE " & _
          "([room number] = @rnum  AND date >= @dt AND ddate <= @dt) " & _
          " OR ([Room number] = @rnum AND date = @dt)"

Now that the query text is simplified with the parameters you can easily see that the condition OR is not needed because it is already included in the first block.

So your code could be written as 

Dim sql = "SELECT [room number],date,ddate FROM date WHERE " & _
          "[room number] = @rnum  AND date >= @dt AND ddate <= @dt " 

Using conn = New SqlConnection("Data Source=zahid\sqlexpress;Initial Catalog=test;Integrated Security=True")
Using cmd = New SqlCommand(sql, conn)
    conn.Open()
    cmd.Parameters.AddWithValue("@rnum", nametxt.Text)
    cmd.Parameters.AddWithValue("@dt", DateTimePicker1.Value)
    Try
        Using dr2 = cmd.ExecuteReader
            ' Probably this test is wrong....
            If dr2.Read = True Then
                MessageBox.Show("room not available...")
            Else
                MessageBox.Show("Login Successful...")
            End If
        End Using
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Using
End Using
If conn.State <> ConnectionState.Closed Then
    conn.Close()
End If

Upvotes: 1

Related Questions