INSERT Statement Doesn't Insert into SQL Server Database working with C# Winform

Question

I am trying to insert the costumer's information into a SQL Server database from a C# Winforms, although when I click on the Add button no error shows, the no data is being added into the database.

Here is the code for the Add function:

 private void acceptButton_Click(object sender, EventArgs e)
 {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString);

        String query = "INSERT INTO Costumer(Name, Address, Phone, Note) VALUES(@name, @address, @phone, @note)";

        SqlCommand command = new SqlCommand(query, con);


        command.Parameters.AddWithValue("@name", nameTextBox.Text);
        command.Parameters.AddWithValue("@address", addressTextBox.Text);
        command.Parameters.AddWithValue("@phone", phoneTextBox.Text);
        command.Parameters.AddWithValue("@note", noteTextBox.Text);

        try
        {
            con.Open();
            command.ExecuteNonQuery();
            con.Close();
            this.Close();
        }
        catch (SqlException exception)
        {
            MessageBox.Show(exception.Message.ToString(), "Error Message");
        }
    }

Here is the design of the Costumer table

CREATE TABLE [dbo].[Costumer] (
  [CostumerId] INT           IDENTITY (1, 1) NOT NULL,
  [Name]       VARCHAR (50)  NULL,
  [Address]    VARCHAR (50)  NULL,
  [Phone]      VARCHAR (16)  NULL,
  [Note]       VARCHAR (250) NULL,
  PRIMARY KEY CLUSTERED ([CostumerId] ASC)
);

Not sure where the issue is here.

Edit: The connection string I used in the winform project.

Here is the connection string:

<add name="myConnectionString" connectionString="Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\database.mdf;Integrated Security=True;Connect Timeout=30;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient"/>

I have used the same connection string for several SELECT queries and they worked fine.

Can you please *show us* the connection string you're using? — marc_s, Feb 09 '16 at 18:15
`command.ExecuteNonQuery ` returns the number of successfully executed rows. If it's more than one, then you are probably looking at the wrong database. — LarsTech, Feb 09 '16 at 18:16
take a look at [ConnectionStrings.Com/Sql Server](http://www.connectionstrings.com/sql-server/) looks like your should try a different connectionstring approach — MethodMan, Feb 09 '16 at 18:34
can you turn on sql profiler and see what is getting executed? — Andy Wiesendanger, Feb 09 '16 at 18:40

score 3 · Accepted Answer · edited May 23 '17 at 12:15

This is a common situation. You have the database file named database.mdf listed between your project files and, if you look at the properties for the file, you will notice the property Copy To Output directory set to Copy Always.

Now, in WinForms applications, the Output Directory is the folder BIN\DEBUG (or RELEASE and x86 variants) under your main project folder.
The DataDirectory substitution string used in your connectionstring points exactly to this folder.
So every inserts you execute adds records to the database in that folder.

Of course, if you don't see the added record, then it is higly probable that you are looking at the database located in your project folder where no insert has been made by your code....

To fix, change the property to Copy If Newer or Copy Never and make sure that the tool used to look at the database table uses a connectionstring that point to the database in BIN\DEBUG folder

Thanks Steve for the answer, this is exactly why I couldn't read the newly added rows. After changing the connection string to the BIN\DEBUG folder everything worked like a charm. — Alfasatwi, Feb 09 '16 at 22:01

INSERT Statement Doesn't Insert into SQL Server Database working with C# Winform

1 Answers1