Re: Parameters

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: cbDevelopment (development_at_remove.700cb.net)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 14:58:14 -0700

OK. I did not see the code from Function1, but assume it is doing
something like:

select *
from table
where something=@field1
            and somethingelse=@field2
            and somethingmore=@field3

and it works when field1/2/3 are single values (2000,2002,and 2003 for
example)

Now you need field1 to be "2002,2003,2004" AND you need field2 to be
"2001,2002,2003" and you need field3 to be "2002,2003". So your
function1 logic becomes something like:

select *
from table
where something in (@field1)
            and somethingelse in (@field2)
            and somethingmore in (@field3)

But you just can't do that if field1="2002,2003,2004". The IN clause
doesn't parse CSV values. You need to get those values into a table and
select them back out.

Add this function to your server:

ALTER FUNCTION CSVToTable (@CSVList varchar(8000))
RETURNS @csvtable table (val varchar(1000))
AS
BEGIN
-- variables for position marking
declare @separatorposition int, @arrayvalue varchar(1000)
-- Pad the list if needed
if substring(rtrim(@csvlist),len(rtrim(@csvlist)),1)<>','
        set @csvlist = @csvlist + ','
-- Loop through string
while patindex('%,%', @csvlist) <> 0
begin
        select @separatorPosition = patindex('%,%', @csvlist)
        select @arrayValue = left(@csvlist, @separatorPosition - 1)
        INSERT into @csvtable(val) values (rtrim(ltrim(@arrayValue)))
        
        select @csvlist = stuff(@csvlist,1,@separatorPosition, '')
end
-- return table
return
END

Now your function1 logic can be:

select *
from table
where something in (select val from dbo.CSVToTable(@field1))
            and somethingelse in (select val from dbo.CSVToTable(@field2))
            and somethingmore in (select val from dbo.CSVToTable(@field3))

This also means you need to change your input variables for function1 and
function2 and my_stored_procedure to accept varchar(8000) (or nvarchar)
instead of varchar(5).

Now for your VB code. Your SQL input parameters are going to expect a
comma-separated value for each listbox value. So you're going to need
something like:

sField1Values="2001,2002,2003"
Me.SqlSelectCommand1.Parameters("@field1").Value = sField1Values

and so one for the others. You can use the stringbuilder sample code
from my last post to build your CSV string to use.

To summarize. Your SQL functions are only accepting a single value. You
need to expand those fields to accept a whole bunch of values separated
by commas. One the values are in the SQL functions, you need to parse
them out and use them in an IN clause. To parse them for use in an IN
clause, you need to ise another function to break the CSV into a table.
            
Does any of that help?

"=?Utf-8?B?QWRpcw==?=" <Adis@discussions.microsoft.com> wrote in
news:EE3FE949-543A-46E2-B76A-E2AEEEE5789A@microsoft.com:

> Hi,
>
> Thank you for your replay.
>
> I need run "My_Store_Procedure" and pass the parameters multiselected
> in listbox1. But I no find how do it.
>
> In the following lines I show function, Store_Procedure,HTML and no
> working code.
>
> I have one view, function1, function2 and My_Store_Procedure. My
> function2 lock
>
> like this:
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER FUNCTION dbo.My_Function2(@order nvarchar (10),
> @field1 nvarchar (5),
> @field2 nvarchar (5),
> @field3 nvarchar (5))
> RETURNS TABLE
> AS
> RETURN ( SELECT TOP 100 PERCENT field4, field5, field6, ORDER,
> SUM(field1)
>
> AS Myfield1, SUM(field2) AS Myfield2, SUM(field3) AS Myfield3
> FROM My_Function1(@order, @field1, @field2, @field3)
> GROUP BY field4, field5, ORDER, field4, field6
> ORDER BY field4 )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> My_Store_Procedure is derived at My_Function2:
>
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
>
> ALTER PROCEDURE dbo.My_Store_Procedure
> (@order nvarchar(10),
> @field1 nvarchar (5),
> @field2 nvarchar (5),
> @field3 nvarchar (5))
> AS SELECT *
> FROM dbo.My_Function2(@order, @field1,@field2, @field3 )
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> -----------------------------------------------------------------------
> ------- <HTML>
> <HEAD>
> <title>WebForm1</title>
> <meta content="Microsoft Visual Studio .NET 7.1"
> name="GENERATOR"> <meta content="Visual Basic .NET 7.1"
> name="CODE_LANGUAGE"> <meta content="JavaScript"
> name="vs_defaultClientScript"> <meta
> content="http://schemas.microsoft.com/intellisense/ie5"
> name="vs_targetSchema">
> </HEAD>
> <body MS_POSITIONING="GridLayout">
> <form id="Form1" method="post" runat="server">
> <CR:CRYSTALREPORTVIEWER id=CrystalReportViewer1
> style="Z-INDEX: 101;
> LEFT: 24px; POSITION: absolute; TOP: 168px" runat="server"
> DisplayGroupTree="False" DisplayToolbar="False" Width="753px"
> Height="1095px" ReportSource='<%#
> "c:\inetpub\wwwroot\crystal1\CrystalReport1.rpt" %>' Visible="False"
> ToolTip="Select">
> </CR:CRYSTALREPORTVIEWER><asp:dropdownlist
> id="Dropdownlist4"
> style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 8px"
> runat="server" Width="88px" Height="24px"
> OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
> <asp:ListItem Value="1998"
> Selected="True">1998</asp:ListItem>
> <asp:ListItem Value="1999">1999</asp:ListItem>
> <asp:ListItem Value="2000">2000</asp:ListItem>
> <asp:ListItem Value="2001">2001</asp:ListItem>
> <asp:ListItem Value="2002">2002</asp:ListItem>
> <asp:ListItem Value="2003">2003</asp:ListItem>
> <asp:ListItem Value="2004P">2004P</asp:ListItem>
> <asp:ListItem Value="2004">2004</asp:ListItem>
> </asp:dropdownlist><asp:dropdownlist id="Dropdownlist2"
> style="Z-INDEX:
> 105; LEFT: 112px; POSITION: absolute; TOP: 8px"
> runat="server" Width="88px" Height="24px"
> OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
> <asp:ListItem Value="1998">1998</asp:ListItem>
> <asp:ListItem Value="1999"
> Selected="True">1999</asp:ListItem>
> <asp:ListItem Value="2000">2000</asp:ListItem>
> <asp:ListItem Value="2001">2001</asp:ListItem>
> <asp:ListItem Value="2002">2002</asp:ListItem>
> <asp:ListItem Value="2003">2003</asp:ListItem>
> <asp:ListItem Value="2004P">2004P</asp:ListItem>
> <asp:ListItem Value="2004">2004</asp:ListItem>
> </asp:dropdownlist><asp:dropdownlist id="Dropdownlist3"
> style="Z-INDEX:
> 104; LEFT: 208px; POSITION: absolute; TOP: 8px"
> runat="server" Width="81" Height="24px"
> OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
> <asp:ListItem Value="1998">1998</asp:ListItem>
> <asp:ListItem Value="1999">1999</asp:ListItem>
> <asp:ListItem Value="2000"
> Selected="True">2000</asp:ListItem>
> <asp:ListItem Value="2001">2001</asp:ListItem>
> <asp:ListItem Value="2002">2002</asp:ListItem>
> <asp:ListItem Value="2003">2003</asp:ListItem>
> <asp:ListItem Value="2004P">2004P</asp:ListItem>
> <asp:ListItem Value="2004">2004</asp:ListItem>
> </asp:dropdownlist><asp:label id="Label1"
> style="Z-INDEX: 103; LEFT:
> 336px; POSITION: absolute; TOP: 8px" runat="server"
> Width="288px" Height="20px" BorderStyle="None"
> BorderColor="#0000C0"
> BackColor="RoyalBlue" ForeColor="White"
> Font-Bold="True">Conceptos</asp:label><asp:listbox
> id="listbox1"
> style="Z-INDEX: 102; LEFT: 336px; POSITION: absolute; TOP: 32px"
> runat="server"
> Width="288px" Height="116px"
> OnSelectedIndexChanged="ChangeWhereClause"
> AutoPostBack="True"></asp:listbox><asp:button id="Button1"
> style="Z-INDEX: 106; LEFT: 232px; POSITION: absolute; TOP: 56px"
> runat="server"
> Width="72px" BackColor="#C0C0FF" Text="Button1"
> Enabled="False"></asp:button><asp:button id="Button2" style="Z-INDEX:
> 108; LEFT: 232px; POSITION: absolute; TOP: 96px" runat="server"
> Width="72px" BackColor="#FFC0FF"
> Text="Exportar"></asp:button><asp:dropdownlist id="DropDownList1"
> style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 96px"
> runat="server" Width="184px"
> Height="24px"></asp:dropdownlist>
> </form>
> </body>
> </HTML>
> -----------------------------------------------------------------------
>
> #Region
> ....
>
> Me.SqlSelectCommand1.CommandText = "[My_Store_Procedure]"
> Me.SqlSelectCommand1.CommandType =
> System.Data.CommandType.StoredProcedure
> Me.SqlSelectCommand1.Connection = Me.SqlConnection1
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
> System.Data.SqlDbType.Int, 4,
> System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
> CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@order",
> System.Data.SqlDbType.NVarChar, 10))
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@field1",
> System.Data.SqlDbType.NVarChar, 5))
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@field2",
> System.Data.SqlDbType.NVarChar, 5))
> Me.SqlSelectCommand1.Parameters.Add(New
> System.Data.SqlClient.SqlParameter("@field3",
> System.Data.SqlDbType.NVarChar, 5))
>
> ....
> ------------------------
>
> Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
> Dim strWhereClause As String = ""
> For Each li in listbox1.Items
> If li.Selected Then
>
> strWhereClause &= "order=" & li.Value & " Or " '<----------
> what is wrong?
>
> End If
> Next
> If strWhereClause.Length > 0 Then
>
> strWhereClause = Left(strWhereClause, strWhereClause.Length() -
> 4) strWhereClause = "WHERE " & strWhereClause
> Dim strSql = "Select * " _
> & "From My_Store_Procedure " & strWhereClause & "
> Order By
> LastName"
>
> Me.SqlSelectCommand1.Parameters("@field1").Value =
> Dropdownlist4.SelectedItem.Text
> Me.SqlSelectCommand1.Parameters("@field2").Value =
> Dropdownlist2.SelectedItem.Text
> Me.SqlSelectCommand1.Parameters("@field3").Value =
> Dropdownlist3.SelectedItem.Text
> Me.SqlDataAdapter1.Fill(DataSet21.My_Store_Procedure)
>
> Dim oRpt As New CrystalReport1
> oRpt.SetDataSource(DataSet21)
> CrystalReportViewer1.Visible = True
> CrystalReportViewer1.ReportSource = oRpt
>
> End Sub
> ...
>
> In the listbox simple selection mode i used the following code:
>
> Me.SqlSelectCommand1.Parameters("@order").Value =
> listbox1.SelectedItem.Value
>
> and work fine.
>
> In the listbox multiple selection i Tryed put:
>
> strWhereClause &= "order=" &
> Me.SqlSelectCommand1.Parameters("@order").value = li.Value
> & " Or " ' <-------------------- what is wrong?
>
> instead of:
>
> strWhereClause &= "EmployeeID=" & li.Value & " Or "
>
> ...and no work.
>
> Thank you again,
>
> Adis.

Quantcast