none
update sql table via adaper RRS feed

  • Question

  • Hi list,

    I have loaded my data from a sql table into a datagridview using the following code:

    this.grdview.DataSource = dt; // dt is a datatable which is loaded with data from sql using connection and sql adapter

    now once I update the data in grdview, I use the following code to update the sql table:

    mydataAdapter.Update(this.grdview.DataSource);

    but C# does not like it? any idea?

    thanks

    Arvin

    • Moved by Damon Bu - MSFT Wednesday, August 21, 2013 1:29 AM It is a thread about Data Platform
    Tuesday, August 20, 2013 5:52 PM

Answers

  • Hello,

    Welcome toMSDN Support Forum.

    From your description, you want to know how to update by DataAdapter.

    If I have misunderstood, please let me know.

    Please see the sample below:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    namespace ADONET
    {
        class UpdataWithDataRow
        {
            internal void Show()
            {
                //1.Create A Connection             
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        string connectionString = "server=(localdb)\\v11.0;Integrated Security=SSPI;database=TestDataBase";
                        SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
                        connectionStringBuilder.Add("min pool size", 0);
                        connectionStringBuilder.Add("max pool size", 1);
                        SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                        connection.Open();
                        SqlCommand selectCommand = connection.CreateCommand();
                        selectCommand.CommandText = "select * from Course";
                        SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        DataRow dr = dt.NewRow();
                        dr["CourseID"] = 6;
                        dr["CourseName"] = "Chinese";
                        dt.Rows.Add(dr);
                        dt.Rows[0]["CourseName"] = "Chinese";
                        da.InsertCommand = new SqlCommand("insert Course(CourseID,CourseName) values(@CourseID,@CourseName)", connection);
                        da.InsertCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.InsertCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.UpdateCommand = new SqlCommand("update Course set CourseName = @CourseName where CourseID = @CourseID", connection);
                        da.UpdateCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.UpdateCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.Update(dt);
                        connection.Close();
                        scope.Complete();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
                Console.ReadLine();
            }
        }
    }

    You can change it to meet your needs.

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 22, 2013 9:37 AM
    Moderator

All replies

  • Hello,
    Here is a thread:

    http://social.msdn.microsoft.com/Forums/en-US/c29b7466-e6af-4d35-9e18-1bb0cc29449b/how-to-update-database-through-datarow

    It is similar with your problem.

    You can maybe find idea from it.

    Wednesday, August 21, 2013 12:01 PM
  • I'm not sure what the error is that you are getting but I would change your code and specify the DataTable instead of the DataGridView DataSource in the call to DataAdapter.Update method: 

    http://msdn.microsoft.com/en-us/library/z1z2bkx2.aspx?cs-save-lang=1&cs-lang=csharp#code-snippet-1


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Wednesday, August 21, 2013 12:11 PM
  • Hello,

    Welcome toMSDN Support Forum.

    From your description, you want to know how to update by DataAdapter.

    If I have misunderstood, please let me know.

    Please see the sample below:

    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Data.Entity;
    using System.Data.SqlClient;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Transactions;
    namespace ADONET
    {
        class UpdataWithDataRow
        {
            internal void Show()
            {
                //1.Create A Connection             
                using (TransactionScope scope = new TransactionScope())
                {
                    try
                    {
                        string connectionString = "server=(localdb)\\v11.0;Integrated Security=SSPI;database=TestDataBase";
                        SqlConnectionStringBuilder connectionStringBuilder = new SqlConnectionStringBuilder(connectionString);
                        connectionStringBuilder.Add("min pool size", 0);
                        connectionStringBuilder.Add("max pool size", 1);
                        SqlConnection connection = new SqlConnection(connectionStringBuilder.ConnectionString);
                        connection.Open();
                        SqlCommand selectCommand = connection.CreateCommand();
                        selectCommand.CommandText = "select * from Course";
                        SqlDataAdapter da = new SqlDataAdapter(selectCommand);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        DataRow dr = dt.NewRow();
                        dr["CourseID"] = 6;
                        dr["CourseName"] = "Chinese";
                        dt.Rows.Add(dr);
                        dt.Rows[0]["CourseName"] = "Chinese";
                        da.InsertCommand = new SqlCommand("insert Course(CourseID,CourseName) values(@CourseID,@CourseName)", connection);
                        da.InsertCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.InsertCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.UpdateCommand = new SqlCommand("update Course set CourseName = @CourseName where CourseID = @CourseID", connection);
                        da.UpdateCommand.Parameters.Add("@CourseID", SqlDbType.Int, 0, "CourseID");
                        da.UpdateCommand.Parameters.Add("@CourseName", SqlDbType.NVarChar, 15, "CourseName");
                        da.Update(dt);
                        connection.Close();
                        scope.Complete();
                    }
                    catch (Exception)
                    {
                        throw;
                    }
                }
                Console.ReadLine();
            }
        }
    }

    You can change it to meet your needs.

    I look forward to hearing from you.

    Best Regards.


    <THE CONTENT IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, WHETHER EXPRESS OR IMPLIED>
    Thanks
    MSDN Community Support

    Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Thursday, August 22, 2013 9:37 AM
    Moderator