I have a datagridview where i need to display the filtered search of a channels:
the user will specify:
@ChannelType int,
@ChannelStatus int,
@govId int,
@DistId int,
@Budget money,
@dt varchar(max),
@minSqm int,
@maxSqm int
My problem is with the specification list (@dt): it is a checkedlistbox where the user will choose specifications and i have to find the channels they have the selected specs.
So i need to store this in an array to make the where in close condition.
So i listed the specs item id in an array in c# and tried to passed to sql but it is not working.
What I have tried:
Stored procedure:
USE [Tenant Management]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[BuySearchEngine]
@ChannelType int,
@ChannelStatus int,
@govId int,
@DistId int,
@Budget money,
@dt varchar(max),
@minSqm int,
@maxSqm int
AS
select DISTINCT c.ID,c.Name,cc.[Channel Category], gv.GOVERNATOR + ' '+dst.District+' '+ct.City +' '+c.street As Location,c.Surface
,c.Floor,c.[Selling Price],c.[Market price]
from [dbo].[Channel_availableSpecs] asp
inner join [dbo].[Channel] c on asp.Channel_Id = c.ID
inner join [dbo].[Channel_Category] cc on c.[Channel Category ID] = cc.ID
inner join [dbo].[Governator] gv on c.[Governator ID] = gv.ID
inner join [dbo].[District] dst on c.[District ID] = dst.id
inner join [dbo].[City] ct on c.[City ID] = ct.id
where c.[Channel Type] =@ChannelType
and c.[Channel Status]=@ChannelStatus
and c.[Governator ID]=@govId
and c.[District ID]=@DistId
and c.[Selling Price] < @Budget
and c.Surface >= @minSqm
and c.Surface <= @maxSqm
and asp.ChennelSpec_Id IN (SELECT asp.ChennelSpec_Id FROM Fn_split(@dt, ','))
C#:
List<string> list = new List<string>();
private void checkedListBox1_ItemCheck(object sender, ItemCheckEventArgs e)
{
list.Add(checkedListBox1.SelectedValue.ToString());
}
private void next_Click(object sender, EventArgs e)
{ int govId,distId,minSqm,maxSqm;
Int32.TryParse(governator.SelectedValue?.ToString(),out govId);
Int32.TryParse(district.SelectedValue?.ToString(),out distId);
Int32.TryParse(sqm_from.Text,out minSqm);
Int32.TryParse(sqm_to.Text,out maxSqm);
if (rentbutton == 1)
{
rent_panel.Show();
}
else
{
buy_search_panel.Show();
string[] Specs = new string[list.Count];
Utilities.BuySearch(Specs, govId, distId, minSqm,maxSqm,availability);
list.Clear();
}
availabilityFill();
}
public static void BuySearch(Array array,int govId,int distId, int minSqm,int maxSqm,DataGridView dataGridView)
{
string specs = Get_comma_delimited_string(array);
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Execute BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt";
cmd.Parameters.Add("@ChannelType", SqlDbType.Int).Value = "1";
cmd.Parameters.Add("@ChannelStatus", SqlDbType.Int).Value ="5" ;
cmd.Parameters.Add("@govId", SqlDbType.Int).Value = govId;
cmd.Parameters.Add("@DistId", SqlDbType.Int).Value = distId;
cmd.Parameters.Add("@dt", SqlDbType.VarChar).Value = specs;
cmd.Parameters.Add("@minSqm", SqlDbType.Int).Value = minSqm;
cmd.Parameters.Add("@maxSqm", SqlDbType.Int).Value = maxSqm;
conn.Open();
DataTable dt = new DataTable();
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
dataGridView.DataSource = dt;
conn.Close();
}