Parameters

G

Guest

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").Value =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "EmployeeID=" &
Me.SqlSelectCommand1.Parameters("@EmployeeID").value = 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.
 
G

Guest

Hi,

Thank you for your replay.

I need run "My_Store_Procedure" and pass the parameters multiselected in
listbox1. But I no find how do it.

In the following lines I show function, Store_Procedure,HTML and no working
code.

I have one view, function1, function2 and My_Store_Procedure. My function2
lock

like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.My_Function2(@order nvarchar (10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT field4, field5, field6, ORDER,
SUM(field1)

AS Myfield1, SUM(field2) AS Myfield2, SUM(field3) AS Myfield3
FROM My_Function1(@order, @field1, @field2, @field3)
GROUP BY field4, field5, ORDER, field4, field6
ORDER BY field4 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My_Store_Procedure is derived at My_Function2:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.My_Store_Procedure
(@order nvarchar(10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
AS SELECT *
FROM dbo.My_Function2(@order, @field1,@field2, @field3 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------------------------------
<HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1" name="GENERATOR">
<meta content="Visual Basic .NET 7.1" name="CODE_LANGUAGE">
<meta content="JavaScript" name="vs_defaultClientScript">
<meta content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<CR:CRYSTALREPORTVIEWER id=CrystalReportViewer1 style="Z-INDEX: 101;
LEFT: 24px; POSITION: absolute; TOP: 168px" runat="server"
DisplayGroupTree="False" DisplayToolbar="False" Width="753px" Height="1095px"
ReportSource='<%# "c:\inetpub\wwwroot\crystal1\CrystalReport1.rpt" %>'
Visible="False" ToolTip="Select">
</CR:CRYSTALREPORTVIEWER><asp:dropdownlist id="Dropdownlist4"
style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998" Selected="True">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist2" style="Z-INDEX:
105; LEFT: 112px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999" Selected="True">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist3" style="Z-INDEX:
104; LEFT: 208px; POSITION: absolute; TOP: 8px"
runat="server" Width="81" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000" Selected="True">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:label id="Label1" style="Z-INDEX: 103; LEFT:
336px; POSITION: absolute; TOP: 8px" runat="server"
Width="288px" Height="20px" BorderStyle="None" BorderColor="#0000C0"
BackColor="RoyalBlue" ForeColor="White"
Font-Bold="True">Conceptos</asp:label><asp:listbox id="listbox1"
style="Z-INDEX: 102; LEFT: 336px; POSITION: absolute; TOP: 32px"
runat="server"
Width="288px" Height="116px" OnSelectedIndexChanged="ChangeWhereClause"
AutoPostBack="True"></asp:listbox><asp:button id="Button1" style="Z-INDEX:
106; LEFT: 232px; POSITION: absolute; TOP: 56px" runat="server"
Width="72px" BackColor="#C0C0FF" Text="Button1"
Enabled="False"></asp:button><asp:button id="Button2" style="Z-INDEX: 108;
LEFT: 232px; POSITION: absolute; TOP: 96px" runat="server"
Width="72px" BackColor="#FFC0FF"
Text="Exportar"></asp:button><asp:dropdownlist id="DropDownList1"
style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 96px"
runat="server" Width="184px" Height="24px"></asp:dropdownlist>
</form>
</body>
</HTML>
-----------------------------------------------------------------------

#Region
.....

Me.SqlSelectCommand1.CommandText = "[My_Store_Procedure]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@order", System.Data.SqlDbType.NVarChar,
10))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field1", System.Data.SqlDbType.NVarChar,
5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field2", System.Data.SqlDbType.NVarChar,
5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field3", System.Data.SqlDbType.NVarChar,
5))

.....
------------------------

Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
Dim strWhereClause As String = ""
For Each li in listbox1.Items
If li.Selected Then

strWhereClause &= "order=" & li.Value & " Or " '<----------
what is wrong?

End If
Next
If strWhereClause.Length > 0 Then

strWhereClause = Left(strWhereClause, strWhereClause.Length() - 4)
strWhereClause = "WHERE " & strWhereClause
Dim strSql = "Select * " _
& "From My_Store_Procedure " & strWhereClause & " Order By
LastName"

Me.SqlSelectCommand1.Parameters("@field1").Value =
Dropdownlist4.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field2").Value =
Dropdownlist2.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field3").Value =
Dropdownlist3.SelectedItem.Text
Me.SqlDataAdapter1.Fill(DataSet21.My_Store_Procedure)

Dim oRpt As New CrystalReport1
oRpt.SetDataSource(DataSet21)
CrystalReportViewer1.Visible = True
CrystalReportViewer1.ReportSource = oRpt

End Sub
....

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

Me.SqlSelectCommand1.Parameters("@order").Value =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "order=" &
Me.SqlSelectCommand1.Parameters("@order").value = li.Value
& " Or " ' <-------------------- what is wrong?

instead of:

strWhereClause &= "EmployeeID=" & li.Value & " Or "

....and no work.

Thank you again,

Adis.
 
C

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?



Hi,

Thank you for your replay.

I need run "My_Store_Procedure" and pass the parameters multiselected
in listbox1. But I no find how do it.

In the following lines I show function, Store_Procedure,HTML and no
working code.

I have one view, function1, function2 and My_Store_Procedure. My
function2 lock

like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.My_Function2(@order nvarchar (10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT field4, field5, field6, ORDER,
SUM(field1)

AS Myfield1, SUM(field2) AS Myfield2, SUM(field3) AS Myfield3
FROM My_Function1(@order, @field1, @field2, @field3)
GROUP BY field4, field5, ORDER, field4, field6
ORDER BY field4 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My_Store_Procedure is derived at My_Function2:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.My_Store_Procedure
(@order nvarchar(10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
AS SELECT *
FROM dbo.My_Function2(@order, @field1,@field2, @field3 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
------- <HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1"
name="GENERATOR"> <meta content="Visual Basic .NET 7.1"
name="CODE_LANGUAGE"> <meta content="JavaScript"
name="vs_defaultClientScript"> <meta
content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<CR:CRYSTALREPORTVIEWER id=CrystalReportViewer1
style="Z-INDEX: 101;
LEFT: 24px; POSITION: absolute; TOP: 168px" runat="server"
DisplayGroupTree="False" DisplayToolbar="False" Width="753px"
Height="1095px" ReportSource='<%#
"c:\inetpub\wwwroot\crystal1\CrystalReport1.rpt" %>' Visible="False"
ToolTip="Select">
</CR:CRYSTALREPORTVIEWER><asp:dropdownlist
id="Dropdownlist4"
style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998"
Selected="True">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist2"
style="Z-INDEX:
105; LEFT: 112px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999"
Selected="True">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist3"
style="Z-INDEX:
104; LEFT: 208px; POSITION: absolute; TOP: 8px"
runat="server" Width="81" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000"
Selected="True">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:label id="Label1"
style="Z-INDEX: 103; LEFT:
336px; POSITION: absolute; TOP: 8px" runat="server"
Width="288px" Height="20px" BorderStyle="None"
BorderColor="#0000C0"
BackColor="RoyalBlue" ForeColor="White"
Font-Bold="True">Conceptos</asp:label><asp:listbox
id="listbox1"
style="Z-INDEX: 102; LEFT: 336px; POSITION: absolute; TOP: 32px"
runat="server"
Width="288px" Height="116px"
OnSelectedIndexChanged="ChangeWhereClause"
AutoPostBack="True"></asp:listbox><asp:button id="Button1"
style="Z-INDEX: 106; LEFT: 232px; POSITION: absolute; TOP: 56px"
runat="server"
Width="72px" BackColor="#C0C0FF" Text="Button1"
Enabled="False"></asp:button><asp:button id="Button2" style="Z-INDEX:
108; LEFT: 232px; POSITION: absolute; TOP: 96px" runat="server"
Width="72px" BackColor="#FFC0FF"
Text="Exportar"></asp:button><asp:dropdownlist id="DropDownList1"
style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 96px"
runat="server" Width="184px"
Height="24px"></asp:dropdownlist>
</form>
</body>
</HTML>
-----------------------------------------------------------------------

#Region
....

Me.SqlSelectCommand1.CommandText = "[My_Store_Procedure]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@order",
System.Data.SqlDbType.NVarChar, 10))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field1",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field2",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field3",
System.Data.SqlDbType.NVarChar, 5))

....
------------------------

Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
Dim strWhereClause As String = ""
For Each li in listbox1.Items
If li.Selected Then

strWhereClause &= "order=" & li.Value & " Or " '<----------
what is wrong?

End If
Next
If strWhereClause.Length > 0 Then

strWhereClause = Left(strWhereClause, strWhereClause.Length() -
4) strWhereClause = "WHERE " & strWhereClause
Dim strSql = "Select * " _
& "From My_Store_Procedure " & strWhereClause & "
Order By
LastName"

Me.SqlSelectCommand1.Parameters("@field1").Value =
Dropdownlist4.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field2").Value =
Dropdownlist2.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field3").Value =
Dropdownlist3.SelectedItem.Text
Me.SqlDataAdapter1.Fill(DataSet21.My_Store_Procedure)

Dim oRpt As New CrystalReport1
oRpt.SetDataSource(DataSet21)
CrystalReportViewer1.Visible = True
CrystalReportViewer1.ReportSource = oRpt

End Sub
...

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

Me.SqlSelectCommand1.Parameters("@order").Value =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "order=" &
Me.SqlSelectCommand1.Parameters("@order").value = li.Value
& " Or " ' <-------------------- what is wrong?

instead of:

strWhereClause &= "EmployeeID=" & li.Value & " Or "

...and no work.

Thank you again,

Adis.
Click to expand...
 
G

Guest

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 said:
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?



Hi,

Thank you for your replay.

I need run "My_Store_Procedure" and pass the parameters multiselected
in listbox1. But I no find how do it.

In the following lines I show function, Store_Procedure,HTML and no
working code.

I have one view, function1, function2 and My_Store_Procedure. My
function2 lock

like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER FUNCTION dbo.My_Function2(@order nvarchar (10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
RETURNS TABLE
AS
RETURN ( SELECT TOP 100 PERCENT field4, field5, field6, ORDER,
SUM(field1)

AS Myfield1, SUM(field2) AS Myfield2, SUM(field3) AS Myfield3
FROM My_Function1(@order, @field1, @field2, @field3)
GROUP BY field4, field5, ORDER, field4, field6
ORDER BY field4 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My_Store_Procedure is derived at My_Function2:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dbo.My_Store_Procedure
(@order nvarchar(10),
@field1 nvarchar (5),
@field2 nvarchar (5),
@field3 nvarchar (5))
AS SELECT *
FROM dbo.My_Function2(@order, @field1,@field2, @field3 )

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-----------------------------------------------------------------------
------- <HTML>
<HEAD>
<title>WebForm1</title>
<meta content="Microsoft Visual Studio .NET 7.1"
name="GENERATOR"> <meta content="Visual Basic .NET 7.1"
name="CODE_LANGUAGE"> <meta content="JavaScript"
name="vs_defaultClientScript"> <meta
content="http://schemas.microsoft.com/intellisense/ie5"
name="vs_targetSchema">
</HEAD>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<CR:CRYSTALREPORTVIEWER id=CrystalReportViewer1
style="Z-INDEX: 101;
LEFT: 24px; POSITION: absolute; TOP: 168px" runat="server"
DisplayGroupTree="False" DisplayToolbar="False" Width="753px"
Height="1095px" ReportSource='<%#
"c:\inetpub\wwwroot\crystal1\CrystalReport1.rpt" %>' Visible="False"
ToolTip="Select">
</CR:CRYSTALREPORTVIEWER><asp:dropdownlist
id="Dropdownlist4"
style="Z-INDEX: 107; LEFT: 16px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998"
Selected="True">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist2"
style="Z-INDEX:
105; LEFT: 112px; POSITION: absolute; TOP: 8px"
runat="server" Width="88px" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999"
Selected="True">1999</asp:ListItem>
<asp:ListItem Value="2000">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:dropdownlist id="Dropdownlist3"
style="Z-INDEX:
104; LEFT: 208px; POSITION: absolute; TOP: 8px"
runat="server" Width="81" Height="24px"
OnSelectedIndexChanged="ChangeWhereClause" AutoPostBack="True">
<asp:ListItem Value="1998">1998</asp:ListItem>
<asp:ListItem Value="1999">1999</asp:ListItem>
<asp:ListItem Value="2000"
Selected="True">2000</asp:ListItem>
<asp:ListItem Value="2001">2001</asp:ListItem>
<asp:ListItem Value="2002">2002</asp:ListItem>
<asp:ListItem Value="2003">2003</asp:ListItem>
<asp:ListItem Value="2004P">2004P</asp:ListItem>
<asp:ListItem Value="2004">2004</asp:ListItem>
</asp:dropdownlist><asp:label id="Label1"
style="Z-INDEX: 103; LEFT:
336px; POSITION: absolute; TOP: 8px" runat="server"
Width="288px" Height="20px" BorderStyle="None"
BorderColor="#0000C0"
BackColor="RoyalBlue" ForeColor="White"
Font-Bold="True">Conceptos</asp:label><asp:listbox
id="listbox1"
style="Z-INDEX: 102; LEFT: 336px; POSITION: absolute; TOP: 32px"
runat="server"
Width="288px" Height="116px"
OnSelectedIndexChanged="ChangeWhereClause"
AutoPostBack="True"></asp:listbox><asp:button id="Button1"
style="Z-INDEX: 106; LEFT: 232px; POSITION: absolute; TOP: 56px"
runat="server"
Width="72px" BackColor="#C0C0FF" Text="Button1"
Enabled="False"></asp:button><asp:button id="Button2" style="Z-INDEX:
108; LEFT: 232px; POSITION: absolute; TOP: 96px" runat="server"
Width="72px" BackColor="#FFC0FF"
Text="Exportar"></asp:button><asp:dropdownlist id="DropDownList1"
style="Z-INDEX: 109; LEFT: 32px; POSITION: absolute; TOP: 96px"
runat="server" Width="184px"
Height="24px"></asp:dropdownlist>
</form>
</body>
</HTML>
-----------------------------------------------------------------------

#Region
....

Me.SqlSelectCommand1.CommandText = "[My_Store_Procedure]"
Me.SqlSelectCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@order",
System.Data.SqlDbType.NVarChar, 10))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field1",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field2",
System.Data.SqlDbType.NVarChar, 5))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@field3",
System.Data.SqlDbType.NVarChar, 5))

....
------------------------

Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
Dim strWhereClause As String = ""
For Each li in listbox1.Items
If li.Selected Then

strWhereClause &= "order=" & li.Value & " Or " '<----------
what is wrong?

End If
Next
If strWhereClause.Length > 0 Then

strWhereClause = Left(strWhereClause, strWhereClause.Length() -
4) strWhereClause = "WHERE " & strWhereClause
Dim strSql = "Select * " _
& "From My_Store_Procedure " & strWhereClause & "
Order By
LastName"

Me.SqlSelectCommand1.Parameters("@field1").Value =
Dropdownlist4.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field2").Value =
Dropdownlist2.SelectedItem.Text
Me.SqlSelectCommand1.Parameters("@field3").Value =
Dropdownlist3.SelectedItem.Text
Me.SqlDataAdapter1.Fill(DataSet21.My_Store_Procedure)

Dim oRpt As New CrystalReport1
oRpt.SetDataSource(DataSet21)
CrystalReportViewer1.Visible = True
CrystalReportViewer1.ReportSource = oRpt

End Sub
...

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

Me.SqlSelectCommand1.Parameters("@order").Value =
listbox1.SelectedItem.Value

and work fine.

In the listbox multiple selection i Tryed put:

strWhereClause &= "order=" &
Me.SqlSelectCommand1.Parameters("@order").value = li.Value
& " Or " ' <-------------------- what is wrong?

instead of:

strWhereClause &= "EmployeeID=" & li.Value & " Or "

...and no work.

Thank you again,

Adis.
Click to expand...
Click to expand...
 
C

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

Guest

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).BeginInit()
'
'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_anterior", "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").Value =
Dropdownlist2.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@año_actual").Value =
Dropdownlist3.SelectedItem.Text

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAñosSumaCompara)
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.PhysicalApplicationPath +
"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.rpt" %>'
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
------------------------------------------------------------------------------------------
 
G

Guest

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).BeginInit()
'
'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_anterior", "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").Value =
Dropdownlist2.SelectedItem.Text
'Me.SqlSelectCommand1.Parameters("@año_actual").Value =
Dropdownlist3.SelectedItem.Text

Me.SqlDataAdapter1.Fill(DataSet31.sp_fnBaseAñosSumaCompara)
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.PhysicalApplicationPath +
"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.rpt" %>'
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
 
C

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ñosSumaCompara)
Dim oRpt As New Resultados
oRpt.SetDataSource(DataSet31)
CrystalReportViewer1.ReportSource = oRpt

End Sub

And that's it. Let me know how it works.
 
G

Guest

Hi,

Works very, very nice.

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

Thank you.
 
G

Guest

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Obtaining Data Based Upon Multiple Selections From a ListBox... 0
parameters 0
Datagrid > DataList problem - Any clues? 1
Parameters 0
Unexpected behaviour of Listbox MultiSelect 5
Transfer between two listboxes 0
insert in DataGrid not working 1
How does one update all records displayed in a asp page 1

Members online

Total: 122 (members: 4, guests: 118)
Robots: 92

Forum statistics

Threads
474,266
Messages
2,571,081
Members
48,772
Latest member
Backspace Studios

Latest Threads

Top