0

I'm using the following code to store data from form inputs into a database.

The code seems to execute successfully and the message box states that one row has been affected (as expected).

When I look at the data sources window within visual studio there are no new rows saved within the data set.

Where are the new rows being saved to? How do I commit them to the database?

Thanks in advance.

    private void btnSavePublication_Click(object sender, EventArgs e)
    {
        // get the inputs
        int pubID = Convert.ToInt32(txtPubID.Text);
        string pubTitle = txtTitle.Text;
        long pubBarcode = Convert.ToInt64(txtBarcode.Text);
        string pubDate = txtRecievedDate.Text;
        decimal pubPrice = Convert.ToDecimal(txtPrice.Text);

        // retrieve the connection string
        string myConnectionString = Properties.Settings.Default.Database1ConnectionString;

        // open the connection to the database
        SqlConnection connection = new SqlConnection();
        connection.ConnectionString = myConnectionString;

        // create a SQL command to update the table
        SqlCommand command = new SqlCommand();
        command.Connection = connection;

        // set up the paramaters
        command.Parameters.AddWithValue("@PublicationID", pubID);
        command.Parameters.AddWithValue("@PublicationTitle", pubTitle);
        command.Parameters.AddWithValue("@PublicationBarcode", pubBarcode);
        command.Parameters.AddWithValue("@PublicationDate", pubDate);// TODO parse the date
        command.Parameters.AddWithValue("@PublicationPrice",pubPrice);

        // create the SQL query
        command.CommandText = "INSERT INTO Publication (PublicationID, PublicationTitle, " + 
                                                        "PublicationBarcode, PublicationDate, "+ 
                                                        "PublicationPrice)" +
                              "VALUES ( @PublicationID, @PublicationTitle, @PublicationBarcode, " + 
                              "@PublicationDate, @PublicationPrice)";

        // run the query (hopefully)
        try {
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            MessageBox.Show( rowsAffected + " rows affected");

        }
        catch {
            MessageBox.Show("Wakka wakka");
        }
        finally {
            connection.Close();
        }
    }

edit : This is my connection string -

"Data Source=(LocalDB)\\v11.0;AttachDbFilename=|DataDirectory|\\Database1.mdf;Integr" +
        "ated Security=True;Connect Timeout=30"
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tonyedwardspz
  • 1,707
  • 2
  • 22
  • 45
  • Did you refresh the VS interface? – C Bauer Jul 07 '14 at 13:20
  • Yes, and tried restarting VS – tonyedwardspz Jul 07 '14 at 13:21
  • 1
    Does your connection string contains the |DataDirectory| substitution string? – Steve Jul 07 '14 at 13:22
  • When you do a select it shows the first 200 rows by default. You're sure that's not the proble? – Complexity Jul 07 '14 at 13:22
  • Confirm that the data sources window is using the same db as "myConnectionString". Unless your connection string has autocommit disabled, this data has been stored somewhere. – Adam47 Jul 07 '14 at 13:24
  • @Complexity - There are only 3 rows in the database, the ones i inputted during setup. – tonyedwardspz Jul 07 '14 at 13:24
  • You are *certain* that it's returning `1` and not `-1`? The fact that you pass a date as a string is a bit of a red flag for me. – James Jul 07 '14 at 13:25
  • Ok, so we could already get rid of that one. Maybe try to write some code to query all the records from the database and see how many they are. If in your visual studio browser you've a different amount then you're looking at the wrong database. – Complexity Jul 07 '14 at 13:25
  • You may also look at the database's log file to check whether the query has actually been run. – Tarec Jul 07 '14 at 13:26
  • 4
    Is this perhaps the classic issue of the **application** writing to one location (perhaps $project$/bin/debug/your.data), and you are looking at the project level ($project$/your.data)? i.e. two different files? – Marc Gravell Jul 07 '14 at 13:27
  • @MarcGravell still waiting a response to my question. Usually that's the reason – Steve Jul 07 '14 at 13:27
  • @Steve - yes it does i think - see edit – tonyedwardspz Jul 07 '14 at 13:29
  • 2
    @Steve it is also why I never, ever, develop against file-based connections unless I can help it; give me a proper database server (event .\sqlexpress will do) and I'm happy ;p – Marc Gravell Jul 07 '14 at 13:29
  • 1
    Then I think that this is your situation - http://stackoverflow.com/questions/17147249/why-dont-changes-to-database-save/17147460#17147460 – Steve Jul 07 '14 at 13:29
  • Possible duplicate of [INSERT statement affects 1 row, but the VS Server Explorer shows that it doesn't actually update the … database](http://stackoverflow.com/questions/6636513/insert-statement-affects-1-row-but-the-vs-server-explorer-shows-that-it-doesnt) – stakx - no longer contributing Jul 07 '14 at 13:48
  • 1
    @Steve. That was my problem. Thanks for the help! – tonyedwardspz Jul 07 '14 at 14:20

0 Answers0