sql - 在按钮上单击附加 OR... 在 SQL 查询中

问题描述

是否可以根据按钮的点击次数附加到 SQL 查询？

例如。每次我单击按钮(+)时，我想添加OR [PIN]=@LogID#其中#是一个数字，每次按下加号(+)按钮时都会增加。

我也希望能够通过使用减号(-)按钮以同样的方式减少。

每次单击(+)都会出现一个新的文本字段，其编号OR [PIN]=@LogID#与我的 SQL 查询查询中的附加行相同SelectCommand="SELECT * FROM [PINCode] WHERE ([PIN]=@LogID)"。如果我点击（-）按钮，则减少。

关于如何做到这一点的任何想法？我将不胜感激任何指向正确方向的链接或建议。

我不是在这里寻找一个完整的解决方案，只是帮助开始。搜索什么，在哪里阅读。

实施收到的示例脚本

默认.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

默认.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>

标签： sqlasp.netappendimagebutton

解决方案

当然，既然您希望能够将 1 添加到“N”个选项中？

然后，这提出了一个中继器解决方案。

所以，假设我们有这个标记：

        <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" />

因此，我们有一个 + 按钮、一个 - 按钮（用于每一行）、一个数据库搜索按钮，当然还有我们的简单中继器。

所以，加载这个的代码，说添加一行，删除一行，我们得到这个代码：

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

所以，现在唯一的“困难”部分是搜索例程。

我们需要 N 个选项。

我们不能使用 sql concattion，因为这是 USER 输入。如果我们要生成数字并且用户不能编辑/更改，那么好的，你可以在这里合并。

所以，我们现在需要选择的“列表”，我们仍然需要强类型参数，我们仍然需要 sql 注入保护。

因此，这将起作用：

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

所以，没有太多的代码。我认为可以“尝试”为添加按钮添加 HTML，但如上所示？我们拥有无限的灵活性，您可以向该转发器添加相当多的普通 jane 标记，并随着时间的推移添加更多功能 - 而不必更改太多代码。

sql - 在按钮上单击附加 OR... 在 SQL 查询中

问题描述

解决方案

推荐阅读