如何在sql查询中使用like关键字

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

我有一个如下所示的SQL查询我想使用变量streetname,keyword1,radius并使用like关键字执行sql查询但是它们是查询语法的一些问题可以任何人帮助

  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();
}

2 个解决方案

#1

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 -

编辑:正如Rob在评论中提到的那样,编辑这个答案以获得更好的安全性 -

"...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;

#2

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.

如果你没有收到任何错误消息并且查询只是没有返回任何数据,你必须连接像我们之前已经说过的Sachin Shanbhag那样的查询字符串。

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:

但是如果你真的得到一条消息,你的SQL语法中有错误,我认为这不是因为LIKE关键字,因为从技术上讲,这种语法是正确的:

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 ...".

如果你的语法确实有错误,可能是因为“ACOS(COS(x)* 24534”的东西。尝试将其留在第一步,只做“SELECT * FROM ...”。

#1