Insert, Update and Delete Query not working, Why?

Question

0.00/5 (No votes)

See more:

.NET3.0

, +

Hi,

I try to create simple Insert Update and Delete with Visual C# & SQl Server 2005.

Its working fine till I Rebuild or Reopen with Close Application, but when I look into My SQL DB (.mdf) after Any Insert , Update or Delete Query that give me successfull Msgbox My Database still not updated. Why ? ? ? ?

Here is My Code :

Connection :

C#

public void ConnecttoDb()
       {
           string myconn;
           myconn = "Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + "\Data.mdf;Integrated Security=True;User Instance=True";
           con = new SqlConnection(myconn);
           cmd.Connection = con;
           try
           {
               con.Open();
               //cmd = new SqlCommand("select name from comp",con);
               MessageBox.Show("Connect Success","Database",MessageBoxButtons.OKCancel);
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }

Insert Button :

private void btnInsert_Click(object sender, EventArgs e)
        {
            cmd.CommandText = "insert into comp values (" + tbNo.Text + ",'" + tbName.Text + "') ";
            cmd.CommandType = cmd.CommandType;
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Insert Done...", "Insert");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

Update Button :

C#

{
            cmd.CommandText = "Update comp SET name='" + tbName.Text + "' where no=" + tbNo.Text + " ";
            cmd.CommandType = cmd.CommandType;
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Update Done...", "Update");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }

        }

New Code (also not working....)

private void buttonUpdate_Click(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection();                     
            SqlCommand command = new SqlCommand();
            connection.ConnectionString = @"Data Source=.\SQLEXPRESS; AttachDbFilename=" + Application.StartupPath + "\\University.mdf;Integrated Security=True";
            command.Connection = connection;
            command.CommandText = "UPDATE Students SET FirstName=@FirstName, LastName=@LastName, Gender=@Gender, Age=@Age, Address=@Address WHERE StudentID=@StudentID"; 
                                                                         
            command.Parameters.Clear();
            command.Parameters.AddWithValue("@FirstName", textBoxFirstName.Text);
            command.Parameters.AddWithValue("@LastName", textBoxLastName.Text);
            command.Parameters.AddWithValue("@Gender", textBoxGender.Text);
            command.Parameters.AddWithValue("@Age", textBoxAge.Text);
            command.Parameters.AddWithValue("@Address", textBoxAddress.Text); 
            command.Parameters.AddWithValue("@StudentID", textBoxStudentID.Text);     
                                                                        
     try                                                                 
     {                                                                   
         connection.Open();                                              
         int result = command.ExecuteNonQuery();  
                                                                         
         if (result > 0)                                              
             MessageBox.Show("Update Successful!");                      
         else                                                            
             MessageBox.Show("Update Failed!");                          
    }                                                                   
     catch (SqlException ex)                                             
    {                                                                   
         MessageBox.Show("An error occured." + ex);                           
    }                                                                   
    finally                                                             
     {                                                                   
        connection.Close();
         MessageBox.Show("Connection Closed")  ;                                  
     }                            
        }

Posted 1-Jan-12 20:52pm

s1a2b3

Updated 1-Jan-12 22:12pm

v3

Add a Solution

6 solutions

Solution 6

Make following changes

C#

public void ConnecttoDb()
       {
           string myconn;
           myconn = "Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + "\Data.mdf;Integrated Security=True;User Instance=True";
           con = new SqlConnection(myconn);
           //cmd.Connection = con; line 1
           try
           {
               con.Open();
               //cmd = new SqlCommand("select name from comp",con);
               MessageBox.Show("Connect Success","Database",MessageBoxButtons.OKCancel);
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }

C#

private void btnInsert_Click(object sender, EventArgs e)
        {
           
            try
            {
                ConnecttoDb();
                cmd.CommandText = &quot;insert into comp values (&quot; + tbNo.Text + &quot;,&#39;&quot; + tbName.Text + &quot;&#39;) &quot;;
            cmd.CommandType = cmd.Text;
                cmd.ExecuteNonQuery();
                MessageBox.Show("Insert Done...", "Insert");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

Yogesh

Posted 2-Jan-12 0:43am

Yogesh Pednekar

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2012-01-01T22:13:00

Solution 5

Looks like your database file is copied in the project output folder on each compile. So the modifications are made in the copy of the MDF file and probably every time you compile the application, you actually copy an older version of the mdf file to the output directory.

Posted 1-Jan-12 22:13pm

Wendelius

Comments

s1a2b3 2-Jan-12 4:17am

How to trace if its happend as what you say? what is solution?

Wendelius 2-Jan-12 4:24am

One possibility is to place the MDF file in a single location and if it's included in your project, exclude it. After that change the connection string to point to that static location (something like c:\mydatabase\...)

s1a2b3 2-Jan-12 4:39am

Hey thx, its working now with static path, plz also tell me how can i set it dynamic path like |Data Directory|\Db.mdf
:)

Wendelius 2-Jan-12 4:42am

You can use the dynamic path, but do not include to original database in your project and copy it to the output directory. Otherwise the 'older' or original version always replaces the mdf and you loose the modifications. Perhaps create a directory called Data next to the output dit and you can reference the datafile using something like ..\Data\Datafile.mdf

s1a2b3 2-Jan-12 4:56am

If I write AttachDbFilename=|Data Directory|\University.mdf its shows error invalid value for key "attachdbfile" why?

Wendelius 2-Jan-12 5:03am

If that's you're actual connection string, try using relative path. Like ..\data and so on.

Anil Avhad [IND] · Accepted Answer · 2012-01-01T21:51:00

Solution 3

check is Connection open after click on insert button

if Not open then open connection execute Insert cmd and finally close conection

similarly use for Update also.

Posted 1-Jan-12 21:51pm

Anil Avhad [IND]

Comments

s1a2b3 2-Jan-12 4:01am

its goes to msgbox that display successs and then also close connection but when refresh database its not updated.

Tanveer A · Accepted Answer · 2012-01-01T22:00:00

rectified codes.

public void ConnecttoDb()
{
string myconn;
myconn = "Data Source=.\SQLEXPRESS;AttachDbFilename=" + Application.StartupPath + "\Data.mdf;Integrated Security=True;User Instance=True";
con = new SqlConnection(myconn);
con.open(); cmd.Connection = con;
try
{
//cmd = new SqlCommand("select name from comp",con);
MessageBox.Show("Connect Success","Database",MessageBoxButtons.OKCancel);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}

insert statement

C#

private void btnInsert_Click(object sender, EventArgs e)
        {
            cmd.CommandText = insert into comp values ("'" + tbNo.Text + ",'" + tbName.Text + "') ";
            cmd.CommandType = cmd.CommandType.CommandText;
            try
            {
                cmd.ExecuteNonQuery();
                MessageBox.Show("Insert Done...", "Insert");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Error");
            }
        }

Nigam Patel · Accepted Answer · 2012-01-01T21:41:00

Solution 2

It may possible that insert ,update and delete is not perform into you connection sting database.

Please check app_data folder are you able to find any duplicate database.

Posted 1-Jan-12 21:41pm

Nigam Patel

Comments

s1a2b3 2-Jan-12 3:59am

where it is located?, i am using visual c# not asp.net.

Nigam Patel 2-Jan-12 6:36am

check it on your project output folder may you will find it. let me know if you are not able to solve the issue.

Un_NaMeD · Accepted Answer · 2012-01-01T21:14:00

Solution 1

Just guessing...

After your operations(insert, update) done properly, Have you tried closing connection btw. your database?

con.Close();

Operations may be successful, but cause of not closing it may not affect the database.

Posted 1-Jan-12 21:14pm

Un_NaMeD

Comments

s1a2b3 2-Jan-12 3:26am

Yup, Doing
Con.close();
same goes after close the connection.

Un_NaMeD 2-Jan-12 3:51am

Are you totally sure your commands are proper?
You've written for insert:
"insert into comp values (" + tbNo.Text + ",'" + tbName.Text + "')";
And for update:
cmd.CommandText = "Update comp SET name='" + tbName.Text + "' where no=" + tbNo.Text + " ";

apostrophe(') mismatching in 2 commands..

Also, you should be sure you really open the connection.

s1a2b3 2-Jan-12 4:09am

open connection surely, wait i place another full code which i tried...

Insert, Update and Delete Query not working, Why?

6 solutions

Solution 5

Solution 3

Solution 4

Solution 2

Solution 6

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us