Solved

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

Posted on 2008-09-30
12
2,233 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:James00
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 14

Expert Comment

by:jaiganeshsrinivasan
ID: 22611763
you have to provide
this.queriesTableAdapter.Update();
0
 

Author Comment

by:James00
ID: 22611886
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
0
 
LVL 14

Accepted Solution

by:
jaiganeshsrinivasan earned 200 total points
ID: 22611980
hi, iam not sure as why you are writing a separate Insert Query for your Personas table...since you already have a table adapter...you can use the same to perform insert / update / delete operations like

PersonasTableAdapter pTA = new PersonasTableAdapter();
pTA.Insert(your values goes here);

or

PersonasDataTable dt = new PersonasDataTable();
PersonasRow newDr = dt.NewPersonasRow();
newDr.nombres = the_value;
newDr.appleidoP = the_value;
newDr.applliedoM = the_value;
dt.addNewPersonasRow(newDr);
PersonasTableAdapter pTa = new ...
pTa.Update(dt);

the advantage of typed data set is that it will generate all the DML statements ...you just need to call the methods to perform the update...let me know if you need more help....

if you still insist on have the InsertQuery...paste teh QUERY that you have written inside InsertQuery
0
 
LVL 13

Assisted Solution

by:SameerJagdale
SameerJagdale earned 50 total points
ID: 22612026
does your insert query works in sql ? have you tried that by passing the values in place of textbox.text...
0
 
LVL 12

Expert Comment

by:rionroc
ID: 22612188
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.
:)
0
 
LVL 3

Assisted Solution

by:trunghieubkit
trunghieubkit earned 50 total points
ID: 22612296
After doing the operation:
              this.queriesTableAdapter.InsertQuery(this.txtName.Text, this.txtLastName.Text, this.txtMiddleName.Text);

Your data has not been saved into database files (hard disk). So you can't be able to see it in your database unless doing the this.queriesTableAdapter.Update(); operation by following

Recommending that you should do Update operation when you really want to save into your database, because it'll take least system's effort.


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);

        this.queriesTableAdapter.Update();

    }

    catch (Exception ex)

    {

        MessageBox.Show("Query failed: " + ex.Message);

        return;

    }
 

    this.lblMessage.Text = "It worked!";

}

Open in new window

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 14

Expert Comment

by:jaiganeshsrinivasan
ID: 22612308
@trunghieubkit - the same solution has been given before(if you check my replies) and it does not work
0
 

Author Comment

by:James00
ID: 22612437
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! =)

0
 

Author Comment

by:James00
ID: 22612475
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'.
0
 
LVL 12

Assisted Solution

by:rionroc
rionroc earned 200 total points
ID: 22621175
Hello

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


http://www.planet-source-code.com/Upload_PSC/ftp/A1_Employe2077567302007.zip

If that sample program runs at your computer, meaning your vs is OK.

Great is our GOD.
:)
0
 

Author Comment

by:James00
ID: 22631152
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.
0
 

Author Closing Comment

by:James00
ID: 31501854
I ranked everything average since I was the one that found the solution. But thanks for all the quick responses!
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now