asked on

How do you execute INSERT statements into a SQL database using C#?

I'm having a really hard time creating a Windows appilcation that can connect to a SQL Server 2008 Express database and insert some values into a table. I've been able to set the connection to the database succesfully, and running SELECT queries is a breeze. It works flawlessly.

The problem is when I try to create an INSERT query. It runs the query succesfully, but after I close the application down, I go to the databse and I found out that the values weren't inserted at all. I'm using a TableAdapter to do this. I get some values from 3 text boxes and I use those to insert values into a table at the push of a button. The code that I have at the CLICK event of that button is at the bottom of the post.

My TableAdapter object has various queries that I have created. I also use some SELECT queries to make sure the values have been inserted into the database. It appears that the values are in fact inserted into the table, because the SELECT queries manage to get that information from the database. However, once I close the application and open in it again I can't seem to find values inserted.

Finally I've noticed that whenever I insert some values into the table using this application, I get a LDF file in the path were the database MDF file exists. These seems to me like the insert queries are actually stored in this Log file, but they are somehow never uploaded into the database. How can I make these INSERT queries be inserted for real. I've spent countless hours on this and I can't seem to get it fixed. Any help would be greatly appreciated. THanks!

private void btnInsertarPersona_Click(object sender, EventArgs e) {
   // Verify that text inside text boxes has correct format...
   try
   {
      this.queriesTableAdapter.InsertQuery(this.txtName.Text, this.txtLastName.Text, this.txtMiddleName.Text);
   }
   catch (Exception ex)
   {
      MessageBox.Show("Query failed: " + ex.Message);
      return;
   }
 
   this.lblMessage.Text = "It worked!";
}

Open in new window

Jai S

you have to provide
this.queriesTableAdapter.Update();

James00

ASKER

queriesTableAdapter doesn't even have an Update() method. I created a new Query dragging it from the Toolbox and putting into the DataSet designer. I was then able to specify the type of query and all the rest of the information. I've attached a screenshot so you can see what I mean. Thanks!
DataSetDesigner.JPG

ASKER CERTIFIED SOLUTION

Jai S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

SameerJagdale

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rionroc

Hello

I believe sample program source code is the best tool for learning.
I hope this helps you:

http://download.microsoft.com/download/6/4/7/6474467e-b2b7-40ea-a478-1d3296e78adf/CSharp.msi

Great is our GOD.
:)

SOLUTION

trunghieubkit

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jai S

@trunghieubkit - the same solution has been given before(if you check my replies) and it does not work

James00

ASKER

Thanks for all the responses! After trying your solutions I'm still unable to fix this problem. I tried following what jaiganeshsrinivasan suggested. That is, using the Personas TableAdapter directly. This is the code I inserted:

this.personasTableAdapter1.Insert(...values here....);

However, this still behaves exactly as the code I had before. So I also added the Update() method everyone was talking about (PersonasTableAdapter class does have the Update method). This is what I added:

this.personasTableAdapter1.Update(this.aNSPACDataSet);

Again, this still didn't fix the issue. However, I've been noticing that the changes (i.e.data inserted with INSERT queries) seem to be loaded into the database until I compile again. Here is an example:

1) Using the text boxes and the button, I insert several rows of values into the database.
2) I issue SELECT queries through the UI and I confirm that the previous changes were made.
3) I exit the application and check the database. None of the changes were submitted. However, if I run the application again I can still see the changes submitted in step 1.
4) Finally, I recompile again. Now after I load the application again I'm not able to see the changes made in step 1.

I have no idea why this happens, but I assume it has to do with the LDF file somehow. I think rionroc samples could be of great help. However, if somebody can provide a solution it would be greatly appreciated. I'm pretty sure it must be something really simple that I have overlooked. It shouldn't be this complicating creating a simple application that INSERTs data into a database. This should be pretty straight forward.

Finally, it might be relevant to mention that I tried doing the same application but on an Access 2007 database. I encountered the same problem and I thought it was only Access related. Hope this gives more info on this. Thanks! =)

James00

ASKER

UPDATE

I don't know why, but I've also noticed that sometimes after compiling I get a huge list of warnings (around 37). Here is one of the warning messages:

The type 'AsistenciaANSPAC.ANSPACDataSetTableAdapters.QueriesTableAdapter' in 'C:\Users\James\Documents\Visual Studio 2008\Projects\AsistenciaANSPAC\AsistenciaANSPAC\ANSPACDataSet.Designer.cs' conflicts with the imported type 'AsistenciaANSPAC.ANSPACDataSetTableAdapters.QueriesTableAdapter' in 'c:\Users\James\Documents\Visual Studio 2008\Projects\AsistenciaANSPAC\AsistenciaANSPAC\obj\Debug\TempPE\ANSPACDataSet.Designer.cs.dll'. Using the type defined in 'C:\Users\James\Documents\Visual Studio 2008\Projects\AsistenciaANSPAC\AsistenciaANSPAC\ANSPACDataSet.Designer.cs'.

SOLUTION

rionroc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James00

ASKER

I've found a solution to this. It was a pretty stupid oversight on my part. I was compiling and this was replacing the database I had already modified. Every time you compile the database in the DEBUG or RELEASE folder is replaced with the one that you have in the Project directory.

Thanks for everyone's help, I'll assign some points to the people that posted here.

James00

ASKER

I ranked everything average since I was the one that found the solution. But thanks for all the quick responses!