Insert not working???

Graeme

Hi I am trying my first windows application. I have created a connection string in my app.config. I have tried some test coding to see if my connection works prior to completing the coding for textbox/combobox insertions to my .sdf (sqlce) file.

Here is the code I used to test the connection:

private void btnInsertTestCert_Click(object sender, EventArgs e)

{

SqlCeConnection conn = null;

string cnString = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

try

{

conn = new SqlCeConnection(cnString);

conn.Open();

SqlCeCommand cmd = conn.CreateCommand();

cmd.CommandText = "INSERT INTO tblTestCert (HoseNumber, Customer, HoseType, TestedTo)Values('1', 'Albion', 'Chemical', '225')";

cmd.ExecuteNonQuery();

}

finally

{

conn.Close();

}

I assume the connection works fine as I get no error, yet when I view table data there is no record in the database??

Yet if I click the click event button twice I get a duplicate value error

Any idea what I'm doing wrong???

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Graeme19y ago
Hi Brandon

Switched to Access db using your coding and it worked fine, so at least I know its as you said and its only the embeddded file not being updated.

Thanks for your help :)
0
19y ago
"COMMIT" is a widely used command to send to a database environment to basically tell it to save any changes that were made. When you make changes to most databases (Oracle SQL namely), the changes are not saved until you pass the command to commit them. That way, you can do a "ROLLBACK" if you need to just in case you entered some invalid data and want to get it out. A rollback will undo all changes back to the last commit.

Try implementing it and checking the local data source, not the embedded one. You will probably see the changes stick :)
0
Ryan Alford19y ago
are you sure that you are connecting? the way that you have the Try block, you are not catching anything. if there is an error, you will not get an error message and it will just move to closing the connection. debug your code and see if you get an error at the "conn.Open()".
0
Graeme19y ago
Hi Brandon

I'm still not quite understanding this, I have taken a look at the link you recommened Lesson 3 was of great interest on the matter.

Unfortunately it doesnt really cover the embedded source file connection using your commit and transaction methods.

I have tried your example but I am having no luck still??

Could you explain what the first argument "COMMIT" is for.

Thank you for your help on this matter

Graeme
0
19y ago
I ran into this same problem awhile back when working with MS Access. Are you checking the data base that is embedded in the application, or are you checking the remote (outside of the application) database?

Because if you are checking the embedded database, this method of changing its values will not work. What you doing here when you create database commands and pass them via command string is your creating a connection to a remote (not embedded) data source and modifying it. However, using that method, the changes will not stay permanently until you commit them by passing the database a commit command.

The data is being inserted into the database, but since it is a remote source, the changes will not be saved until you commit them. At least thats my take on it and its generally been that way when using Oracle SQL "back in the day", lol.

Essentially, your not even making the changes to the embedded source. In order to do that, check out some information on ADO.NET.

http://www.csharp-station.com/Tutorials/AdoDotNet/Lesson01.aspx
0
Graeme19y ago
Hi the database is local, testcert.sdf is actually part of the project.

I am very new to this, but I cant understand why if it is not inserting into the db then why am I getting a duplicate error when I click the event twice ??
0
19y ago
I dont see where you have explicitly commited the changes to the database. If your working with a remote database (as what it looks to be) you will need to pass another command to the database to commit the changes.

Ex:

SqlCeCommand commitCmd = new SqlCeCommand("COMMIT", conn);

commitCmd.ExecuteNonQuery();

Im not sure if you need a transaction to make changes to an SQL database. I am using my OleDb (MS Access) knowledge but I would assume its similar. Also, you may need to create a transaction in order to commit the changes. In that case, try this:

//Not sure if this class exists in your context,
//in mine it is "OleDbTransaction"
SqlCeTransaction trans = conn.BeginTransaction();

//Include the transaction
SqlCeCommand commitCmd = new
SqlCeCommand("COMMIT", conn, trans);

commitCmd.ExecuteNonQuery();

Have you tried adding the database as a data source and manipulating the embedded data source and allowing it to make changes to the database? Unless of course you want to keep the database remote, then you will have to pass commands to it.
0