INSERT statement not adding any data without throwing error

Question

I am trying to add train objects to a database to hold their details for persistence.

I have it working so I can add the trains to a list. But when I try to set up an INSERT statement to add the train objects details to a database. nothing is added to my database when i check it after. I don't get any errors thrown anywhere either.

Can anyone see anything wrong with my INSERT statement?

        //If the type combobox has Express selected
        if (cbxType.Text == "Express")
        {
            //Create a new train with its specific details
            Train train = trainFactory.TFactory("Express");
            //Checks for error when making train
            if (train == null)
                MessageBox.Show("Can't Create Train");
            else //Executes adding a new Express Train
            {
                //Stores the details of the textboxes/Combo boxes into the train details for each Train object
                train.Type = cbxType.Text;
                train.Departure = cbxDepartStation.Text;
                train.Destination = cbxDepartStation.Text;
                //Converts the time into DateTime format before passing to variable
                train.DepartureTime = TimeSpan.Parse(txtDepartureTime.Text);
                //Converts the date into DateTime format before passing to variable
                train.DepartureDay = DateTime.Parse(txtDepartureDay.Text);
                //If intermediate stops are selected. Throw exception
                if (chbPeterborough.IsChecked == true || chbDarlington.IsChecked == true ||
                            chbYork.IsChecked == true || chbNewcastle.IsChecked == true)
                {
                    throw new Exception();
                }
                //If first class radio button is checked, sets first class to true, else false
                if (chbFirstClass.IsChecked == true)
                {
                    train.FirstClass = true;
                }
                else
                {
                    train.FirstClass = false;
                }

                //Adds a train object to the train list with its specific details
                trains.add(train);

                //String to hold all the Intermediate stops together in one for displaying to user
                string intStops = string.Join(", ", train.IntermediateStop.Where(s => !string.IsNullOrEmpty(s)));

                //Sql sequence to connect to database and insert details of each train
                SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Trains.mdf;Integrated Security=True");
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "INSERT train (id, departure, destination, type, intermediate, departure_time, departure_date, sleeperBerth, firstClass) " +
                                  "VALUES ( @trainID , @departure, @destination, @type, @intermediate, @dep_time, @dep_date, @sleep, @first)";
                cmd.Parameters.AddWithValue("@trainID", train.TrainID);
                cmd.Parameters.AddWithValue("@departure", train.Departure);
                cmd.Parameters.AddWithValue("@destination", train.Destination);
                cmd.Parameters.AddWithValue("@type", train.Type);
                cmd.Parameters.AddWithValue("@intermediate", intStops);
                cmd.Parameters.AddWithValue("@dep_time", train.DepartureTime);
                cmd.Parameters.AddWithValue("@dep_date", train.DepartureDay);
                cmd.Parameters.AddWithValue("@sleep", train.SleeperBerth);
                cmd.Parameters.AddWithValue("@first", train.FirstClass);

                cmd.Connection = con;

                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();

Answer 1

The whole AttachDbFileName= approach is flawed - at best! When running your app in Visual Studio, it will be copying around the .mdf file (from your App_Data directory to the output directory - typically .\bin\debug - where you app runs) and most likely, your INSERT works just fine - but you're just looking at the wrong .mdf file in the end!

If you want to stick with this approach, then try putting a breakpoint on the myConnection.Close() call - and then inspect the .mdf file with SQL Server Management Studio - I'm almost certain your data is there.

The real solution in my opinion would be to

1. install SQL Server Express (and you've already done that anyway)
2. install SQL Server Management Studio
3. create your database in SSMS, give it a logical name (e.g. Trains)

4. connect to it using its logical database name (given when you create it on the server) - and don't mess around with physical database files and user instances. In that case, your connection string would be something like:

   Data Source=.\\SQLEXPRESS;Database=Trains;Integrated Security=True
   

   and everything else is exactly the same as before...

Also see Aaron Bertrand's excellent blog post Bad habits to kick: using AttachDbFileName for more background info.

Answer 2

Try to change this code

 cmd.CommandText = "INSERT train (id, departure, destination, type, intermediate, departure_time, departure_date, sleeperBerth, firstClass) " +
                              "VALUES ( @trainID , @departure, @destination, @type, @intermediate, @dep_time, @dep_date, @sleep, @first)";

into

 cmd.CommandText = "INSERT INTO train (id, departure, destination, type, intermediate, departure_time, departure_date, sleeperBerth, firstClass) " +
                              "VALUES ( @trainID , @departure, @destination, @type, @intermediate, @dep_time, @dep_date, @sleep, @first)";

I only added INTO in your INSERT text query