Tuesday, October 4, 2011
Using Parameters.AddWithValue with a Like SQL query
When adding parameters to SQL queries in code behind ASP.NET pages, the ideal syntax is to use a SQL parameter to help build your query string, rather than creating a text string on the fly.
There is a trick, though, if your query contains the ‘Like’ keyword.
As an example, consider the following code snippet which opens a database connection to a data reader and populates a gridview:
The preferred way is instead to use Parameters.AddWithValue (which superseded Parameters.Add in ASP.NET 2.0). This ensures your parameter is handled correctly when your SQL query is being built:
This query will populate a Gridview with people whose name = John, but you may wish to use the ‘Like’ SQL keyword with the ‘%’ notation to find names such as ‘McJohn’ or ‘Johnny’. To use the ‘Like’ keyword with Sql Command parameters you cannot simply use:
instead, you need to add the ‘%’ signs to the AddWith Value command:
There is a trick, though, if your query contains the ‘Like’ keyword.
As an example, consider the following code snippet which opens a database connection to a data reader and populates a gridview:
Dim conString As String =
WebConfigurationManager.ConnectionStrings("myCon").ConnectionString
Dim con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand("select * from People where name = "
& txtName.txt, con)
With con
con.Open()
Gridview1.DataSource = cmd.ExecuteReader()
Gridview1.DataBind()
End With
This code is not recommended, as the item in txtName.txt is not being validated or stipped of any potentially nasty input, such as another sql query.
The preferred way is instead to use Parameters.AddWithValue (which superseded Parameters.Add in ASP.NET 2.0). This ensures your parameter is handled correctly when your SQL query is being built:
Dim conString As String =
WebConfigurationManager.ConnectionStrings("myCon").ConnectionString
Dim con As SqlConnection = New SqlConnection(conString)
Dim cmd As SqlCommand = New SqlCommand("select * from People where name = @Name"
, con)
cmd.Parameters.AddWithValue("@Name", txtName.Text)
With con
con.Open()
Gridview1.DataSource = cmd.ExecuteReader()
Gridview1.DataBind()
End With
This query will populate a Gridview with people whose name = John, but you may wish to use the ‘Like’ SQL keyword with the ‘%’ notation to find names such as ‘McJohn’ or ‘Johnny’. To use the ‘Like’ keyword with Sql Command parameters you cannot simply use:
Dim cmd As SqlCommand = New SqlCommand
("select * from People where name Like %@Name%", con)
instead, you need to add the ‘%’ signs to the AddWith Value command:
Dim cmd As SqlCommand = New SqlCommand
("select * from People where name Like @Name", con)
cmd.Parameters.AddWithValue("@Name", "%" + txtName.txt + "%")
