current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I Have a sql query as below:

Select CatId 
From tbl_T2H_Category 
Where Category IN ('Category3', 'Category4', 'Category6')

Now what i want is the values inside IN clause should be added dynamically from the checkboxlist. I am getting comma separated values from my control and passing that to the sql query like this:

string mystring = "Category3,Category4,Category6";
cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (" + mystring + ")";

This is not executing on sql side, because sql only recognises strings if they are inside "'" "'" (single quotes). Kindly help me write the appropriate query.

share|improve this question
11  
Step 1, learn about parameterised queries. –  Arran Jun 11 '13 at 14:21
    
What version of sql server? Later versions have a better way of handling this. –  Joel Coehoorn Jun 11 '13 at 14:22
    
i am using sql server 2008 R2 –  user1990370 Jun 11 '13 at 14:58

6 Answers 6

up vote 5 down vote

You want to use a Table-Value Parameter. The MSDN article shown here demonstrates it better than I can:

http://msdn.microsoft.com/en-us/library/bb675163.aspx

share|improve this answer
1  
+1 for not proposing the obvious, dangerous SELECT 'INJECT' + 'ME' FROM Table –  phadaphunk Jun 11 '13 at 14:25
up vote 0 down vote

Just wrap every category in string with single quote?

Something like 

var mystring = "'Category3','Category4','Category6'"
share|improve this answer
3  
This will leave him open to sql injection. –  phadaphunk Jun 11 '13 at 14:24
1  
And be sure there is no way, user can forge category values - i.e. if user can submit category with name ';drop table Category;-- you're in trouble –  Ondrej Svejdar Jun 11 '13 at 14:25
up vote 0 down vote

Split your comma separated values into an array with

 string[] myArr = commaSepString.Split(',');

and then join them with commas, surrounded by commas like this

 string newString = "'"+myArr.Join("','")+"'";
share|improve this answer
up vote 0 down vote

correct syntax

string mystring = "'Category3','Category4','Category6'";
cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (" + mystring + ")";

but please dont use it this was. you can use it like below(but this is not recommended as well)

cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN ('" + cat_1 + "','" + cat_2 + "','" + cat_3 + "')";

I would use this one below, it is alot secure

string commandText = "Select CatId From tbl_T2H_Category Where Category IN (@cat_1,@cat_2 @cat_3)";
 SqlCommand command = new SqlCommand(commandText, connection);
 command.Parameters.Add("@cat1", SqlDbType.Varchar);
 command.Parameters["@cat1"].Value = "category1";
 command.Parameters.Add("@cat2", SqlDbType.Varchar);
 command.Parameters["@cat2"].Value = "category2";
 command.Parameters.Add("@cat3", SqlDbType.Varchar);
 command.Parameters["@cat3"].Value = "category3";

this last one is much secure, it prevents sql-injection

share|improve this answer
up vote 0 down vote

This is best dealt with using Table-Valued Parameters.

Your first step is to create the type:

CREATE TYPE dbo.StringList AS TABLE (Value NVARCHAR(MAX) NOT NULL);

Your next step is to create a datatable in c# from your comma separated list to pass to your SqlCommand:

string mystring = "Category3,Category4,Category6";
string[] myarray = mystring.Split(",".ToCharArray());

DataTable table = new DataTable();
table.Columns.Add("Value", typeof(string));

for (int i = 0; i < myarray.Length; i++)
{
    var row = table.NewRow();
    row[0] = myarray[i];
    table.Rows.Add(row);
}

Finally you can pass this to your SqlCommand:

cmd.commanText = "Select CatId From tbl_T2H_Category Where Category IN (SELECT Value FROM @Strings)";
cmd.Parameters.Add(new SqlParameter("@Strings", SqlDbType.Structured)).Value = table;
share|improve this answer
up vote 0 down vote

Try this...

public String returnCategories(string categories) 
    {
        string categoriesConc = "";

        string[] split = categories.Split(',');

        for (int i = 0; i < split.Length; i++) 
        {
            if (string.IsNullOrEmpty(categoriesConc))
            {
                categoriesConc = split[i].ToString();
            }
            else 
            {
                categoriesConc = categoriesConc + "," + split[i].ToString();
            }
        }

        return categoriesConc;
    }
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.