CODEWITHSUNDEEP
sqlsql-serversqlexception
Arash
Arash

Reputation: 3061

SQL exception was unhandled in c#

When I want to debug this code it gives error on objConnection.Open():

sqlExeption was Unhandled and say(A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server))

    SqlConnection objConnection = new SqlConnection(
"server=localhost;database=pubs;" +
"user id=sa;password=");
    SqlDataAdapter objDataAdapter = new SqlDataAdapter();
    DataSet objDataSet = new DataSet();
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        // Set the SelectCommand properties... 
        objDataAdapter.SelectCommand = new SqlCommand();
        objDataAdapter.SelectCommand.Connection =
              objConnection;
        objDataAdapter.SelectCommand.CommandText =
         "SELECT au_lname, au_fname, title, price " +
         "FROM authors " +
        "JOIN titleauthor ON authors.au_id = " +
        "titleauthor.au_id " +
         "JOIN titles ON titleauthor.title_id = " +
        "titles.title_id " +
         "ORDER BY au_lname, au_fname";
        objDataAdapter.SelectCommand.CommandType =
             CommandType.Text;
        // Open the database connection... 
        **objConnection.Open();**
        // Fill the DataSet object with data... 
        objDataAdapter.Fill(objDataSet, "authors");
        // Close the database connection... 
        objConnection.Close();
        // Set the DataGridView properties  
        // to bind it to our data... 
        grdAuthorTitles.AutoGenerateColumns = true;
        grdAuthorTitles.DataSource = objDataSet;
        grdAuthorTitles.DataMember = "authors";
        // Clean up 
        objDataAdapter = null;
        objConnection = null; 
    }

Upvotes: 0

Views: 920

Answers (2)

marc_s
marc_s

Reputation: 755227

First of all, with the SqlDataAdapter, you don't need to specifically open and close the SqlConnection yourself - the adapter will do that for you.

Secondly, I'd strongly recommend putting all your ADO.NET code into using(.....) { .... } blocks as a best practise.

Furthermore, on your local PC, typically you don't need to specify a specific user for your database, but you can use the built-in Windows authentication directly (integrated security=SSPI) in your SQL connection string.

And as a last thing: if you don't need multiple tables in your DataSet, it's easier and better to use DataTable instead - less overhead, less performance penalties. No need to specify table names as data members and so forth. Just plain easier.

Try this code:

public Form1()
{
    InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
    using(SqlConnection objConnection = new SqlConnection("server=(local);database=pubs;integrated security=SSPI;"))
    {
        SqlDataAdapter objDataAdapter = new SqlDataAdapter();

        // Set the SelectCommand properties... 
        objDataAdapter.SelectCommand = new SqlCommand();
        objDataAdapter.SelectCommand.Connection = objConnection;
        objDataAdapter.SelectCommand.CommandText =
            "SELECT au_lname, au_fname, title, price FROM authors " +
            "JOIN titleauthor ON authors.au_id = titleauthor.au_id " +
            "JOIN titles ON titleauthor.title_id = titles.title_id " +
            "ORDER BY au_lname, au_fname";

        DataTable tblData = new DataTable();

        // Fill the DataSet object with data... 
        objDataAdapter.Fill(tblData);

        // Set the DataGridView properties  
        // to bind it to our data... 
        grdAuthorTitles.AutoGenerateColumns = true;
        grdAuthorTitles.DataSource = tblData;
   }
}

That way, disposal of the referenced classes will be handled automatically for you.

Upvotes: 1

Kevin LaBranche
Kevin LaBranche

Reputation: 21088

This error typically occurs when your server name is wrong or the sql server is not on.

localhost and (local) are treated differently for sql server. You might want to try (local).

Here's a list of connection strings to help you out.

If you are using sql express then you might want to change it from localhost to .\sqlexpress.

To check that your SQL Server is on be sure the services for it are on. You can do this in services and also in the Configuration Manager.

Upvotes: 3

Related Questions