Insert (Save) and display multiple values from single column in Database using C# and VB.Net in ASP.Net

Last Reply 4 months ago By pandeyism

Posted 4 months ago

string fee= "$12";
string date= "01/July/2019";
string Time = "5:00 pm";
string Descripiton = "This is testing description";
string Name="Hrithik Roshan";


I have following string above i want when i insert it in database it should insert as formated string like

so when i call it it should receive as formated string, i have one column as nvarchar(2000) so i want store every thing in one column as formatted string


it should display like this when i bind it in listview
Custom Quote: $12
Date: 01/July/2019 5:00 pm
Description=This is testing description
Name: Hrithik Roshan

Please advice

Posted 4 months ago

Hi nauna,

Refer below sample.

HTML

<asp:Button Text="Insert" runat="server" OnClick="Insert" />
<div>
    <asp:ListView runat="server" ID="ListView1" OnItemDataBound="ListView1_ItemDataBound">
        <ItemTemplate>
            <asp:Label ID="lblFees" runat="server" />
            <br />
            <asp:Label ID="lblDate" runat="server" />
            <br />
            <asp:Label ID="lblDesc" runat="server" />
            <br />
            <asp:Label ID="lblName" runat="server" />
            <br />
        </ItemTemplate>
    </asp:ListView>

Namespaces

C#

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

VB.Net

Imports System.Data.SqlClient
Imports System.Data

Code

C#

protected void Page_Load(object sender, EventArgs e)
{
    if (!this.IsPostBack)
    {
        DataTable dt = BindListView();
        ListView1.DataSource = dt;
        ListView1.DataBind();
    }
}

protected void Insert(object sender, EventArgs e)
{
    string fee = "$12";
    string date = "01/July/2019";
    string Time = "5:00 pm";
    string Descripiton = "This is testing description";
    string Name = "Hrithik Roshan";
    string formatedString = fee + "," + date + "," + Time + "," + Descripiton + "," + Name;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("INSERT INTO tblData VALUES(@Data)", con);
    cmd.Parameters.AddWithValue("@Data", formatedString);
    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
    DataTable dt = BindListView();
    ListView1.DataSource = dt;
    ListView1.DataBind();
}

private static DataTable BindListView()
{
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    SqlConnection con = new SqlConnection(constr);
    SqlCommand cmd = new SqlCommand("SELECT Data FROM tblData", con);
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);
    return dt;
}

protected void ListView1_ItemDataBound(object sender, ListViewItemEventArgs e)
{
    DataTable dt = BindListView();
    ListViewDataItem dataItem = (ListViewDataItem)e.Item;
    if (e.Item.ItemType == ListViewItemType.DataItem)
    {
        Label lblFees = dataItem.FindControl("lblFees") as Label;
        Label lblDate = dataItem.FindControl("lblDate") as Label;
        Label lblDesc = dataItem.FindControl("lblDesc") as Label;
        Label lblName = dataItem.FindControl("lblName") as Label;

        string[] formatedData = dt.Rows[0]["Data"].ToString().Split(',');
        lblFees.Text = formatedData[0];
        lblDate.Text = formatedData[1] + " " + formatedData[2];
        lblDesc.Text = formatedData[3];
        lblName.Text = formatedData[4];
    }
}

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 = BindListView()
        ListView1.DataSource = dt
        ListView1.DataBind()
    End If
End Sub

Protected Sub Insert(ByVal sender As Object, ByVal e As EventArgs)
    Dim fee As String = "$12"
    Dim date1 As String = "01/July/2019"
    Dim Time As String = "5:00 pm"
    Dim Descripiton As String = "This is testing description"
    Dim Name As String = "Hrithik Roshan"
    Dim formatedString As String = fee & "," & date1 & "," & Time & "," & Descripiton & "," & Name
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("INSERT INTO tblData VALUES(@Data)", con)
    cmd.Parameters.AddWithValue("@Data", formatedString)
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()
    Dim dt As DataTable = BindListView()
    ListView1.DataSource = dt
    ListView1.DataBind()
End Sub

Private Shared Function BindListView() As DataTable
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Dim con As SqlConnection = New SqlConnection(constr)
    Dim cmd As SqlCommand = New SqlCommand("SELECT Data FROM tblData", con)
    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
    Dim dt As DataTable = New DataTable()
    da.Fill(dt)
    Return dt
End Function

Protected Sub ListView1_ItemDataBound(ByVal sender As Object, ByVal e As ListViewItemEventArgs)
    Dim dt As DataTable = BindListView()
    Dim dataItem As ListViewDataItem = CType(e.Item, ListViewDataItem)
    If e.Item.ItemType = ListViewItemType.DataItem Then
        Dim lblFees As Label = TryCast(dataItem.FindControl("lblFees"), Label)
        Dim lblDate As Label = TryCast(dataItem.FindControl("lblDate"), Label)
        Dim lblDesc As Label = TryCast(dataItem.FindControl("lblDesc"), Label)
        Dim lblName As Label = TryCast(dataItem.FindControl("lblName"), Label)
        Dim formatedData As String() = dt.Rows(0)("Data").ToString().Split(","c)
        lblFees.Text = formatedData(0)
        lblDate.Text = formatedData(1) & " " & formatedData(2)
        lblDesc.Text = formatedData(3)
        lblName.Text = formatedData(4)
    End If
End Sub

Screenshot