SQL INSERT Command from Visual Studio

Question

I am having a problem with an INSERT statement from Visual Studio actually inserting records into the table

The statement is:

Dim cmd As New System.Data.SqlClient.SqlCommand
cmd.CommandType = System.Data.CommandType.Text
cmd.CommandText = "INSERT Document (CompanyName) VALUES ('Code Test SUCCESS')"

cmd.Connection = sqlConnection1

sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

The problem I think is with:

"INSERT Document (CompanyName) VALUES ('Code Test SUCCESS')"

In the Document table there are only two columns:

DocumentID (Primary Key, auto incrementing)
CompanyName

The application runs fine doesn't throw any errors but the data will not appear as a new record.

Many thanks in advance.

EDIT: The connection String is

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostTracker.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PostTracker.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True") — Gek
– Gek, Commented Mar 14, 2014 at 15:20

Steve · Accepted Answer · 2014-03-14 16:00:50Z

1

You use DataDirectory substitution string for your connection. So, if you don't have any error then the record is inserted but it is not in the database that you are looking at.

In WinForms applications, the DataDirectory is replaced using the working directory of your application. This working directory, inside a debug session of Visual Studio, is PROJECTFOLDER\BIN\DEBUG (or x86 variant).

If you check your data using Server Explorer inside Visual Studio chances are that the connection string used by Server Explorer points to the MDF file located in the PROJECTFOLDER where no record has been inserted.

edited Mar 14, 2014 at 16:00

answered Mar 14, 2014 at 15:16

Steve

217k22 gold badges242 silver badges296 bronze badges

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

9 Comments

Gek Over a year ago

OK so the problem is with the Connection String then? The Database explorer is pointing to the MDF file but its a dropdown from the DataConnections folder?

Steve Over a year ago

No, it is as expected. Just add another connection to your server explorer, point it to the MDF file in the debug folder, and name it something to differentiate from the previuos one. You keep the first for schema changes and other administrative tasks on your db. The second one serves to check if your operations works as expected. Check also the property Copy to Output Directory on the MDF file. It should be set to something Copy if newer.

Gek Over a year ago

Just to clarify, Server Explorer = DataBase Explorer? I have the connection setup pointing to the Debug now

Steve Over a year ago

VIEW -> Server Explorer, then right click on Data Connections, add Connection (but this is on Vs2013, let me check Vs2010) - Yes the same

Gek Over a year ago

Great so I have the new connection added pointing to the Debug now. The new connection string (pointing to the debug) does not seem to work from the code though Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Users\v-shgeth\Documents\Visual Studio 2010\Projects\PostTracker\PostTracker\bin\Debug\PostTracker.mdf";Integrated Security=True;Connect Timeout=30;User Instance=True

|

Collectives™ on Stack Overflow

SQL INSERT Command from Visual Studio

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related