Skip to content
Advertisement

On button click append OR… in SQL Query

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.SqlClient

Public 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 Sub
End Class

Default.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 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

So, say we whack + a few times, and we thus have this:

enter image description here

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 Sub

And 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 Sub

Ok, 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
    Next
End Sub

So, 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 Using

End Sub

so, 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.

1 People found this is helpful
Advertisement