Question insert into - working with access and not with sql server

[hazee]

Access:

Dim cn As New OleDbConnection
cn.ConnectionString = "Provider=Microsoft.jet.oledb.4.0; data source=c:\db1.mdb;"
cn.Open()
Dim cmd As New OleDbCommand("insert into tblEmp values (2,'Mike','Accountant',#10/10/1988#)", cn)
Dim i = cmd.ExecuteNonQuery
MsgBox("Record Inserted")

Above works well and inserts a record. Primary key is getting violated properly if I try to insert the same record again.

But below doesnt insert any record. Code is executed properly and a message box is also shown. If I dont stop the project from running state and insert the same record again I get primary key violation notice but if I stop the program and re-execute it inserts the record again.

I am using SQL server express - It seems it has some thing to do with the same.

SQL Server:

Dim cn1 As New SqlConnection
cn1.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database1.mdf;Integrated Security=True;User Instance=True"
cn1.Open()
Dim cmd1 As New SqlCommand("insert into tblEmp values (4,'Mike','Accountant','10/10/1988')", cn1)
Dim j = cmd1.ExecuteNonQuery
MsgBox("Record Inserted")

 

[cjard]

Read the DNU link in my signature, possibly a side effect of this.. Incidentally, there's an easier way to do your data access, described in the suite of Microsoft tutorials linked under DW4 in my signature. Start with Creating a Simple Data Application

 

[hazee]

Thanks cjard.

But my suorce is working perfetly in MS Access 2003/2007. Problem comes only with SQL server express edition.

 

[InertiaM]

I doubt this is specifically a SQL Server Express problem. My guess is that the problem is here :-

but if I stop the program and re-execute it inserts the record again.

Are you only seeing the problem when you are debugging your application? If so, see if it the database is being overwritten every time you compile.