Record inserted successfully but database not updated C# and SQL Server Compact Database

Question

0.00/5 (No votes)

See more:

I'm using a C# windows form application
Database type is Microsoft® SQL Server® Compact and version is 4.0.8876.1
When I insert data message box pop up "New Record Added" but I check the database no data inserted table is empty
Why is that? Any wrong with my code?
Thank in advance!

This is my code database and application exe in the same directory

C#

int index = cb1.SelectedIndex;
            string apPath = Path.GetDirectoryName(Application.ExecutablePath);


            SqlCeConnection con = new SqlCeConnection("Data Source=" + app_path + "\\application.sdf;Persist Security Info=False;");
            con.Open();
            SqlCeCommand cm = new SqlCeCommand("INSERT INTO tbl_user(u_id,u_name,u_scl,u_que) VALUES (@u_id,@u_name, @u_scl, @u_que)", con);
            cm.Parameters.AddWithValue("@u_id", 2);
            cm.Parameters.AddWithValue("@u_name", txtname.ToString());
            cm.Parameters.AddWithValue("@u_scl", txtscl.ToString());
            cm.Parameters.AddWithValue("@u_que", index);

            try
            {
                int eff = cm.ExecuteNonQuery();

                if (eff == 1)
                {
                    con.Close();
                    MessageBox.Show("New Record Added " , "Done");
                    
                }
                else
                {
                    con.Close();
                    MessageBox.Show("not inserted ", "Error");
                }
            }
            catch (SqlCeException ex)
            {
                MessageBox.Show(ex.ToString());
            }

Posted 7-Nov-13 2:56am

Manoj Chamikara

Add a Solution

Comments

ZurdoDev 7-Nov-13 9:00am

Are you sure you're looking at the same database?

Manoj Chamikara 7-Nov-13 9:08am

Yes 100% positive

ZurdoDev 7-Nov-13 9:16am

Then is there a trigger? At first glance, the code looks right so if there is no error, it is inserting.

joshrduncan2012 7-Nov-13 9:21am

I think the parameters aren't pointing to the text property of the textboxes.

Richard C Bishop 7-Nov-13 10:43am

Good catch, post that as a solution.

joshrduncan2012 7-Nov-13 10:46am

Just did! :)

2 solutions

Solution 2

4 down vote accepted

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. MyDB)

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=MyDB;Integrated Security=True

and everything else is exactly the same as before...

Posted 31-Oct-14 11:40am

ZERO-ITE

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

joshrduncan2012 · Accepted Answer · 2013-11-07T04:46:00

Solution 1

You should look at the parameters and make sure that they are pointing to the text property of the textboxes.

Posted 7-Nov-13 4:46am

joshrduncan2012

Comments

Richard C Bishop 7-Nov-13 10:48am

+5, good catch.

joshrduncan2012 7-Nov-13 10:58am

Thanks!