CODEWITHSUNDEEP
c#asp.netsql
Ahmad Danial
Ahmad Danial

Reputation: 79

SQL Like statement not return a wildcard output asp.net c#

i have these sql statement to output job field based on user input. 

    SELECT jf.name, j.jname, c.cname, cj.url
    FROM company_has_job cj
        INNER JOIN job j ON j.id = cj.job_id
        INNER JOIN company c ON c.id = cj.company_id
        INNER JOIN jobfield_has_job jhj ON jhj.job_id = j.id
        INNER JOIN jobfield jf ON jf.id = jhj.jobfield_id
    WHERE jf.name LIKE '%' + @InputJob + '%'

for example when user input query "Programmer", the system will output all programmer job defined in the database.but the system does not show any result if i enter "Program" in the search box. This is my control parameter:

<SelectParameters>
    <asp:ControlParameter ControlID="TextBox4" Name="InputExpertise" 
        PropertyName="Text"/>
</SelectParameters>

can you tell me where i did wrong? your help is much appreciated.

Upvotes: 2

Views: 835

Answers (2)

dcreight
dcreight

Reputation: 641

Can't comment yet, but perhaps this will answer the question, do any of the columns that you join on allow NULLs? The inner join will not return results that include a null.

consider this query:

select wb.employee,wb.Dept from web_user wb
inner join Employee e on wb.Dept = e.Department
where wb.employee like '%Ted%'

No results

but this:

select wb.employee,wb.Dept from web_user wb
left join Employee e on wb.Dept = e.Department
where wb.employee like '%Ted%'

Returns: Ted NULL

Also it my be helpful to debug, set a breakpoint on the actual call to the database to get the SQL statement it is using and then paste that into a query editor. I've done this many times when a query does not behave. Once you know what the program is using for the SQL statement that may also help you find the issue.

Upvotes: 1

Justin Pihony
Justin Pihony

Reputation: 67065

I am not sure if this is just a typo, but shouldn't it be 

WHERE jf.name LIKE '''%' + @InputJob + '%'''

Notice that I made it so that the code will end up as LIKE '%Program%', whereas it looks like it will end up as LIKE %Program% currently.

Upvotes: 3

Related Questions