Connecting to SQL in c# 'Connection is invalid' error

Question

I am struggling to get a connection established between my windows form c# program and a sqlexpress database which has no username or password is the authentication type is windows authentication.

I believe the problem is with the string but I don't understand, I can connect in a windows console application but not a windows form application. I have tried various connection strings..

The last line produces the error

System.ArgumentException: 'Connection is invalid'

Any positive help is highly appreciated, have looked everywhere and search SO and cannot find similar question

        {
            // New instance of ExcelEngine created (opening excel with no workbooks open)
            using (ExcelEngine excelEngine = new ExcelEngine())
            {
                // Create excel application object
                IApplication application = excelEngine.Excel;

                //Assigns default application version
                application.DefaultVersion = ExcelVersion.Excel2013;

                // New workbook created with one worksheet
                IWorkbook workbook = application.Workbooks.Create(1);

                //Access a worksheet in workbook
                IWorksheet worksheet = workbook.Worksheets[0];

                if (worksheet.ListObjects.Count == 0)
                {
                    //Estabilishing the connection in the worksheet 
                    string connectionString = "Server =NBE\\SQLEXPRESS; Initial Catalog = BikeStores; Trusted_Connection = True";
                    // "Data Source = NICHOLASBOACHIE\\SQLEXPRESS; Initial Catalog = BikeStores; Integrated Security = SSPI";

                    string query = "SELECT * FROM [BikeStores].[sales].[staffs]";

                    IConnection connection = workbook.Connections.Add("SQLConnection", "Connection with SQL Server", connectionString, query, ExcelCommandType.Sql);

                    //Create Excel table from external connection (intitate worksheet)
                    worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, connection, worksheet.Range["A1"]);


                }

change your query to a simple query like SELECT * FROM TABLE — Marwen Jaffel
– Marwen Jaffel, Commented Jan 15, 2020 at 9:22

Ryan Thomas · Accepted Answer · 2020-01-15 09:59:46Z

1

Looks like your connection string format is invalid. The System.Data.SqlClient library has a SqlConnectionStringBuilder class which you may find useful.

It is documented here

It has been a while since I have used this class but something like this:

SqlConnection myConnection = new SqlConnection();
SqlConnectionStringBuilder myBuilder = new SqlConnectionStringBuilder();
myBuilder.IntegratedSecurity = true;
myBuilder.InitialCatalog = "BikeStores";
myBuilder.DataSource = "NBE\\SQLEXPRESS";
myConnection.ConnectionString = myBuilder.ConnectionString;

answered Jan 15, 2020 at 9:59

Ryan Thomas

2,2002 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Connecting to SQL in c# 'Connection is invalid' error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related