How to write/read comma-separated data from an SQL table and load them into a datagridview column?

Question

I want to write comma-separated string values entered in a text box to an SQL table and later fetch them from the table and load them into a data grid view column.

Answer 1

HI @Priyantha Niroshan,

If you want to use VB.NET to implement this feature, you can refer to the following examples.

Dim connectionString As String = "your_connection_string_here"

Dim keywords As String = TextBox1.Text

Dim query As String = "INSERT INTO DEMO (KeyWords) VALUES (@KeyWords)"

Using connection As New SqlConnection(connectionString)
    connection.Open()
    
    Using command As New SqlCommand(query, connection)
        command.Parameters.AddWithValue("@KeyWords", keywords)
        
        command.ExecuteNonQuery()
    End Using
End Using

Dim query As String = "SELECT value AS KeyWords FROM DEMO CROSS APPLY STRING_SPLIT(KeyWords, ',')"

Dim table As New DataTable()
Using connection As New SqlConnection(connectionString)
    connection.Open()
    Using command As New SqlCommand(query, connection)
        Using adapter As New SqlDataAdapter(command)
            adapter.Fill(table)
        End Using
    End Using
End Using
DataGridView1.DataSource = table

Best Regards.

Jiachen Li

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To write the comma-separated list, you can use a table-value parameter. I have an article about this on my web site: https://www.sommarskog.se/arrays-in-sql-2008.html

To produce a comma-separated list from a set of rows in SQL Server, you can use the string_agg function.

Whatever you do, don't store the comma-separated list as such. It will only cause you pain.

Answer 3

HI @Priyantha Niroshan

To fetch comma-separated data, you could use string_split like this:

CREATE TABLE #DEMO (ID INT, KeyWords VARCHAR(50))
INSERT INTO #DEMO VALUES(1,'Sam,Mike,Jim,Eric,Rose')

SELECT * FROM #DEMO

SELECT ID,value AS KeyWords 
FROM #DEMO CROSS APPLY STRING_SPLIT(KeyWords,',')

Best regards,

Cosmog

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".