Question ConnectionString password issue

[waheedrafiq]

Hi all

I have try many different sites to see if I can resolved my problem which is that I can't establish a authentication with my database

I am creating a very basic app that uses SQL database , so I install my sql server 2012 and created a basic app in c# with the following fields

userID
First Name
Surname

and a saved button.

keep getting the following error System.Data.SQLClient.sqlException = {"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)"}

this is what my connection strings looks like

connection.ConnectionString = "Data Source=XBIRD/MSSQLSERVER;Initial Catalog=testingSQL.mdf;" +
                //"Trusted_Connection=True;"+
                "User id=sqluser;" +
                "Password=password;";

I really don't want to use sqluser account that I created in windows and added the account in sql server manager--> login want I want to use is Windows Authentication


here is rest of my code

any advice would be very much appreciated , I also had a look at the ConnectionString.com site

        private void btnSave_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection();
            //string SqlConnStr = "Data Source=XBIRD;" +
              //  "Initial Catalog=testingSQL.mdf;" +
                //"Authentication=Windows Authentication;";
               // "User id=MSSQLSERVER;" +
                //"Password=password;";


            connection.ConnectionString = "Data Source=XBIRD/MSSQLSERVER;Initial Catalog=testingSQL.mdf;" +
                //"Trusted_Connection=True;"+
                "User id=sqluser;" +
                "Password=password;";
                
            
            connection.Open();
            MessageBox.Show("connection Open");


            SqlCommand cmd = new SqlCommand();
            cmd.Connection = connection;
            cmd.Parameters.AddWithValue("@userID", txtID.Text);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);


            cmd.CommandText = "INSERT INTO testingSQL(userID , Name, Surname) VALUES (@userID , @Name, @Surname)";


            try
            {
               // connection.Open();
                int rowsAffected = cmd.ExecuteNonQuery();
                MessageBox.Show("Data is saved");
            }
            catch (Exception ex)
            {
            }
            finally
            {
                connection.Close();
            }
        }
    }

 

[jmcilhinney]

Is the SQL Server instance installed on the same machine as the application or are they on different machines? Is it a default instance of SQL Server or a named instance? Are you trying to connect to an attached database or are you trying to attach a data file that is part of your application? If you don't know what that last question means, please explain EXACTLY how you created the database.

 

[waheedrafiq]

Thank you so much for your reply back , I am using SQL 2012 and this is install on my machine , am also using VS 2013 , also I am trying to do is demonstrate to myself that I am able to save data into a database that also sits on the same machine.






I have attach additional information which might aid you in helping me , I have also since posting this tread try the following from SQL Server connection strings - ConnectionStrings.com

Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;
Password=myPassword;

this too failed with the same error

 

[jmcilhinney]

It looks as though your instance is a default instance, in which case it has no name. In some places you will see MSSQLSERVER used where the instance name is used but that is just so that there is something to see rather than a blank. The instance actually has no name so you don't use an instance name in your connection string; only a server name. You'll notice that most of the examples at connectionstrings.com are like that.

Note also that, if the instance is on the local machine, you don't actually have to use the machine name. You can instead use a dot or or "(localhost)". The good thing about that is that you can move the application to another machine and the connection string will work without change if you're still connecting to a default instance on the same machine.

Also, the Database or Initial Catalog attributes are used if you are trying to connect to an attached database, which it appears that you are not. If you are trying to connect to a data file that is part of your project then you need to use the AttachDbFilename attribute instead. You'll also find examples of that at connectionstrings.com.

Finally, you might want to follow the first link in my signature below to learn how local data files are managed. I say that because the default behaviour often confuses people because it can appear that data is not being saved when it actually is.

 

[waheedrafiq]

Firstly I would like to thank you for pointing me to the correct direction and provided additional clarification on the subject , I had to removed testingSQL.mdf from my one drive as I think this was causing problems ended up created a folder "database" and place the file within , this method work , I then open Microsoft SQL Server Manager and created a new testingSqlDatabase.mdf from there I then open Server explorer and created a new Data Connections. The following code works and saves data into the databasebase.

both method work and I have you to thank for , my understanding has now increase with the aid of internet and books I should be able to pick up this topic fast

here my working code form those who wish to learn from it

private void btnSave_Click(object sender, EventArgs e)
{
SqlConnection connection = new SqlConnection();



connection.ConnectionString = "Data Source=XBIRD;Initial Catalog=testingSqlDatabase;Integrated Security=True;";


connection.Open();
MessageBox.Show("connection Open");


SqlCommand cmd = new SqlCommand();
cmd.Connection = connection;
cmd.Parameters.AddWithValue("@userID", txtID.Text);
cmd.Parameters.AddWithValue("@FirstName", txtName.Text);
cmd.Parameters.AddWithValue("@Surname", txtSurname.Text);


MessageBox.Show("Insert data takes place next");


cmd.CommandText = "INSERT INTO tblNames(userID , FirstName, Surname) VALUES (@userID , @FirstName, @Surname)";
MessageBox.Show("Insert data has taken place");


int rowsAffected = cmd.ExecuteNonQuery();
MessageBox.Show("Data is saved");
connection.Close();
MessageBox.Show(" connection has now closed");


try
{
// connection.Open();
// int rowsAffected = cmd.ExecuteNonQuery();
// MessageBox.Show("Data is saved");
}
catch (Exception ex)
{


}
finally
{



}




}