Click here to Skip to main content
15,890,947 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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
/****** Object:  StoredProcedure [dbo].[BuySearchEngine]    Script Date: 8/31/2018 4:22:27 PM ******/
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#:

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();

       }
Posted
Updated 31-Aug-18 4:31am

SQL
and asp.ChennelSpec_Id IN (SELECT asp.ChennelSpec_Id FROM Fn_split(@dt, ','))

For each item in the results returned from Fn_split, you're selecting the column from the outer table.

Your code is equivalent to:
SQL
and asp.ChennelSpec_Id = asp.ChennelSpec_Id
which is obviously going to be true for every row where asp.ChennelSpec_Id is not NULL.

You need to compare against the values returned from your Fn_split function. The precise code will depend on the name of the column returned by that function, but it will look something like:
SQL
and asp.ChennelSpec_Id IN (SELECT value FROM Fn_split(@dt, ','))
 
Share this answer
 
Comments
Member 13852666 4-Sep-18 5:58am    
I have tried to modify it and many other but still not working.

I am receiving an error: 'Could not find stored procedure 'Execute BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt'.'
Richard Deeming 4-Sep-18 7:43am    
If you're using ADO.NET and setting the CommandText to "Execute BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt", then you need to set the CommandType to Text.

Alternatively, set the CommandType to StoredProcedure, and set the CommandText to "BuySearchEngine", without the parameter names or the Execute.

If it still doesn't work, then the stored procedure doesn't exist in the database you're connecting to.
Member 13852666 4-Sep-18 9:01am    
string specs = Get_comma_delimited_string(array);
MessageBox.Show(specs);
SqlConnection conn = new SqlConnection(ConnectionString);
SqlCommand cmd = conn.CreateCommand();

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt,@minSqm,@maxSqm";
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();


Still not working, could not find stored procedure BuySearchEngine
Richard Deeming 4-Sep-18 9:04am    
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "BuySearchEngine @ChannelType, @ChannelStatus, @govId, @DistId, @Budget, @dt,@minSqm,@maxSqm";


Go back and read my previous comment again.

"Set the CommandType to StoredProcedure, and set the CommandText to "BuySearchEngine", without the parameter names or the Execute."

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "BuySearchEngine"; // WITHOUT the parameter names!
Member 13852666 4-Sep-18 9:57am    
Thank you
What does Fn_split return?
I don't believe it returns asp.ChennelSpec_Id for each item in @dt.
if it returns a column then might need to do a slight change in the select:

SELECT whateverthenameofthereturncolumnis FROM Fn_split(@dt, ',')
 
Share this answer
 
Comments
Member 13852666 4-Sep-18 6:24am    
I have tried to send the specs in an array but it is not working. so i am sending the list in a comma separated string so Fn_split returns all the items.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900