Re: Parameters

From: Adis (Adis_at_discussions.microsoft.com)
Date: 10/20/04 

Date: Wed, 20 Oct 2004 08:09:06 -0700

Hi,

Thank you for your replay.

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

In the following lines I show function, Store_Procedure,HTML and no working
code.
 
I have one view, function1, function2 and My_Store_Procedure. My function2
lock

like this:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

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

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

My_Store_Procedure is derived at My_Function2:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

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

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

#Region
....

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

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

Sub ChangeWhereClause(Sender As System.Object, e As System.EventArgs)
    Dim strWhereClause As String = ""
    For Each li in listbox1.Items
      If li.Selected Then
       
        strWhereClause &= "order=" & li.Value & " Or " '<----------
what is wrong?

      End If
    Next
    If strWhereClause.Length > 0 Then
      
      strWhereClause = Left(strWhereClause, strWhereClause.Length() - 4)
      strWhereClause = "WHERE " & strWhereClause
      Dim strSql = "Select * " _
                 & "From My_Store_Procedure " & strWhereClause & " Order By
LastName"

Me.SqlSelectCommand1.Parameters("@field1").Value =
Dropdownlist4.SelectedItem.Text
        Me.SqlSelectCommand1.Parameters("@field2").Value =
Dropdownlist2.SelectedItem.Text
        Me.SqlSelectCommand1.Parameters("@field3").Value =
Dropdownlist3.SelectedItem.Text
        Me.SqlDataAdapter1.Fill(DataSet21.My_Store_Procedure)
        
        Dim oRpt As New CrystalReport1
        oRpt.SetDataSource(DataSet21)
        CrystalReportViewer1.Visible = True
        CrystalReportViewer1.ReportSource = oRpt
    
End Sub
...

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

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

and work fine.

In the listbox multiple selection i Tryed put:

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

instead of:

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

...and no work.

Thank you again,

Adis.

"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==?=" <Adis@discussions.microsoft.com> wrote in
> news:F00BB894-82D3-415F-A42B-3E777C49D02A@microsoft.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").Value =
> > listbox1.SelectedItem.Value
> >
> > and work fine.
> >
> > In the listbox multiple selection i Tryed put:
> >
> > strWhereClause &= "EmployeeID=" &
> > Me.SqlSelectCommand1.Parameters("@EmployeeID").value = li.Value
> > & " Or " ' <-------------------- what is wrong?
> >
> >
> > instead of:
> >
> >
> > strWhereClause &= "EmployeeID=" & li.Value & " Or "
> >
> > ...and no work.
> >
> > In listbox multiselection mode:
> >
> > How to dealing with parameters?
> >
> > How to pass the parameter? "@EmployeeID".
> >
> > For example: @EmployeeID instead EmployeeID
> >
> >
> >
> > Thank you in advance,
> >
> > Adis.
> >
>
>