SQL insert clause.

Question

SQL insert clause.

MiPakTeh 1,476

Hi All,
I just do a simple database for learning.Create Database and Table, ok but when to INSERT statement error area "Column Name" for button_4.

Here some code to trying.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace Store_6  
{  
    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
            button1.Text = "Create Database";  
            button2.Text = "Create Table";  
            button3.Text = "Data Analisis";  
            button4.Text = "Save";  
            button5.Text = "Update";  
  
  
  
        }  
  
        private void button1_Click(object sender, EventArgs e)  
        {  
            SqlConnection conn = new SqlConnection(@"server=(localdb)\v11.0");  
            using (conn)  
            {  
                conn.Open();  
                string sql = string.Format(@"CREATE DATABASE [TRIAL] ON PRIMARY (NAME=TRIAL_a, FILENAME='{0}\TRIAL.mdf')  
                            LOG ON (NAME=TRIAL_log, FILENAME='{0}\TRIAL.ldf')", @"C:\Users\family\Documents\");  
                SqlCommand command = new SqlCommand(sql, conn);  
                command.ExecuteNonQuery();  
            }  
        }  
  
        private void button2_Click(object sender, EventArgs e)  
        {  
            SqlConnection conn = new SqlConnection(@"server=(localdb)\v11.0");  
            SqlCommand command = new SqlCommand(@"CREATE TABLE Store(Lokasi varchar(200), Nitrogen decimal, Phosphate decimal, Kalium real)",conn);  
            conn.Open();  
            command.ExecuteNonQuery();  
        }  
  
        private void button3_Click(object sender, EventArgs e)  
        {  
            SqlDataAdapter da = new SqlDataAdapter("Select * from Store", @"server=(localdb)\v11.0");  
            DataSet ds = new DataSet();  
            da.Fill(ds, "Store");  
            dataGridView1.DataSource = ds.Tables["Store"].DefaultView;  
        }  
  
  
  
        private void button4_Click(object sender, EventArgs e)  
        {  
            string StrQuery = "";  
  
            for (int i = 0; i < dataGridView1.Rows.Count; i++)  
            {  
  
            SqlConnection conn = new SqlConnection(@"server=(localdb)\v11.0");  
            conn.Open();  
            SqlCommand comm = new SqlCommand(StrQuery, conn);  
  
                StrQuery = @"INSERT INTO Store VALUES ("+ dataGridView1.Rows[i].Cells[0].Value + dataGridView1.Rows[i].Cells[1].Value + dataGridView1.Rows[i].Cells[2].Value + dataGridView1.Rows[i].Cells[3].Value +")";  
  
                comm.CommandText = StrQuery;  
                comm.ExecuteNonQuery();  
            }  
  
              
  
  
        }  
  
        private void button5_Click(object sender, EventArgs e)  
        {  
  
        }  
  
        private void button6_Click(object sender, EventArgs e)  
        {  
  
        }  
  
        private void button7_Click(object sender, EventArgs e)  
        {  
  
        }  
    }  
}

Accepted answer

3 additional answers

Your answer

Answer 1

I slapped together a code sample with all that should be needed. Tested adding a record and modifying a record.

The following _ = new SqlCommandBuilder(_sqlDataAdapter); glues things together.

Code can be optimized and perhaps placed into a class method.

using System;  
using System.Data;  
using System.Data.SqlClient;  
using System.Windows.Forms;  
  
namespace DataAdapterFormApp  
{  
    public partial class Form1 : Form  
    {  
        private static string ConnectionString =  
            "Data Source=.\\sqlexpress;Initial Catalog=ForumExample;Integrated Security=True";  
  
        private SqlDataAdapter _sqlDataAdapter = new SqlDataAdapter();  
        private readonly DataSet _dataSet = new DataSet();  
        private readonly SqlConnection connection = new SqlConnection(ConnectionString);  
  
        public Form1()  
        {  
            InitializeComponent();  
            Shown += OnShown;  
        }  
  
        private void OnShown(object sender, EventArgs e)  
        {  
            _sqlDataAdapter = new SqlDataAdapter(  
                "SELECT id, FirstName, LastName, HiredDate FROM dbo.employee;",   
                connection);  
  
            _sqlDataAdapter.Fill(_dataSet);  
            _ = new SqlCommandBuilder(_sqlDataAdapter);  
            dataGridView1.DataSource = _dataSet.Tables[0];  
            dataGridView1.Columns[0].ReadOnly = true;  
        }  
  
        private void UpDateAllButton_Click(object sender, EventArgs e)  
        {  
            _sqlDataAdapter.Update(_dataSet);  
        }  
    }  
}

EDIT

If only adding records, the above can be altered to use the following SELECT statement which returns zero rows and in this case only new records (and modifications to new records are done)

SELECT TOP 0 id, FirstName, LastName, HiredDate FROM dbo.employee;

Answer 2

StrQuery = @"INSERT INTO Store VALUES ("+ dataGridView1.Rows[i].Cells[0].Value + dataGridView1.Rows[i].Cells[1].Value + dataGridView1.Rows[i].Cells[2].Value + dataGridView1.Rows[i].Cells[3].Value +")";

You concat all values to one big string, that don't work, they must be comma-separated values, see https://learn.microsoft.com/en-us/sql/t-sql/statements/insert-transact-sql?view=sql-server-ver16 => Samples.

Better use parameterized statements, see https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlparameter?view=dotnet-plat-ext-6.0

Answer 3

Karen Payne MVP 35,586 Volunteer Moderator

There is no need to write SQL as a SqlDataAdapter when used correctly does updates for you. Take time to read the documentation along with making the SqlDataAdapter a private scoped field in your form.

MiPakTeh 1,476

Help me Karen;
SqlConnection con = new SqlConnection(@"server = (localdb)\v11.0");
string qry = "SELECT * FROM Store";
SqlDataAdapter da = new SqlDataAdapter(qry, con);
//Fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds, "Store");
//Update a row in DataSet Table
DataTable dt = ds.Tables["Store"];
dt.Rows[1]["Lokasi"] = "";

            string sql = "update Store set somecolumn = 10 where ...";  
            SqlDataAdapter adapter = new SqlDataAdapter();  
            try  
            {  
                con.Open();  
                SqlCommand cmd = new SqlCommand(sql, con);  
                //select the update command  
                adapter.UpdateCommand = cmd;  
                //update the data source  
                adapter.Update(ds, "Store");  
                MessageBox.Show("DataBase updated !! ");  
            }  
            catch (Exception ex)  
            {  
                MessageBox.Show("" + ex.Message);  
                con.Close();  
            }

Answer 4

Jack J Jun 25,296

@MiPakTeh , Welcome to Microsoft Q&A, based on my test, I reproduced your problem.

First, Please set dataGridView1.AllowUserToAddRows to be false in your constructor.

Second, Please ensure that your database has the data, otherwise you will add empty row to the table.

Third, Please use string.format method in your button4_click event, like the following:

private void button4_Click(object sender, EventArgs e)  
        {  
            string StrQuery = "";  
  
            for (int i = 0; i < dataGridView1.Rows.Count; i++)  
            {  
  
                SqlConnection conn = new SqlConnection(@"str");  
                conn.Open();  
                SqlCommand comm = new SqlCommand(StrQuery, conn);  
                StrQuery = String.Format("INSERT INTO Store VALUES('{0}',{1},{2},{3})", dataGridView1.Rows[i].Cells[0].Value, dataGridView1.Rows[i].Cells[1].Value, dataGridView1.Rows[i].Cells[2].Value, dataGridView1.Rows[i].Cells[3].Value);  
                //StrQuery = @"INSERT INTO Store VALUES (" + dataGridView1.Rows[i].Cells[0].Value + dataGridView1.Rows[i].Cells[1].Value + dataGridView1.Rows[i].Cells[2].Value + dataGridView1.Rows[i].Cells[3].Value + ")";  
  
                comm.CommandText = StrQuery;  
                comm.ExecuteNonQuery();  
            }  
            Console.WriteLine("success created");  
        }

Hope my advice could help you.

Best Regards,
Jack

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

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.

MiPakTeh 1,476

Hi JackJJun,

First, Please set dataGridView1.AllowUserToAddRows to be false in your constructor. = Yes

Second, Please ensure that your database has the data, otherwise you will add empty row to the table.

        private void button3_Click(object sender, EventArgs e)  
        {  
            SqlDataAdapter da = new SqlDataAdapter("Select * from Store", @"server=(localdb)\v11.0");  
            DataSet ds = new DataSet();  
            da.Fill(ds, "Store");  
            dataGridView1.DataSource = ds.Tables["Store"].DefaultView;  
  
  
            var table = new DataTable();  
            for (int i = 0; i < 5; i++)  
            {  
                var row = table.NewRow();  
                table.Rows.Add(row);  
            }  
        }

Third, Please use string.format method in your button4_click event, like the following: =Yes
Still cannot add empty row.
Thank.

Jack J Jun 25,296 Reputation points

2022-06-14T01:51:01.647+00:00
@MiPakTeh , thanks for the feedback, how do you want to insert the records to the database? Do you want to insert the duplicated records from the database?

var table = new DataTable(); for (int i = 0; i < 5; i++) { var row = table.NewRow(); table.Rows.Add(row); }

The above code will not affect the data in the database table and I mean that you need to add some data to your database table instead of a Datatable.

Still cannot add empty row.

Therfore, Do you want to insert a empty row to the database table? It may be meaningless.
MiPakTeh 1,476 Reputation points

2022-06-14T12:54:20.093+00:00
JackJJun,

How do you want to insert the records to the database?.
By click button3 Datagridview1 appear then start key in the Record.
After that using "Insert to" by click button4.

Do you want to insert the duplicated records from the database?
No.

Do you want to insert a empty row to the database table? It may be meaningless.
When we set dataGridView1.AllowUserToAddRows = False, we need to add the empty row.

Thank JackJJun.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-06-14T14:38:57.723+00:00

See the following on how to prevent duplicate records and my Microsoft article.
Jack J Jun 25,296 Reputation points

2022-06-16T05:33:50.6+00:00

@MiPakTeh , I am still confused about your problem. In the button3_click event, you load the table Store into datagirdview. In the button4_click event, you insert the datagirdview data to table Store. Therefore, you are inserting the duplicated records into the database. Could you provide your current database data and what do you want to show in the datagirdview finally?
Olaf Helper 47,441 Reputation points

2022-06-16T06:01:44.363+00:00

StrQuery = String.Format("INSERT INTO Store VALUES('{0}',{1},{2},{3})"

@Jack J Jun , Nice string formating; in .NET/ for UI, but depending on the data type it will fail in SQL.
MiPakTeh 1,476 Reputation points

2022-06-17T04:07:25.123+00:00

Hi JackJJun ,
If we set dataGridView1.AllowUserToAddRows = False then how to add the row?

Can you show me in the code? Which button?

Thank for your help.
Jack J Jun 25,296 Reputation points

2022-06-17T05:10:45.593+00:00

@MiPakTeh ，thanks for the feedback, If you set dataGridView1.AllowUserToAddRows = False, It means that the last row of the datagirdview is not empty. Please refer to the doc link DataGridView.AllowUserToAddRows .

Then, the key problem is that do you want to insert the data to datagirdview or do you want to insert the data to table?
MiPakTeh 1,476 Reputation points

2022-06-18T07:25:16.05+00:00

Hi JackJJun, sorry send twice question.

This code can insert for only one row.How to insert the whole rows in the table.
can show me.
Thank.
Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-06-18T09:11:24.233+00:00

I find it interesting what some will do such as this rather than use parameters.

Share via

SQL insert clause.

EDIT

3 additional answers

Your answer