Connection string not working in C# windows application

Question

For some reasons, I am unable to establish a data connection using my connection string. I was doing with the below code

var connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;
SqlCommand cmd = new SqlCommand();
SqlConnection con = new SqlConnection(connectionString);
cmd.Connection = connectionString;

cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = " dbo.SelectAll";

SqlParameter param = new SqlParameter("@tradeDate","201401");

param.Direction = ParameterDirection.Input;
param.DbType = DbType.String;
cmd.Parameters.Add(param);

But for some reasons when I am initializing the connection property to my command using cmd.Connection = connectioString, is is throwing an exception as follows

Cannot implicitly convert type 'string' to 'System.Data.SqlClient.SqlConnection'

Yeah, watch your types. Connection expects a SqlConnection object, not a string. — Robert Harvey
– Robert Harvey, Commented Apr 22, 2014 at 20:00

Pseudonym · Accepted Answer · 2016-10-17 13:23:57Z

5

You're confusing the connection string needed to connect to the database and the actual SqlConnection.

try this (for your specific code):

cmd.Connection = con;

According to MSDN here is a proper example:

private static void CreateCommand(string queryString, string connectionString)
 {
    using (SqlConnection connection = new SqlConnection(connectionString))
     {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

Link to original article: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.aspx

edited Oct 17, 2016 at 13:23

answered Apr 22, 2014 at 19:59

Pseudonym

2,0722 gold badges18 silver badges38 bronze badges

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

Comments

Soner Gönül · Accepted Answer · 2014-04-22 20:30:32Z

5

I think you need just

cmd.Connection = con;

You are try to do set your SqlCommand.Connection property with your connection string. But this property is for specify your SqlConnection object, not your connection string.

From documentation;

Gets or sets the SqlConnection used by this instance of the SqlCommand.

And since there is no implicit conversation from SqlConnection to string, that's why you get compile time error.

As a side note, use using statement to dispose your SqlConnection and SqlCommand like;

using(SqlConnection con = new SqlConnection(connectionString))
using(SqlCommand cmd = new SqlCommand())
{
   cmd.Connection = con;
   ...
   ...
}

or you can use SqlConnection.CreateCommand() method to create your SqlCommand associated your SqlConnection inside your using statement like;

using(SqlConnection con = new SqlConnection(connectionString))
using(SqlCommand cmd = con.CreateCommand())
{
   ...
   ...
}

edited Apr 22, 2014 at 20:30

answered Apr 22, 2014 at 19:59

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

1 Comment

Pseudonym Over a year ago

Wow, great minds think alike, 4 second difference in almost the exact same response.

Collectives™ on Stack Overflow

Connection string not working in C# windows application

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related