Parameters

Adis

Asp.Net
Visual Studio 2003
SQL Server.

Hi,

Obtaining Data Based Upon Multiple Selections From a ListBox...

I have database in Sqlserver and ListBox (Multiple Selection Mode) in my
Visual Studio Webform. I wish obtain various records from My_Store_Procedure
and fill dataset.
I used the following code and no work:

.....

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 &= "EmployeeID=" & 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"
....

In the listbox simple selection mode i used the following code:

Me.SqlSelectCommand1.Parameters("@EmployeeID").Val ue =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "EmployeeID=" &
Me.SqlSelectCommand1.Parameters("@EmployeeID").val ue = li.Value
& " Or " ' <-------------------- what is wrong?
instead of:
strWhereClause &= "EmployeeID=" & li.Value & " Or "

....and no work.

In listbox multiselection mode:

How to dealing with parameters?

How to pass the parameter? "@EmployeeID".

For example: @EmployeeID instead EmployeeID

Thank you in advance,

Adis.

Nov 18 '05 #1

Subscribe Post Reply

2316

cbDevelopment

Form what I see, it seems you are trying to pass multiple values to a
parameter that only wants one value. I also do not see in your SQL where
you are defining a parameter, so I am not sure whats going on...

There are two ways you can accomplish what you want. You can stay with
generated sql and drop the stored procedure, or you can make the stored
procedure accept a varchar value, pass it a comma-separated string of
values, then handle it in the SP code.

In your case, I would lean towards the first choice. Here's a better way
to do what you want. This uses a stringbuilder which is more efficient
for joining strings and the IN clause which is better for matching a
value from a list.

dim sSQL as new system.text.stringbuilder
with sSQL
' we need to have at least one value in case nothing is selected
.append("select * from CustomerTable where EmployeeID in (0,")
for each li in listbox1.items
if li.selected then .append(li.value & ",")
next
.length -=1 ' trim off the trailing comma
.append (") order by lastname")
end with

Other observations:

It looks like your performing a query against a stored procedure. You
can't do that. You just run it and pass the parameters. Your stored
procedure call may look like:

dim iEmployeesID as integer = 151
dim sSQL as string="My_Stored_Procedure " & iEmployeeID

Does this mean you can do:

dim sEmployeeIDs as string= "151,152,153,154,155"
dim sSQL as string="My_Stored_Procedure " & iEmployeeID

Yes, but you have to handle that in your stored procedure. One thing at
a time first though. If the generated SQL doesn't do it for you, or you
really need to use a stored procedure (need, not want), I will clarify.

Best of luck!
"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:F0**********************************@microsof t.com:

Asp.Net
Visual Studio 2003
SQL Server.

Hi,

Obtaining Data Based Upon Multiple Selections From a ListBox...

I have database in Sqlserver and ListBox (Multiple Selection Mode) in
my Visual Studio Webform. I wish obtain various records from
My_Store_Procedure and fill dataset.
I used the following code and no work:

....

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 &= "EmployeeID=" & 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"
...

In the listbox simple selection mode i used the following code:

Me.SqlSelectCommand1.Parameters("@EmployeeID").Val ue =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "EmployeeID=" &
Me.SqlSelectCommand1.Parameters("@EmployeeID").val ue = li.Value
& " Or " ' <-------------------- what is wrong?
instead of:
strWhereClause &= "EmployeeID=" & li.Value & " Or "

...and no work.

In listbox multiselection mode:

How to dealing with parameters?

How to pass the parameter? "@EmployeeID".

For example: @EmployeeID instead EmployeeID

Thank you in advance,

Adis.

Nov 18 '05 #2

Adis

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.rp t" %>'
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_Procedu re)

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.

"cbDevelopment" wrote:

Form what I see, it seems you are trying to pass multiple values to a
parameter that only wants one value. I also do not see in your SQL where
you are defining a parameter, so I am not sure whats going on...

There are two ways you can accomplish what you want. You can stay with
generated sql and drop the stored procedure, or you can make the stored
procedure accept a varchar value, pass it a comma-separated string of
values, then handle it in the SP code.

In your case, I would lean towards the first choice. Here's a better way
to do what you want. This uses a stringbuilder which is more efficient
for joining strings and the IN clause which is better for matching a
value from a list.

dim sSQL as new system.text.stringbuilder
with sSQL
' we need to have at least one value in case nothing is selected
.append("select * from CustomerTable where EmployeeID in (0,")
for each li in listbox1.items
if li.selected then .append(li.value & ",")
next
.length -=1 ' trim off the trailing comma
.append (") order by lastname")
end with

Other observations:

It looks like your performing a query against a stored procedure. You
can't do that. You just run it and pass the parameters. Your stored
procedure call may look like:

dim iEmployeesID as integer = 151
dim sSQL as string="My_Stored_Procedure " & iEmployeeID

Does this mean you can do:

dim sEmployeeIDs as string= "151,152,153,154,155"
dim sSQL as string="My_Stored_Procedure " & iEmployeeID

Yes, but you have to handle that in your stored procedure. One thing at
a time first though. If the generated SQL doesn't do it for you, or you
really need to use a stored procedure (need, not want), I will clarify.

Best of luck!
"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:F0**********************************@microsof t.com:
Asp.Net
Visual Studio 2003
SQL Server.

Hi,

Obtaining Data Based Upon Multiple Selections From a ListBox...

I have database in Sqlserver and ListBox (Multiple Selection Mode) in
my Visual Studio Webform. I wish obtain various records from
My_Store_Procedure and fill dataset.
I used the following code and no work:

....

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 &= "EmployeeID=" & 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"
...

In the listbox simple selection mode i used the following code:

Me.SqlSelectCommand1.Parameters("@EmployeeID").Val ue =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "EmployeeID=" &
Me.SqlSelectCommand1.Parameters("@EmployeeID").val ue = li.Value
& " Or " ' <-------------------- what is wrong?
instead of:
strWhereClause &= "EmployeeID=" & li.Value & " Or "

...and no work.

In listbox multiselection mode:

How to dealing with parameters?

How to pass the parameter? "@EmployeeID".

For example: @EmployeeID instead EmployeeID

Thank you in advance,

Adis.

Nov 18 '05 #3

cbDevelopment

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==?=" <Ad**@discussions.microsoft.com> wrote in
news:EE**********************************@microsof t.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.rp t" %>' 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_Procedu re)

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.

Nov 18 '05 #4

Adis

Hi,

Thank you for your usefull help.

I still don't get some basics.

I have simplified database and code, to make my project easier to explain.

1) Table: tbMyTable.

Id orderiD year value
01 101 1998 15
02 101 1999 20
03 101 2000 10
04 101 2001 14
05 102 1998 8
06 102 1999 16
07 102 2000 14
08 102 2001 9
09 103 1998 13
10 103 1999 23
11 103 2000 7
12 103 2001 5

2) Table: csvtable
I must be doing something totally wrong ....The function CSVToTable have ref
to csvtable and in your second post i don't see this table in detail. I
assumed this table (csvtable) would be lock like closer to:

CSVList val id
1998 1 1
1999 2 2
2000 3 3
2001 4 4

3) View: vsMyTable

ALTER VIEW dbo.vsMyTable
AS
SELECT *
FROM dbo.tbMyTable

4) I added function CSVToTable in my server. Ok.

5) Now, I don't clearly understand the following step:

a) Take MyFunction1, OR

b) Create Function1 as your recommend.
-----

a)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fnMyFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT year, orderid, CASE WHEN aÃ±o = @year1 THEN valor ELSE 0
END AS year1,
CASE WHEN aÃ±o = @year2 THEN valor ELSE 0 END AS year2,
CASE WHEN aÃ±o = @year3 THEN valor ELSE 0 END AS year3
FROM dbo.vsMyTable ' ref to My View.
WHERE (aÃ±o = @year1) OR
(aÃ±o = @year2) OR
(aÃ±o = @year3) )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------

b)

select *
from table ---------> 'you ref from "Table", must be a view or function?
anyway not work for me.
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))

I must be doing something totally wrong ....I wrongly applied the upper
syntaxis:

Create FUNCTION dbo.fnFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT *
FROM dbo.fnMyFunction1 ----------->' developed in (a). I refer from
"MyFunction1", and not work for me.

WHERE year1 in (select val from dbo.CSVToTable (@year1)) and
year2 in (select val from dbo.CSVToTable (@year2)) and
year3 in (select val from dbo.CSVToTable (@year3)))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----

The upper function no work, my interpretation is erroneous. I receive Server
Msg 208, Level 16,

State 3, Procedure fnFunction1, Line 8
Invalid object name 'dbo.fnMyFunction1'.

NOTE: "fnMyFunction1" is a) function, and "fnFunction1" is your recommended
function in b).

I need, as minimum, clarify to me step 2 and step 5. I assum the following
step 6 as something like:

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

and use the stringbuilder sample code from your last post to build your CSV
string to use.

Thank you so much for all your help...

Adis

"cbDevelopment" wrote:

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==?=" <Ad**@discussions.microsoft.com> wrote in
news:EE**********************************@microsof t.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.rp t" %>' 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_Procedu re)

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.

Nov 18 '05 #5

cbDevelopment

1: It woud help a lot if you could give me the real names of the tables
and stored procedures. This would give me a context to work from.
"MyStoredProcedure" and "MyTable" mean nothing, but "sp_GetYearlySales"
and "CustomerSales" mean something. I can then figure out from context
what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a temporary
table that will be removed after the function completes. The structure
of this table variable is a single field. If you pass in "1,2,3,4,5", it
returns:

val
-----
1
2
3
4
5

3: I don't understand why a view is being introduced. We don't need to
use a view for anything.

4: You can test that fn_CSVToTable is installed in your server by going
to query analyser and executing:

select * from dbo.fn_CSVToTable('1,2,3,4,5')

You will get the results shown above. If you don't, there is something
wrong.

5: I hope you understand that the generic names are confusing me. From
what I understand so far, your original attempt was having ASP.NET call a
stored procedure. This stored procedure called a function. That
function called another function. I think they were called
MyStoredProcedure, MyFunction2 and MyFunction1.

Function1 must be returning some sort of table and Function2 is doing a
SUM and GROUP on the results. There is an odd line "My_Store_Procedure
is derived at My_Function2" Do you mean that My_Store_Procedure _is_
My_function2?

Lets see if we can start this over...

"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:B5**********************************@microsof t.com:

Hi,

Thank you for your usefull help.

I still don't get some basics.

I have simplified database and code, to make my project easier to
explain.

1) Table: tbMyTable.

Id orderiD year value
01 101 1998 15
02 101 1999 20
03 101 2000 10
04 101 2001 14
05 102 1998 8
06 102 1999 16
07 102 2000 14
08 102 2001 9
09 103 1998 13
10 103 1999 23
11 103 2000 7
12 103 2001 5

2) Table: csvtable
I must be doing something totally wrong ....The function CSVToTable
have ref to csvtable and in your second post i don't see this table in
detail. I assumed this table (csvtable) would be lock like closer to:

CSVList val id
1998 1 1
1999 2 2
2000 3 3
2001 4 4

3) View: vsMyTable

ALTER VIEW dbo.vsMyTable
AS
SELECT *
FROM dbo.tbMyTable

4) I added function CSVToTable in my server. Ok.

5) Now, I don't clearly understand the following step:

a) Take MyFunction1, OR

b) Create Function1 as your recommend.
-----

a)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fnMyFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT year, orderid, CASE WHEN aÃ±o = @year1 THEN valor
ELSE 0 END AS year1,
CASE WHEN aÃ±o = @year2 THEN valor ELSE 0 END AS
year2, CASE WHEN aÃ±o = @year3 THEN valor ELSE 0
END AS year3
FROM dbo.vsMyTable ' ref to My View.
WHERE (aÃ±o = @year1) OR
(aÃ±o = @year2) OR
(aÃ±o = @year3) )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------

b)

select *
from table ---------> 'you ref from "Table", must be a view or
function? anyway not work for me.
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))

I must be doing something totally wrong ....I wrongly applied the
upper syntaxis:

Create FUNCTION dbo.fnFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT *
FROM dbo.fnMyFunction1 ----------->' developed in (a). I refer from
"MyFunction1", and not work for me.

WHERE year1 in (select val from dbo.CSVToTable (@year1)) and
year2 in (select val from dbo.CSVToTable (@year2)) and
year3 in (select val from dbo.CSVToTable (@year3)))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----

The upper function no work, my interpretation is erroneous. I receive
Server Msg 208, Level 16,

State 3, Procedure fnFunction1, Line 8
Invalid object name 'dbo.fnMyFunction1'.

NOTE: "fnMyFunction1" is a) function, and "fnFunction1" is your
recommended function in b).

I need, as minimum, clarify to me step 2 and step 5. I assum the
following step 6 as something like:

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

and use the stringbuilder sample code from your last post to build
your CSV string to use.

Thank you so much for all your help...

Adis

Nov 18 '05 #6

Adis

Hi,

Thank you.
You are helping me a lot.

Below I sent you project code.
About fn_CSVToTable: understood.
I have introduced view due to complex joins.
fn_CSVToTable: work OK.
-----------------------
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports System.io
Imports System.Configuration
Imports Microsoft.VisualBasic
Public Class WebForm2
Inherits System.Web.UI.Page
Dim crReportDocument As ReportDocument
Dim crExportOptions As ExportOptions
Dim crDiskFileDestinationOptions As DiskFileDestinationOptions
#Region " CÃ³digo generado por el DiseÃ±ador de Web Forms "

'El DiseÃ±ador de Web Forms requiere esta llamada.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataSet31 = New Crystal1.DataSet3
CType(Me.DataSet31,
System.ComponentModel.ISupportInitialize).BeginIni t()
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New

System.Data.Common.DataTableMapping("Table", "sp_fnBaseAÃ±osSumaCompara", New

System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("empresa",

"empresa"), New System.Data.Common.DataColumnMapping("polo", "polo"), New

System.Data.Common.DataColumnMapping("tit_emp", "tit_emp"), New

System.Data.Common.DataColumnMapping("orden", "orden"), New

System.Data.Common.DataColumnMapping("texto", "texto"), New

System.Data.Common.DataColumnMapping("aÃ±o_anterio r", "aÃ±o_anterior"), New

System.Data.Common.DataColumnMapping("presupuesto" , "presupuesto"), New

System.Data.Common.DataColumnMapping("aÃ±o_actual" , "aÃ±o_actual")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[sp_fnBaseAÃ±osSumaCompara]"
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("@orden",

System.Data.SqlDbType.NVarChar, 10))
Me.SqlSelectCommand1.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@aÃ±o_anterior ",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@presupuesto",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@aÃ±o_actual",

System.Data.SqlDbType.NVarChar, 5))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=ADISPC;packet
size=4096;integrated

security=SSPI;data source=ADI" & _
"SPC;persist security info=False;initial catalog=Orion"
'
'DataSet31
'
Me.DataSet31.DataSetName = "DataSet3"
Me.DataSet31.Locale = New System.Globalization.CultureInfo("es-ES")
CType(Me.DataSet31,
System.ComponentModel.ISupportInitialize).EndInit( )

End Sub
Protected WithEvents CrystalReportViewer1 As
CrystalDecisions.Web.CrystalReportViewer
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents DataSet31 As Crystal1.DataSet3
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents listbox1 As System.Web.UI.WebControls.ListBox
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist3 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist2 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist4 As
System.Web.UI.WebControls.DropDownList
Protected li As System.Web.UI.WebControls.ListItem

'NOTA: el DiseÃ±ador de Web Forms necesita la siguiente declaraciÃ³n del
marcador de posiciÃ³n.
'No se debe eliminar o mover.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

MyBase.Init
'CODEGEN: el DiseÃ±ador de Web Forms requiere esta llamada de mÃ©todo
'No la modifique con el editor de cÃ³digo.
InitializeComponent()
CrystalReportViewer1.ReportSource = crReportDocument
With DropDownList1.Items
.Add("Rich Text (RTF)")
.Add("Portable Document (PDF)")
.Add("MS Word (DOC)")
.Add("MS Excel (XLS)")
.Add("Crystal Report (RPT)")
.Add("HTML 3.2 (HTML)")
.Add("HTML 4.0 (HTML)")
End With

End Sub

#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

MyBase.Load
'Introducir aquÃ* el cÃ³digo de usuario para inicializar la pÃ¡gina
If Not IsPostBack Then

'listbox1.SelectedIndex = 2
Dropdownlist4.SelectedIndex = 2
cargar_listbox()

End If

End Sub
Sub cargar_listbox()
Dim strSql As String = "Select texto, orden From tablaVI_RESULTADOS
Order By orden"
Dim objConn As SqlClient.SqlConnection = _
New SqlConnection("Data Source=adispc;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=Orion")
Dim objCmd As New SqlCommand(strSql, objConn)

Try
objConn.Open()
listbox1.DataSource = objCmd.ExecuteReader()
listbox1.DataTextField = "texto"
listbox1.DataValueField = "orden"
listbox1.DataBind()

Catch exc As SqlException
Response.Write(exc.ToString())
Finally
objConn.Dispose()
End Try
End Sub
Sub ChangeWhereClause(ByVal Sender As System.Object, ByVal e As
System.EventArgs)
'VARIANTE CON LISTBOX MULTIPLE
Dim strWhereClause As String = ""
For Each li In listbox1.Items
If li.Selected Then
strWhereClause &= "orden =" &
Me.SqlSelectCommand1.Parameters("@orden").Value =

li.Value & " Or "

End If
Next

If strWhereClause.Length > 0 Then
strWhereClause = Left(strWhereClause, strWhereClause.Length() -
4)
strWhereClause = "WHERE " & strWhereClause
Dim strSql = "Select *" _
& "From sp_fnBaseAÃ±osSumaCompara " '& strWhereClause & " or " _

End If

'Me.SqlSelectCommand1.Parameters("@orden").Value =
listbox1.SelectedItem.Value
'Me.SqlSelectCommand1.Parameters("@aÃ±o_anterior") .Value =
Dropdownlist4.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@presupuesto").V alue =
Dropdownlist2.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@aÃ±o_actual").V alue =
Dropdownlist3.SelectedItem.Text

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAÃ±osSu maCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt
End Sub
Sub ExportReport()

Dim ExportPath As String
ExportPath = Request.PhysicalApplicationPath + "Exported\"
If Directory.Exists(ExportPath) = False Then
Directory.CreateDirectory(Request.PhysicalApplicat ionPath +
"Exported\")
End If

crDiskFileDestinationOptions = New DiskFileDestinationOptions
crExportOptions = crReportDocument.ExportOptions

Select Case DropDownList1.SelectedItem.Text 'this contains the value
of the selected

export format.

Case "Rich Text (RTF)"

'Export to RTF.
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"RichTextFormat.rtf"
'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.RichText
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "Portable Document (PDF)"
'Export to PDF
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"PortableDoc.pdf"

'set the required report ExportOptions properties
With crExportOptions
.DestinationOptions = crDiskFileDestinationOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.PortableDocFormat
End With

Case "MS Word (DOC)"
'Export to Word
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Word.doc"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.WordForWindows
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "MS Excel (XLS)"
'Export to Excel

'append a filename to the export path and set this file as
the filename property

for
'the DestinationOptions class
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Excel.xls"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.Excel
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "Crystal Report (RPT)"
'Export to Crystal reports:

crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Report.rpt"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.CrystalReport
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "HTML 3.2 (HTML)"
'Export to HTML32:

Dim HTML32Formatopts As New HTMLFormatOptions

With crExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.HTML32
End With

With HTML32Formatopts
.HTMLBaseFolderName = ExportPath + "Html32Folder"
'Foldername to place HTML

files
.HTMLFileName = "HTML32.html"
.HTMLEnableSeparatedPages = False
.HTMLHasPageNavigator = False
End With

crExportOptions.FormatOptions = HTML32Formatopts

Case "HTML 4.0 (HTML)"
'Export to Html 4.0:

Dim HTML40Formatopts As New HTMLFormatOptions

With crExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.HTML40
End With

With HTML40Formatopts
.HTMLBaseFolderName = ExportPath + "Html40Folder" '
Foldername to place HTML

files
.HTMLFileName = "HTML40.html"
.HTMLEnableSeparatedPages = True
.HTMLHasPageNavigator = True
.FirstPageNumber = 1
.LastPageNumber = 3
End With

crExportOptions.FormatOptions = HTML40Formatopts

End Select 'export format

Try
' Export the report
crReportDocument.Export()
Catch err As Exception
Response.Write("<BR>")
Response.Write(err.Message.ToString)
End Try

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

Button1.Click
ExportReport()

End Sub
End Class
--------------------
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm2.aspx.vb"

Inherits="Crystal1.WebForm2"%>
<%@ Register TagPrefix="cr" Namespace="CrystalDecisions.Web"
Assembly="CrystalDecisions.Web,

Version=9.1.5000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm2</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"

content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<CR:CrystalReportViewer id=CrystalReportViewer1 style="Z-INDEX: 100;

LEFT: 48px; POSITION: absolute; TOP: 216px" runat="server" Width="753px"
Height="1095px"

ReportSource='<%# "c:\inetpub\wwwroot\crystal1\CrystalReport1.rp t" %>'
DisplayGroupTree="False"

DisplayToolbar="False">
</CR:CrystalReportViewer>
<asp:dropdownlist id="Dropdownlist4" style="Z-INDEX: 109; LEFT: 8px;

POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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:DropDownList id="DropDownList1" style="Z-INDEX: 108; LEFT: 16px;

POSITION: absolute; TOP: 80px"
runat="server" Width="184px" Height="24px"></asp:DropDownList>
<asp:label id="Label1" style="Z-INDEX: 103; LEFT: 336px; POSITION:

absolute; TOP: 8px" runat="server"
Width="288px" Height="20px" Font-Bold="True" ForeColor="White"

BackColor="RoyalBlue" BorderColor="#0000C0"
BorderStyle="None">Conceptos</asp:label>
<asp:listbox id="listbox1" style="Z-INDEX: 102; LEFT: 336px; POSITION:

absolute; TOP: 32px" runat="server"
Width="288px" Height="116px" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause"
SelectionMode="Multiple"></asp:listbox>
<asp:Button id="Button1" style="Z-INDEX: 106; LEFT: 232px; POSITION:

absolute; TOP: 56px" runat="server"
Width="72px" BackColor="#C0C0FF" Text="Button1"></asp:Button>
</form>
</body>
</HTML>
------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.Vs_Base
AS
SELECT dbo.Codiari9.AÃ±o, dbo.coclaem9.POLO, dbo.coclaem9.EMPRESA,
dbo.coclaem9.TIT_EMP,
dbo.tablaVI_RESULTADOS.ORDEN,

dbo.tablaVI_ANEXO_DE_GASTOS_DIVISAS1_DETALLE.TEXTO ,
dbo.Codiari9.Saldo
FROM dbo.tablaVI_RESULTADOS INNER JOIN
dbo.Coplade92 ON
dbo.tablaVI_RESULTADOS.ORDEN =
dbo.Coplade92.TABLAVI_RESULTADOS INNER JOIN
dbo.coclaem9 INNER JOIN
dbo.Codiari9 ON dbo.coclaem9.EMPRESA =
dbo.Codiari9.EMPRESA ON

dbo.Coplade92.CUENTA2 = dbo.Codiari9.CUENTA AND
dbo.Coplade92.CTO_GTO_2 = dbo.Codiari9.CTO_GTO1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.fnBaseAÃ±osCompara(@orden nvarchar(10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT AÃ±o, ORDEN, TEXTO,POLO, EMPRESA, TIT_EMP, Saldo, CASE
WHEN aÃ±o =

@aÃ±o_anterior THEN saldo ELSE 0 END AS aÃ±o_anterior,
CASE WHEN aÃ±o = @presupuesto THEN saldo ELSE 0 END AS
presupuesto,
CASE WHEN aÃ±o = @aÃ±o_actual THEN saldo ELSE 0 END AS
aÃ±o_actual,
CASE WHEN aÃ±o = @aÃ±o_anterior THEN aÃ±o ELSE 0 END AS
aÃ±o_anterior_lb,
CASE WHEN aÃ±o = @presupuesto THEN aÃ±o ELSE 0 END AS
presupuesto_lb,
CASE WHEN aÃ±o = @aÃ±o_actual THEN aÃ±o ELSE 0 END AS
aÃ±o_actual_lb

FROM dbo.Vs_Base
WHERE (AÃ±o = @aÃ±o_anterior) AND (ORDEN = @orden) OR
(AÃ±o = @presupuesto) AND (ORDEN = @orden)OR
(AÃ±o = @aÃ±o_actual) AND (ORDEN = @orden))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fnBaseAÃ±osSumaCompara(@orden nvarchar (10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT EMPRESA, POLO, TIT_EMP, ORDEN, TEXTO,
MAX(aÃ±o_anterior_lb) AS

aÃ±o_anterior_lb2, MAX(presupuesto_lb) AS presupuesto_lb2,
MAX(aÃ±o_actual_lb) AS aÃ±o_actual_lb2,
SUM(aÃ±o_anterior) AS aÃ±o_anterior,

SUM(presupuesto) AS presupuesto, SUM(aÃ±o_actual) AS aÃ±o_actual
FROM dbo.fnBaseAÃ±osCompara(@orden, @aÃ±o_anterior, @presupuesto,
@aÃ±o_actual)

fnBaseAÃ±osCompara
GROUP BY EMPRESA, TIT_EMP, ORDEN, TEXTO, POLO
ORDER BY EMPRESA )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.sp_fnBaseAÃ±osSumaCompara
(@orden nvarchar(10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
AS SELECT *
FROM dbo.fnBaseAÃ±osSumaCompara(@orden, @aÃ±o_anterior,@presupuesto,
@aÃ±o_actual )

fnBaseAÃ±osSumaCompara
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thank again.

Adis
------------------------------------------------------------------------------------------

"cbDevelopment" wrote:

1: It woud help a lot if you could give me the real names of the tables
and stored procedures. This would give me a context to work from.
"MyStoredProcedure" and "MyTable" mean nothing, but "sp_GetYearlySales"
and "CustomerSales" mean something. I can then figure out from context
what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a temporary
table that will be removed after the function completes. The structure
of this table variable is a single field. If you pass in "1,2,3,4,5", it
returns:

val
-----
1
2
3
4
5

3: I don't understand why a view is being introduced. We don't need to
use a view for anything.

4: You can test that fn_CSVToTable is installed in your server by going
to query analyser and executing:

select * from dbo.fn_CSVToTable('1,2,3,4,5')

You will get the results shown above. If you don't, there is something
wrong.

5: I hope you understand that the generic names are confusing me. From
what I understand so far, your original attempt was having ASP.NET call a
stored procedure. This stored procedure called a function. That
function called another function. I think they were called
MyStoredProcedure, MyFunction2 and MyFunction1.

Function1 must be returning some sort of table and Function2 is doing a
SUM and GROUP on the results. There is an odd line "My_Store_Procedure
is derived at My_Function2" Do you mean that My_Store_Procedure _is_
My_function2?

Lets see if we can start this over...

"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:B5**********************************@microsof t.com:
>

Nov 18 '05 #7

Adis

Hi,

Thank you.
You are helping me a lot.

Below I sent you project code.
About fn_CSVToTable: understood.
I have introduced view due to complex joins.
fn_CSVToTable: work OK.
-----------------------
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data.SqlClient
Imports System.io
Imports System.Configuration
Imports Microsoft.VisualBasic
Public Class WebForm2
Inherits System.Web.UI.Page
Dim crReportDocument As ReportDocument
Dim crExportOptions As ExportOptions
Dim crDiskFileDestinationOptions As DiskFileDestinationOptions
#Region " CÃ³digo generado por el DiseÃ±ador de Web Forms "

'El DiseÃ±ador de Web Forms requiere esta llamada.
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataSet31 = New Crystal1.DataSet3
CType(Me.DataSet31,
System.ComponentModel.ISupportInitialize).BeginIni t()
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping() {New

System.Data.Common.DataTableMapping("Table", "sp_fnBaseAÃ±osSumaCompara", New

System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("empresa",

"empresa"), New System.Data.Common.DataColumnMapping("polo", "polo"), New

System.Data.Common.DataColumnMapping("tit_emp", "tit_emp"), New

System.Data.Common.DataColumnMapping("orden", "orden"), New

System.Data.Common.DataColumnMapping("texto", "texto"), New

System.Data.Common.DataColumnMapping("aÃ±o_anterio r", "aÃ±o_anterior"), New

System.Data.Common.DataColumnMapping("presupuesto" , "presupuesto"), New

System.Data.Common.DataColumnMapping("aÃ±o_actual" , "aÃ±o_actual")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[sp_fnBaseAÃ±osSumaCompara]"
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("@orden",

System.Data.SqlDbType.NVarChar, 10))
Me.SqlSelectCommand1.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@aÃ±o_anterior ",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New

System.Data.SqlClient.SqlParameter("@presupuesto",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@aÃ±o_actual",

System.Data.SqlDbType.NVarChar, 5))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=ADISPC;packet
size=4096;integrated

security=SSPI;data source=ADI" & _
"SPC;persist security info=False;initial catalog=Orion"
'
'DataSet31
'
Me.DataSet31.DataSetName = "DataSet3"
Me.DataSet31.Locale = New System.Globalization.CultureInfo("es-ES")
CType(Me.DataSet31,
System.ComponentModel.ISupportInitialize).EndInit( )

End Sub
Protected WithEvents CrystalReportViewer1 As
CrystalDecisions.Web.CrystalReportViewer
Protected WithEvents SqlDataAdapter1 As
System.Data.SqlClient.SqlDataAdapter
Protected WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Protected WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Protected WithEvents DataSet31 As Crystal1.DataSet3
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents listbox1 As System.Web.UI.WebControls.ListBox
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents DropDownList1 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist3 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist2 As
System.Web.UI.WebControls.DropDownList
Protected WithEvents Dropdownlist4 As
System.Web.UI.WebControls.DropDownList
Protected li As System.Web.UI.WebControls.ListItem

'NOTA: el DiseÃ±ador de Web Forms necesita la siguiente declaraciÃ³n del
marcador de posiciÃ³n.
'No se debe eliminar o mover.
Private designerPlaceholderDeclaration As System.Object

Private Sub Page_Init(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

MyBase.Init
'CODEGEN: el DiseÃ±ador de Web Forms requiere esta llamada de mÃ©todo
'No la modifique con el editor de cÃ³digo.
InitializeComponent()
CrystalReportViewer1.ReportSource = crReportDocument
With DropDownList1.Items
.Add("Rich Text (RTF)")
.Add("Portable Document (PDF)")
.Add("MS Word (DOC)")
.Add("MS Excel (XLS)")
.Add("Crystal Report (RPT)")
.Add("HTML 3.2 (HTML)")
.Add("HTML 4.0 (HTML)")
End With

End Sub

#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

MyBase.Load
'Introducir aquÃ* el cÃ³digo de usuario para inicializar la pÃ¡gina
If Not IsPostBack Then

'listbox1.SelectedIndex = 2
Dropdownlist4.SelectedIndex = 2
cargar_listbox()

End If

End Sub
Sub cargar_listbox()
Dim strSql As String = "Select texto, orden From tablaVI_RESULTADOS
Order By orden"
Dim objConn As SqlClient.SqlConnection = _
New SqlConnection("Data Source=adispc;" & _
"Integrated Security=SSPI;" & _
"Initial Catalog=Orion")
Dim objCmd As New SqlCommand(strSql, objConn)

Try
objConn.Open()
listbox1.DataSource = objCmd.ExecuteReader()
listbox1.DataTextField = "texto"
listbox1.DataValueField = "orden"
listbox1.DataBind()

Catch exc As SqlException
Response.Write(exc.ToString())
Finally
objConn.Dispose()
End Try
End Sub
Sub ChangeWhereClause(ByVal Sender As System.Object, ByVal e As
System.EventArgs)
'VARIANTE CON LISTBOX MULTIPLE
Dim strWhereClause As String = ""
For Each li In listbox1.Items
If li.Selected Then
strWhereClause &= "orden =" &
Me.SqlSelectCommand1.Parameters("@orden").Value =

li.Value & " Or "

End If
Next

If strWhereClause.Length > 0 Then
strWhereClause = Left(strWhereClause, strWhereClause.Length() -
4)
strWhereClause = "WHERE " & strWhereClause
Dim strSql = "Select *" _
& "From sp_fnBaseAÃ±osSumaCompara " '& strWhereClause & " or " _

End If

'Me.SqlSelectCommand1.Parameters("@orden").Value =
listbox1.SelectedItem.Value
'Me.SqlSelectCommand1.Parameters("@aÃ±o_anterior") .Value =
Dropdownlist4.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@presupuesto").V alue =
Dropdownlist2.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@aÃ±o_actual").V alue =
Dropdownlist3.SelectedItem.Text

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAÃ±osSu maCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt
End Sub
Sub ExportReport()

Dim ExportPath As String
ExportPath = Request.PhysicalApplicationPath + "Exported\"
If Directory.Exists(ExportPath) = False Then
Directory.CreateDirectory(Request.PhysicalApplicat ionPath +
"Exported\")
End If

crDiskFileDestinationOptions = New DiskFileDestinationOptions
crExportOptions = crReportDocument.ExportOptions

Select Case DropDownList1.SelectedItem.Text 'this contains the value
of the selected

export format.

Case "Rich Text (RTF)"

'Export to RTF.
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"RichTextFormat.rtf"
'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.RichText
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "Portable Document (PDF)"
'Export to PDF
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"PortableDoc.pdf"

'set the required report ExportOptions properties
With crExportOptions
.DestinationOptions = crDiskFileDestinationOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.PortableDocFormat
End With

Case "MS Word (DOC)"
'Export to Word
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Word.doc"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.WordForWindows
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "MS Excel (XLS)"
'Export to Excel

'append a filename to the export path and set this file as
the filename property

for
'the DestinationOptions class
crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Excel.xls"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.Excel
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "Crystal Report (RPT)"
'Export to Crystal reports:

crDiskFileDestinationOptions.DiskFileName = ExportPath +
"Report.rpt"

'set the required report ExportOptions properties
With crReportDocument.ExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.CrystalReport
.DestinationOptions = crDiskFileDestinationOptions
End With

Case "HTML 3.2 (HTML)"
'Export to HTML32:

Dim HTML32Formatopts As New HTMLFormatOptions

With crExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.HTML32
End With

With HTML32Formatopts
.HTMLBaseFolderName = ExportPath + "Html32Folder"
'Foldername to place HTML

files
.HTMLFileName = "HTML32.html"
.HTMLEnableSeparatedPages = False
.HTMLHasPageNavigator = False
End With

crExportOptions.FormatOptions = HTML32Formatopts

Case "HTML 4.0 (HTML)"
'Export to Html 4.0:

Dim HTML40Formatopts As New HTMLFormatOptions

With crExportOptions
.ExportDestinationType = ExportDestinationType.DiskFile
.ExportFormatType = ExportFormatType.HTML40
End With

With HTML40Formatopts
.HTMLBaseFolderName = ExportPath + "Html40Folder" '
Foldername to place HTML

files
.HTMLFileName = "HTML40.html"
.HTMLEnableSeparatedPages = True
.HTMLHasPageNavigator = True
.FirstPageNumber = 1
.LastPageNumber = 3
End With

crExportOptions.FormatOptions = HTML40Formatopts

End Select 'export format

Try
' Export the report
crReportDocument.Export()
Catch err As Exception
Response.Write("<BR>")
Response.Write(err.Message.ToString)
End Try

End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles

Button1.Click
ExportReport()

End Sub
End Class
--------------------
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="WebForm2.aspx.vb"

Inherits="Crystal1.WebForm2"%>
<%@ Register TagPrefix="cr" Namespace="CrystalDecisions.Web"
Assembly="CrystalDecisions.Web,

Version=9.1.5000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<title>WebForm2</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema"

content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<CR:CrystalReportViewer id=CrystalReportViewer1 style="Z-INDEX: 100;

LEFT: 48px; POSITION: absolute; TOP: 216px" runat="server" Width="753px"
Height="1095px"

ReportSource='<%# "c:\inetpub\wwwroot\crystal1\CrystalReport1.rp t" %>'
DisplayGroupTree="False"

DisplayToolbar="False">
</CR:CrystalReportViewer>
<asp:dropdownlist id="Dropdownlist4" style="Z-INDEX: 109; LEFT: 8px;

POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause">
<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:DropDownList id="DropDownList1" style="Z-INDEX: 108; LEFT: 16px;

POSITION: absolute; TOP: 80px"
runat="server" Width="184px" Height="24px"></asp:DropDownList>
<asp:label id="Label1" style="Z-INDEX: 103; LEFT: 336px; POSITION:

absolute; TOP: 8px" runat="server"
Width="288px" Height="20px" Font-Bold="True" ForeColor="White"

BackColor="RoyalBlue" BorderColor="#0000C0"
BorderStyle="None">Conceptos</asp:label>
<asp:listbox id="listbox1" style="Z-INDEX: 102; LEFT: 336px; POSITION:

absolute; TOP: 32px" runat="server"
Width="288px" Height="116px" AutoPostBack="True"

OnSelectedIndexChanged="ChangeWhereClause"
SelectionMode="Multiple"></asp:listbox>
<asp:Button id="Button1" style="Z-INDEX: 106; LEFT: 232px; POSITION:

absolute; TOP: 56px" runat="server"
Width="72px" BackColor="#C0C0FF" Text="Button1"></asp:Button>
</form>
</body>
</HTML>
------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER VIEW dbo.Vs_Base
AS
SELECT dbo.Codiari9.AÃ±o, dbo.coclaem9.POLO, dbo.coclaem9.EMPRESA,
dbo.coclaem9.TIT_EMP,
dbo.tablaVI_RESULTADOS.ORDEN,

dbo.tablaVI_RESULTADOS.TEXTO,
dbo.Codiari9.Saldo
FROM dbo.tablaVI_RESULTADOS INNER JOIN
dbo.Coplade92 ON
dbo.tablaVI_RESULTADOS.ORDEN =
dbo.Coplade92.TABLAVI_RESULTADOS INNER JOIN
dbo.coclaem9 INNER JOIN
dbo.Codiari9 ON dbo.coclaem9.EMPRESA =
dbo.Codiari9.EMPRESA ON

dbo.Coplade92.CUENTA2 = dbo.Codiari9.CUENTA AND
dbo.Coplade92.CTO_GTO_2 = dbo.Codiari9.CTO_GTO1

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
---------------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER FUNCTION dbo.fnBaseAÃ±osCompara(@orden nvarchar(10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT AÃ±o, ORDEN, TEXTO,POLO, EMPRESA, TIT_EMP, Saldo, CASE
WHEN aÃ±o =

@aÃ±o_anterior THEN saldo ELSE 0 END AS aÃ±o_anterior,
CASE WHEN aÃ±o = @presupuesto THEN saldo ELSE 0 END AS
presupuesto,
CASE WHEN aÃ±o = @aÃ±o_actual THEN saldo ELSE 0 END AS
aÃ±o_actual,
CASE WHEN aÃ±o = @aÃ±o_anterior THEN aÃ±o ELSE 0 END AS
aÃ±o_anterior_lb,
CASE WHEN aÃ±o = @presupuesto THEN aÃ±o ELSE 0 END AS
presupuesto_lb,
CASE WHEN aÃ±o = @aÃ±o_actual THEN aÃ±o ELSE 0 END AS
aÃ±o_actual_lb

FROM dbo.Vs_Base
WHERE (AÃ±o = @aÃ±o_anterior) AND (ORDEN = @orden) OR
(AÃ±o = @presupuesto) AND (ORDEN = @orden)OR
(AÃ±o = @aÃ±o_actual) AND (ORDEN = @orden))
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fnBaseAÃ±osSumaCompara(@orden nvarchar (10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT EMPRESA, POLO, TIT_EMP, ORDEN, TEXTO,
MAX(aÃ±o_anterior_lb) AS

aÃ±o_anterior_lb2, MAX(presupuesto_lb) AS presupuesto_lb2,
MAX(aÃ±o_actual_lb) AS aÃ±o_actual_lb2,
SUM(aÃ±o_anterior) AS aÃ±o_anterior,

SUM(presupuesto) AS presupuesto, SUM(aÃ±o_actual) AS aÃ±o_actual
FROM dbo.fnBaseAÃ±osCompara(@orden, @aÃ±o_anterior, @presupuesto,
@aÃ±o_actual)

fnBaseAÃ±osCompara
GROUP BY EMPRESA, TIT_EMP, ORDEN, TEXTO, POLO
ORDER BY EMPRESA )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE dbo.sp_fnBaseAÃ±osSumaCompara
(@orden nvarchar(10),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
AS SELECT *
FROM dbo.fnBaseAÃ±osSumaCompara(@orden, @aÃ±o_anterior,@presupuesto,
@aÃ±o_actual )

fnBaseAÃ±osSumaCompara
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Thank again.

Adis
------------------------------------------------------------------------------------------
"cbDevelopment" wrote:

1: It woud help a lot if you could give me the real names of the tables
and stored procedures. This would give me a context to work from.
"MyStoredProcedure" and "MyTable" mean nothing, but "sp_GetYearlySales"
and "CustomerSales" mean something. I can then figure out from context
what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a temporary
table that will be removed after the function completes. The structure
of this table variable is a single field. If you pass in "1,2,3,4,5", it
returns:

val
-----
1
2
3
4
5

3: I don't understand why a view is being introduced. We don't need to
use a view for anything.

4: You can test that fn_CSVToTable is installed in your server by going
to query analyser and executing:

select * from dbo.fn_CSVToTable('1,2,3,4,5')

You will get the results shown above. If you don't, there is something
wrong.

5: I hope you understand that the generic names are confusing me. From
what I understand so far, your original attempt was having ASP.NET call a
stored procedure. This stored procedure called a function. That
function called another function. I think they were called
MyStoredProcedure, MyFunction2 and MyFunction1.

Function1 must be returning some sort of table and Function2 is doing a
SUM and GROUP on the results. There is an odd line "My_Store_Procedure
is derived at My_Function2" Do you mean that My_Store_Procedure _is_
My_function2?

Lets see if we can start this over...

"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:B5**********************************@microsof t.com:
Hi,

Thank you for your usefull help.

I still don't get some basics.

I have simplified database and code, to make my project easier to
explain.

1) Table: tbMyTable.

Id orderiD year value
01 101 1998 15
02 101 1999 20
03 101 2000 10
04 101 2001 14
05 102 1998 8
06 102 1999 16
07 102 2000 14
08 102 2001 9
09 103 1998 13
10 103 1999 23
11 103 2000 7
12 103 2001 5

2) Table: csvtable
I must be doing something totally wrong ....The function CSVToTable
have ref to csvtable and in your second post i don't see this table in
detail. I assumed this table (csvtable) would be lock like closer to:

CSVList val id
1998 1 1
1999 2 2
2000 3 3
2001 4 4

3) View: vsMyTable

ALTER VIEW dbo.vsMyTable
AS
SELECT *
FROM dbo.tbMyTable

4) I added function CSVToTable in my server. Ok.

5) Now, I don't clearly understand the following step:

a) Take MyFunction1, OR

b) Create Function1 as your recommend.
-----

a)
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.fnMyFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT year, orderid, CASE WHEN aÃƒÂ±o = @year1 THEN valor
ELSE 0 END AS year1,
CASE WHEN aÃƒÂ±o = @year2 THEN valor ELSE 0 END AS
year2, CASE WHEN aÃƒÂ±o = @year3 THEN valor ELSE 0
END AS year3
FROM dbo.vsMyTable ' ref to My View.
WHERE (aÃƒÂ±o = @year1) OR
(aÃƒÂ±o = @year2) OR
(aÃƒÂ±o = @year3) )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------

b)

select *
from table ---------> 'you ref from "Table", must be a view or
function? anyway not work for me.
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))

I must be doing something totally wrong ....I wrongly applied the
upper syntaxis:

Create FUNCTION dbo.fnFunction1(@year1 varchar (8000),
@year2 varchar (8000),
@year3 varchar (8000))
RETURNS TABLE
AS
RETURN ( SELECT *
FROM dbo.fnMyFunction1 ----------->' developed in (a). I refer from
"MyFunction1", and not work for me.

WHERE year1 in (select val from dbo.CSVToTable (@year1)) and
year2 in (select val from dbo.CSVToTable (@year2)) and
year3 in (select val from dbo.CSVToTable (@year3)))

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----

The upper function no work, my interpretation is erroneous. I receive
Server Msg 208, Level 16,

State 3, Procedure fnFunction1, Line 8
Invalid object name 'dbo.fnMyFunction1'.

NOTE: "fnMyFunction1" is a) function, and "fnFunction1" is your
recommended function in b).

I need, as minimum, clarify to me step 2 and step 5. I assum the
following step 6 as something like:

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

and use the stringbuilder sample code from your last post to build
your CSV string to use.

Thank you so much for all your help...

Adis

Nov 18 '05 #8

cbDevelopment

I have looked over your code and determined the following:

I am going to make the assumption that the only item you need multiple
values for is "ListBox1", with populates the parameter "orden"

You need the fn_CSVToTable. I believe you have this already.

This function needs the following changes:

ALTER FUNCTION dbo.fnBaseAÃ±osSumaCompara(
@orden nvarchar (2000),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5)
)
RETURNS TABLE

This function needs the following changes

ALTER FUNCTION dbo.fnBaseAÃ±osCompara(
@orden nvarchar(2000),
@aÃ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT AÃ±o, ORDEN, TEXTO,POLO, EMPRESA, TIT_EMP, Saldo,
CASE WHEN aÃ±o = @aÃ±o_anterior
THEN saldo ELSE 0 END AS aÃ±o_anterior,
CASE WHEN aÃ±o = @presupuesto
THEN saldo ELSE 0 END AS presupuesto,
CASE WHEN aÃ±o = @aÃ±o_actual
THEN saldo ELSE 0 END AS aÃ±o_actual,
CASE WHEN aÃ±o = @aÃ±o_anterior
THEN aÃ±o ELSE 0 END AS aÃ±o_anterior_lb,
CASE WHEN aÃ±o = @presupuesto
THEN aÃ±o ELSE 0 END AS presupuesto_lb,
CASE WHEN aÃ±o = @aÃ±o_actual
THEN aÃ±o ELSE 0 END AS aÃ±o_actual_lb
FROM dbo.Vs_Base
WHERE
(
AÃ±o = @aÃ±o_anterior
OR AÃ±o = @presupuesto
OR AÃ±o = @aÃ±o_actual
)
AND ORDEN in
(select val from dbo.fnCSVToTable(@orden))

I suspect some of your parentheses were not correct in the original. If
I have mistaken the logic, simply correct the where clause.

Finally, in your VB code, this needs to change:

Sub ChangeWhereClause(ByVal Sender As System.Object, _
ByVal e As System.EventArgs)
'VARIANTE CON LISTBOX MULTIPLE
Dim strOrderNumbers As String = ""

For Each li In listbox1.Items
If li.Selected Then
strOrderNumbers &= li.Value & ","

End If

Next

If strOrderNumbers.Length > 0 Then
strOrderNumbers = Left(strOrderNumbers, _
strOrderNumbers.Length() - 1)

End If

with me.selectcommand1
.Parameters("@orden").Value = strOrderNumbers
.Parameters("@aÃ±o_anterior").Value = Dropdownlist4.SelectedItem.Text
.Parameters("@presupuesto").Value = Dropdownlist2.SelectedItem.Text
.Parameters("@aÃ±o_actual").Value = Dropdownlist3.SelectedItem.Text

end with

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAÃ±osSu maCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt

End Sub

And that's it. Let me know how it works.
"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:60**********************************@microsof t.com:

Hi,

Thank you.
You are helping me a lot.

Below I sent you project code.
About fn_CSVToTable: understood.
I have introduced view due to complex joins.
fn_CSVToTable: work OK.

Thank again.

Adis
-----------------------------------------------------------------------
-------------------
"cbDevelopment" wrote:
1: It woud help a lot if you could give me the real names of the
tables and stored procedures. This would give me a context to work
from. "MyStoredProcedure" and "MyTable" mean nothing, but
"sp_GetYearlySales" and "CustomerSales" mean something. I can then
figure out from context what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a
temporary table that will be removed after the function completes.
The structure of this table variable is a single field. If you pass
in "1,2,3,4,5", it returns:

Nov 18 '05 #9

Adis

Hi,

Works very, very nice.

Now, I will be to try with new (second) ListBox multiselected.

Thank you.

"cbDevelopment" wrote:

I have looked over your code and determined the following:

I am going to make the assumption that the only item you need multiple
values for is "ListBox1", with populates the parameter "orden"

You need the fn_CSVToTable. I believe you have this already.

This function needs the following changes:

ALTER FUNCTION dbo.fnBaseAÃƒÂ±osSumaCompara(
@orden nvarchar (2000),
@aÃƒÂ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃƒÂ±o_actual nvarchar (5)
)
RETURNS TABLE

This function needs the following changes

ALTER FUNCTION dbo.fnBaseAÃƒÂ±osCompara(
@orden nvarchar(2000),
@aÃƒÂ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃƒÂ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT AÃƒÂ±o, ORDEN, TEXTO,POLO, EMPRESA, TIT_EMP, Saldo,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_anterior
THEN saldo ELSE 0 END AS aÃƒÂ±o_anterior,
CASE WHEN aÃƒÂ±o = @presupuesto
THEN saldo ELSE 0 END AS presupuesto,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_actual
THEN saldo ELSE 0 END AS aÃƒÂ±o_actual,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_anterior
THEN aÃƒÂ±o ELSE 0 END AS aÃƒÂ±o_anterior_lb,
CASE WHEN aÃƒÂ±o = @presupuesto
THEN aÃƒÂ±o ELSE 0 END AS presupuesto_lb,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_actual
THEN aÃƒÂ±o ELSE 0 END AS aÃƒÂ±o_actual_lb
FROM dbo.Vs_Base
WHERE
(
AÃƒÂ±o = @aÃƒÂ±o_anterior
OR AÃƒÂ±o = @presupuesto
OR AÃƒÂ±o = @aÃƒÂ±o_actual
)
AND ORDEN in
(select val from dbo.fnCSVToTable(@orden))

I suspect some of your parentheses were not correct in the original. If
I have mistaken the logic, simply correct the where clause.

Finally, in your VB code, this needs to change:

Sub ChangeWhereClause(ByVal Sender As System.Object, _
ByVal e As System.EventArgs)
'VARIANTE CON LISTBOX MULTIPLE
Dim strOrderNumbers As String = ""

For Each li In listbox1.Items
If li.Selected Then
strOrderNumbers &= li.Value & ","

End If

Next

If strOrderNumbers.Length > 0 Then
strOrderNumbers = Left(strOrderNumbers, _
strOrderNumbers.Length() - 1)

End If

with me.selectcommand1
.Parameters("@orden").Value = strOrderNumbers
.Parameters("@aÃƒÂ±o_anterior").Value = Dropdownlist4.SelectedItem.Text
.Parameters("@presupuesto").Value = Dropdownlist2.SelectedItem.Text
.Parameters("@aÃƒÂ±o_actual").Value = Dropdownlist3.SelectedItem.Text

end with

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAÃƒÂ±os SumaCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt

End Sub

And that's it. Let me know how it works.
"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:60**********************************@microsof t.com:
Hi,

Thank you.
You are helping me a lot.

Below I sent you project code.
About fn_CSVToTable: understood.
I have introduced view due to complex joins.
fn_CSVToTable: work OK.

Thank again.

Adis
-----------------------------------------------------------------------
-------------------
"cbDevelopment" wrote:
1: It woud help a lot if you could give me the real names of the
tables and stored procedures. This would give me a context to work
from. "MyStoredProcedure" and "MyTable" mean nothing, but
"sp_GetYearlySales" and "CustomerSales" mean something. I can then
figure out from context what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a
temporary table that will be removed after the function completes.
The structure of this table variable is a single field. If you pass
in "1,2,3,4,5", it returns:

Nov 18 '05 #10

Adis

Hi,

This code works OK.

Now I add new parameter: @empresa and second listbox with multiple selection.
I tried and function no work with the second listbox.

Thank you in advance,

Adis

"cbDevelopment" wrote:

I have looked over your code and determined the following:

I am going to make the assumption that the only item you need multiple
values for is "ListBox1", with populates the parameter "orden"

You need the fn_CSVToTable. I believe you have this already.

This function needs the following changes:

ALTER FUNCTION dbo.fnBaseAÃƒÂ±osSumaCompara(
@orden nvarchar (2000),
@aÃƒÂ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃƒÂ±o_actual nvarchar (5)
)
RETURNS TABLE

This function needs the following changes

ALTER FUNCTION dbo.fnBaseAÃƒÂ±osCompara(
@orden nvarchar(2000),
@aÃƒÂ±o_anterior nvarchar (5),
@presupuesto nvarchar (5),
@aÃƒÂ±o_actual nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT AÃƒÂ±o, ORDEN, TEXTO,POLO, EMPRESA, TIT_EMP, Saldo,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_anterior
THEN saldo ELSE 0 END AS aÃƒÂ±o_anterior,
CASE WHEN aÃƒÂ±o = @presupuesto
THEN saldo ELSE 0 END AS presupuesto,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_actual
THEN saldo ELSE 0 END AS aÃƒÂ±o_actual,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_anterior
THEN aÃƒÂ±o ELSE 0 END AS aÃƒÂ±o_anterior_lb,
CASE WHEN aÃƒÂ±o = @presupuesto
THEN aÃƒÂ±o ELSE 0 END AS presupuesto_lb,
CASE WHEN aÃƒÂ±o = @aÃƒÂ±o_actual
THEN aÃƒÂ±o ELSE 0 END AS aÃƒÂ±o_actual_lb
FROM dbo.Vs_Base
WHERE
(
AÃƒÂ±o = @aÃƒÂ±o_anterior
OR AÃƒÂ±o = @presupuesto
OR AÃƒÂ±o = @aÃƒÂ±o_actual
)
AND ORDEN in
(select val from dbo.fnCSVToTable(@orden))

I suspect some of your parentheses were not correct in the original. If
I have mistaken the logic, simply correct the where clause.

Finally, in your VB code, this needs to change:

Sub ChangeWhereClause(ByVal Sender As System.Object, _
ByVal e As System.EventArgs)
'VARIANTE CON LISTBOX MULTIPLE
Dim strOrderNumbers As String = ""

For Each li In listbox1.Items
If li.Selected Then
strOrderNumbers &= li.Value & ","

End If

Next

If strOrderNumbers.Length > 0 Then
strOrderNumbers = Left(strOrderNumbers, _
strOrderNumbers.Length() - 1)

End If

with me.selectcommand1
.Parameters("@orden").Value = strOrderNumbers
.Parameters("@aÃƒÂ±o_anterior").Value = Dropdownlist4.SelectedItem.Text
.Parameters("@presupuesto").Value = Dropdownlist2.SelectedItem.Text
.Parameters("@aÃƒÂ±o_actual").Value = Dropdownlist3.SelectedItem.Text

end with

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAÃƒÂ±os SumaCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt

End Sub

And that's it. Let me know how it works.
"=?Utf-8?B?QWRpcw==?=" <Ad**@discussions.microsoft.com> wrote in
news:60**********************************@microsof t.com:
Hi,

Thank you.
You are helping me a lot.

Below I sent you project code.
About fn_CSVToTable: understood.
I have introduced view due to complex joins.
fn_CSVToTable: work OK.

Thank again.

Adis
-----------------------------------------------------------------------
-------------------
"cbDevelopment" wrote:
1: It woud help a lot if you could give me the real names of the
tables and stored procedures. This would give me a context to work
from. "MyStoredProcedure" and "MyTable" mean nothing, but
"sp_GetYearlySales" and "CustomerSales" mean something. I can then
figure out from context what you are trying to accomplish.

2: In the fn_CSVToTable, there is a table _variable_ named csvtable.
This does not need to created in your database. It is like a
temporary table that will be removed after the function completes.
The structure of this table variable is a single field. If you pass
in "1,2,3,4,5", it returns:

Nov 18 '05 #11

Similar topics