Click here to Skip to main content
14,697,262 members
Please Sign up or sign in to vote.
5.00/5 (2 votes)
See more:
In c#, I decided to substitute local SQL database for my previous programs used Access or Excel DB services before.
I want to use this database locally and I do not decide on using it in web.
But there are some problems.
I made a program and add a local database through:
Solution Explorer> right click on name of project> Add > New Item
then select "Local Database" and add a database with name: Database1.sdf

Then I added a tabel with two fields.
Also This class was added to my program automaticly: Database1DataSet
In my code I wrote:
        Database1DataSet ds = new Database1DataSet();
        SqlConnection cn;
        SqlDataAdapter da;
        DataRow dr;
        SqlCommandBuilder cb;
        bool newMode;
        const string TableName = "Users";

        public Form1()
        {
            InitializeComponent();
            InitalizeDatabase();
        }

        private void InitalizeDatabase()
        {
            newMode = false;
            string strSql = "SELECT * FROM " + TableName;
            cn = new SqlConnection("Data Source=(local);Initial 
Catalog=Database1;Integrated Security=True;Connection Timeout=10");
            da = new SqlDataAdapter(strSql, cn);
            cn.Open();
            da.Fill(ds, TableName);
            //////////
            textBox_UserName.DataBindings.Add(
                new Binding("Text", ds, TableName + ".UserName"));
            textBox_Password.DataBindings.Add(
                new Binding("Text", ds, TableName + ".Password"));
            dataGrid1.DataBindings.Clear();
            dataGrid1.DataBindings.Add(
                new Binding("DataSource", ds, TableName));
            //////////
            cn.Close();
        }

There was no error in compiling, but a runtime error appears each time I run the program at the line:
cn.Open();

:confused:Exception:
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)

I tried many string connections, and always an exception was appeared with one of these codes: 0, 26, 40
It harassed me. I have not installed SQL but SQL services are avalable in my Windows7 by default. and I actived all of them manually:


Although some of prograsmmers say no SQL installation is needed but I got doughbtful.
Is there anyone help me how to deal with such a problem?
Thx very much.
Posted
Updated 11-Jan-10 5:09am
v8

If you didn't confirm that there's a named instance called SQLExpress, then I don't see why you'd expect this to work. Download and install SQL Server Express Edition, then work from there.

Also, DON'T post comments in the answers area, it just messes things up. It's not possible to see what you posted while we reply. Edit your post or use the area down the bottom, where there's a general forum you can use.
   
I ran into exactly the same problem. Apparently Microsoft changed how .NET works with SQL Express. Now if you want to connect to .sdf file, you need to use SqlCeConnection, SqlCeCommand, CqlCeReader, etc. instead of SqlConnection.

I wish they would provide a meaningful exception, when they introduce changes like this.

Victor,
Software Developer
Free Image Converter

Flip My Photos
   
Try
Data Source =".\Database1.sdf";

This is if your database is directly in debug or release folder
   
Comments
zinduyaar 12-Apr-15 3:28am
   
dino gr8 answer thanks.........though i didn't understand the concept but it works perfectly....thanks again
The error is saying either database name you provided (Database1) does not exist on the sqlserver, or, you cant access it through windows authentication.

open SQLServer Management studio and check if in 'Databases' category, your database (Database1) is attached. if it isnt , attach it.

if it was attached, try this :

cn=new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog=Database1;Persist Security Info=True;User ID=sa;Password=Pass;")

where you must replace 'sa' and 'Pass' with correct username and password which is defined in your database.

and if problem exists after all these, try this :

cn=new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database.mdf;Persist Security Info=True;User ID=sa;Password=Password;Connect Timeout=30;User Instance=False")

-------------------------
Regards

H.Maadani
   
v3
Thanks guys,
It solved my problem:
cn = new SqlConnection("Data Source=.\\SQLEXPRESS;Integrated Security=True");


Best regards,
Arash
   
unfortunately there is another exception with:
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

image:
   
I think you need only

cn = new SqlConnection("Data Source=.\Database1.sdf");

No need for the rest of the connection string
   
OK. you need to correct your connectio as below :

cn = new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog=Database1;Integrated Security=True;");

---------------------

Regards

H.Maadani
   
OK aasser. that explains a lot.

so, you have SQLExpress, because VS installs it during its installation.
thats why we set "Server=.\\SQLEXPRESS". ok?

now, problem is : your database is not attached!

What is Database1? do you have its script as a *.sql file?
do you have its files?

you must create a database in sql server first to be able to connect to it!

P.S. : you created a dataset using Add> New Item...> Local database
huh? well, that is not creating the database on sql server!!

maybe we cn fix it this way :

cn = new SqlConnection("Data Source=Database1.sdf;AttachDbFilename={0}\\Database1.sdf;Integrated Security=True");

replace {0} with the path to sdf file. to get that path, right click on database1, select database properties, and you'll see the path at General:name.
   
v2
Thx mr. maadani,
I corrected the code to:
cn = new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog=Database1;Integrated Security=True;");
da = new SqlDataAdapter(strSql, cn);
cn.Open();

My problem solved to some extend, but there is another exception I dealt with:
:confused:
Cannot open database "Database1" requested by the login. The login failed.
Login failed for user 'Ar-PC\Ar'.


what should I do now?
   
v2
I have no SQL server application except for whatever VS.net installed for me. I do not know what you mean by SQLServer Management.
Therfore, I tried both of these codes(I have no password):
cn = new SqlConnection("Server=.\\SQLEXPRESS;Initial Catalog=Database1;Persist Security Info=True;User ID=;Password=;");
and also:
cn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\Database1.sdf;Persist Security Info=True;User ID=;Password=;Connect Timeout=30;User Instance=False");
but both of them towarded with such an exception:
Login failed for user ''. The user is not associated with a trusted SQL Server connection.

:((
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900