Can't connect to SQL Server with C#

Question

private void button1_Click(object sender, EventArgs e)
{ 
    string connetionString = null;    

    SqlConnection cnn;
    connetionString = "My_Connection";

    cnn = new SqlConnection(connetionString);

    try
    {
        cnn.Open();

        SqlCommand cmd = new SqlCommand("INSERT INTO person " +
              "(name, lastname, phone) VALUES('@name', '@lastname', '@phone')");
        cmd.CommandType = CommandType.Text;
        cmd.Connection = cnn;

        cmd.Parameters.AddWithValue("@name", txtName.Text);
        cmd.Parameters.AddWithValue("@lastname", txtLastname.Text);
        cmd.Parameters.AddWithValue("@phone", Convert.ToInt32(txtPhone.Text));

        MessageBox.Show("Successful");

        cnn.Close();
    }
    catch (SqlException ex)
    {
        MessageBox.Show("You failed!" + ex.Message);
    }
}

Answer 1

There is a number of issues with this code. It appears that you're trying to reference a connection string from a configuration file. However, you can't just pass the name to the connection - you must actually retrieve it and assign it to your SqlConnection. Alternatively, you can hardcode a connection string, plenty of examples of those are available at connectionstrings.com.

It is usually not a good idea to put the connection string directly in your application however, as you need to reuse them throughout your application and it's best to have them declared in a central location.

private void button1_Click(object sender, EventArgs e)
{
    // retrieve connection from configuration file
    // requires a reference to System.Configuration assembly in your project
    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["My_Connection"].ConnectionString;

    //alternatively, hardcode connection string (bad idea!)
    //string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

    using(SqlConnection connection = new SqlConnection(connectionString))
    using(SqlCommand command = new SqlCommand("INSERT INTO person (name, lastname, phone) VALUES(@name, @lastname, @phone)", connection))
    {
        // you should avoid AddWithValue here, see http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/
        // thanks marc_s! http://stackoverflow.com/users/13302/marc-s
        command.Parameters.AddWithValue("@name", txtName.Text);
        command.Parameters.AddWithValue("@lastname", txtLastname.Text);
        command.Parameters.AddWithValue("@phone", Convert.ToInt32(txtPhone.Text));
        connection.Open();
        command.ExecuteNonQuery();          
    }

    MessageBox.Show("Successful");
}

The connection string is passed to the connection in its constructor, and the connection is passed to the command via its constructor.

Notice that there is no need to explicitly close the connection, the using statements take care of those for you. When the variable is out of scope, the connection will be closed, even if there is an exception.

An example of a connection string in your configuration file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="My_Connection" providerName="System.Data.SqlClient" connectionString="Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;" />
  </connectionStrings>
</configuration>

Answer 2

You have to write valid connetionString. If you ask what connection string is?

Here is answer

'In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection. Whilst commonly used for a database connection, the data source could also be a spreadsheet or text file.

The connection string may include attributes such as the name of the driver, server and database, as well as security information such as user name and password.'

Something like that

Server=myServerAddress;Database=myDataBase;User Id=myUsername; Password=myPassword; 

They are a number of things to worry about when connecting to SQL Server on another machine.

• Host/IP Address of the machine
• Initial Catalog (database name)
• Valid username/password

Very often SQL server may be running as a default intance which means you can simply specify the hostname/ip address but you may encounter a scenario where it is running as a named instance (Sql Express for instance). In this scenario you'll have to specify hostname\instance name

Answer 3

Your connection string is missing! Define it or get from your config. "My_Connection" doesn't contain server, database, user, password. Use something like:

var connectionString = "Data Source=ServerName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;";

Answer 4

using (SqlConnection connection = new SqlConnection("Data Source=ServerName; Initial Catalog=DatabaseName;User ID=UserName;Password=Password"))
{
using (SqlCommand command = new SqlCommand())
{
    command.Connection = connection;            // <== lacking
    command.CommandType = CommandType.Text;
    command.CommandText = "INSERT INTO person " +
                  "(name,lastname,phone) VALUES('@name', '@lastname', '@phone')";
    cmd.Parameters.AddWithValue("@name", txtName.Text);
    cmd.Parameters.AddWithValue("@lastname", txtLastname.Text);
    cmd.Parameters.AddWithValue("@phone", Convert.ToInt32(txtPhone.Text));

    try
    {
        connection.Open();
        int recordsAffected = command.ExecuteNonQuery();
    }
    catch(SqlException)
    {
        // error here
    }
    finally
    {
        connection.Close();
    }
}
}

Here is the working code.

Sincerely,

Thiyagu Rajendran

**Please mark the replies as answers if they help and unmark if they don't.