title: SQL command error Incorrect Syntax near 'and'
body: <p>I am a newbie student of VB.net and struggling through a form I cannot figure out how to solve. The form is a search form which can search the assets table based on four inputs. User can choose which inputs to give. The interface of the form looks like the picture I've attached <a href="https://i.stack.imgur.com/hyMZQ.png" rel="nofollow noreferrer">here</a>. The problem is that its giving me Incorrect syntax near the keyword 'and' error when I check any checkbox.</p>
  
  <p>Any other approach to achieve the desired results is also highly encouraged.</p>
  
  <p>I am using the following code</p>
  
  <pre><code>    Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
      sCommand = New SqlCommand("", con)
      Dim script As String
      script = "Select * from tblassets where [asset_costprice] like %"
      If checkassetsupplier.Checked = False AndAlso checkassetstatus.Checked = False AndAlso CheckassetID.Checked = False AndAlso checkassetsupplier.Checked = False Then
          sCommand.CommandText = "Select * from tblassets"
      End If
      If checkassetsupplier.Checked = True Then
          script = script &amp; " and [Asset_supplier] = @p1"
          sCommand.Parameters.AddWithValue("@p1", cbsearchsupplier.Text)
      End If
      If checkassetstatus.Checked = True Then
          script = script &amp; " and asset_status = @p2"
          sCommand.Parameters.AddWithValue("@p2", cbsearchstatus.SelectedIndex)
      End If
      If CheckassetID.Checked = True Then
          script = script &amp; " and asset_id = @p3"
          sCommand.Parameters.AddWithValue("@p3", tbsearchassetid.Text)
      End If
      If checkassetname.Checked = True Then
          script = script &amp; " and asset_name = @p4"
          sCommand.Parameters.AddWithValue("@p4", tbsearchassetname.Text)
      End If
      sCommand.CommandText = script
      sAdapter = New SqlDataAdapter(sCommand)
      sBuilder = New SqlCommandBuilder(sAdapter)
      sDs = New DataSet()
      sAdapter.Fill(SMSSDataSet, SMSSDataSet.tblassets.TableName)
      Dim dt As New DataTable
      With dt
          .Columns.Add("No", GetType(Integer))
          .Columns.Add("Status", GetType(String))
          .Rows.Add("0", "Held")
          .Rows.Add("1", "Depreciated")
          .Rows.Add("2", "Partially Sold")
          .Rows.Add("3", "Sold")
      End With
      dgvsearch.AutoGenerateColumns = False
      dgvsearch.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Asset ID", .DataPropertyName = "asset_id", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.ColumnHeader})
      dgvsearch.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Name", .DataPropertyName = "asset_name", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill})
      dgvsearch.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Price", .DataPropertyName = "unit_price", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill})
      dgvsearch.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Supplier", .DataPropertyName = "asset_supplier", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill})
      dgvsearch.Columns.Add(New DataGridViewComboBoxColumn() With {.HeaderText = "Status", .DataSource = dt, .DisplayMember = "Status", .ValueMember = "No", .DataPropertyName = "asset_status", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill})
      dgvsearch.Columns.Add(New DataGridViewTextBoxColumn() With {.HeaderText = "Quantity", .DataPropertyName = "quantity", .ReadOnly = True, .AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill})
  
      dgvsearch.DataSource = SMSSDataSet.tblassets
  
  
  End Sub
  </code></pre>