SQL command error Incorrect Syntax near 'and'

Question

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 here. The problem is that its giving me Incorrect syntax near the keyword 'and' error when I check any checkbox.

Any other approach to achieve the desired results is also highly encouraged.

I am using the following 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 & " and [Asset_supplier] = @p1"
        sCommand.Parameters.AddWithValue("@p1", cbsearchsupplier.Text)
    End If
    If checkassetstatus.Checked = True Then
        script = script & " and asset_status = @p2"
        sCommand.Parameters.AddWithValue("@p2", cbsearchstatus.SelectedIndex)
    End If
    If CheckassetID.Checked = True Then
        script = script & " and asset_id = @p3"
        sCommand.Parameters.AddWithValue("@p3", tbsearchassetid.Text)
    End If
    If checkassetname.Checked = True Then
        script = script & " 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

Answer 1

Just a shot in the dark, but your script uses like but then doesn't say what it should be like, and doesn't have any ' ' to show what the like statement is.

https://www.w3schools.com/sql/sql_like.asp

Maybe change to this and see if it helps:

 script = "Select * from tblassets where [asset_costprice] like '%'"

I would also change all your ifs to have =' ' as well, and see what errors happen now.