Home > front end >  How to save data when i add in datagridview into database sql server
How to save data when i add in datagridview into database sql server

Time:11-17

First, I'm really sorry about my English pronounciation. I have a datagridview, and i connect it to sql server which has been dip into my project in visual studio. However, when i save info in datagridview, it works but in database it is not saved in my database in sql server. Can you help me, please

And this is my code:



using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Lab3_Bai09
{
    public partial class Form1 : Form
    {
        SqlConnection cnn = null;
        string connetionString;
        public Form1()
        {
            InitializeComponent();
            connetionString = "Data Source=(LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\QLSV.mdf;Integrated Security=True";
            cnn = new SqlConnection(connetionString);
            cnn.Open();
            cnn.Close();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            txtB2.Text = txtB1.Text;
        }
        private void button2_Click(object sender, EventArgs e)
        {
            txtB1.Text = txtB2.Text;
        }
        private void Save_Click(object sender, EventArgs e)
        {
            cnn.Open();
            SqlCommand sqlCommand;
            SqlDataAdapter adapter = new SqlDataAdapter();
            string sql = "";
            string sex = "";
            if(checkBox1.Checked == true)
            {
                sex = "Nam";
            }
            else
            {
                sex = "Nu";
            }
            string[] subjectList = txtB1.Text.Split('\n');
            int countSubject = subjectList.Length;
            sql = $"insert into SINHVIEN(MSSV, HOTEN, CHUYENNGANH, GIOITINH, SOMON) values ('{textBox1.Text}', '{textBox2.Text}', '{comboBox1.Text}', '{sex}', '{countSubject}')";
            sqlCommand = new SqlCommand(sql, cnn);
            adapter.InsertCommand = new SqlCommand(sql, cnn);
            adapter.InsertCommand.ExecuteNonQuery();
            MessageBox.Show("Đã thêm mới dữ liệu thành công!");
            this.sINHVIENTableAdapter1.Fill(this.qLSVDataSet1.SINHVIEN);
            textBox1.Clear();
            textBox2.Clear();
            checkBox1.Checked = false;
            checkBox2.Checked = false;
            txtB1.Clear();
            txtB2.Clear();
            cnn.Close();    
        }
        private void Delete_Click(object sender, EventArgs e)
        {
            cnn.Open();
            DialogResult result = MessageBox.Show("Bạn có muốn xóa dòng đã chọn?", "Delete", MessageBoxButtons.YesNoCancel);
            if (result == DialogResult.Yes)
            {
                SqlCommand sqlCommand;
                SqlDataAdapter adapter = new SqlDataAdapter();
                string sql = "";
                int selectedrowindex = dataGridView1.SelectedCells[0].RowIndex;
                DataGridViewRow selectedRow = dataGridView1.Rows[selectedrowindex];
                string c = selectedRow.Cells[0].Value.ToString();
                sql = $"delete from SINHVIEN where MSSV = {c}";
                sqlCommand = new SqlCommand(sql, cnn);
                adapter.InsertCommand = new SqlCommand(sql, cnn);
                adapter.InsertCommand.ExecuteNonQuery();
                MessageBox.Show("Xóa dữ liệu thành công");
                this.sINHVIENTableAdapter1.Fill(this.qLSVDataSet1.SINHVIEN);
            }
            cnn.Close();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'qLSVDataSet1.SINHVIEN' table. You can move, or remove it, as needed.
            this.sINHVIENTableAdapter1.Fill(this.qLSVDataSet1.SINHVIEN);
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (e.CloseReason == CloseReason.UserClosing)
            {
                DialogResult result = MessageBox.Show("Bạn có muốn thoát?", "Exit", MessageBoxButtons.YesNo);
                if (result == DialogResult.Yes)
                {
                    Application.Exit();
                }
                else
                {
                    e.Cancel = true;
                }
            }
            else
            {
                e.Cancel = true;
            }
        }
    }
}


CodePudding user response:

It looks like you are just pulling values from text boxes and a combo box. There is a line for this.sINHVIENTableAdapter1.Fill(this.qLSVDataSet1.SINHVIEN); but where are those declared? I don't see you calling anything that writes the data back to the database from that adapter or dataset. After you declare the sqlCommand, you write a new instance of an sql command into the adapter, do you mean to do that?

sqlCommand = new SqlCommand(sql, cnn);
adapter.InsertCommand = new SqlCommand(sql, cnn);
adapter.InsertCommand.ExecuteNonQuery();

I will say that you are leaving yourself wide open for SQL injection. The SQL INSERT should be handled more like this:

sql = $"insert into SINHVIEN(MSSV, HOTEN, CHUYENNGANH, GIOITINH, SOMON) values (@mssv, @hoten, @chuy, @sex, @countsub)";
sqlCommand = new SqlCommand(sql, cnn);
sqlCommand.Parameters.AddWithValue("@mssv", textBox1.Text);
sqlCommand.Parameters.AddWithValue("@hoten", comboBox1.Text);
sqlCommand.Parameters.AddWithValue("@chuy", textBox2.Text);
sqlCommand.Parameters.AddWithValue("@sex", sex);
sqlCommand.Parameters.AddWithValue("@countSub", countSubject);
adapter.InsertCommand = sqlCommand;
adapter.InsertCommand.ExecuteNonQuery();

See this post for how to bind a datagridview to a datatable: How to bind Dataset to DataGridView in windows application.

  • Related