SQL statement help

.NET
1

Hi,

I am trying to run this sql statement:

SELECT tbl_vacancies.job_id, tbl_vacancies.job_name, " +
             "tbl_vacancies.location, tbl_vacancies.voucher, tbl_vacancies.details, " +
             "tbl_vacancies.availability, tbl_vacancies.region, " +
             "(SELECT COUNT(*) FROM tbl_applications WHERE tbl_applications.ID = tbl_vacancies.job_id and viewed = 0) AS VIEWED " +
             "FROM tbl_vacancies WHERE tbl_vacancies.availability = '" + DropDownList1.SelectedItem + "' AND (tbl_vacancies.location like '%" + txt_search.Text +
             "%') OR (tbl_vacancies.job_name like '%" + txt_search.Text + "%') ORDER BY tbl_vacancies.location ASC

But the problem is that the LIKE works but the WHERE tbl_vacancies.availability = ‘" + DropDownList1.SelectedItem + "’ does not fire…

Any ideas why?

Thanks

2

Hey,

I am trying this:


"SELECT tbl_vacancies.job_id, tbl_vacancies.job_name, " +
             "tbl_vacancies.location, tbl_vacancies.voucher, tbl_vacancies.details, " +
             "tbl_vacancies.availability, tbl_vacancies.region, " +
             "(SELECT COUNT(*) FROM tbl_applications WHERE tbl_applications.ID = tbl_vacancies.job_id and viewed = 0) AS VIEWED " +
             "FROM tbl_vacancies WHERE tbl_vacancies.availability = '" + DropDownList1.SelectedValue + "' AND (tbl_vacancies.location like '%" + txt_search.Text +
             "%') OR (tbl_vacancies.job_name like '%" + txt_search.Text + "%') ORDER BY tbl_vacancies.location ASC"

I understand that i should be using paraterized queries, i will apply this once i get the query working. BUt the query above is not working. If i use this:

"SELECT tbl_vacancies.job_id, tbl_vacancies.job_name, " +
             "tbl_vacancies.location, tbl_vacancies.voucher, tbl_vacancies.details, " +
             "tbl_vacancies.availability, tbl_vacancies.region, " +
             "(SELECT COUNT(*) FROM tbl_applications WHERE tbl_applications.ID = tbl_vacancies.job_id and viewed = 0) AS VIEWED " +
             "FROM tbl_vacancies WHERE tbl_vacancies.availability = '" + DropDownList1.SelectedValue + "' ORDER BY tbl_vacancies.location ASC"

Then it works but i need the LIKE statement also…

Any ideas?

3

It is because you are basicaly saying this:
tbl_vacancies.availability = ‘ListItem’

You need to replace:
DropDownList1.SelectedItem
with
DropDownList1.SelectedItem.Value

Another thing, why does your column name have a “.” in? I would also suggest using parameters, as this leaves ur site open to sql injection attacks

4

Thanks guys!

I should know better, obviously you know the best way to go about such problems.

I have amended my query and put the parenthesis around the OR clauses, and also used parameterized queries!

All works good now… :smiley:

Thanks again

5

Read Nightstalkers reply again, very carefully, and then correct your code.

6

please do yourself – and us – a favour and learn to apply formatting to your SQL

what you currently have is unreadable and causes a lot of needless work

SELECT tbl_vacancies.job_id
     , tbl_vacancies.job_name
     , tbl_vacancies.location
     , tbl_vacancies.voucher
     , tbl_vacancies.details
     , tbl_vacancies.availability
     , tbl_vacancies.region
     , ( SELECT COUNT(*) 
           FROM tbl_applications 
          WHERE tbl_applications.ID = tbl_vacancies.job_id 
            and viewed = 0) AS VIEWED 
  FROM tbl_vacancies 
 WHERE tbl_vacancies.availability = '" + DropDownList1.SelectedValue + "' 
   AND (
       tbl_vacancies.location like '%" + txt_search.Text + "%'
       ) 
    OR (
       tbl_vacancies.job_name like '%" + txt_search.Text + "%'
       ) 
ORDER 
    BY tbl_vacancies.location ASC

now it’s a lot clearer where your problem is coming from

you have a couple of pairs of unnecessary parentheses in the WHERE clause

[indent]WHERE x AND (y) OR (z)[/indent]take those out

now add back a pair of parentheses around both OR conditions

WHERE x AND (y OR z)

see the difference?

:cool: