C# and SQL Express Problems

Hi,

Im having an issue with the INSERT statement in C# using SQL Express.

First off, I believe my INSERT statement is correct. Using it in console mode works fine (adding it manually to the db), I've also had a resident SQL expert check it out and he said it looked good.

In my software, it seems to work, but nothing saves. Im writing my own DVD catalogue, mostly to get practice with both C# and SQL working together.

For example:

On my form, there a 3 options.

1. Catalogue Number

2. Dvd Type

3. Dvd Name

The catalogue option gets the number from a table in the DB via SELECT statement. This works.

The DVD Type reads options from another table in the DB via SELECT statement. This works.

The DVD Name is manually typed in a text box.

Once all 3 are filled out, I click the button to save it to the DB. No exceptions are thrown, and the form moves to the "next" entry. (Ie. in catalogue number, if it was 1, it becomes 2)

Upon exiting the program, I look at the DB to find nothing there.

Heres my code for adding (teh click handler for the button).

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (txtID.Text, cmbType.Text, txtName.Text)"; //This is wrong for the purpose of this post, simply to eliminate a few lines of String.Concat code!

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

I apologize if this has come up before, I didnt find an exact solution to this.

Thanks in advance for any help!

What is the error you are getting?

can you also post the exact String.Concat value?

|||

There is no error.

It seems to work, only after the program finishes execution, the changes dont commit.

Heres the code with the String.Concat

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (";

sQuery = String.Concat(sQuery, " ' ", txtID.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", cmbType.Text, " ' ");

sQuery = String.Concat(sQuery, " ' ", txtName.Text, " ' )";

sqlConn = new SqlConnection(sConnection);

sqlCommand = new SqlCommand(sQuery, sqlConn);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

|||

I wouldn't understand why.

Just for your knowledge, you should be using parameterized queries as they are securer and prevent SQL injection attacks - it is best practice to use them. It can also resolve some common problems and reduces the whole string parsing routine as well as making the code cleaner. :-)

Example:

SqlConnection sqlConn;

SqlCommand sqlCommand;

String sQuery;

sQuery = "INSERT INTO DVD (ID, Type, Name) VALUES (@.p1, @.p2, @.p3)";

SqlParameter p1 = new SqlParameter("@.p1", this.txtID.Text);

SqlParameter p2 = new SqlParameter("@.p2", this.cmbType.Text);

SqlParameter p3 = new SqlParameter("@.p3", this.txtName.Text);

using (sqlConn = new SqlConnection(sConnection))

{

using (sqlCommand = new SqlCommand(sQuery, sqlConn))

{

sqlCommand.Parameters.Add(p1);

sqlCommand.Parameters.Add(p2);

sqlCommand.Parameters.Add(p3);

sqlConn.Open();

sqlCommand.ExecuteNonQuery();

sqlConn.Close();

}

}

Also if you are finding that once the application closes and you find the values in the database being inserted, be sure that you are not entering into the debugger (stepping through) line by line as this can also cause some confusion on what is actually happen, just let it run and see what happens.

|||

Ok, I switched it over to the Parameterized Query, but it still does the same thing.

Im getting really frustrated by this now!!! It does the same thing on 2 machines.

It really doesnt make any sense at all. I query the table for the highest "ID" of the movie (SELECT MAX(ID) FROM Dvd), add 1, and display that value for the next DVD to be entered. When I enter one and click ADD, the number increments like it should, so if nothing else, it seems to be putting at least the ID into the table by creating a new row for it. I try this several times (ie. Add 10 DVDs), and the number counts correctly, once I exit the program, there is nothing in the DB. When I restart the program, the counter starts at 1 again...

Any ideas why nothing is actually saving in the database? Ive never seen anything like this before.

|||

maybe you should post the entire class?

gives us an overall view on what might be going wrong...

have a good one

adam

|||

About an hour ago, I came across the reason behind it.

It has to do with using the Express Editions of C# and SQL Server (or at least the person posted the reasoning behind it).

Since the db is an object in the solution, it gets copied (in its original, empty state) everytime the program gets compiled. So, while I was looking at what I believed to be the db, it was actually somewhere else, and everytime the program was run, a "fresh" copy of the db was copied over the one that actually had the data.

Does anyone know of any programs that will load SQL Server (Express) .mdf files? I have yet to come across one. I tried installing the Management Tools Suite, but I cant get it to open the file correctly (the program errors when I try), I have a feeling it has to do with this machine, I'll try another machine tomorrow and see what I can come up with. I'll post the results here, but in the meantime, if anyone knows of any software that will open and show the .mdf files, please let me know!

Thanks.

|||

As it turns out, that was the problem the whole time. Now, I just copy the .mdf file over and attach it to SQL Management Studio Express.

The SQL Management Studio Express allows you to "attach" the .mdf file to the current open db. THis will allow you to run queries to get whatever data from the database.

Thanks everyone for your help.

|||

hi,

i read your answer, but still i could not get the solution. because when i open the SQL Server Mangement, there is only one record affected by insert statment so i can not insert more than one record becase the insert treated as an update statment.

and when i used the update statment, no changes are done to the table data

pls pls help

|||Hmm, if you want to post your code, (or at least snippets of it) to show where things should be working and are not, that would be helpful. Off the top of my head, you may have an improper insert statement, or else the connectionstring you use may be off a bit. Post what you have and I'll try to help out.|||

Hi again,

I used the below code and it affect only one row

private void form_Load(object sender, EventArgs e)

{

strSql=”update tbl set fldName=’AAA’”;
cmd=new sqlCommand(strSql, conn);
conn.Open();
cmd.ExecuteNonQuery();
conn.Close()

}

So I changed my code and used button_Click instead of form_load and I was able to insert many rows. But when I close the application and open it again the previous data disappeared like that if I am using blank database.

So any idea. What I understand that the application creates a copy of the .mdf file while it is running and after closing the application it overwrite the original .mdf file

Correct me if am wrong and how to overcome this because I need to use it as database with historical data

I really really need your help

Thanks for your time

|||i get the answer. thats by setting the copy to output directory to copy if newer. before it was set to copy always.|||

I just set mine to copy never!

But it doesnt overwrite the file AFTER closing the application. It copies the .MDF to the output directory at compile time. All I did at first, was run the program, once it was finished, I would copy the db.mdf and db_log.ldf files to a TEMP folder and open them from there with SQL Management Studio Express. The reason I copied, when I tried to attach the file, it wouldnt let me browse to C:\documents and settings\......\visual studio 2005\Projects\...\bin

Did that copy if newer setting solve the problem(s)?