Cannot connect to database by custom code using the same connection string the sqldatasource uses

Question

I can't find the mistake in my code below.When I use SQLDataSource to connect my database,there's no error.However,if I try to write the custom code to connect the database using the same connection string SQLDataSource uses,I encounter this error:

(provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I suppose there's no mistake in my code,but in the configuration settings of SQL 2008 Express.

Thanks in advance...

    SqlConnection sqlcon = new SqlConnection();
    sqlcon.ConnectionString = "Data Source=ERHANEMREEROGLU/SQLEXPRESS;Initial Catalog=KET;Integrated Security=True";
    sqlcon.Open();

    SqlCommand sqlcmd = new SqlCommand();
    sqlcmd.Connection=sqlcon;
    sqlcmd.CommandText = "SELECT * FROM Login";
    sqlcmd.CommandType = CommandType.Text;

    sqlcmd.ExecuteNonQuery();

    sqlcon.Close();

refer to this post. http://stackoverflow.com/questions/11350983/unable-to-connect-to-sql-server-2008-in-net — Vaibhav Gautam, Jul 06 '12 at 09:25

score 1 · Accepted Answer · answered Jul 06 '12 at 09:25

1

Shouldn't this read:

sqlcon.ConnectionString = "Data Source=ERHANEMREEROGLU\\SQLEXPRESS;Initial Catalog=KET;Integrated Security=True";

answered Jul 06 '12 at 09:25

Kell

3,252
20
19

1

I'm gonna post the answer after 6 hours,because I'm new in the Stack Overflow Website,thanks again... – Erhan Emre Eroğlu Jul 06 '12 at 11:17

Habib · Answer 2 · 2012-07-06T11:37:03.600

0

You are executing ExecuteNonQuery(); which is for Insert/update/delete statements. You may fill a DataReader using ExecuteReader or a dataset/datatable using SQLDataAdapter.

SqlDataReader reader = sqlcmd.ExecuteReader();
        while (reader.Read())
        {
            Console.WriteLine(String.Format("{0}", reader[0]));
        }

You can try:

using (SqlConnection sqlcon  = new SqlConnection(
        "Data Source=ERHANEMREEROGLU\\SQLEXPRESS;Initial Catalog=KET;Integrated Security=True"))
        {
        sqlcon.Open();
        using (SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM Login", sqlcon ))
        {
            DataTable t = new DataTable();
            a.Fill(t);
        }
        }

edited Jul 06 '12 at 11:37

answered Jul 06 '12 at 09:25

Habib

219,104
29
407
436

there is no method as sqlcmd.ExecuteyQuery(); – Asif Mushtaq Jul 06 '12 at 09:30
I just posted where the error occurs.I have the same codes you wrote below in my project,but the problem is in establishing the connection.Debug cannot reach those lines with data adapter and gridview etc... – Erhan Emre Eroğlu Jul 06 '12 at 09:38
@ErhanEmreEroğlu, not too sure , but try backslash instead of forward in your connection string datasource, like "Data Source=ERHANEMREEROGLU\SQLEXPRESS;..... – Habib Jul 06 '12 at 09:58
yes that is the problem indeed,but .net denies "\" because you know "\s" has a different meaning.@Kell gave the solution.It is using double backslash. – Erhan Emre Eroğlu Jul 06 '12 at 11:35
@ErhanEmreEroğlu, just use @ in the start of the string or double backslash – Habib Jul 06 '12 at 11:36

score 0 · Answer 3 · answered Jul 06 '12 at 09:25

There problem is in the statement in sqlcmd.ExecuteNonQuery();

SqlDataAdapter adapter = new SqlDataAdapter("SELECT * FROM Login", sqlcmd))            
DataTable t = new DataTable();
adapter.Fill(t);

Actualy you are executing a query and you need either SqlDataReader or SqlDataAdapter here.

score 0 · Answer 4 · 2012-07-06T09:35:44.170

0

You should change your line from:

sqlcmd.ExecuteNonQuery();

to:

  SqlDataReader dr = sqlcmd.ExecuteReader();

edited Jul 06 '12 at 09:35

answered Jul 06 '12 at 09:28

Why downvote? there is no method as ExceuteQuery() in SqlCommand. sqlcmd.ExecuteQuery() will not compile. – Asif Mushtaq Jul 06 '12 at 09:34

score 0 · Answer 5 · answered Jul 06 '12 at 09:29

0

Try to check if your SQL server allows remote connections, you can set that in the sql server configuration manager

and then enable the remote TCP connection

answered Jul 06 '12 at 09:29

JohnnBlade

4,261
1
21
22

ok,I try that...But can you guess how SQLDataSource can connect to database even I have problems with the configurations – Erhan Emre Eroğlu Jul 06 '12 at 09:46

Eternal-student · Answer 6 · 2012-07-06T09:46:31.500

0

If you are using Windows Authentication on your SQL database you may need to change the Integrated Security to SSPI? Although I'm not overly sure. - edit reading some information about this and SSPI is equivalent to True, so see suggestions below instead.

sqlcon.ConnectionString = "Data Source=ERHANEMREEROGLU/SQLEXPRESS;Initial Catalog=KET;Integrated Security=SSPI";

But as John Blade said you may also need to check in the Configuration Manager that it accepts remote connections.

Also make sure you've added the Windows user to the database. You can do this by using the SQL Mangement Studio Tools.

edited Jul 06 '12 at 09:46

answered Jul 06 '12 at 09:34

Eternal-student

1
1

ok,I try that...But can you guess how SQLDataSource can connect to database even I have problems with the configurations – Erhan Emre Eroğlu Jul 06 '12 at 09:46
I'm not sure why you would be able to with the sqldatasource but not with your code. Erm, you could check the firewall settings too. This site has a good set of screen prints and instructions about checking the connections to the database. http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/ – Eternal-student Jul 06 '12 at 10:10
I don't know if this is the solution either but could you try putting your connection string in the the SqlConnection variable? sqlcon.ConnectionString = "Data Source=ERHANEMREEROGLU/SQLEXPRESS;Initial Catalog=KET;Integrated Security=True"; SqlConnection sqlcon = new SqlConnection(sqlcon.ConnectionString); – Eternal-student Jul 06 '12 at 10:26

Cannot connect to database by custom code using the same connection string the sqldatasource uses

6 Answers6