locked
Format DataTable Column RRS feed

  • Question

  • User974100899 posted

    Hello - I have a column in my DataTable that has a semi-colon seperated list.  I want to take have the data in the DataTable be "stacked" on top of each other.  For example if my DataTable is like this

    static DataTable GetTable()
    {
    	DataTable table = new DataTable();
    	table.Columns.Add("Dosage", typeof(int));
    	table.Columns.Add("Drug", typeof(string));
    	table.Columns.Add("Patient", typeof(string));
    	table.Columns.Add("Date", typeof(DateTime));
    	table.Rows.Add(25, "Indocin;Methadone;Dilauted;Narcan;", "David", DateTime.Now);
    	return table;
    }

    I want the Drug to appear like this:

    Indocin;

    Methadone;

    Dilauted;

    Narcan;

    What syntax would update the data table so the data displays as I need?

    Sunday, May 6, 2018 2:06 AM

All replies

  • User-1716253493 posted
            string str = "Indocin;Methadone;Dilauted;Narcan;";
            string[] strarray = str.Split(';');
            foreach (string s in strarray)
            {
                if (!string.IsNullOrWhiteSpace(s))
                {
                    table.Rows.Add(25, s, "David", DateTime.Now);
                }
            } 

    Sunday, May 6, 2018 5:35 AM
  • User516094431 posted

    As per my understanding, you can't store data look like as you describe. 

    But you can achieve your requirement using different technique. One of them check following code.

    static void Main(string[] args)
            {
                var dt = GetTable();
                foreach (DataRow item in dt.Rows)
                {
                    string[] strArray = item[1].ToString().Split(';');
                    foreach (var str in strArray)
                    {
                        if(!string.IsNullOrEmpty(str))
                            Console.WriteLine(str+";");
                    }
                }
            }
            static DataTable GetTable()
            {
                DataTable table = new DataTable();
                table.Columns.Add("Dosage", typeof(int));
                table.Columns.Add("Drug", typeof(string));
                table.Columns.Add("Patient", typeof(string));
                table.Columns.Add("Date", typeof(DateTime));
                table.Rows.Add(25, "Indocin;Methadone;Dilauted;Narcan;", "David", DateTime.Now);
                return table;
            }


      In strArray, you have all name of drug. You can use as you required.

    Sunday, May 6, 2018 5:44 AM
  • User-369506445 posted

    hi

    you can use Environment.NewLine for new line and please try below code

     protected void Page_Load(object sender, EventArgs e)
            {
                string text = "Indocin;Methadone;Dilauted;Narcan;";
                text = text.Replace(";",  System.Environment.NewLine);
                
                DataTable table = new DataTable();
                table.Columns.Add("Dosage", typeof(int));
                table.Columns.Add("Drug", typeof(string));
                table.Columns.Add("Patient", typeof(string));
                table.Columns.Add("Date", typeof(DateTime));
                table.Rows.Add(25, text, "David", DateTime.Now);
                
                GridView1.DataSource = table;
                GridView1.DataBind();
            }        
    
            protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
            {
                GridView1.Columns[1].ItemStyle.Width = Unit.Pixel(80);
            }

    and in Html

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
    
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCreated="GridView1_RowCreated">
                    <Columns>
                        <asp:BoundField DataField="Dosage" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Drug" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Patient" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Date" HeaderText="Dosage" ReadOnly="True" />
                    </Columns>
                </asp:GridView>
    
            </div>
        </form>
    </body>
    </html>

    and you result be below like :

    Sunday, May 6, 2018 6:25 AM
  • User974100899 posted

    hi

    you can use Environment.NewLine for new line and please try below code

     protected void Page_Load(object sender, EventArgs e)
            {
                string text = "Indocin;Methadone;Dilauted;Narcan;";
                text = text.Replace(";",  System.Environment.NewLine);
                
                DataTable table = new DataTable();
                table.Columns.Add("Dosage", typeof(int));
                table.Columns.Add("Drug", typeof(string));
                table.Columns.Add("Patient", typeof(string));
                table.Columns.Add("Date", typeof(DateTime));
                table.Rows.Add(25, text, "David", DateTime.Now);
                
                GridView1.DataSource = table;
                GridView1.DataBind();
            }        
    
            protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
            {
                GridView1.Columns[1].ItemStyle.Width = Unit.Pixel(80);
            }

    and in Html

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head runat="server">
        <title></title>
    </head>
    <body>
        <form id="form1" runat="server">
            <div>
    
                <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCreated="GridView1_RowCreated">
                    <Columns>
                        <asp:BoundField DataField="Dosage" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Drug" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Patient" HeaderText="Dosage" ReadOnly="True" />
                        <asp:BoundField DataField="Date" HeaderText="Dosage" ReadOnly="True" />
                    </Columns>
                </asp:GridView>
    
            </div>
        </form>
    </body>
    </html>

    and you result be below like :

    It looks like this solution would work - however, I was providing an example, and I do not know the values that will exist in the DataTable.  Just always know that I will have a Data Column called Drug that will be in this format that I will have to format before binding to an asp:textbox

    Sunday, May 6, 2018 2:55 PM
  • User-369506445 posted

    No matter, you can use below code for each cell you want

    GridView1.Columns[1].ItemStyle.Width = Unit.Pixel(80);

    Sunday, May 6, 2018 3:02 PM
  • User974100899 posted

    No matter, you can use below code for each cell you want

    GridView1.Columns[1].ItemStyle.Width = Unit.Pixel(80);

    But I am not using a GridView?  I am taking the data from the DataTable and binding it to the text box.

    Sunday, May 6, 2018 3:04 PM
  • User-369506445 posted

    your mean was , you want to bind your dataTable to asp:textbox control?

    Sunday, May 6, 2018 4:16 PM
  • User974100899 posted

    I know how to bind my DataTable to the text boxes, I just do not know how to 'split' on every ; like you provided an example of.

    Sunday, May 6, 2018 6:36 PM
  • User-369506445 posted

    If you want bind each colum of datatable to a text box, 

    You need to set the textbox to be multiline, this can be done two ways:

    In the control:

    <asp:TextBox runat="server" ID="MyBox" TextMode="MultiLine" Rows="10" />

    Code Behind:

    MyBox.TextMode = TextBoxMode.MultiLine;
    MyBox.Rows = 10;

    This will render as a <textarea>

    And

    string text = "Indocin;Methadone;Dilauted;Narcan;";
                text = text.Replace(";",  System.Environment.NewLine);
                
                DataTable table = new DataTable();
                table.Columns.Add("Dosage", typeof(int));
                table.Columns.Add("Drug", typeof(string));
                table.Columns.Add("Patient", typeof(string));
                table.Columns.Add("Date", typeof(DateTime));
                table.Rows.Add(25, text, "David", DateTime.Now);


    Sunday, May 6, 2018 6:45 PM
  • User974100899 posted

    :( - I understand how to do such when the data is provided, however how can I do something like ---- note this is pseudocode

    DataTable table = new DataTable();

    //Add data to DataTable

    //This is where I stay confused

    string text = data.Column["Drug"];

    text = text.Replace(";", System.Environment.NewLine);

    In doing such ALL semi-colons in the column Drug is/are replaced with Environment.NewLine.

    Sunday, May 6, 2018 6:48 PM
  • User-369506445 posted

    Environment.NewLine. A newline on Windows equals \r\n. To add newlines in a C# string, there are a couple options. There is a constant from the base class library and the actual string literal. 

    Example. Here we need to create a string with a line break in the middle of it, which will form a two-line string. We will use the Environment.NewLine constant for this, which is defined by the .NET Framework and could vary by platform.

    Sunday, May 6, 2018 6:59 PM
  • User974100899 posted

    :( I am still lost.  I am querying an API and I populate my DataTable column by this syntax newRow["Drugs"] = listItem["Drugs_Admin_X200_X"]; which would provide the results in a semi-colon list like how I populated my DataTable in my OP.  I am in need of a way to alter ONE column in my DataTable to add a NewLine everytime a semi-colon is encountered.

    Sunday, May 6, 2018 7:06 PM
  • User-369506445 posted

    What's the result of listItem["Drugs_Admin_X200_X"]; ?

    Can you put here a instance of it

    Sunday, May 6, 2018 7:09 PM
  • User974100899 posted

    What's the result of listItem["Drugs_Admin_X200_X"]; ?

    Can you put here a instance of it

    The returned data is like such - a semi-colon seperated list of anywhere between 0 and 20 names.

    "Indocin;Methadone;Dilauted;Narcan;"
    Sunday, May 6, 2018 7:11 PM
  • User-369506445 posted

    Well, then your data are split with simi-colon

    If you want show each word into new line . You have to use \n or Environment.NewLine

    listItem["Drugs_Admin_X200_X"].Replace(";",  System.Environment.NewLine);
    Sunday, May 6, 2018 7:17 PM
  • User974100899 posted

    Where would I place your syntax?  Intellisense does not allow me to add .Replace in this line

    newRow[""] = listItem["Drugs_Admin_X200_X].

    Only options I have are Equals, GetHashCode, GetType, GetString

    Sunday, May 6, 2018 7:22 PM
  • User-369506445 posted
    listItem["Drugs_Admin_X200_X"].ToString().Replace(";",  System.Environment.NewLine);

    Sunday, May 6, 2018 7:26 PM
  • User974100899 posted

    this provides no debug or compile error, however when my form loads it loads empty.  As if the binding never took place?

     newRow["Drugs"] = listItem["Drugs_Admin_X200_X"].ToString().Replace(";",  System.Environment.NewLine);

    Sunday, May 6, 2018 7:44 PM
  • User-1716253493 posted

    Have you try my code?

    Split the string into an array, loop the array to insert tablerow

    If you want single record only, simply replace semicolon with <br>

    Sunday, May 6, 2018 10:11 PM
  • User303363814 posted

    A DataTable holds data.  Data does not have a format.  There are various controls which you can display on a web page.  Controls are used to format data.

    Please show the code for your control, that is where you should do formatting.

    A Text Box is designed to show its contents in a single line (although multiple lines are possible)

    A List Box is built from the ground up to show its contents on multiple lines.

    If you choose the right control you want have to fight it to get the format you want.

    Why do you chose a Text Box to get a "stacked" look?  That is not the prime purpose of a Text Box.  The prime purpose of a LIst Box is to show "stacked" data.

    Monday, May 7, 2018 1:03 AM
  • User-369506445 posted

    hi

    it's may the listItem["Drugs_Admin_X200_X"] is empty.

    for test you can try below

                newRow["Drugs"] = "Indocin;Methadone;Dilauted;Narcan;".ToString().Replace(";", System.Environment.NewLine);
    

    also i try it below like and you can put your newRow["Drugs"] or  listItem["Drugs_Admin_X200_X"]  istead of string text

    protected void Page_Load(object sender, EventArgs e)
            {
                string text = "Indocin;Methadone;Dilauted;Narcan;";
                text = text.Replace(";",  System.Environment.NewLine);
    
                MyBox.Text = text;
    
             } 

    and in Html

     <asp:TextBox runat="server" ID="MyBox" TextMode="MultiLine" Rows="5" />

    and my result was below like :

    Monday, May 7, 2018 4:39 AM