none
Writing StringColleciton to an MS Access Long Text field RRS feed

  • Question

  • Hi,

    I'm in the process of converting an old Delphi program to C#.  The data is stored in an MS Access DB.  The program has an in-memory object that encapsulates the data and much of the program functionality.  Among the fields in the object are a number of fields that are implemented as TStringList in Delphi.  The Delphi code uses the TStringList.Text property to write the data to an MS Access LongText field.  On the windows forms, the data is displayed (in Delphi) in TMemo fields.  In C#, it is displayed in multi-line text boxes. When I attempt to insert a line in the Access DB from C#, I get an error that seems to be objecting to the embedded NewLine characters in the data.

    How should I go about this?  Is a StringCollection not the correct object to use to replace a Delphi TStringList.  I do need to preserve the line breaks in the data when I write to the DB.

    Thanks,

    Ray

    Friday, February 15, 2019 9:48 PM

Answers

  • Hello,

    Here is a quickly mocked up code sample where I pass in a List<string> where each item has a new line.

    Note the connection and exception handling is in the following NuGet package.

    using System;
    using System.Collections.Generic;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp4
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var nameList = new List<string>()
                {
                    $"Karen{Environment.NewLine}",
                    $"Mary{Environment.NewLine}",
                    $"Tom{Environment.NewLine}",
                    $"Jane{Environment.NewLine}"
                };
    
                var ops = new DataOperations();
    
                ops.InsertRecord(nameList);
                if (ops.IsSuccessFul)
                {
                    MessageBox.Show("See console");
                }
                else
                {
                    MessageBox.Show(ops.LastExceptionMessage);
                }
            }
        }
    }
    


    Data class where the list is joined with no delimited as a string array.

    using System;
    using System.Collections.Generic;
    using System.Data.OleDb;
    using System.IO;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace WindowsFormsApp4
    {
        public class DataOperations : AccessConnection
        {
            public DataOperations()
            {
                DefaultCatalog = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb");
            }
    
            public void InsertRecord(List<string> pNameList)
            {
    
                using (var cn = new OleDbConnection(ConnectionString))
                {
                    using (var cmd = new OleDbCommand {Connection = cn})
                    {
                        cmd.CommandText = "INSERT INTO Table1 (Comments) VALUES (@Comments)";
    
                        cmd.Parameters.AddWithValue("@Comments", string.Join("", pNameList.ToArray()));
    
                        try
                        {
                            cn.Open();
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "SELECT TOP 1 id, Comments FROM Table1;";
                            var reader = cmd.ExecuteReader();
                            if (reader != null && reader.HasRows)
                            {
                                reader.Read();
    
                                Console.WriteLine($"Id is {reader.GetInt32(0)}");
                                Console.WriteLine("Comments below");
                                Console.WriteLine($"{reader.GetString(1)}");
                            }
    
    
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e);
                        }
                    }
                }
            }
    
    
        }
    }
    

    Output

    Inserted data

    Schema


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Ray Porter Sunday, February 17, 2019 5:41 PM
    Friday, February 15, 2019 10:15 PM
    Moderator

All replies

  • Hello,

    Here is a quickly mocked up code sample where I pass in a List<string> where each item has a new line.

    Note the connection and exception handling is in the following NuGet package.

    using System;
    using System.Collections.Generic;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp4
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }
    
            private void button1_Click(object sender, EventArgs e)
            {
                var nameList = new List<string>()
                {
                    $"Karen{Environment.NewLine}",
                    $"Mary{Environment.NewLine}",
                    $"Tom{Environment.NewLine}",
                    $"Jane{Environment.NewLine}"
                };
    
                var ops = new DataOperations();
    
                ops.InsertRecord(nameList);
                if (ops.IsSuccessFul)
                {
                    MessageBox.Show("See console");
                }
                else
                {
                    MessageBox.Show(ops.LastExceptionMessage);
                }
            }
        }
    }
    


    Data class where the list is joined with no delimited as a string array.

    using System;
    using System.Collections.Generic;
    using System.Data.OleDb;
    using System.IO;
    using BaseConnectionLibrary.ConnectionClasses;
    
    namespace WindowsFormsApp4
    {
        public class DataOperations : AccessConnection
        {
            public DataOperations()
            {
                DefaultCatalog = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb");
            }
    
            public void InsertRecord(List<string> pNameList)
            {
    
                using (var cn = new OleDbConnection(ConnectionString))
                {
                    using (var cmd = new OleDbCommand {Connection = cn})
                    {
                        cmd.CommandText = "INSERT INTO Table1 (Comments) VALUES (@Comments)";
    
                        cmd.Parameters.AddWithValue("@Comments", string.Join("", pNameList.ToArray()));
    
                        try
                        {
                            cn.Open();
                            cmd.ExecuteNonQuery();
    
                            cmd.CommandText = "SELECT TOP 1 id, Comments FROM Table1;";
                            var reader = cmd.ExecuteReader();
                            if (reader != null && reader.HasRows)
                            {
                                reader.Read();
    
                                Console.WriteLine($"Id is {reader.GetInt32(0)}");
                                Console.WriteLine("Comments below");
                                Console.WriteLine($"{reader.GetString(1)}");
                            }
    
    
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine(e);
                        }
                    }
                }
            }
    
    
        }
    }
    

    Output

    Inserted data

    Schema


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by Ray Porter Sunday, February 17, 2019 5:41 PM
    Friday, February 15, 2019 10:15 PM
    Moderator
  • Thanks, Karen.  The problem turned out to not be with my StringCollection fields at all.  It was a column in the database named "Level".  For some reason the OleDBCommand was throwing an SQL syntax error due to the name of that column.  I assumed it was the collection field because I could see embedded new line characters in the exception data.  I changed that column name in the DB and the problem went away.

    Ray

    Sunday, February 17, 2019 5:40 PM