CODEWITHSUNDEEP
c#sql-server-2005
shweta
shweta

Reputation: 9

sql query problem

protected void Button1_Click(object sender, EventArgs e) 
{
    SqlConnection myConnection = new SqlConnection("Data Source=DELL-PC\\SQLEXPRESS;Initial Catalog=eclass;Persist Security Info=True;integrated security = true");
    myConnection.Open();
    string key = txtsearchkey.Text.ToString();

    SqlCommand q1 = new SqlCommand("select cat_id from category where cat_name='" + (ddsearchcat.SelectedItem.ToString() + "'"), myConnection);
    string cat = q1.ExecuteScalar().ToString();

    SqlCommand q2 = new SqlCommand("select subcat_id from subcategory where subcat_name= '" + (ddsearchsubcat.SelectedItem.ToString() + "'"), myConnection);
    string subcat = q2.ExecuteScalar().ToString();

    SqlCommand q3 = new SqlCommand("select adid from adType where adtype= '" + (ddsearchtype.SelectedItem.ToString()) + "'", myConnection);
    string adtype = q3.ExecuteScalar().ToString();

    String date = ddsearchdays.SelectedItem.ToString();

    if (chkAdimg.Checked)
    {
        if (chkAdVideo.Checked)
        {
            SqlCommand query = new SqlCommand("select title,ad_description from postad where ad_description like " + txtsearchkey + " and category_id=" + cat + " and subcategory_id=" + subcat + " and ad_id=" + adtype + " and video is not null and img_id is not null and adType INNER JOIN adType AS adType_1 ON adType.adid = adType_1.adid CROSS JOIN  category CROSS JOIN  subcategory CROSS JOIN userdetails", myConnection);           

            DataSet ds = new DataSet();
            SqlDataAdapter ad = new SqlDataAdapter(query);
            ad.Fill(ds);
            foreach (DataRow dr in ds.Tables[0].Rows)
            {
                    Response.Write(dr[0].ToString());
            }
        }
    }
}

This query is giving me a problem saying

An expression of non-boolean type specified in a context where a condition is expected, near 'INNER...

What change should I make in my query

Upvotes: 0

Views: 139

Answers (4)

Nighil
Nighil

Reputation: 4127

select title,ad_description from postad where ad_description like " + txtsearchkey + " and category_id=" + cat + " and subcategory_id=" + subcat + " and ad_id=" + adtype + " and video is not null and img_id is not null and adType INNER JOIN adType AS adType_1 ON adType.adid = adType_1.adid CROSS JOIN  category CROSS JOIN  subcategory CROSS JOIN userdetails", myConnection);

where are you using inner join after where condition??

i think this may be right 

select title,ad_description 
from postad 
INNER JOIN adType AS adType_1 ON adType.adid = adType_1.adid 
CROSS JOIN category 
CROSS JOIN subcategory 
CROSS JOIN userdetails 
where ad_description like " + txtsearchkey + " 
  and category_id=" + cat + " 
  and subcategory_id=" + subcat + " 
  and ad_id=" + adtype + " 
  and video is not null 
  and img_id is not null

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38543

You have your joins after your WHERE clause and it needs to be before the WHERE clause and after the FROM

select 
    title,
    ad_description 
from postad 
INNER JOIN adType AS adType_1 ON adType.adid = adType_1.adid 
CROSS JOIN  category 
CROSS JOIN  subcategory 
CROSS JOIN userdetails
where ad_description like " + txtsearchkey + " 
and category_id=" + cat + " 
and subcategory_id=" + subcat + " 
and ad_id=" + adtype + " 
and video is not null 
and img_id is not null 
and adType "

Upvotes: 0

amit_g
amit_g

Reputation: 31270

The varchar columns should have quotes enclosing the value. For example

where ad_description like " + txtsearchkey + " and

should be

where ad_description like '" + txtsearchkey + "' and

In addition

img_id is not null and adType INNER JOIN

should have

img_id is not null INNER JOIN

i.e. the adType seems to be misplaced.

This is not a good way of making dynamic SQL. Not only it exposes to SQL injection but also is almost impossible to maintain.

Upvotes: 0

Iain Ward
Iain Ward

Reputation: 9946

I assume it's here where you've got

...and adType INNER JOIN adType...

Your joins should be done before the WHERE clause, not to mention you really should be using parameters for the values instead of plain text to avoid things like sql injection, and you will probably need %'s in the values you want to do a LIKE on, but I digress...

Upvotes: 1

Related Questions