Solved

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

Posted on 2008-09-30
12
2,236 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:Jai S
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:
Jai S 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
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
 
LVL 14

Expert Comment

by:Jai S
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB: Convert 2 dates to specific format 24 48
SQL Help 27 46
C# Gridview 1 33
average of calculation (TSQL) 4 10
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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