CODEWITHSUNDEEP
c#.netsql-server
ankur
ankur

Reputation: 4733

Select query with multiple like conditions in Sql

All,

I have a view from which some filtered data is to be fetched by sql query

View structure:

BridgeId      int
Name          varchar
DisplayName   varchar

There is a search text box in which user can enter any one of the values for filtering.

due to project old framework i have to query from c# itself.

public static List<ConferenceBridges> GetSearchList(string search)
        {
            DB db = new DB(SERVER_NAME, DATA_BASE_NAME);
            string searchQuery = string.Format("select BridgeId,Name,OwnerId from vConferenceBridgesDetails where BridgeId like '%' + {0} + '%' OR Name like'%' + {0} + '%' OR DisplayName like '%' + {0} + '%'", search);
            DataTable table = db.GetData(searchQuery);
            List<ConferenceBridges> bridgeList = new List<ConferenceBridges>();
            if (table != null && table.Rows.Count > 0)
            {
                foreach (DataRow item in table.Rows)
                {
                    bridgeList.Add(new ConferenceBridges(item));
                }
            }
            return bridgeList;
        }

the problems : when ever i enter a bridge id it is int but passed as string so it give error and for string values it give invalid column name.

How can i make the query so that it accept all the parameters.

Upvotes: 1

Views: 1940

Answers (3)

Vinay Pandey
Vinay Pandey

Reputation: 8913

Use '%YourValue%' inplace of '%' + YourValue + '%' aditional ' and + sign is causing the issue.

Upvotes: 0

x4rf41
x4rf41

Reputation: 5337

'%' + {0} + '%' is just wrong.

if search = "abc" this would generate: "Name like '%'+abc+'%'" which is obviously invalid sql

if you use '%{0}%' instead, you will have valid sql (if there is no ' in search)

use this:

string searchQuery = string.Format("select BridgeId,Name,OwnerId from vConferenceBridgesDetails where BridgeId like '%{0}%' OR Name like '%{0}%' OR DisplayName like '%{0}%'", search);

Upvotes: 0

Ehsan
Ehsan

Reputation: 32681

Firstly, BridgeId is an int and like cannot be implemented on int so either change it to varchar or use =

Secondly, whereever you are using like statement you have to pass it with single quotes like

string searchQuery = string.Format("select BridgeId,Name,OwnerId from vConferenceBridgesDetails where BridgeId  =  {0}  OR Name like '% + {0} + %' OR DisplayName like '% + {0} + %'", search);

Upvotes: 0

Related Questions