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 using the minus (-) button.
A new textfield should appear for every (+) click with the same number # as the appending line in the query OR [PIN]=@LogID# to my SQL query SelectCommand="SELECT * FROM [PINCode] WHERE ([PIN]=@LogID)". And decrease if I click on (-) button.
Any idea on how to do this? I would appreciate any links or suggestions pointing me in the right direction.
I´m not looking for a finished solution here just help on were to start. What to search for, where to read.
Implemented the example script recieved
Default.aspx.vb
Imports System.Data.SqlClientPublic Class _Default Inherits System.Web.UI.Page Dim rstData As New DataTable Protected 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 If End Sub Sub CreateTable() rstData.Columns.Add("MyPin", GetType(String)) End Sub 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 Sub Protected Sub Repeater1_ItemCommand(source As Object, e As RepeaterCommandEventArgs) Handles Repeater1.ItemCommand End Sub 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 Sub 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 Next End Sub 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()) 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 PINCode WHERE PIN IN (" & strWhere & ")" cmdSQL.Connection.Open() Dim rstResults As New DataTable rstResults.Load(cmdSQL.ExecuteReader) For Each OneRow As DataRow In rstResults.Rows Debug.Print("Computername = " & OneRow("PIN")) Debug.Print("PIN = " & OneRow("PIN")) Next End Using End Sub Protected Sub PINCodeConnectionString_Selecting(sender As Object, e As SqlDataSourceSelectingEventArgs) Handles PINCodeConnectionString.Selecting End SubEnd ClassDefault.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="PINCode._Default" %><!DOCTYPE html><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title></title></head><body> <form id="form1" runat="server"> <asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> <div style="float:left"> My Pin: <asp:TextBox ID="txtPin" runat="server" Text = '<%# Eval("MyPin") %>' ></asp:TextBox> </div> <div style="float:left;margin-left:10px"> <asp:ImageButton ID="cmdMinus" runat="server" ImageUrl="Content/minus.jpg" Style="height:26px;width:20px" OnClick="cmdMinus_Click"/> </div> <div style="clear:both"></div> </ItemTemplate> </asp:Repeater> <br /> <asp:ImageButton ID="cmdAdd" runat="server" ImageUrl="Content/Plus.jpg" Height="46" Width="40" style="margin-left:20px" /> <br /> <br /> <asp:Button ID="cmdSearch" runat="server" Text="Search Database" Width="133px" /> <asp:SqlDataSource ID="PINCodeConnectionString" runat="server" ConnectionString="<%$ ConnectionStrings:PINCodeConnectionString %>" SelectCommand="SELECT * FROM [PINCode]" UpdateCommand="UPDATE [PINCode] SET [Status] = @Status"> <UpdateParameters> <asp:Parameter Name="Status" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" CellPadding="4" DataSourceID="PINCodeConnectionString" ForeColor="#333333" GridLines="None"> <AlternatingRowStyle BackColor="White" /> <Columns> <asp:CommandField ShowEditButton="True" ShowSelectButton="True" /> </Columns> <EditRowStyle BackColor="#2461BF" /> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#EFF3FB" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <SortedAscendingCellStyle BackColor="#F5F7FB" /> <SortedAscendingHeaderStyle BackColor="#6D95E1" /> <SortedDescendingCellStyle BackColor="#E9EBEF" /> <SortedDescendingHeaderStyle BackColor="#4870BE" /> </asp:GridView> </form></body></html>Advertisement
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:
<asp:Repeater ID="Repeater1" runat="server"> <ItemTemplate> <div style="float:left"> My Pin: <asp:TextBox ID="txtPin" runat="server" Text = '<%# Eval("MyPin") %>' ></asp:TextBox> </div> <div style="float:left;margin-left:10px"> <asp:ImageButton ID="cmdMinus" runat="server" ImageUrl="Content/minus.png" Style="height:26px;width:20px" OnClick="cmdMinus_Click"/> </div> <div style="clear:both"></div> </ItemTemplate> </asp:Repeater> <br /> <asp:ImageButton ID="cmdAdd" runat="server" ImageUrl="Content/Add.png" Height="46" Width="40" style="margin-left:20px" /> <br /> <br /> <asp:Button ID="cmdSearch" runat="server" Text="Search Database" Width="133px" />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.