CODEWITHSUNDEEP
c#sqlsql-serverinsert
Joseph
Joseph

Reputation: 21

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

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.

Upvotes: 0

Views: 478

Answers (2)

Joseph
Joseph

Reputation: 21

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? :-)

Upvotes: 0

marc_s
marc_s

Reputation: 755321

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

  1. install SQL Server Express (and you've already done that anyway)

  2. install SQL Server Management Studio Express

  3. create your database in SSMS Express, give it a logical name (e.g. adatok)

  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=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.

Upvotes: 2

Related Questions