How to pass comma separated column values from sql sever in a variable in c#

Question

In my table I have inserted values from multiple dropdowns now I want to pass those values in where clause of my c# code how to do it please help

SqlCommand command = new SqlCommand("select distinct comp_type from master_office_complaint 
where comp_id='" + reader["marked_officer"]+ "'", conn);

Here marked_officer column contains multiple values.

You need a `WHERE comp_id IN (...)` clause. Now, the goal is to make this query protected against SQL injections. — Cid, Feb 13 '20 at 10:14
Does this answer your question? [Pass Array Parameter in SqlCommand](https://stackoverflow.com/questions/2377506/pass-array-parameter-in-sqlcommand) — Cid, Feb 13 '20 at 10:14
Use parameters, to be safe from sql injection your query should be : select distinct comp_type from master_office_complaint where comp_id in (select value from string_split(@marked_officer, ',')) — Marc Guillot, Feb 13 '20 at 10:18
@MarcGuillot i am getting this error -System.Data.SqlClient.SqlException: 'Must declare the scalar variable "@marked_officer".' — piya kataria, Feb 13 '20 at 10:27
@SZT its a sql server table's column with multiple comma separated values like (,12,4,5) etc basically i have inserted values form dropdown — piya kataria, Feb 13 '20 at 10:36
so you mean the column itself contains a value like (12, 4,5)? not like row[1].column["mo"] = 12, row[2].column["mo"] = 4 ? I would suggest please put the sql command in a string and look at the resultant string. If it's still confusing please put the resultant string here so we can take a look — SZT, Feb 13 '20 at 10:43
marked_officer 12,11,10 ,12,10 1 ,2 this is how my table coumn looks like — piya kataria, Feb 13 '20 at 10:51
And what's the value of "select distinct comp_type from master_office_complaint where comp_id='" + reader["marked_officer"]+ "'"?? do something like this: string pQuery = "select distinct comp_type from master_office_complaint where comp_id='" + reader["marked_officer"]+ "'" Console.WriteLine(pQuery) it will allow you to look at the sql query you are trying to generate. — SZT, Feb 13 '20 at 10:55
@SZT in return i m getting this output select distinct comp_type from master_office_complaint where comp_id= '12 .....it means among 4 column data i m getting just first one :( — piya kataria, Feb 13 '20 at 11:02
That means reader["marked_officer"] doesn't have all the values you need, you need to iterate through your record and generate a string that holds all the values you are looking for, and also your query should look like this .... where comp_id in ('your_New_string_Variable') — SZT, Feb 13 '20 at 11:06
no column does have all the values but query not fetching all so can u explain more about iteration like if i apply for loop then how? — piya kataria, Feb 13 '20 at 11:12
SqlCommand command3 = new SqlCommand("select distinct comp_type from master_office_complaint where comp_id =" + reader["marked_officer"].ToString() + "", conn2); using (SqlDataReader reader3 = command3.ExecuteReader()) { while (reader3.Read()) { source_name = reader3["comp_type"].ToString(); } reader3.Close(); }this is how part of code looks like — piya kataria, Feb 13 '20 at 11:13

score 2 · Accepted Answer · answered Feb 13 '20 at 11:33

2

Please find the below answer on SQL Server.

SELECT DISTINCT comp_type FROM master_office_complaint
WHERE comp_id IN ('ID1','ID2','ID3')

answered Feb 13 '20 at 11:33

Swapnil Mhaske

141
1
6

How to pass comma separated column values from sql sever in a variable in c#

1 Answers1