parameters

[Guest]

Hi,

My purpose consists on filtering the fields "years" and "clients" of a
project WinForm, in a similar way to as I make it

without any problems in a previous project ASP.NET.

The filtering task must be on the server side because my database is very
extensive and, in this case, filter with Dataview

is not convenient.

I have a database in SQL Server side with a view, two functions and a stored
procedure:

1. The table "tblSales" has three fields: year, client and quantity.

2. The function "fn_com1" is prepared to filter the field "year".

3. The function "fn_com2" has as purpose filtering of the field "client."

4. The stored procedure "sp_fncom" is created starting from the function
"fn_com2" and it contains the year, client and

quantity fields.

5. The SQL Server has a denominated function "CSVToTable", a Comma Separates
Valued (CSV) that works fine.

II-WinForm1:

Visual Studio WinForm, Form1, Design Mode:

Controls:

ListBox1: It contains as Items the field "year"
("2000","2001","2002","2003","2004","2005").

ListBox2: It contains as Items the field "client". Must be (" 101"," 102","
103")

(Important: Both Listbox has the multiselection activated as Multiextended.)

Button1: To execute the stored procedure already described.

Datagrid1: To show results of the stored procedure.

Also, the corresponding accesses to data, etc. (SqlDataAdpater, Datasets, etc)

III- WinForm1 Visual Basic code:

Here I have the greatest problem. I don't find the correct syntax to execute
the queries.

I don’t find the necessary Visual Studio Space Name. Maybe I need to create
my custom Space Name?

I need inside the Windows form code something similar to:


Sub ChangeWhereClause (ByVal Sender As System.Object, ByVal e As
System.EvenArgs)

'For example, to filter "year" field I put:

Protected li As System.Web.UI.WebControls.ListItem.

(Used in a previous project ASP.NET and ListItem class not exists in Windows
Forms).


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

....etc

End sub
---------------------------------------------------------------------------------

In the followings lines I show the Public Class Form1 code:


Imports ADODB
Imports System
Imports System.Collections
Imports System.Data
Imports System.Data.SqlClient
Imports System.io
Imports System.Configuration
Imports Microsoft.VisualBasic


Public Class Form1
Inherits System.Windows.Forms.Form


#Region " Código generado por el Diseñador de Windows Forms "

Public Sub New()
MyBase.New()



'El Diseñador de Windows Forms requiere esta llamada.
InitializeComponent()

'Agregar cualquier inicialización después de la llamada a
InitializeComponent()

End Sub

'Form reemplaza a Dispose para limpiar la lista de componentes.
Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub

'Requerido por el Diseñador de Windows Forms
Private components As System.ComponentModel.IContainer

'NOTA: el Diseñador de Windows Forms requiere el siguiente procedimiento
'Puede modificarse utilizando el Diseñador de Windows Forms.
'No lo modifique con el editor de código.

Friend WithEvents ListBox1 As System.Windows.Forms.ListBox
Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
Friend WithEvents DataSet11 As Experimental.DataSet1
Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid
Friend WithEvents ListBox2 As System.Windows.Forms.ListBox
Friend WithEvents Button1 As System.Windows.Forms.Button


Protected li As System.Web.UI.WebControls.ListItem ‘ Not works in WinForms


<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.ListBox1 = New System.Windows.Forms.ListBox
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.DataSet11 = New Experimental.DataSet1
Me.DataGrid1 = New System.Windows.Forms.DataGrid
Me.ListBox2 = New System.Windows.Forms.ListBox
Me.Button1 = New System.Windows.Forms.Button
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).BeginInit()
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).BeginInit()
Me.SuspendLayout()
'
'ListBox1
'
Me.ListBox1.Items.AddRange(New Object() {"2000", "2001", "2002",
"2003"})
Me.ListBox1.Location = New System.Drawing.Point(120, 40)
Me.ListBox1.Name = "ListBox1"
Me.ListBox1.SelectionMode =
System.Windows.Forms.SelectionMode.MultiExtended
Me.ListBox1.Size = New System.Drawing.Size(120, 95)
Me.ListBox1.TabIndex = 0
'
'SqlDataAdapter1
'
Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
Me.SqlDataAdapter1.TableMappings.AddRange(New
System.Data.Common.DataTableMapping()
{New System.Data.Common.DataTableMapping("Table", "spFnCom", New
System.Data.Common.DataColumnMapping() {New
System.Data.Common.DataColumnMapping("year",
"year"), New System.Data.Common.DataColumnMapping("client", "client"), New
System.Data.Common.DataColumnMapping("quantity", " quantity ")})})
'
'SqlSelectCommand1
'
Me.SqlSelectCommand1.CommandText = "[ spFnCom]"
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("@year", System.Data.SqlDbType.NVarChar,
2000))
Me.SqlSelectCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@client",
System.Data.SqlDbType.NVarChar, 2000))
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation id=RENEPC;packet
size=4096;integrated security=SSPI;data source=""RE" & _
"NEPC"";persist security info=False;initial catalog=prueba"
'
'DataSet11
'
Me.DataSet11.DataSetName = "DataSet1"
Me.DataSet11.Locale = New System.Globalization.CultureInfo("es-ES")
'
'DataGrid1
'
Me.DataGrid1.DataMember = ""
Me.DataGrid1.DataSource = Me.DataSet11. spFnCom
Me.DataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText
Me.DataGrid1.Location = New System.Drawing.Point(176, 184)
Me.DataGrid1.Name = "DataGrid1"
Me.DataGrid1.Size = New System.Drawing.Size(296, 80)
Me.DataGrid1.TabIndex = 1
'
'ListBox2
'
Me.ListBox2.Items.AddRange(New Object() {"101", "102", "103"})
Me.ListBox2.Location = New System.Drawing.Point(296, 40)
Me.ListBox2.Name = "ListBox2"
Me.ListBox2.SelectionMode =
System.Windows.Forms.SelectionMode.MultiExtended
Me.ListBox2.Size = New System.Drawing.Size(120, 95)
Me.ListBox2.TabIndex = 2
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(536, 80)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 3
Me.Button1.Text = "Button1"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(664, 406)
Me.Controls.Add(Me.Button1)
Me.Controls.Add(Me.ListBox2)
Me.Controls.Add(Me.DataGrid1)
Me.Controls.Add(Me.ListBox1)
Me.Name = "Form1"
Me.Text = "Form1"
CType(Me.DataSet11,
System.ComponentModel.ISupportInitialize).EndInit()
CType(Me.DataGrid1,
System.ComponentModel.ISupportInitialize).EndInit()
Me.ResumeLayout(False)

End Sub

#End Region


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


Dim sOrder As String = ""

For Each oList In ListBox1.Items

If oList.Selected Then

sOrder &= oList.Value & ","

End If


Next

If sOrder.Length > 0 Then

sOrder = Strings.Left(sOrder, sOrder.Length() - 1)

End If

'----------------------------------------------------------
Dim sOrder1 As String = ""


For Each oList In ListBox2.Items

If oList.Selected Then

sOrder1 &= oList.Value & ","

End If


Next

If sOrder1.Length > 0 Then

sOrder1 = Strings.Left(sOrder1, sOrder1.Length() - 1)

End If



With Me.SqlSelectCommand1
.Parameters("@year").Value = sOrder
.Parameters("@client").Value = sOrder1


Me.SqlDataAdapter1.Fill (DataSet11. spFnCom)


End With

End Sub

End Class

--------------------------------------------------------------------
IV- SQL Server Scripts:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
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
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------------------------------------------------------------------
create view dbo.vs_Sales

as

select year, client, quantity

from dbo.tbl_Sales

go

-------------------------------------------------------------------

Create function dbo.fncom1

(@year nvarchar (2000))

returns table

as

return (select year, client, quantity

from dbo.vs_Sales

where year in (select val from dbo.csvtotable (@year)))

go

-------------------------------------------------------------------

create function dbo.fncom2

(@year nvarchar (2000), @client nvarchar (2000))

returns table

as

return (select year, client, quantity

from dbo. Fncom1 (@year)

where client in (select val from dbo.csvtotable (@client)))

go

---------------------------------------------------------------------

create procedure dbo.spfncom

(@year (2000), @client(2000))

as

select top 100 percent year, client, quantity

from dbo.fncompara1 (@year, @client)

go
----------------------------------------------------------------------

Thank you in advance,


Adis