C# Sql Connection String

Question

I'm a 17 year old software engineering student and am having trouble with linking my sql database to my C# Win App. I was able to accomplish this task using a access database but the database needs to be in SQL. Any Help would be greatly appreciated! The code i have so far is :

Form1.cs
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.OleDb; // all Non-SqlServer Databases ie oracle, access, sqlLite
using System.Configuration;

namespace SqlWinApp
{
    public partial class Form1 : Form
    {
        // Declare and init data objects
        // Connect to an external data source
        //OleDbConnection cnDataCon =
        //    new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=H:/SEWD/ASP/dataTestJr/App_Data/dbWaxStax.accdb");
        SqlConnection cnDataCon =
           new SqlConnection(ConfigurationManager.ConnectionStrings["cnExternalData"].ConnectionString);
        // dataset: Container object for data tables  
        DataSet dsData = new DataSet();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                cnDataCon.Open();
                {
                    loadDdlTitles();
                }
            }
            catch (Exception errDesc)
            {
                string strMsgError = "Error encountered on open: " + errDesc.Message.ToString().Replace('\'', ' ');
                MessageBox.Show(@"<script language='javascript'>alert('" + strMsgError + "')</script>");
                MessageBox.Show(@"<script language='javascript'>alert('Application will terminate')</script>");
                return;
            }
        }
        private void loadDdlTitles()
        {
            //Response.Write(@"<script language='javascript'>alert('loadDDlTitles')</script>");
            // store sql into a string in order to be utilized at a later time.
            string strSqlTitles = "SELECT * FROM tblTitles ORDER BY title";
            // data adapters act as data filters
            OleDbDataAdapter daTitles = new OleDbDataAdapter();
            // command syncs the data source with the filter (data sdapter) and readies it for instantiation
            OleDbCommand cmNameTitles = new OleDbCommand(strSqlTitles, cnDataCon);
            // select command syncs the filter with the data 
            daTitles.SelectCommand = cmNameTitles;
            try
            {
                daTitles.Fill(dsData, "tblTitlesInternal"); // blow pt.
            }
            catch (Exception errDesc)
            {
                string strMsgError = "Error encountered in data adapter object: " + errDesc.Message.ToString().Replace('\'', ' ');
                MessageBox.Show(@"<script language='javascript'>alert('" + strMsgError + "')</script>");
                MessageBox.Show(@"<script language='javascript'>alert('Application will terminate')</script>");
            }
            // Connect control obj to datasource and populate 
            ddlTitle.DataSource = dsData.Tables["tblTitlesInternal"];
            ddlTitle.DisplayMember = "nameTitle";
            ddlTitle.ValueMember = "nameTitlesID";
        }

    } 

}

In my App.config i have:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="cnExternalData" connectionString="Data Source=|DataDirectory|215-6576.All-Purpose Handyman.dbo; Provider=Microsoft.ACE.OLEDB.12.0" />
    <add name="SqlWinApp.Properties.Settings.ConnectionString" connectionString="Data Source=215-6576;User ID=sa"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
    <startup> 
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
    </startup>
</configuration>

Finally, My database is named 215-6576.All-PurposeHandyman.dbo and the table i am using is named tblTitles. Any help again would be greatly appreciated! Thank you!

Answer 1

An invaluable website I've gone to repeatedly is ConnectionStrings.com.

Assuming everything you already have is correct, you just need to modify your SQL connection string in the config file:

<add name="SqlWinApp.Properties.Settings.ConnectionString" connectionString="Server=215-6576;User ID=sa; Database=All-PurposeHandyman; Password=password"
      providerName="System.Data.SqlClient" />

If your sa account has a password, you will need to provide that as well via "Password=[Password]" in that same connectionString attribute.

EDIT

In your C# code, you don't need the braces around your call to loadDdlTitles();, you can safely remove those.

EDIT2

Added password attribute into modified connection string to make clear how it should work.

Answer 2

Well, I see 3 problems just off the bat (and there might be more if I looked more closely). The two that are causing you trouble are:

1. You're still calling your Access connection string
2. Your SQL connection string is formatted incorrectly.

The third problem isn't major, but it'll be annoying when you go to fix #1: your connection string name is really long.

Modify your sql connection string thusly:

<add name = "SqlConnection" connectionString="[yourConnectionStringHere]" />

Then modify your calling code:

SqlConnection cnDataCon = 
        new SqlConnection(ConfigurationManager.ConnectionStrings["SqlConnection"].ConnectionString);

For the specific connection string, I recommend heading to http://connectionstrings.com and taking a look. But it will be something like this:

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

Answer 3

You need to have the server/machine name in the connection string. This link has some information and examples to use:

http://msdn.microsoft.com/en-us/library/jj653752%28v=vs.110%29.aspx