question

PopaCristianValentin-9148 avatar image
0 Votes"
PopaCristianValentin-9148 asked AlbertKallal-4360 edited

Gridview search

Hello,
i have a problem with my gridview, i have a string that shows me a result from a search box in another page, and i want to add sql "like" command to my string:

strSql = "select * from tbl_search where tx_search='" + Request.QueryString["selectedvalue"].ToString() + "'";

For sure i need a command parameter too, but i don't know how to write it and how to put sql "like" in this string.

Thank you!

sql-server-generaldotnet-csharpdotnet-aspnet-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

AlbertKallal-4360 avatar image
1 Vote"
AlbertKallal-4360 answered AlbertKallal-4360 edited

Ok, assuming this grid view:

123181-image.png


Ok, now we assume that some city will be passed - but as noted , we want "Like with %

Our code could look like this:


        protected void Page_Load(object sender, EventArgs e)
         {
             if (!IsPostBack)
             {
                 LoadGrid(Request.QueryString["City"]);
             }
         }
    
         void LoadGrid(string strCity = "")
         {
             using (SqlCommand cmdSQL = new SqlCommand("SELECT * FROM tblHotels",
                              new SqlConnection(Properties.Settings.Default.TEST3)))
             {
                 if ((strCity != "") & (strCity != null))
                 {
                     cmdSQL.CommandText += " WHERE City Like @City + '%' ";
                     cmdSQL.Parameters.Add("@City", SqlDbType.NVarChar).Value = strCity;
                 }
                 cmdSQL.Connection.Open();
                 GridView1.DataSource = cmdSQL.ExecuteReader();
                 GridView1.DataBind();
    
             }
         }


and our output would now be say for passing Jasp this:

123099-image.png

So, I not only in above show how you use Like, but I also made it optional, so if nothing is passed, then all records show.

but, the meat and potatoes part is the like - you have to add it to the sql as a + '%'

But, we still have sql injection protection here - since we don't ever concatenate the passed value.

Regards,

Albert D. Kallal (Access MVP 2003-2017)
Edmonton, Alberta Canada



image.png (41.0 KiB)
image.png (76.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.