CODEWITHSUNDEEP
sqlasp.net-mvc
Hanzou
Hanzou

Reputation: 73

Wildcard in SQL Query with Parameters

I am using ASP.NET MVC, I would like to put wildcard in my @stype like this: %@stype%, but I don't know where to put it here in my code, just comment if you want to see more of my code, please help.

Models:

public class SearchParameters
{
    public string sarcode { get; set; }
    public string stype { get; set; }
    public SearchParameters()
    {
        this.sarcode = string.Empty;
        this.stype = string.Empty;
    }

    internal List<SqlParameter> ToSqlParameterList()
    {
        List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter("@sarcode", this.sarcode ?? string.Empty));
        parameters.Add(new SqlParameter("@stype", this.stype ?? string.Empty));
        return parameters;
    }
}

Query

SELECT sarcode,stype,amount,filterno FROM tblsar WHERE ((sarcode = @sarcode OR @sarcode = '') AND (stype = @stype OR @stype = ''))

Javascript

var table = $('#SARDatatable').DataTable({
    "processing": true,
    dom: "<'row'<'col-sm-6'l><'col-sm-6'f>>" +
        "<'row'<'col-sm-12'tr>>" +
        "<'row'<'col-sm-2'i><'col-sm-5'B><'col-sm-5'p>>",
    buttons: [
        'copyHtml5',
        'excelHtml5',
        'csvHtml5',
        'pdfHtml5',
        'print'
    ],
    "ajax": {
        "url": '/Home/GetAllSAR',
        "type": "POST",
        "datatype": "json",
        "data": function (d) {
            d.searchParameters = {};
            d.searchParameters.sarcode = $('#txtSAR').val();
            d.searchParameters.stype = $('#txtSType').val();
        }
    },
    "columns": [
        { "data": "sarcode", "autoWidth": true },
        { "data": "stype", "autoWidth": true },
        { "data": "amount", "autoWidth": true },
        { "data": "filterno", "autoWidth": true }
    ]
});

Upvotes: 0

Views: 333

Answers (2)

LastCyborg
LastCyborg

Reputation: 124

it is in your SQL code, try using the keyword like

WHERE ((sarcode like  '%' + @sarcode + '%' OR @sarcode = '')

Upvotes: 0

Richard Boyce
Richard Boyce

Reputation: 413

I would suggest using CHARINDEX instead. For example

WHERE CHARINDEX(@sarcode, sarcode) > 0

Upvotes: 1

Related Questions