insert problem in C#, using sqlcommand

Question

when I run this sql:

insert into table1(ID,Name) values ('10','Saeed');

it seems that the record has been inserted, and if I read the table using (select * from table1) it shows me the inserted record, but after closing the program, it disappears.

it's the code:

string constr="Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|" +   
    "\\Database1.mdf;Integrated Security=True;User Instance=True";

SqlConnection con = new SqlConnection(constr);
con.Open();

SqlCommand cmd = new SqlCommand(
    "insert into st (ID,Name) values ('10','saeed');", con);

cmd.ExecuteNonQuery();
cmd.Close();

I inserted some records in it manually, and when I read the database, the manually inserted records exist.

It is not a transaction problem wont be solved with a transaction!

Answer 1

Guess: You are working with SQL Server Express and "Database1.mdf" within your project is configured (in properties window) for "Copy to Output Directory" value "Always"

Greets Flo

Answer 2

Try to specify: "Initial Catalog=InstanceDB;" as well to make sure it does not create a new db name when you restart the application.

Answer 3

error in your code itself its

con.Close();

not

cmd.Close();

there is no close method for SqlCommand

Answer 4

I agree with pascal. Sounds like the transaction isn't being committed.

(Edited to provide clearer code block from my Comment below)

con.Open(); 
trans = con.BeginTransaction(); 

SqlCommand cmd = new SqlCommand( "insert into st (ID,Name) values ('10','saeed');", con);

cmd.ExecuteNonQuery(); 
tran.Commit(); 

Answer 5

The issue sounds like you started a transaction and forgot to commit it. However, if you are using the exact code you posted this is not a transaction problem because you are not using one.

That makes me think there is something funky going on with your connection string.

For kicks and giggles trying changing your connection string to something like this

Server=myServerName\theInstanceName;Database=myDataBase;Trusted_Connection=True;