Inserting data into a SQL Server express database

Question

I am trying to insert customer details into a database. Following is the code which is throwing an ArgumentException - how can I handle it?

try
{                
    string constring = "DataSource=.\\SQLEXPRESS;"+"Integrated security=true;"+"User Instance=true;"+"AttachDBFileName=|DataDirectory|Database1.sdf;"+"Initial catalog=Database1";

    using(SqlConnection connection = new SqlConnection(constring))
    {
        connection.Open();

        SqlCommand cins = new SqlCommand("INSERT INTO Customer(cid,cname,mobi)" + "VALUES(@cid , @cname , @mobi)", connection);

        cins.Parameters.AddWithValue("@cid", textBox3.Text);
        cins.Parameters.AddWithValue("@cname", ncustname.Text);
        cins.Parameters.AddWithValue("@mobi", ncustmno.Text);
        cins.ExecuteNonQuery();

        MessageBox.Show("new customer added");
        ncustpn.Hide();
        Class1.refreshcustomertable(cdgv);
        cdgv.Show();          
    }
}
catch (Exception e1)
{
    MessageBox.Show(e1.ToString());
}

On which line exactly? What is the exception details? Please be more specific? — Soner Gönül, Mar 17 '15 at 14:01
SqlConnection connection = new SqlConnection(constring); on this line it shows keyword not supported:datasource — Vrij Sharma, Mar 17 '15 at 14:12
why you have this `"AttachDBFileName=|DataDirectory|Database1.sdf;"` if you use an SQL Server? — adricadar, Mar 17 '15 at 14:13
actually i copied the code from somewhere and replaced the name with my dbname..i dont know exactly so what should i use than? — Vrij Sharma, Mar 17 '15 at 14:16

score 1 · Answer 1 · answered Mar 17 '15 at 14:50

1

The problem is here.

string constring = "DataSource=.\\SQLEXPRESS;"+"Integrated security=true;"+"User Instance=true;"+"AttachDBFileName=|DataDirectory|Database1.sdf;"+"Initial catalog=Database1";

Its not DataSource, Allow a space in between like Data Source

string constring = "Data Source=.\\SQLEXPRESS;"+"Integrated security=true;"+"User Instance=true;"+"AttachDBFileName=|DataDirectory|Database1.sdf;"+"Initial catalog=Database1";

answered Mar 17 '15 at 14:50

Abdul Saleem

10,098
5
45
45

Try changing User Instance to true and false – Abdul Saleem Mar 17 '15 at 15:43
Make sure the database file exists on the path from where you run. If you're debugging, then on your bin/debug folder. And also make sure it contains username, password or not. If yes, then you need to supply username and password, and set Integrated Security = false; – Abdul Saleem Mar 17 '15 at 15:47
As @adricadar said, If you're using database from sql server instance, have created a database named database1 within sql server management studio, then the connection string you need is like below – Abdul Saleem Mar 17 '15 at 15:58
string cnStr = "Data Source=.\\SQLEXPRESS;Integrated security=true;Initial catalog=Database1"; – Abdul Saleem Mar 17 '15 at 15:59

score 0 · Answer 2 · edited May 23 '17 at 11:52

0

From @Sayka response you have to use Data Source, also you have to remove "AttachDBFileName=|DataDirectory|Database1.sdf;" from connection string. Why?

You are using a SQL Server, that means you don't care were is database located on disk, it's SQL Server job. You connect to SQL Server through some parameters (Data Source, Initial Catalog, etc)

string constring = "Data Source=.\\SQLEXPRESS;"+"Integrated security=true;"+"User Instance=true;"+"Initial catalog=Database1";

Note: Please make sure SQL Server service is running (follow step 1 from answer).

When you check if SQL Server is running, you will see something like this SQL Server (SQLEXPRESS) in parentheses you don't have SQLEXPRESS, than you should replace SQLEXPRESS from Data Source=.\\SQLEXPRESS with what you find in parentheses. (example: if you find SQL Server (SQLEXPRESS2012) results Data Source=.\\SQLEXPRESS2012)

edited May 23 '17 at 11:52

Community

1
1

answered Mar 17 '15 at 15:25

adricadar

9,971
5
33
46

Not only you can take data from any of the SQL instances installed on your machine, but can also take it from local sql databases (.sdf, .mdf files) by attaching from a disk path like the above ConnectionString – Abdul Saleem Mar 17 '15 at 15:41
@Sayka you are right, but in his case he copied the connection string and replaced with his database name. And i inhereted that he have the `Database1` already attached in SQL Server. And the path that he provided may be wrong. – adricadar Mar 17 '15 at 15:54
I am getting an error which is cannot open "database1" requested by login.The login failed..how can I solve this? – Vrij Sharma Mar 18 '15 at 05:51
@VrijSharma try this solution for getting a connection string http://stackoverflow.com/questions/5626335/login-failed-for-user-trying-to-access-a-sql-server-express-database – adricadar Mar 18 '15 at 06:32

Inserting data into a SQL Server express database

2 Answers2