ConnectionString Database c#

Question

I am create a database utility and I seem to not be able to get my connectionstring correct.

   SqlConnection conn = new SqlConnection();
   conn.ConnectionString =  
        "Data Source=.\\SQLExpress;" +
        "User Instance=true;" +
        "Integrated Security=true;" +
        "AttachDbFilename=|DataDirectory|ConfigurationData.mdf;";

I believe this is in the correct format. As for the data source, my sql server is SQLExpress which runs sql server 2008 R2. My database is named ConfigurationData. Am I missing something?

When I run it, it opens the database - I assume it does since it does not through exception - but when I try inserting into a table, it does not actually insert it yet it executes the command.

conn.Open();
        try
        {

          SqlCommand comm = new SqlCommand("INSERT INTO Test " + "(id,number) " + "  VALUES(" + 10 + " , " + 12 + ")", conn);
          comm.ExecuteNonQuery();
            Console.WriteLine("Database is created successfully", "MyProgram");
        }
        catch (Exception ex)
        {

        }
        finally
        {
            if ((conn.State == ConnectionState.Open))
            {
                conn.Close();
            }
        }

Most likely you're copying the .mdf file every time you build/debug, which overwrites the database the program is using with the copy you have in your solution. Can you verify this? — Lasse V. Karlsen
– Lasse V. Karlsen, Commented Jul 16, 2013 at 18:44
I am new to databases and they way I check is that when I insert into the table, I place a breakpoint and open the table to check to see if it was inserted — haysam
– haysam, Commented Jul 16, 2013 at 18:46
9 out of 10 times this is a case of acting and verifying on two different databases. I would suggest you open SSMS and hand type the same insert statement — Aaron Anodide
– Aaron Anodide, Commented Jul 16, 2013 at 19:19

Community · Accepted Answer · 2017-05-23 12:11:17Z

1

EDIT Just remembered that I had answered a similar question a while back. Check it out:

Why can't I insert data into local database (SQL Compact Edition) with C#?

I don't think it is the connection string issue. But for your reference, a good site to refer to is http://www.connectionstrings.com/sql-server-2008/

You would need one of these:

Attach a database file, located in the data directory, on connect to a local SQL Server Express instance

Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;
Trusted_Connection=Yes;

Attach a database file on connect to a local SQL Server Express instance

Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;
Trusted_Connection=Yes;

Using an User Instance on a local SQL Server Express instance The User Instance functionality creates a new SQL Server instance on the fly during connect. This works only on a local SQL Server instance and only when connecting using windows authentication over local named pipes. The purpose is to be able to create a full rights SQL

Server instance to a user with limited administrative rights on the computer.

Data Source=.\SQLExpress;Integrated Security=true;
AttachDbFilename=C:\MyFolder\MyDataFile.mdf;User Instance=true;

To use the User Instance functionality you need to enable it on the SQL Server. This is done by executing the following command: sp_configure 'user instances enabled', '1'. To disable the functionality execute sp_configure 'user instances enabled', '0'.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Jul 16, 2013 at 18:45

reggie

13.8k13 gold badges43 silver badges57 bronze badges

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

2 Comments

haysam Over a year ago

It throws and error saying that the .\ unrecognized escaped sequence. would it be find if it was .\\?

DanteTheEgregore Over a year ago

Don't use "\\". Format the string like @"C:\...\"

Kurubaran · Accepted Answer · 2013-07-16 19:09:54Z

0

try (local) instead of dot, dot is not recognized in Win XP

conn.ConnectionString =  
        "Data Source=(local)\\SQLExpress;" +
        "User Instance=true;" +
        "Integrated Security=true;" +
        "AttachDbFilename=|DataDirectory|ConfigurationData.mdf;";

edited Jul 16, 2013 at 19:09

answered Jul 16, 2013 at 18:52

Kurubaran

8,9625 gold badges47 silver badges71 bronze badges

Comments

prospector · Accepted Answer · 2013-07-16 19:20:47Z

0

You're SQL Statement isn't right also, and you should use parameters, but here is what you should have

SqlCommand comm = new SqlCommand("INSERT INTO Test (id, number)  VALUES('" + 10 + " ', '" + 12 + "')", conn);

answered Jul 16, 2013 at 19:20

prospector

3,4791 gold badge25 silver badges42 bronze badges

Comments

DanteTheEgregore · Accepted Answer · 2013-07-16 19:26:34Z

0

Why not just use the SqlConnectionStringBuilder class?:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = @"(local)\SQLExpress";
builder.UserInstance = true;
builder.IntegratedSecurity = true;
builder.AttachDBFilename = "|DataDirectory|ConfigurationData.mdf";
SqlConnection conn = new SqlConnection(builder.ConnectionString());

The output:

    "Data Source=(local)\\SQLExpress;AttachDbFilename=|DataDirectory|ConfigurationData.mdf;Integrated Security=True;User Instance=True"

edited Jul 16, 2013 at 19:26

answered Jul 16, 2013 at 19:19

DanteTheEgregore

1,5505 gold badges23 silver badges44 bronze badges

Comments

Guruprasad J Rao · Accepted Answer · 2013-07-19 14:07:48Z

One of the way to do this is to add your connection string in web.config file as shown below:

Jus click on the properties of database on the database explorer. There you will find connectionstring in its properties. Jus add it in connectionstring below.

<configuration>
    <connectionStrings>
        <add name="ConnectionName" connectionString="your connection string" providerName="System.Data.SqlClient"/>
    </connectionStrings>
</configuration>

Then in the page you can store it in string or directly refer to your connection as shown below:

Connection con=new SqlConnection();
con.ConnectionString=ConfigurationManager.ConnectionStrings["connString"].ToString();

and I suspect your insert statement is not properly declared.

Just try this:

SqlCommand comm = new SqlCommand("INSERT INTO Test (id,number) VALUES('10' ,'12')", con);

That's all from my part... Hope it helped you..

Collectives™ on Stack Overflow

ConnectionString Database c#

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related