none
Data is not updated in SQL within SqlDataAdapter.Update statement RRS feed

  • Question

  •  

    I need to insert new rows in my database. I'm a beginner at .Net. And just practising now before use it in my program.

    Sql database containes 1 table named Phrases. Fields: PhraseID (Identity), PhraseCode, PhraseAnswers. There are 5 records in it.

    This is my code

     

    Code Snippet

    static void Main()

    {

    SqlConnection conn = new SqlConnection();

    conn.ConnectionString = "Data Source = OL4IK\\SQLEXPRESS; Database = phrase; Integrated Security = True";

     

    SqlDataAdapter da = new SqlDataAdapter();

    System.Data.DataSet ds = new System.Data.DataSet();

     

    da.SelectCommand = new SqlCommand("SELECT * FROM Phrases", conn);

    da.InsertCommand = new SqlCommand("INSERT INTO Phrases (PhraseCode,PhraseAnswers) VALUES (@code,@answers)", conn);

    da.InsertCommand.Parameters.Add("@code", SqlDbType.VarChar, 15, "PhraseCode");

    da.InsertCommand.Parameters.Add("@answers", SqlDbType.VarChar, 15, "PhraseAnswers");

     

    da.Fill(ds);

     

    DataRow row = ds.Tables[0].NewRow();

    row[1] = "Olena";

    row[2] = "Ganzhenko";

    ds.Tables[0].Rows.Add(row);

    da.Update(ds.Tables[0]);

    }

     

     

    When I execute this it add 6th row to my table and I can see in the debugger that new row gets state "Unchanged". In Database explorer there are no changes. When I execute it again I can see total amount of record 5!!! Not 6!

    If I try to access database with SQL Server Management Studio I can see one new row in there. I tried to execute code again with another values. It just has put new values instead of previous like if I used UPDATE sql statement!

    What does wrong with it???

    Help me please.

    Monday, August 6, 2007 5:34 PM

Answers

  • I've fixed it! :-)

    The problem was that my SQL database in the Server Management Studio was referenced to the .mdf file in Debug folder. So debugger each time copied blank database to Debug folder. And I've just changed reference to the project folder.

    Now it works very nice!

    Thank you, all!
    Tuesday, August 7, 2007 2:23 AM
  •  

     

    The main problem was that I've made reference in my code to the database file in Debug folder. So each time it was replaced by original version. Be careful with that. I couldn't figure out that for couple days.

     

    Here I will put some parts of my code with comments. I hope it will help you.

     

    This function how I created DataSet:

     

    Code Block

            public static System.Data.DataSet CreateDataSet(string query)
            {
                SqlConnection conn = new SqlConnection();
                System.Data.DataSet DS = new System.Data.DataSet();
                SqlDataAdapter DA = new SqlDataAdapter();
                SqlCommand Select = new SqlCommand();

     

                // conn - is my Connection String. Check it carefully.

                // I had problems with a correct string

                conn.ConnectionString = "Data Source = OL4IK\\SQLEXPRESS; Database = Phrases; Integrated Security = True";
                Select.CommandText = query;  // query is a command from the main program
                Select.Connection = conn;
                DA.SelectCommand = Select;
                DA.Fill(DS);

                return DS;           
            }

     

     

     Here is part of code from main program. How it works in general.

    I want to note that updates are made directly to the database (not to dataset). Dataset is used to get information and then process it.

     

    Code Block

                string query;
                string answers;

     

    // Here I create query-string for my SQL database
                query = "SELECT PhraseAnswers FROM Phrases WHERE PhraseID = " + questionNumber;

     

    // Now it creates dataset with the rows I need. This is information

    // I want to process. I use function I mentioned before 
                System.Data.DataSet DS = CreateDataSet(query);
                System.Data.DataTableReader DR = DS.CreateDataReader(DS.Tables[0]);

     

    /* Here it gets information from dataset with the help of method of DataTableReader - DR.Read(). And then put values I need to my variables like this - answers = (string)DR.GetValue(0);

    .....some code ..... */


     

    // The update part of code. The way I do it :-)))

                SqlCommand Update = new SqlCommand();
                SqlConnection conn = new SqlConnection();

     

    // New query for my SQL database
                query = "UPDATE Phrases SET PhraseAnswers = '" + answers
                    + "' WHERE PhraseID = " + questionNumber;

    // again this connection string            
                conn.ConnectionString = "Data Source = OL4IK\\SQLEXPRESS; Database = Phrases; Integrated Security = True";

     

    // Set properties for the Update method
                Update.Connection = conn;
                Update.CommandText = query;

                conn.Open();
                Update.ExecuteNonQuery(); // Finally!
                conn.Close();

     

     

    OK, that's it.

     

    I hope this will help you in finding solution!

    Please, give me feedback if it was helpful.

     

    Good luck to you!

    Tuesday, January 1, 2008 5:31 PM

All replies

  • Please! Somebody help me!!! I'll eally appreciate this.

    Thank you

    Monday, August 6, 2007 11:34 PM
  • It's a while since I used DataSets so I can't really shed much light on the problem.

    However I'd recommend that if you're going to use DataSets you should use typed DataSets as they're a lot nicer to work with. For example you wouldn't have to use "row[1]" and "row[2]"; the datarows would have properties with the appropriate names, i.e. PhraseCode and PhraseAnswers.

    Alternatively you can try my Foundation product. It's free to use for up to 20 tables, and as you only have one table you should be fine for the forseeable future.

    Instead of the code you have there you'd do something like this:

    Code Snippet

    ITATDbContext.DefaultDbContext = new ITATDbContext("Data Source = OL4IK\\SQLEXPRESS; Database = phrase; Integrated Security = True");

    Phrases newPhrase = new Phrases();
    newPhrase.PhraseCode = "Olena";
    newPhrase.PhraseAnswers = "Ganzhenko";

    newPhrase.Save();



    If you wanted a list of all of the Phrases you'd do:

    Code Snippet

    ITATEntitySet<Phrases> phrases = Phrases.GetAll();



    Regards,

    Sean
    Tuesday, August 7, 2007 12:20 AM
  • I've fixed it! :-)

    The problem was that my SQL database in the Server Management Studio was referenced to the .mdf file in Debug folder. So debugger each time copied blank database to Debug folder. And I've just changed reference to the project folder.

    Now it works very nice!

    Thank you, all!
    Tuesday, August 7, 2007 2:23 AM
  • HI

     

    How did u solve ur problem with updating SQl from SqldataAdapater.update statement? I have the same problem. Will u pls tell me what to do to solve this proble. In my case, dataset is updating perfectly, which I can see , but the database is not updating although I write the code SqlDataAdapter.update(dataset).

    pls help me.

     

    thanks

     

    Thursday, December 27, 2007 10:25 PM
  •  

     

    The main problem was that I've made reference in my code to the database file in Debug folder. So each time it was replaced by original version. Be careful with that. I couldn't figure out that for couple days.

     

    Here I will put some parts of my code with comments. I hope it will help you.

     

    This function how I created DataSet:

     

    Code Block

            public static System.Data.DataSet CreateDataSet(string query)
            {
                SqlConnection conn = new SqlConnection();
                System.Data.DataSet DS = new System.Data.DataSet();
                SqlDataAdapter DA = new SqlDataAdapter();
                SqlCommand Select = new SqlCommand();

     

                // conn - is my Connection String. Check it carefully.

                // I had problems with a correct string

                conn.ConnectionString = "Data Source = OL4IK\\SQLEXPRESS; Database = Phrases; Integrated Security = True";
                Select.CommandText = query;  // query is a command from the main program
                Select.Connection = conn;
                DA.SelectCommand = Select;
                DA.Fill(DS);

                return DS;           
            }

     

     

     Here is part of code from main program. How it works in general.

    I want to note that updates are made directly to the database (not to dataset). Dataset is used to get information and then process it.

     

    Code Block

                string query;
                string answers;

     

    // Here I create query-string for my SQL database
                query = "SELECT PhraseAnswers FROM Phrases WHERE PhraseID = " + questionNumber;

     

    // Now it creates dataset with the rows I need. This is information

    // I want to process. I use function I mentioned before 
                System.Data.DataSet DS = CreateDataSet(query);
                System.Data.DataTableReader DR = DS.CreateDataReader(DS.Tables[0]);

     

    /* Here it gets information from dataset with the help of method of DataTableReader - DR.Read(). And then put values I need to my variables like this - answers = (string)DR.GetValue(0);

    .....some code ..... */


     

    // The update part of code. The way I do it :-)))

                SqlCommand Update = new SqlCommand();
                SqlConnection conn = new SqlConnection();

     

    // New query for my SQL database
                query = "UPDATE Phrases SET PhraseAnswers = '" + answers
                    + "' WHERE PhraseID = " + questionNumber;

    // again this connection string            
                conn.ConnectionString = "Data Source = OL4IK\\SQLEXPRESS; Database = Phrases; Integrated Security = True";

     

    // Set properties for the Update method
                Update.Connection = conn;
                Update.CommandText = query;

                conn.Open();
                Update.ExecuteNonQuery(); // Finally!
                conn.Close();

     

     

    OK, that's it.

     

    I hope this will help you in finding solution!

    Please, give me feedback if it was helpful.

     

    Good luck to you!

    Tuesday, January 1, 2008 5:31 PM