By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,688 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,688 IT Pros & Developers. It's quick & easy.

Parameters

P: n/a
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
Share this Question
Share on Google+
10 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.