none
Create a single-quoted comma Separated String from DateGridView RRS feed

  • Question

  • I have a DataGridview with two columns. how do I create a single-quoted comma separated string that I can pass to a SQL Query?

    e.g.

    A1111
    B2333
    C3444
    D4555

    I want to create a string as
    'A1111', B2333', 'C3444', 'D4555'

    Wednesday, June 13, 2018 9:12 PM

All replies

  • The following gets each row value without considering if a row has a empty cell.

    using System;
    using System.Linq;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                dataGridView1.Rows.Add("A1111");
                dataGridView1.Rows.Add("B2333");
                dataGridView1.Rows.Add("C3444");
                dataGridView1.Rows.Add("D4555");
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var result = $"SELECT Id FROM Customers WHERE CustomerName IN ({string.Join(",", dataGridView1.Rows.Cast<DataGridViewRow>().Where(row => row.IsNewRow == false).Select(row => $"'{row.Cells["Column1"].Value}'").ToArray())})";
                Console.WriteLine(result);
            }
        }
    }

    Output

    SELECT AccountNumber FROM Customers WHERE CustomerName IN ('A1111','B2333','C3444','D4555')

    This checks for empty cells

    var items = dataGridView1.Rows.Cast<DataGridViewRow>()
        .Where(row => row.IsNewRow == false && !string.IsNullOrWhiteSpace(Convert.ToString(row.Cells["Column1"].Value)))
        .Select(row => $"'{row.Cells["Column1"].Value}'").ToArray();
    
    var result = $"SELECT Id FROM Customers WHERE CustomerName IN ({string.Join(",", items)})";
    Console.WriteLine(result);


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, June 13, 2018 10:30 PM
    Moderator
  • It is adding some empty strings towards the end of the array, how do I exclude these empty strings that are in the bottom of gridview 
    Monday, June 18, 2018 2:08 PM