How to add Data to DataTable and to SQL Table using C#

NazHim 201 Reputation points
2021-07-10T05:08:09.633+00:00

Hi All
How to add Data to DataTable and to SQL Table using C#

i am used code is insert at below

//in StuDetForm

 public static DataTable stuDetDataTable = new DataTable("StuDet");
 public static DataSet stuDetDataSet = new DataSet("StuDetDataSet");
 public static BindingSource stuDetBindingSource = new BindingSource();
 public static SqlDataAdapter stuDetTableAdapter = new SqlDataAdapter(); 

 private void Cre_stuDetDataTable_Col()
 {
            DataColumn stuIdColumn = new DataColumn();
            stuIdColumn.DataType = System.Type.GetType("System.String");
            stuIdColumn.ColumnName = "StuId";
            stuIdColumn.AutoIncrement = false;
            stuDetDataTable.Columns.Add(stuIdColumn);

            DataColumn stuNamColumn = new DataColumn();
            stuNamColumn.DataType = System.Type.GetType("System.String");
            stuNamColumn.ColumnName = "StuId";
            stuNamColumn.AutoIncrement = false;
            stuDetDataTable.Columns.Add(stuNamColumn);

            stuDetDataTable = stuDetDataSet.Tables.Add("StuDet");

            stuDetBindingSource.DataMember = "StuDet";
            stuDetBindingSource.DataSource = stuDetDataSet;
            stuDetBindingNavigator.BindingSource = stuDetBindingSource;
            stuDetDataGridView.DataSource = stuDetBindingSource;
 }

 //in StuDetInsForm

 private void InsRec_StuDet()
 {
                DataRow StuDetNewRow;
                StuDetNewRow = StuDetForm.stuDetDataTable.NewRow();
                BanAccDetNewRow["StuId"] = "7452";
                BanAccDetNewRow["StuNam"] = "Reno";
                SqlCommandBuilder builder = new SqlCommandBuilder(StuDetForm.stuDetTableAdapter);
                StuDetForm.stuDetDataTable.Rows.Add(StuDetNewRow);
                StuDetForm.stuDetTableAdapter.Update(StuDetForm.stuDetDataSet, "StuDet");
 }

getting error
System.ArgumentException: 'Column 'StuId' does not belong to table StuDet.'

anybody can give me some example..?

with regards
NazHim

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,494 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Timon Yang-MSFT 9,576 Reputation points
    2021-07-12T06:53:11.91+00:00

    After modifying the Datatable, you can update it to the database like this:

                string connString = @"connString";  
      
                string insertString = @"insert into ScoreTable values(@id,@name,@math,@science,@english,@history)";  
                SqlCommand insertCommand = new SqlCommand(insertString);  
                insertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 10, "Name");  
                insertCommand.Parameters.Add("@math", SqlDbType.Float, 2, "Math");  
                insertCommand.Parameters.Add("@science", SqlDbType.Float, 2, "Science");  
                insertCommand.Parameters.Add("@english", SqlDbType.Float, 4, "English");  
                insertCommand.Parameters.Add("@history", SqlDbType.Float, 4, "History");  
                insertCommand.Parameters.Add("@id", SqlDbType.Int, 4, "id");  
                using (SqlConnection connection = new SqlConnection(connString))  
                {  
                    connection.Open();  
                    insertCommand.Connection = connection;  
                    using (SqlDataAdapter adapter = new SqlDataAdapter())  
                    {  
                        adapter.InsertCommand = insertCommand;  
                        var dataTable = (DataTable)stuDetBindingSource.DataSource;  
                        adapter.Update(dataTable);  
                        Console.WriteLine("Successfully to update the table.");  
                    }  
                }  
    

    Or use SqlCommandBuilder to do it more simply:

                string connString = @"connString";  
                using (SqlConnection connection = new SqlConnection(connString))  
                {  
                    connection.Open();  
                    using (SqlDataAdapter adapter = new SqlDataAdapter())  
                    {  
                        SqlCommand selectCommand = new SqlCommand(@"select * from scoreTable");  
                        selectCommand.Connection = connection;  
                        adapter.SelectCommand = selectCommand;  
                        SqlCommandBuilder cb = new SqlCommandBuilder(adapter);  
                        adapter.UpdateCommand = cb.GetUpdateCommand();  
                        var dataTable = (DataTable)stuDetBindingSource.DataSource;  
                        adapter.Update(dataTable);  
                    }  
                }  
    

    If the response is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    1 person found this answer helpful.

  2. Karen Payne MVP 35,286 Reputation points
    2021-07-10T09:52:27.943+00:00

    Here is an example with just enough to provide a solution. Note your two columns as shown should never compile as you have both column names the same name.

    using System;
    using System.Data;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class StuDetForm : Form
        {
            public StuDetForm()
            {
                InitializeComponent();
            }
    
            public static DataSet stuDetDataSet = new DataSet("StuDetDataSet");
            public static BindingSource stuDetBindingSource = new BindingSource();
    
            private void Cre_stuDetDataTable_Col()
            {
                var stuDetDataTable = new DataTable("StuDet");
    
                var stuIdColumn = new DataColumn
                {
                    DataType = typeof(string), 
                    ColumnName = "StuId"
                };
    
                var stuNamColumn = new DataColumn
                {
                    DataType = typeof(string),
                    ColumnName = "StuNam"
                };
    
                stuDetDataTable.Columns.AddRange(new[] {stuIdColumn, stuNamColumn});
    
                stuDetDataSet.Tables.Add(stuDetDataTable);
    
                stuDetBindingSource.DataMember = "StuDet";
                stuDetBindingSource.DataSource = stuDetDataSet.Tables["StuDet"];
                stuDetDataGridView.DataSource = stuDetBindingSource;
            }
    
            private void Form1_Load(object sender, EventArgs e)
            {
                Cre_stuDetDataTable_Col();
            }
    
            private void AddRow_Click(object sender, EventArgs e)
            {
                var dataTable = (DataTable) stuDetBindingSource.DataSource;
    
                DataRow StuDetNewRow;
                StuDetNewRow = dataTable.NewRow();
                StuDetNewRow["StuId"] = "7452";
                StuDetNewRow["StuNam"] = "Reno";
                dataTable.Rows.Add(StuDetNewRow);
            }
        }
    }