How to use C# datagridview selected multiple row values to a SQL query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have a datagridview in a c# form contains datas. I want to select some of the rows (using select check boxes). And there is an sql table which has thousands of rows. I need to filter only the values which were selected on gridview and insert into a temp table so i can use it for reporting sections. how can i do this.
e.g.
dataGridview1 Column
cat
dog
horse
lion
fox
now i am selecting the checkboxes for cat and dog.
Sql data table view is like this.
name name code
cat cat001
dog dg001
horse hrs001
crow crw001
cat cat002
lion ln001
cat cat003
cat cat004
dog dog002
so I've selected cat and dog in the gridview. So i want a filtered temporary table with datas like this on a button click.
name name code
cat cat001
dog dg001
cat cat002
cat cat003
cat cat004
dog dog002
So i am trying to pass this using a sql query but failed. How can i do this.

What I have tried:

SqlConnection conn1 = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=acc;Integrated Security=True");
SqlCommand cmd1 = new SqlCommand(@"select db.date,db.type,db.refno,db.itmcod,db.qty,db.cuscod, db.cstcod,cus.cusnam INTO ##wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod = "dataGridView1.Rows[j].Cells["title"].Value"'", conn1);
conn1.Open();
cmd1.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlBulkCopy bulkCopy = new SqlBulkCopy(conn1);
bulkCopy.DestinationTableName = "##tmp1";
bulkCopy.WriteToServer(dt);
conn1.Close();

Posted 27-Jun-16 8:10am

Mohamed Shakir

Updated 27-Jun-16 20:47pm

Add a Solution

Comments

[no name] 27-Jun-16 19:50pm

I think, you should use OR in your sql. Example: .....WHERE name='cat' OR name='dog'..

Mohamed Shakir 28-Jun-16 1:25am

we can't surely assign cat or dog because a users selection will be different

Azziet 28-Jun-16 2:35am

convert your values as comma separated then use IN clause

Arasappan 28-Jun-16 2:35am

Please show ur checkbox design..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

jayveebishie · Answer 1 · 2016-06-27T20:47:00

C#

string name = string.Empty;
      for (int i = 0; i < dgvSupplier.Rows.Count; i++)
      {
        if (Convert.ToBoolean(dgvSupplier.Rows[i].Cells["CheckBoxColumn"].Value))
          name += "'" + Convert.ToString(dgvSupplier.Rows[i].Cells["Title"].Value) + "',";
      }

Now change where clause to "select db.date,db.type,db.refno,db.itmcod,db.qty,db.cuscod, db.cstcod,cus.cusnam INTO ##wec from fstktxn as db INNER JOIN fcustomer as cus on db.cuscod = cus.cuscod where itmcod IN (" + name + ")", conn1);