On button click append OR… in SQL Query

Question

Is it possible to append to an SQL query based on number of clicks on a button? Eg. For each time I click on the button (+) I want to add OR [PIN]=@LogID# Where # is a number increasing for everytime I press the plus (+) button. I also would like do be able to decrease the same way by

Accepted Answer

Sure, since you want to be able to add 1 to “N” choices?Then that suggests a repeater solution.So, say we have this markup:

My Pin:

So, we have a + button, a – button (for each row),a database search button, and of course our simple repeater.So, the code to load this up, and say add a row, and delete a row, we get this code:Dim rstData As New DataTableProtected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not IsPostBack Then CreateTable() ViewState("MyTable") = rstData Else rstData = ViewState("MyTable") End IfEnd SubSub CreateTable() rstData.Columns.Add("MyPin", GetType(String))End SubSo, say we whack + a few times, and we thus have this:So, each time we click the + key, we get a new row. We are free to type in a value.So, our add button code is this:Protected Sub cmdAdd_Click(sender As Object, e As ImageClickEventArgs) Handles cmdAdd.Click RepToTable() ' save any user edits Dim OneRow As DataRow OneRow = rstData.NewRow OneRow("MyPin") = rstData.Rows.Count + 1 ' optional default value?? rstData.Rows.Add(OneRow) Repeater1.DataSource = rstData Repeater1.DataBind()End SubAnd of course we need the – button, say like this:Protected Sub cmdMinus_Click(sender As Object, e As ImageClickEventArgs) RepToTable() ' save edits ' remove the row user clicked on Dim cmdDel As ImageButton = sender Dim rowClick As RepeaterItem = cmdDel.Parent rstData.Rows(rowClick.ItemIndex).Delete() rstData.AcceptChanges() Repeater1.DataSource = rstData Repeater1.DataBind()End SubOk, so that gives us, “add as many as we want”Or remove a row.I do have that little routine that when we add or remove (or search), the user MIGHT have editing the values, so we have a routine to take the repeater rows, and send them back to the table. That little routine is this:Sub RepToTable() ' move values from grid back to table For Each rRow As RepeaterItem In Repeater1.Items rstData.Rows(rRow.ItemIndex).Item("MyPin") = CType(rRow.FindControl("txtPin"), TextBox).Text NextEnd SubSo, now the only “hard” part is the search routine.We need N options.We can NOT use sql concattion since this is USER input. If we were to generate the numbers and user could NOT edit/change, then ok, you could just concenate here.So, we now need the “list” of choices and we STILL want strong typed parameters, nd we still want sql injection protection.So, this will work:Protected Sub cmdSearch_Click(sender As Object, e As EventArgs) Handles cmdSearch.Click RepToTable() ' save any user edits Using cmdSQL As New SqlCommand("", New SqlConnection(My.Settings.TEST4)) Dim strWhere As String = "" ' grab each repater row, add a paramter - no concat of user input - no sql injection For i = 0 To Repeater1.Items.Count - 1 If strWhere <> "" Then strWhere &= "," strWhere &= "@" & i Dim txtPIN As TextBox = Repeater1.Items(i).FindControl("txtPin") cmdSQL.Parameters.Add("@" & i, SqlDbType.Int).Value = txtPIN.Text Next cmdSQL.CommandText = "SELECT * from tblHotels WHERE ID IN (" & strWhere & ")" cmdSQL.Connection.Open() Dim rstResults As New DataTable rstResults.Load(cmdSQL.ExecuteReader) For Each OneRow As DataRow In rstResults.Rows Debug.Print("Hotel name = " & OneRow("City")) Debug.Print("City = " & OneRow("City")) Next End UsingEnd Subso, not too much code. I think one could “try” just adding HTML for the add button but as the above shows? We have really un-limited flexibility, and you can add quite much plain jane markup to that repeater, and add more functionally over time – and not really have to change much code.

Advertisement

Answer