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

Priyantha Niroshan 41 Reputation points
2024-09-07T16:06:21.0566667+00:00

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.sample

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,921 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,998 questions
VB
VB
An object-oriented programming language developed by Microsoft that is implemented on the .NET Framework. Previously known as Visual Basic .NET.
2,737 questions
SQL Server Transact-SQL
SQL Server Transact-SQL
SQL Server: A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.Transact-SQL: A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
102 questions
{count} votes

Accepted answer
  1. Jiachen Li-MSFT 32,376 Reputation points Microsoft Vendor
    2024-09-09T06:00:12.5066667+00:00

    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.

    1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 112.7K Reputation points MVP
    2024-09-07T20:10:10.83+00:00

    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.

    0 comments No comments

  2. LiHongMSFT-4306 28,041 Reputation points
    2024-09-09T01:46:41.4233333+00:00

    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".


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.