CODEWITHSUNDEEP
c#asp.netxmlsql-server-2005
mahesh
mahesh

Reputation: 3207

how to use like keyword in sql query

I have a sql query shown below i want to use the variables streetname, keyword1,radius and perform the sql query using like keyword but their is some problem with query syntax can anyone help

  protected void CreateXML(string keyword1, string streetname, string lat, string lng, string radius)
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "data source='192.168.0.221';user id='sa';password='$sql123';persist security info=False;initial catalog=Test;Connect Timeout=100; Min Pool Size=100; Max Pool Size=500";
        con.Open();

        DataSet ds = new DataSet();
        SqlCommand com = new SqlCommand();
        SqlDataAdapter sqlda = new SqlDataAdapter(com);
        sqlda.SelectCommand.CommandText = "SELECT Id,Name1,ZipCode,StreetName,StreetNumber,State1,Lat,Lng, ( 6371 * ACOS( COS( (12.925432/57.2958) ) * COS(  (Lat/57.2958)  ) * COS( ( Lng/57.2958 ) - (77.591667/57.2958) ) + SIN( 12.925432/57.2958 ) * SIN(  Lat/57.2958  ) ) ) AS distance FROM Business_Details where( (distance < '"+radius+"')and(StreetName like '%streetname%')and (Keyword like '%keyword1%') )ORDER BY distance";
        sqlda.SelectCommand.Connection = con;
        sqlda.Fill(ds);
        con.Close();
}

Upvotes: 0

Views: 5032

Answers (2)

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55479

Yes, you need to do this -

"...like '%" + streetname + "%') and (keyword like '%" + keyword1 + "%') )..."

EDIT: As Rob mentions in the comment, editing this answer for better security -

"...like '%' + @streetname + '%') and (keyword like '%' + @keyword1 + '%') )..."

Then you need to add these parameters to the command object -

command.Parameters.Add("@streetname", SqlDbType.VarChar);
command.Parameters["@streetname"].Value = streetname;
command.Parameters.Add("@keyword1", SqlDbType.VarChar);
command.Parameters["@keyword1"].Value = keyword1;

Upvotes: 6

Christian Specht
Christian Specht

Reputation: 36421

If you get no error message and the query just doesn't return any data, you have to concatenate the query string like Sachin Shanbhag already said before me.

But if you really get a message that there is an error in your SQL syntax, I don't think it's because of the LIKE keyword because technically, this syntax is correct:

Keyword like '%keyword1%'

If you really have an error in your syntax, it's probably because of the "ACOS(COS(x) * 24534" stuff. Try to leave that away for the first step and just do "SELECT * FROM ...".

Upvotes: 0

Related Questions