none
c# OleDb Excel Create table syntax error in field definition RRS feed

  • Question

  • I am trying to create an Excel table using Oledb but I get the following error.  "syntax error in field definition" The code that I have setup is below.

     private void btnCreateExcel_Click(object sender, EventArgs e)
            {

                try
                {

                     string connstring = "provider=Microsoft.Jet.OLEDB.4.0;Data Source='c:\\DeveloperInfo\\C#Information\\Test1.xls';Extended Properties=Excel 8.0;";

                    OleDbConnection oleDbConnection = new OleDbConnection(connstring);
                   
                    string commandTxt = "CREATE TABLE Sheet1(ID Text(5), UserID Text(15),Password Text(15))";

                    OleDbConnection connection = new OleDbConnection(connstring);
                    connection.Open();
                    OleDbCommand command = new OleDbCommand(commandTxt ,connection);
                    command.ExecuteNonQuery();
                   
                    connection.Close();

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }

    Thursday, February 22, 2018 1:11 PM

Answers

  • Here is an example that creates and puts in some data. Please note if you run this twice it will fail because the sheets already exists from the first run.

    using System;
    using System.IO;
    using System.Data.OleDb;
    
    namespace ForumQuestion_cs.Classes
    {
        class ForumQuestion
        {
            public void DemoOleDbCreateXls_WithHeaders()
            {
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xls");
    
    
                using (var cn = new OleDbConnection { ConnectionString = fileName.ExcelHeaderConnectionString() })
                {
                    using (var cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = "CREATE TABLE Members(FirstName CHAR(255),LastName CHAR(255),JoinedYear INT)";
    
                        cn.Open();
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Members (FirstName,LastName,JoinedYear) VALUES ('Paul','Gallagher',2013)";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "CREATE TABLE Officiers(FirstName CHAR(255),LastName CHAR(255),Rank CHAR(255))";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Officiers (FirstName,LastName,Rank) VALUES ('Joan','Gallagher','AAA')";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "CREATE TABLE Test1(Description CHAR(255),Amount Double)";
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Test1 (Description,Amount) VALUES ('Dues',12.99)";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        internal static class ConnectionStringExtensions
        {
            public static string ExcelHeaderConnectionString(this string pFileName)
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
                {
                    DataSource = pFileName
                };
    
                // ReSharper disable once PossibleNullReferenceException
                if (Path.GetExtension(pFileName).ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                }
                else
                {
                    throw new Exception("Will not work :-(");
                }
                builder.Add("Extended Properties", "Excel 8.0;HDR=YES");
    
                return builder.ConnectionString;
            }
            public static string ExcelNoHeaderConnectionString(this string pFileName)
            {
                var builder = new OleDbConnectionStringBuilder
                {
                    DataSource = pFileName
                };
                // ReSharper disable once PossibleNullReferenceException
                if (Path.GetExtension(pFileName).ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                }
                else
                {
                    //Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                    throw new Exception("Will not work :-(");
                }
                builder.Add("Extended Properties", "Excel 8.0;HDR=NO");
    
                return builder.ConnectionString;
            }
        }
    }
    


    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 x5789vbn Sunday, February 25, 2018 5:37 AM
    Thursday, February 22, 2018 1:56 PM
    Moderator
  • The keyword Password is a reserved word in this context. Choose a different name for the column:

     string commandTxt = "CREATE TABLE Sheet1 ([ID] Text(5), [UserID] Text(15), [Pswd] Text(15))";

    Also, I would recommend enclosing the column names within brackets.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by x5789vbn Sunday, February 25, 2018 5:38 AM
    Thursday, February 22, 2018 2:58 PM

All replies

  • Here is an example that creates and puts in some data. Please note if you run this twice it will fail because the sheets already exists from the first run.

    using System;
    using System.IO;
    using System.Data.OleDb;
    
    namespace ForumQuestion_cs.Classes
    {
        class ForumQuestion
        {
            public void DemoOleDbCreateXls_WithHeaders()
            {
                var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xls");
    
    
                using (var cn = new OleDbConnection { ConnectionString = fileName.ExcelHeaderConnectionString() })
                {
                    using (var cmd = new OleDbCommand { Connection = cn })
                    {
                        cmd.CommandText = "CREATE TABLE Members(FirstName CHAR(255),LastName CHAR(255),JoinedYear INT)";
    
                        cn.Open();
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Members (FirstName,LastName,JoinedYear) VALUES ('Paul','Gallagher',2013)";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "CREATE TABLE Officiers(FirstName CHAR(255),LastName CHAR(255),Rank CHAR(255))";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Officiers (FirstName,LastName,Rank) VALUES ('Joan','Gallagher','AAA')";
    
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "CREATE TABLE Test1(Description CHAR(255),Amount Double)";
                        cmd.ExecuteNonQuery();
    
                        cmd.CommandText = "INSERT INTO Test1 (Description,Amount) VALUES ('Dues',12.99)";
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        internal static class ConnectionStringExtensions
        {
            public static string ExcelHeaderConnectionString(this string pFileName)
            {
                OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
                {
                    DataSource = pFileName
                };
    
                // ReSharper disable once PossibleNullReferenceException
                if (Path.GetExtension(pFileName).ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                }
                else
                {
                    throw new Exception("Will not work :-(");
                }
                builder.Add("Extended Properties", "Excel 8.0;HDR=YES");
    
                return builder.ConnectionString;
            }
            public static string ExcelNoHeaderConnectionString(this string pFileName)
            {
                var builder = new OleDbConnectionStringBuilder
                {
                    DataSource = pFileName
                };
                // ReSharper disable once PossibleNullReferenceException
                if (Path.GetExtension(pFileName).ToUpper() == ".XLS")
                {
                    builder.Provider = "Microsoft.Jet.OLEDB.4.0";
                }
                else
                {
                    //Builder.Provider = "Microsoft.ACE.OLEDB.12.0"
                    throw new Exception("Will not work :-(");
                }
                builder.Add("Extended Properties", "Excel 8.0;HDR=NO");
    
                return builder.ConnectionString;
            }
        }
    }
    


    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 x5789vbn Sunday, February 25, 2018 5:37 AM
    Thursday, February 22, 2018 1:56 PM
    Moderator
  • The keyword Password is a reserved word in this context. Choose a different name for the column:

     string commandTxt = "CREATE TABLE Sheet1 ([ID] Text(5), [UserID] Text(15), [Pswd] Text(15))";

    Also, I would recommend enclosing the column names within brackets.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    • Marked as answer by x5789vbn Sunday, February 25, 2018 5:38 AM
    Thursday, February 22, 2018 2:58 PM
  • Thanks I was able to use some of this to accomplish what I needed.
    Sunday, February 25, 2018 5:38 AM
  • Thanks, I did need to change the password to pswd since like you mentioned password is a reserved word.  
    Sunday, February 25, 2018 5:40 AM