使用列表框在我的sql语句中声明参数

发布于 2024-10-09 07:15:05 字数 1360 浏览 4 评论 0原文

我有一个允许选择多个值的列表框。

这是我对 gridview 的查询

saocmd.CommandText = "SELECT B603SalesAsOFMASTER.SDESCR, B603SalesAsOFMASTER.DYYYY, B603SalesAsOFMASTER.AsOFSales, B603SalesAsOFMASTER.ASOFPAX, B603SalesAsOFMASTER.YESales, B603SalesAsOFMASTER.YEPAX, B603SalesAsOFMASTER.PCTofSales, B601SalesAsOF.Sales AS CurrentSales, B601SalesAsOF.PAX AS CurrentPAX FROM B603SalesAsOFMASTER INNER JOIN B601SalesAsOF ON B603SalesAsOFMASTER.SDESCR = B601SalesAsOF.SDESCR WHERE (B603SalesAsOFMASTER.DYYYY =@Dyyyy) AND (B601SalesAsOF.DYYYY = (year( getdate() ))) and B603SalesAsOFMASTER.SDESCR in (@regions)order by B603SalesAsOFMASTER.SDESCR"

这是我对列表框的查询

listcmd.CommandText = "SELECT distinct B603SalesAsOFMASTER.SDESCR FROM B603SalesAsOFMASTER"

我希望用户选择他们想要在 gridview 中查询的所有区域。

截至目前,我将每个选定的列表项放入文本框中,

Function list()
    Dim li As ListItem
       For Each li In ListBox1.Items
         If li.Selected Then
            TextBox1.Text &= "'" & li.Text & "' ," & vbCrLf
       End If
Next
End Function

然后在运行查询之前我使用 减去最后的,所以我不会收到错误,我必须在查询中使用 textbox1.text 而不是参数。这比较慢并且是 sql 注入,我需要帮助以更好的方式做到这一点,谢谢

If TextBox1.Text.EndsWith(",") Then
        TextBox1.Text = TextBox1.Text.Substring(0, TextBox1.Text.Length - 1)
            End If

I have a list box that allows multiple values to be selected.

Here is my query for my gridview

saocmd.CommandText = "SELECT B603SalesAsOFMASTER.SDESCR, B603SalesAsOFMASTER.DYYYY, B603SalesAsOFMASTER.AsOFSales, B603SalesAsOFMASTER.ASOFPAX, B603SalesAsOFMASTER.YESales, B603SalesAsOFMASTER.YEPAX, B603SalesAsOFMASTER.PCTofSales, B601SalesAsOF.Sales AS CurrentSales, B601SalesAsOF.PAX AS CurrentPAX FROM B603SalesAsOFMASTER INNER JOIN B601SalesAsOF ON B603SalesAsOFMASTER.SDESCR = B601SalesAsOF.SDESCR WHERE (B603SalesAsOFMASTER.DYYYY =@Dyyyy) AND (B601SalesAsOF.DYYYY = (year( getdate() ))) and B603SalesAsOFMASTER.SDESCR in (@regions)order by B603SalesAsOFMASTER.SDESCR"

Here is my query for my listbox

listcmd.CommandText = "SELECT distinct B603SalesAsOFMASTER.SDESCR FROM B603SalesAsOFMASTER"

I want the user to select all the regions they want to query in the gridview.

as of now i am putting each selected list item into a textbox

Function list()
    Dim li As ListItem
       For Each li In ListBox1.Items
         If li.Selected Then
            TextBox1.Text &= "'" & li.Text & "' ," & vbCrLf
       End If
Next
End Function

then before the query is run i use
to subtract the final , so i dont get an error and i have to use textbox1.text in my query and not the parameter. that is slower and sql injection, i need help doing this a better way thanks

If TextBox1.Text.EndsWith(",") Then
        TextBox1.Text = TextBox1.Text.Substring(0, TextBox1.Text.Length - 1)
            End If

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

白鸥掠海 2024-10-16 07:15:05

您不能使用逗号分隔列表作为选择命令的参数。您必须编写一个表值函数来迭代数据库中的参数(MS-SQL-Server?):逗号分隔的值列表

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END

You can't use your comma separated list as parameter for your select-command. You will have to write a table-valued-function that iterates your parameters in your database(MS-SQL-Server?): Comma-separated List of Values

CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int

   SELECT @pos = 0, @nextpos = 1

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
   RETURN
END
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文