Visual Studio 2008中更新SQL数据库的问题 [英] problem of update SQL database in Visual studio 2008

问题描述

我在C＃2008中有一个SQL数据库，并尝试再添加一行然后更新它。

代码如下，通过显示最新的行成功添加了新行。但是，一旦我检查数据库表，它实际上根本没有改变，即没有任何新添加的行最终保存到数据表中。

这对我来说似乎很奇怪。我搜索过互联网，有人遇到了同样的问题，但到目前为止还没有解决方案。

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.

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!

推荐答案

首先发生的事情是我是你将IDMax设置为不是ID列的最高值，而是设置为数据库中的行数。



不会选择MAX（ID）从表更准确？ （甚至更好，在Db中有一个自动增量Id字段，所以你不必自己设置它）



如果我有这个问题我会停止在更新行的调试和检查Id的值（我也会检查我的列名称是否真的是Discription而不是说明） - 甚至可能将它更改为你知道不在Db中的值


还要检查你要插入的行的rowstate。

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.

当一个单元格的数据在其中时，这是我的Datagridview已更改。

this is my Datagridview when one cell''s data in it is changed.

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:

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!

 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();

更改



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;





似乎DataDirectory导致数据库不能更新!!我不明白原因，但它解决了这个问题。

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.

这篇关于Visual Studio 2008中更新SQL数据库的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！