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

Question

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

Answer 1

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.

Answer 2

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