connecting to database - connectionString connect.Open() error

Question

So I am trying to run a query in a database that searches the database table from a textbox input. My code is

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Query
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void employeeBindingNavigatorSaveItem_Click(object sender, EventArgs e)
        {
            this.Validate();
            this.employeeBindingSource.EndEdit();
            this.tableAdapterManager.UpdateAll(this.personnelDataSet);

        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'personnelDataSet.employee' table. You can move, or remove it, as needed.
            this.employeeTableAdapter.Fill(this.personnelDataSet.employee);

        }

        private void btnExit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        string connectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;";

        private void btnSearch_Click(object sender, EventArgs e)
        {
            string commandText = "SELECT employeeID, name, position, hourlyPayRate " +
                     "FROM dbo.employee WHERE name LIKE '%'+ @Name + '%'";

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                //Create a SqlCommand instance
                SqlCommand command = new SqlCommand(commandText, connection);
                //Add the parameter
                command.Parameters.Add("@Name", SqlDbType.VarChar, 20).Value = textBox1.Text;

                //Execute the query
                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();
                }
                catch
                {
                    //Handle excepetion, show message to user...
                    MessageBox.Show("Error bitch!");
                }
                finally
                {
                    connection.Close();
                }
            }
        }
    }
}

When I take the catch out I can see the error occurs at connection.Open(). The error takes a while to happen which makes me wonder whether there is an issue with string connectionString = "Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;";

This is the error that I receive:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

its probably your connection string that is broken. Have you got ssms? Can you verify your connection string? — user2140173
– user2140173, Commented May 15, 2014 at 10:13
It is your connectionstring. You have just taken the regular connectionstring through copy-paste. Actually fill in the serveraddress etc. — Matthijs
– Matthijs, Commented May 15, 2014 at 10:15
Why don't you replace catch with catch(SqlException ex) and then add to your MessageBox.Show("Error bitch!"+ ex.ToString(); This will give you more information on what is going wrong. — Ravendarksky
– Ravendarksky, Commented May 15, 2014 at 10:15
@Ravendarksky Yeah I wasn't planning on actually leaving it at error bitch, but noted. — Matt
– Matt, Commented May 15, 2014 at 10:20

MoonKnight · Accepted Answer · 2014-05-15 11:21:58Z

1

You need to validate the connection string. If Open() is throwing an SqlException then the connection string is invalid. To enable you to establish the exact form of the connection string you require, take a look at connectionstrings.com.

As to why the exception is showing as unhanded, you need to 'consume' the exception as follows:

try
{
    connection.Open();
    command.ExecuteNonQuery();
}
catch (Exception e)
{
    // Handle excepetion, show message to user...
    MessageBox.Show(e.Message);
}

I hope this helps.

edited May 15, 2014 at 11:21

answered May 15, 2014 at 10:18

MoonKnight

23.9k42 gold badges154 silver badges284 bronze badges

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

2 Comments

Matt Over a year ago

Thanks that helped a lot with the exception. I just don't know how I would go about validating the connection string?

MoonKnight Over a year ago

Hi Matt, What are you attempting to connect to? See edit for a link to connectionstrings.com, this should help you obtain the correct form of connection string for your situation.

Meysam Tolouee · Accepted Answer · 2014-05-15 10:16:23Z

0

Check this out:

  string connectionString = "Server=.\InstanceName;Database=myDataBase;Integrated Security=True;";

Also

string commandText = "SELECT employeeID, name, position, hourlyPayRate 
                     FROM dbo.employee WHERE name LIKE '%@Name%'";

answered May 15, 2014 at 10:16

Meysam Tolouee

5893 silver badges17 bronze badges

Comments

apomene · Accepted Answer · 2014-05-15 10:17:32Z

0

try:

 string connectionString = "Data Source=myServerAddress;Initial Catalog=myDataBase;User id=myUser;Password=myPAss;Connect Timeout=15;Integrated Security=false";

answered May 15, 2014 at 10:17

apomene

14.4k10 gold badges51 silver badges76 bronze badges

1 Comment

MoonKnight Over a year ago

This is definitely not going to help resolve the connection string problem.

Aakshay Subramaniam · Accepted Answer · 2014-05-15 10:34:44Z

0

Try declaring your connection string inside the event.

An alternate method is to link your database through web.config as follows:

  <connectionStrings>
    <connectionString="Data Source=(localdb)\v11.0;Initial Catalog=DBName;Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

And then the connection string can be as:

string cs = ConfigurationManager.ConnectionStrings["DBName"].ConnectionString;

edited May 15, 2014 at 10:34

answered May 15, 2014 at 10:22

Aakshay Subramaniam

1302 silver badges12 bronze badges

Collectives™ on Stack Overflow

connecting to database - connectionString connect.Open() error

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related