C# & SQL: Data not being saved to Database

Question

_{Thursday, August 28, 2008 6:37 AM}

Hi all, I've got a very annoying problem here and I can't figure out how to fix it.
I'm not a total newbie, but this one is trying my patience...

I have a C# Winform app and all I want to do is Insert a new row of data into a database. The Insert works when the application is open (the new data appears in the DataGridView), but when I close/reopen the application the data is no longer there.
It's an MSSQL Database and is stored in the 'Data' directory of my project.

The code I am using is:

string pass = PasswordControl.HashPassword(txtPassword.Text);
int userID = Classes.General.GetDatabaseID("Users", "UserID");

SqlConnection conn = new SqlConnection(<projectName>.Properties.Settings.Default.ConnectionString);
SqlCommand command = new SqlCommand("INSERT INTO Users VALUES(@ID, @Full, @User, @Pass)", conn);
command.Parameters.Add(new SqlParameter("@ID", userID));
command.Parameters.Add(new SqlParameter("@Full", txtFullName.Text));
command.Parameters.Add(new SqlParameter("@User", txtUsername.Text));
command.Parameters.Add(new SqlParameter("@Pass", pass));

conn.Open();
command.ExecuteNonQuery();
conn.Close();

Once this code executes, the new user appears in the DataGridView but NOT in the actual database. If I restart the application the DataGridView no longer shows the new data.
It might also be worth adding that if I add/remove data from the database manually (while the application is open), the DataGridView does NOT reflect those changes at all. It appears that the application is storing the contents of the database into memory when it's opened and is therefore never querying the actual database when I try to add/edit/remove rows.

Any help in resolving this issue would be greatly appreciated.

Ben

All replies (12)

_{Friday, August 29, 2008 9:41 AM ✅Answered | 3 votes}

Hi Ben

I guess if the DB path is referenced something like: 

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Data\Database1.mdf;Integrated Security=True;User Instance=True

then try specifying the full physical path "AttachDbFileName=c:\Project\Data\Database1.mdf"

I hope that helps

Thats how i solved it once :)

_{Thursday, August 28, 2008 6:48 AM}

Hi!

Just out of the blue.

i think you should use VALUES not VALUE in the insert into clause

/M

_{Thursday, August 28, 2008 9:34 AM}

Hey MrSwede. You're right, it should be VALUES.
The code shown in this thread wasn't copied/pasted, I retyped it all manually so it was just a typo.
In my REAL code it says VALUES.

Thanks anyway, though.

Any other suggestions? I'd really like to get this resolved ASAP.

(Edited the original post too)

_{Friday, August 29, 2008 9:48 AM}

Sql Basics:

Are you making sure you have specified parameters in order of columns?

Your statement

INSERT INTO Users VALUES(@ID, @Full, @User, @Pass)

--

Does User Table have ID as First Column, Full as Second Column and so on?

Another possibility

Do you have any identity column in the Table?

_{Friday, August 29, 2008 10:47 AM}

I usually work with servers, not local files, but it seems that the initial catalog is not right

      <setting name="MyProject_connectionString" serializeAs="String">
        <value>Data Source=localhost\SQLExpress; Initial Catalog=INITIAL_CATALOG user=xxx password=xxx" />

     (Changes to use local file, instead of server will be required)

In the database server, if this value is not set, the inserts should be executed over master database (if the table is present, of course. Would it be possible that?)

_{Monday, September 1, 2008 5:14 AM}

Hi Guys, thanks for your replies.

Syed Emad
The connection string was created automatically for me when I added the database to the application. It doesn't seem to be a problem because I can access the data using that string, just can't insert for some reason.

Viral Jain
The columns are in the correct order (UserID, FullName, Username, Password).
Identity Specification on all fields is set to 'No'

Mgestal
What I said to Syed Emad applies here too. Accessing the database is fine. I can login to the application (which authenticates the supplied username/password with the user/pass in the database). Once I'm logged in, I can populate a DataGridView with data from the database, but I just can't seem to add more data.

Any other suggestions?
Basically, I can access the data but can't insert new data.
It's almost as though the application puts the contents of the table/DB into memory but doesn't update the REAL DB when I insert new data.

_{Monday, September 1, 2008 5:29 AM}

Hello ben,

Syed Emad is saying correct it will work that way, he is my teacher and once we solved this problem whit the solution he told u..

just try and check !

Death-Code

_{Monday, September 1, 2008 5:51 AM}

Hi m_umair_85, Syed is right!
I just tried it and came back to post and I saw your reply.

I tried running the application from the .exe inside the Bin directory and it was inserting correctly!
So the only thing I could guess was the the application was starting a NEW database in memory every time I ran the application from VS2005 (and it was putting the db inside the |data directory|).

So I did was Syed Emad said and changed the connectiong string to show the full path of the database and now it's working.

Thanks for your help :)

Ben

_{Monday, September 1, 2008 5:57 AM}

Hi

I am glad that i was able to help someone. Well i wld also like to know the best scenario to wrap the DB when deploying a simple WinApp like inventory management for a single user.

Take care

_{Friday, July 26, 2013 6:27 AM}

Thank u Very much @Syed Emad, i have been researching around this problem for past nearly 7 hours and finally found my solution in your answer :D :D :D 

_{Saturday, June 1, 2019 11:19 PM}

Thank you. this seems to do the trick 

_{Sunday, March 1, 2020 10:32 AM}

Save my day, so everything was working but all new "Run" a new copy of the database was made to the output folder and... damn it