Click here to Skip to main content
15,881,248 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a SQL database in C# 2008 and tried to add one more row and then update it.
The code is as follows, the new row has been successfully added by displaying the newest row. However once I check the database table, it is actually not changed at all, i.e. no any new added rows are finally saved into the data table.
This seems strange to me. I have searched the internet and someone had the same problem, however, no solutions so far.
C#
SqlConnection connGroup;
	string connectionStringGroup = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\German.mdf;Integrated Security=True;User Instance=True";
	connGroup = new SqlConnection(connectionStringGroup);

	//command
	SqlCommand commGroup = new SqlCommand();
	commGroup = new SqlCommand("SELECT * FROM GroupList", connGroup);

	//adapter and dataset
	SqlDataAdapter adapterGroup;
	adapterGroup = new SqlDataAdapter();
	
	adapterGroup.SelectCommand = commGroup;

	DataSet dataSetGroup = new DataSet();
	adapterGroup.Fill(dataSetGroup, "WordGroup");

	int rowsCount = dataSetGroup.Tables["WordGroup"].Rows.Count;
   

	MessageBox.Show(rowsCount .ToString());//the rowsCount is higher and higher after adding new rows, tha means the row has been really added

 MessageBox.Show(dataSetGroup.Tables["WordGroup"].Rows[rowsCount - 1][1].ToString());//further check the newly added row, it has been also shown correct

	//-------------add a new row in the database ------------
 
	DataRow dataRow;
	SqlCommandBuilder commandBuilder;

	dataRow = dataSetGroup.Tables["WordGroup"].NewRow();

	dataRow["Description"] = tbCommonDescriptionm.Text;//only Description field is added and plus additional ID key

	dataSetGroup.Tables["WordGroup"].Rows.Add(dataRow);

//-------------update dataset ------------

	commandBuilder = new SqlCommandBuilder(adapterGroup);
        commandBuilder.RefreshSchema();
        adapterGroup.UpdateCommand = commandBuilder.GetUpdateCommand(true);
        int n_result = adapterGroup.Update(dataSetGroup.Tables["WordGroup"]);
                dataSetGroup.AcceptChanges();

	MessageBox.Show(n_result.ToString());//it is 1, the new row has been added, but when I check the database table, not added!
Posted
Updated 21-Jan-13 11:15am
v6
Comments
F.moghaddampoor 21-Jan-13 16:02pm    
Do you submit changes to your database? after updating or if you are using datagridview do you refresh dataset?
F.moghaddampoor 21-Jan-13 16:05pm    
I mean i'm using linq to update my database and it needs submit changes.
Seraph_summer 21-Jan-13 16:46pm    
I have updated code again, and add the acceptChanges and the UpdateCommand, however, it still does not work.

First thing that occurs to me is that you are setting IDMax not to the highest value of the ID column, but to the number of rows in the database.

Would not select MAX(ID) from table be more accurate? (even better, have an auto increment Id field in the Db so you don't have to set it yourself)

If I had this issue I would be stopping in debug on the update line and checking the value of Id (I'd also check that my column name really was Discription and not Description) - maybe even change it to a value you know isn't in the Db

Also check the rowstate of the row you are trying to insert.
 
Share this answer
 
Comments
Seraph_summer 21-Jan-13 7:22am    
thanks, I agree with you on the ID suggestion. But here, the problem is different.
I found out that with more try, actually, the ID is increased one by one, when I just click the button which executes this row adding operation. that means the new rows have been sucessfully added. Only the problem is that the database is not really saved.
this is my Datagridview when one cell's data in it is changed.

C#
private void dataGridView1_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            if (!boolAdd)
            {
                if (e.RowIndex != -1)
                {
                    var db = new LINQDataContext();
                    int intCurrentRow = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells["Row"].Value.ToString());
                    try
                    {
                        string strYear = dataGridView1.Rows[e.RowIndex].Cells["Year"].Value.ToString();
                        string strMonth = dataGridView1.Rows[e.RowIndex].Cells["Month"].Value.ToString();
                        string strDay = dataGridView1.Rows[e.RowIndex].Cells["Day"].Value.ToString();
                        // MessageBox.Show(strDay);
                        string strHour = dataGridView1.Rows[e.RowIndex].Cells["Hour"].Value.ToString();
                        string strMinute = dataGridView1.Rows[e.RowIndex].Cells["Minute"].Value.ToString();
                        string strSecond = dataGridView1.Rows[e.RowIndex].Cells["Second"].Value.ToString();
                        string strRepetition = dataGridView1.Rows[e.RowIndex].Cells["Repetition"].Value.ToString();
                        string strX = dataGridView1.Rows[e.RowIndex].Cells["X"].Value.ToString();
                        string strY = dataGridView1.Rows[e.RowIndex].Cells["Y"].Value.ToString();
                        string strZ = dataGridView1.Rows[e.RowIndex].Cells["Z"].Value.ToString();
                        string strOperator = dataGridView1.Rows[e.RowIndex].Cells["Operator"].Value.ToString();
                        db.Updatetable(intCurrentRow, strYear, strMonth, strDay, strHour, strMinute, strSecond, strRepetition, strX, strY, strZ, strOperator);
                        LINQDataContext dbo = new LINQDataContext();
                        dataGridView1.DataSource = dbo.mytbls;   //Refresh Grid After Edit
                    }
                    catch { }

                }
            }
        }



My Update Procedure:

SQL
CREATE PROCEDURE dbo.Update1 @Row bigint, @Word nvarchar(50), @Mean1 nvarchar(50), @Mean2 nvarchar(50), @Mean3 nvarchar(50), @Mean4 nvarchar(50), @Example1 nvarchar(MAX), @Example2 nvarchar(MAX), @Example3 nvarchar(MAX), @Example4 nvarchar(MAX), @WordExist nvarchar(10), @Mean1Exist nvarchar(10), @Mean2Exist nvarchar(10), @Mean3Exist nvarchar(10), @Mean4Exist nvarchar(10), @Example1Exist nvarchar(10), @Example2Exist nvarchar(10), @Example3Exist nvarchar(10), @Example4Exist nvarchar(10)
AS
UPDATE info SET Row=@Row , Word=@Word , Mean1=@Mean1 , Mean2=@Mean2 , Mean3=@Mean3 , Mean4=@Mean4 , Example1=@Example1 , Example2=@Example2 , Example3=@Example3 , Example4 =@Example4 ,WordExist=@WordExist, Mean1Exist=@Mean1Exist ,Mean2Exist =@Mean2Exist ,Mean3Exist= @Mean3Exist ,Mean4Exist= @Mean4Exist ,Example1Exist= @Example1Exist ,Example2Exist= @Example2Exist , Example3Exist=@Example3Exist ,Example4Exist= @Example4Exist
WHERE Row=@Row
RETURN


Another way for updating Field by field!


C#
 var db = new MyMapDataDataContext();
var query = from ord2 in db.IsZoomTableFilledTables where ord2.Key == 1 select ord2;
           foreach (var ord2 in query)
           {
               ord2.IsZoomTableFilled6 = 1;
           }
           try
           {
               db.SubmitChanges();
           }
           catch (Exception ex)
           {
               MessageBox.Show(ex.Message);
           }
           db.Dispose();
 
Share this answer
 
v3
Comments
Seraph_summer 21-Jan-13 16:20pm    
thanks for sharing your code, I know there would be different ways to update a database, but I believe sth is wrong my approach, but I really want to find out the reason for it. As saw lot of people have met the same problem with me, it is strange that no one can tell the real reason.
F.moghaddampoor 21-Jan-13 16:24pm    
Maybe there may need to be a submitchanges function that you may forgot to do on your dataset?
Seraph_summer 21-Jan-13 16:55pm    
Can you please just try with my method, create a simple database and try on your PC and see whether it works. It may take you some minutes. I would appreciate it very much if you can help me to find out the reason. The strange thing is that I have used the same method in one of my ASP.net file and it does work!
change

string connectionStringGroup = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\German.mdf;Integrated Security=True;User Instance=True";

to

string connectionStringGroup = "Data Source=.\\SQLEXPRESS;AttachDbFilename=E:\\SynonymWorld\\SynonymWorld\\German.mdf;Integrated Security=True;User Instance=True";


it seems DataDirectory causes the database not to be updated!! I do not understand the reason, but it solve the problem.
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900