How am i using the SclCeCommand and ExecuteNonQuery method wrong?

Question

I'm trying to make a method in my Admin class (for a small course project) which should INSERT a new movie into my database.

I don't get any errors, but the new data is not inserted into the database. Here is my code:

public void AddMovieToDB(int mCodeLable, string title, int yearOfMovie, int lengthInMinutes, string dvdOrVhs, string genreOfMovie)
        {
            int movieCodeLable = mCodeLable;
            string movieTitle = title;
            int year = yearOfMovie;
            int length = lengthInMinutes;
            string typeOf = dvdOrVhs;
            string genre = genreOfMovie;

            string connectionString = @"Data Source=|DataDirectory|\VideoStoreDB.sdf";            
            SqlCeConnection connection = new SqlCeConnection(connectionString);

            connection.Open();

            SqlCeCommand command = new SqlCeCommand(
            "INSERT INTO Movie(MovieCodeLable, Title, Year, 
            LengthMinutes, TypeOf, Genre) 
            VALUES(@mcl, @title, @year, @length, @typeOf, @genre)", connection);

            command.Parameters.AddWithValue("@mcl", movieCodeLable);
            command.Parameters.AddWithValue("@title", movieTitle);
            command.Parameters.AddWithValue("@year", year);
            command.Parameters.AddWithValue("@length", length);
            command.Parameters.AddWithValue("@typeOf", typeOf);
            command.Parameters.AddWithValue("@genre", genre);

            command.ExecuteNonQuery();
            connection.Close();

            Console.WriteLine("Movie added to the DB!");
        }

And this is how i call it from the Main:

Admin admin1 = new Admin();
admin1.AddMovieToDB(0151, "Play ball!", 2012, 124, "DVD", "Drama");

Inspect the return value of `command.ExecuteNonQuery`. It returns the number of records affected. If it's `1`, then most likely, you are looking in the wrong database. Happens often in Visual Studio. — LarsTech, Dec 03 '13 at 20:53
Do you have a post-build event that's copying the database? And as a side-note, why are you basically redefining the values that are passed in? You can just use them directly as the values. — Mike Perrenoud, Dec 03 '13 at 20:54
Does [this answer](http://stackoverflow.com/a/13313075/22437) shed any light on the situation? — Dour High Arch, Dec 03 '13 at 21:02
The return value of `command.ExecuteNonQuery` was 1. How do i look in the right DB? I don't know the path to the "right" db. :) — XT_Nova, Dec 03 '13 at 21:11

Steve · Accepted Answer · 2013-12-03T21:19:30.517

This is a possible scenario.

Your connection string use |DataDirectory|. This means that at RUNTIME, inside VS debug session, the substitution string points to the folder BIN\DEBUG where is located the file sdf copied by VS when it starts the debug session (Check the property Copy to Output directory for your sdf file).

The insert works flawlessy. (you can check that looking at the return value of ExecuteNonQuery()).

You stop the debug and check the content of your table using Server Explorer but you don't find anything.

The only problem is the connection used by your Server Explorer window that point to a different database. The one in the project folder.

I suggest to create two connections on Server Explorer. Leave the current one as is (so this db is your main empty copy ready to be deployed with your application) and add another connection that points to the db in the PROJECTFOLDER\BIN\DEBUG where is located the db used during the debug sessions (Right click 'Add Connections', Select Sql Server Compact Edition and browse to the BIN\DEBUG folder)

When i checked the properties of my .sdf file, it ways `Copy if never`, for ´Copy to Output directory`. What and how, should i point the connection regarding what you said about m Server Explorer? — XT_Nova, Dec 03 '13 at 21:03

How am i using the SclCeCommand and ExecuteNonQuery method wrong?

1 Answers1