不要只搜索姓氏

本文关键字：搜索 | 更新日期: 2023-09-27 18:05:20

在我的webapp中，我有一个搜索框，这样我就可以使用名字或姓氏搜索我的数据库，它将在我的webapp中显示结果。用户输入名字或姓氏。如果按名查询运行正常但如果用户只按姓查询它会显示表中所有数据而不是按姓

 string sql = @"SELECT opd_id AS [OPD No]
       , opd_date AS DATE
       , opd_dpt AS DEPARTMENT
       , opd_pfname + ' ' + opd_plname AS [Patient NAME]
       , opd_age AS AGE
       , opd_gender AS GENDER
       , opd_mob AS [MOBILE NO]
       , opd_fthrname AS [FATHER NAME]
       , opd_hsbndname AS [HUSBAND NAME]
       FROM tbl_OPD 
        WHERE opd_pfname like'%" + fname + @"%' 
        OR opd_plname like'%" + lname + @"% '
        ORDER BY DATE DESC";

不要只搜索姓氏

OR opd_plname like'%" + lname + @"% '

白色space ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ^

还要注意sql注入，可以使用准备好的sql语句和参数绑定来解决这个问题

不需要看到更多的代码，它可以是您只填充fname或lname变量中的一个。假设是这种情况，您可以使用以下代码:

// this assumes that you have only have 1 input for both the first name and lastname 
// and are storing it in a variable named searchForName
using (var connection = new SqlConnection("YOUR_CONFIGURATION_CONNECTION_STRING"))
{
    using (var command = new SqlCommand("SELECT opd_id AS [OPD No], opd_date AS DATE, opd_dpt AS DEPARTMENT, opd_pfname + ' ' + opd_plname AS [Patient NAME], opd_age AS AGE, opd_gender AS GENDER, opd_mob AS [MOBILE NO], opd_fthrname AS [FATHER NAME], opd_hsbndname AS [HUSBAND NAME] FROM tbl_OPD WHERE (opd_pfname like @p0 OR opd_plname like @p0) ORDER BY DATE DESC", connection))
    {
        command.Parameters.Add(new SqlParameter("@p0", string.Format("%{0}%", searchForName)));
        // rest of your code here E.G., sComm.ExecuteNonQuery();
    }
}

如果您知道两个变量都已填充，请尝试以下代码块

using (var connection = new SqlConnection("YOUR_CONFIGURATION_CONNECTION_STRING"))
{
    using (var command = new SqlCommand("SELECT opd_id AS [OPD No], opd_date AS DATE, opd_dpt AS DEPARTMENT, opd_pfname + ' ' + opd_plname AS [Patient NAME], opd_age AS AGE, opd_gender AS GENDER, opd_mob AS [MOBILE NO], opd_fthrname AS [FATHER NAME], opd_hsbndname AS [HUSBAND NAME] FROM tbl_OPD WHERE (opd_pfname like @p0 OR opd_plname like @p1) ORDER BY DATE DESC", connection))
    {
        command.Parameters.Add(new SqlParameter("@p0", string.Format("%{0}%", searchForFirstName)));
        command.Parameters.Add(new SqlParameter("@p1", string.Format("%{0}%", searchForLastName)));
        // rest of your code here E.G., sComm.ExecuteNonQuery();
    }
}

p。正如其他人所说，这里显示的代码有些不安全，您应该尽一切努力避免Sql注入攻击。

相关文章：