本站网址： YippeeSoft开心软件

本文链接: 20080220 c# datagridview

20080220 c# datagridview
http://www.yippeesoft.com

用DataGridView绑定了Access数据库，从数据源显示到datagridview上没有问题，在datagridview上添加行或删除行后更新数据库也没有问题，但在datagridview上修改行数据后更新数据库就产生异常，说是“对于不返回任何键列信息的   SelectCommand，不支持   UpdateCommand   的动态   SQL   生成。

C# code

    DataSet ds = null;SqlDataAdapter adapter = null;SqlCommandBuilder sqlbuilder = null;//该按钮读取数据,并绑定数据private void button1_Click(object sender, EventArgs e) …&leftsign; ds = new DataSet(); SqlConnection cn = new SqlConnection("Data Source=.;User ID=sa;Password=0;Initial Catalog=BMIS"); SqlCommand cm = new SqlCommand(); adapter = new SqlDataAdapter(cm); sqlbuilder = new SqlCommandBuilder(adapter); cm.Connection = cn; cm.CommandType = CommandType.Text; cm.CommandText = "select * from [providerInfo]"; adapter.Fill(ds); //该方法把得到的数据放入一个DataTable中 dataGridView1.DataSource = ds.Tables[0]; 把数据绑定到DataGridView &rightsign;//向数据库返回修改的数据.private void button2_Click(object sender, EventArgs e) …&leftsign; adapter.Update(ds); &rightsign;

这里将我知道的问题所在总结一下，希望对仍旧处于困境中的人有所帮助！
          要用自动生成命令更新数据库，我知道的有三点需要注意：
                            1、必须是单表，也就是说不能和别的表有关联
                            2、表中必须有主键。
                            3、表中字段不能为TEXT（备注）类型

       对于 SqlCommandBuilder 类, MSDN 文档中执行代码时可能会收到以下异常：
发生无法处理 system.data.dll 中 \’ System.Data.MissingPrimaryKeyException \’ 类型的异常

其他信息： 表没有主键。

public DataSet SelectSqlSrvRows(DataSet myDataSet,string myConnection,string mySelectQuery,string myTableName) &leftsign;
    SqlConnection myConn = new SqlConnection(myConnection);
    SqlDataAdapter myDataAdapter = new SqlDataAdapter();
    myDataAdapter.SelectCommand = new SqlCommand(mySelectQuery, myConn);
    SqlCommandBuilder custCB = new SqlCommandBuilder(myDataAdapter);

    myConn.Open();

    DataSet custDS = new DataSet();
   
    //Use one of the following two methods to fix the problem.
    //Using MissingSchemaAction
    myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
    //Using FillSchema
    //custDS.FillSchema(ds, SchemaType.Source, "Customers");

    myDataAdapter.Fill(custDS, "Customers");

    //Code to modify data in DataSet here

    //Without the SqlCommandBuilder this line would fail
    myDataAdapter.Update(custDS, "Customers");

    myConn.Close();

    return custDS;
 &rightsign;

 private void Form1_Load(object sender, System.EventArgs e)
    &leftsign;
        // Bind the DataGridView to the BindingSource
        // and load the data from the database.
        dataGridView1.DataSource = bindingSource1;
        GetData("select * from Customers");
    &rightsign;

    private void reloadButton_Click(object sender, System.EventArgs e)
    &leftsign;
        // Reload the data from the database.
        GetData(dataAdapter.SelectCommand.CommandText);
    &rightsign;

    private void submitButton_Click(object sender, System.EventArgs e)
    &leftsign;
        // Update the database with the user\’s changes.
        dataAdapter.Update((DataTable)bindingSource1.DataSource);
    &rightsign;

    private void GetData(string selectCommand)
    &leftsign;
        try
        &leftsign;
            // Specify a connection string. Replace the given value with a
            // valid connection string for a Northwind SQL Server sample
            // database accessible to your system.
            String connectionString =
                "Integrated Security=SSPI;Persist Security Info=False;" +
                "Initial Catalog=Northwind;Data Source=localhost";

            // Create a new data adapter based on the specified query.
            dataAdapter = new SqlDataAdapter(selectCommand, connectionString);

            // Create a command builder to generate SQL update, insert, and
            // delete commands based on selectCommand. These are used to
            // update the database.
            SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

            // Populate a new data table and bind it to the BindingSource.
            DataTable table = new DataTable();
            table.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataAdapter.Fill(table);
            bindingSource1.DataSource = table;

            // Resize the DataGridView columns to fit the newly loaded content.
            dataGridView1.AutoResizeColumns(
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
        &rightsign;
        catch (SqlException)
        &leftsign;
            MessageBox.Show("To run this example, replace the value of the " +
                "connectionString variable with a connection string that is " +
                "valid for your system.");
        &rightsign;
    &rightsign;

原创文章，转载请注明： 转载自YippeeSoft开心软件

本文链接地址: 20080220 c# datagridview

历史博文

标签：, , ,