INSERT INTO, UPDATE commands don't add data to SQL Server database in C#

Question

Million times sorry for my question. I know there were a thousand questions exactly the same as mine. Still I couldn't find any solutions to my problem by reading earlier posts.

When I run my code, it doesn't throw any exceptions, it runs just fine, still doesn't add any data to my database when using the INSERT INTO command nor alter the data when using UPDATE command.

On the other hand, if I replace the code in the try section with a SELECT-FROM-WHERE query, and read data with SqlDataReader, it works just perfectly. So I assume there's no problem with the connection itself.

Here's my code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace adatbázis_gyakorlás_s_sharp
{
  public partial class Form1 : Form
  {
    public Form1()
    {
        InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
        string kapcslink;
        kapcslink = adatbázis_gyakorlás_s_sharp.Properties.Settings.Default.kapcsolo;
        System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection(kapcslink);

        con.Open();

        try
        {
            string nev;
            int irsz;
            string telepules;

            nev = "Joseph";

            string lekerdezes = "INSERT INTO proba(nev) VALUES (@nev);";

            System.Data.SqlClient.SqlCommand parancs = new System.Data.SqlClient.SqlCommand();
            parancs.CommandText = lekerdezes;
            parancs.CommandType = CommandType.Text;
            parancs.Connection = con;
            parancs.Parameters.Add("@nev", SqlDbType.VarChar).Value="Joseph";
            parancs.ExecuteNonQuery();
        }
        catch(Exception err)
        {
            MessageBox.Show(err.Message);
        }

        con.Close();
    }
  }
}

Any suggestions?

Thanks in advance.

Check if you are updating a local file database but your server database. — dani herrera
– dani herrera, Commented Sep 6, 2015 at 13:08
There are a few issues that may not effect your query but should be taken into consideration. Try and wrap your connection and the command in a using block. This will make sure they are properly cleaned up comming out. When you create your command you can put your connection object as well as your query in the constructor, then you can get rid of specifying these later... — Trevor
– Trevor, Commented Sep 6, 2015 at 13:11
I set up my connection string via Solution Explorer Properties/Settings, and is the following: Data Source=(LocalDB)\v11.0;AttachDbFilename=|DataDirectory|\adatok.mdf;Integrated Security=True;Connect Timeout=30. — Joseph
– Joseph, Commented Sep 6, 2015 at 13:21
Adding to the excellent answer of marc_s below you could read also this one stackoverflow.com/questions/17147249/… — Steve
– Steve, Commented Sep 6, 2015 at 13:42

marc_s · Accepted Answer · 2015-09-06 13:39:57Z

2

The whole User Instance and 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 Mgmt Studio Express - I'm almost certain your data is there.

The real solution in my opinion would be to

install SQL Server Express (and you've already done that anyway)
install SQL Server Management Studio Express
create your database in SSMS Express, give it a logical name (e.g. adatok)
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=adatok;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.

answered Sep 6, 2015 at 13:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

1 Comment

Joseph Over a year ago

Thanks for your answer. Previously I used server based databases, editing them on localhost with phpmyadmin. They worked fine. But this case I'd have liked to use a local database. It looked very simple at the beginning, but I'm beginning to think it's not my way :D

Joseph · Accepted Answer · 2015-09-06 18:43:02Z

Well, many thanks for all af you, guys! The problem really lies in the "Copy To Output Directory" setting. I searched for the bin folder and I found the copy of my .mdf file updated! LOL

I changed the setting to 'Copy if newer', so it worked as I wanted to - except that the file in the bin directory changed every time and not the one in the original folder. But the changes remained even for the next run. That - I think - is acceptable...

Though the setting 'Do not copy' doesn't work for me. When building the project, I get an "An attempt to attach an auto-named database for file C:\Users\Joey\documents\visual studio 2013\Projects\adatbázis gyakorlás s-sharp\adatbázis gyakorlás s-sharp\bin\Debug\adatok.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share." exception. Do you have any idea what can use the file? Can it be solved in any ways?

My general idea was to access one .mdf file from my program so that no sql servers has to be used and the database file would be located somewhere in the program directory. Do you think the biggest problem is my approach? :-)

Collectives™ on Stack Overflow

INSERT INTO, UPDATE commands don't add data to SQL Server database in C#

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related