Replace comma separated id with values from Database in C# and VB.Net in ASP.Net

Hi chetan,

Check this example. Now please take its reference and correct your code.

Database

For this example I have used of Northwind database that you can download using the link given below.

Download Northwind Database

HTML

<asp:GridView runat="server" ID="gvEmployees" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Id" HeaderText="Id" />
        <asp:BoundField DataField="Companyname" HeaderText="Company Name" />
        <asp:BoundField DataField="Datalist" HeaderText="Exhibitorlist" />
    </Columns>
</asp:GridView>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[3] { new DataColumn("Id", typeof(int)),
                        new DataColumn("Companyname", typeof(string)),
                        new DataColumn("Datalist",typeof(string)) });
        dt.Rows.Add(1, "abc", "1,2,3");
        dt.Rows.Add(2, "def", "4,5,7");
        dt.Rows.Add(3, "ghi", "");
        dt.Rows.Add(4, "jkl", "8,10,11");
        for (int i = 0; i < dt.Rows.Count; i++)
        {
            string coun = dt.Rows[i]["Datalist"].ToString();
            if (coun == "" || coun == null)
            {
                dt.Rows[i]["Datalist"] = 0;
            }
            else
            {
                string conString = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
                SqlConnection con = new SqlConnection(conString);
                string query = "SELECT STUFF((SELECT N',' + FirstName FROM Employees WHERE EmployeeID in(" + coun + ") FOR XML PATH(''),TYPE).value('text()[1]','NVARCHAR(MAX)'),1,1,N'') Exhibitorlist";
                SqlCommand cmd = new SqlCommand(query, con);
                con.Open();
                dt.Rows[i]["Datalist"] = cmd.ExecuteScalar().ToString();
                con.Close();
            }
        }
        gvEmployees.DataSource = dt;
        gvEmployees.DataBind();
    }
}

VB.Net

Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
    If Not Me.IsPostBack Then
        Dim dt As DataTable = New DataTable()
        dt.Columns.AddRange(New DataColumn(2) {New DataColumn("Id", GetType(Integer)), New DataColumn("Companyname", GetType(String)), New DataColumn("Datalist", GetType(String))})
        dt.Rows.Add(1, "abc", "1,2,3")
        dt.Rows.Add(2, "def", "4,5,7")
        dt.Rows.Add(3, "ghi", "")
        dt.Rows.Add(4, "jkl", "8,10,11")
        For i As Integer = 0 To dt.Rows.Count - 1
            Dim coun As String = dt.Rows(i)("Datalist").ToString()
            If coun = "" OrElse coun Is Nothing Then
                dt.Rows(i)("Datalist") = 0
            Else
                Dim conString As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
                Dim con As SqlConnection = New SqlConnection(conString)
                Dim query As String = "SELECT STUFF((SELECT N',' + FirstName FROM Employees WHERE EmployeeID in(" & coun & ") FOR XML PATH(''),TYPE).value('text()[1]','NVARCHAR(MAX)'),1,1,N'') Exhibitorlist"
                Dim cmd As SqlCommand = New SqlCommand(query, con)
                con.Open()
                dt.Rows(i)("Datalist") = cmd.ExecuteScalar().ToString()
                con.Close()
            End If
        Next

        gvEmployees.DataSource = dt
        gvEmployees.DataBind()
    End If
End Sub

Screenshot