Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: .NET3.0 C#3.0 C# Visual-Studio , +
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 :
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 :
 
{
            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 21:52pm
s1a2b3643
Edited 1-Jan-12 23:12pm
v3
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
s1a2b3 at 2-Jan-12 4:17am
   
How to trace if its happend as what you say? what is solution?
Mika Wendelius at 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 at 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
:)
Mika Wendelius at 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 at 2-Jan-12 4:56am
   
If I write AttachDbFilename=|Data Directory|\University.mdf its shows error invalid value for key "attachdbfile" why?
Mika Wendelius at 2-Jan-12 5:03am
   
If that's you're actual connection string, try using relative path. Like ..\data and so on.
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
s1a2b3 at 2-Jan-12 4:01am
   
its goes to msgbox that display successs and then also close connection but when refresh database its not updated.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

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
 

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");
            }
        }
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
s1a2b3 at 2-Jan-12 3:59am
   
where it is located?, i am using visual c# not asp.net.
Nigam Patel at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Make following changes
 
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);
           }
 

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

 
Yogesh
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

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.
  Permalink  
Comments
s1a2b3 at 2-Jan-12 3:26am
   
Yup, Doing
Con.close();
same goes after close the connection.
Un_NaMeD at 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 at 2-Jan-12 4:09am
   
open connection surely, wait i place another full code which i tried...

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

  Print Answers RSS
0 OriginalGriff 320
1 DamithSL 265
2 CPallini 235
3 Maciej Los 190
4 Sergey Alexandrovich Kryukov 184
0 OriginalGriff 5,415
1 DamithSL 4,422
2 Maciej Los 3,820
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,911


Advertise | Privacy | Mobile
Web03 | 2.8.141216.1 | Last Updated 2 Jan 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100