none
Generating one number with multiple rows in datagridview RRS feed

  • Question

  • I am trying to create a billing form. I have two tables in sql-server. The text boxes should insert the date to table 1 and datagridview to table 2.

    I am not getting any errors but I have a text box which generates auto number, and under each number there should be multiple rows in datagridview.

    The code below is saving only the last row in the table.here is the code

    con = new SqlConnection("data source=localhost;initial catalog=testDb;integrated security=true"); con.Open(); cmd = new SqlCommand(@"select CashirReport.ReportId,CashirReport.ReportNumber,CashirReport.ReportDate , CashirReportDetails.SerialNumber, CashirReportDetails.RefDetails,CashirReportDetails.Amount from CashirReport inner join CashirReportDetails on CashirReport.ReportId=CashirReportDetails.ReportDetailsNumber", con); da = new SqlDataAdapter(); da.SelectCommand = cmd; dt = new DataTable(); da.Fill(dt); } private void btnsave_Click(object sender, EventArgs e) { for (int i = 0; i < dgvDetails.Rows.Count - 1; i++) { cmd = new SqlCommand(@"insert into CashirReport(ReportDate,Total)values(@ReportDate,@Total); insert into CashirReportDetails(SerialNumber,RefDetails,Amount)values(@SerialNumber,@RefDetails,@Amount)",con); cmd.Parameters.AddWithValue("@ReportDate", SqlDbType.Date).Value = dtDate.Value.Date; cmd.Parameters.AddWithValue("@Total", SqlDbType.Money).Value = txtAmount.Text; cmd.Parameters.AddWithValue("@SerialNumber", dgvDetails.Rows[i].Cells[0].Value); cmd.Parameters.AddWithValue("@RefDetails", dgvDetails.Rows[i].Cells[1].Value); cmd.Parameters.AddWithValue("@Amount", dgvDetails.Rows[i].Cells[2].Value); } cmd.ExecuteNonQuery();



    Wednesday, July 4, 2018 8:11 AM

All replies

  • Hello,

    I would recommend only executing one insert at a time, check for errors, if none then move to the next insert. For example in the following post pretty much the same question is asked but not in a for/next yet with little change this will work with a for/next.

    It can be better by adding a transaction so if any insert fails everything is rolled back e.g. if you expected to add 20 records and one failed after adding 15 then the 15 would be undone. Also, on the ExecuteNonQuery you can assign a variable, check the result, it did work, did it fail without error?

    In regards to exception handling, here there is none but place holders in the two catchs for you to decide.

    private void button2_Click(object sender, EventArgs e)
    {
        string Qry1 = "INSERT INTO tbladdbook(fBookTitle,fAuthor,fBookYr,fEdition,fPublication,fAccNo,fCallNo,fCategory,fBarCodeNo,fCurrentCopies) " + 
                        "VALUES (@Title, @Author, @BookYr, @Edition, @Publication, @AccNo, @CallNo, @Category, @BarCode, @Copies)";
    
        string Qry2 = "INSERT INTO tbltruecopies(fBookTitle, fAuthor, fBarCodeNo, fTrueCopies) " + 
                        "VALUES (@Title, @Author, @Barcode, @Copies)";
    
        using (var cn = new SqlConnection("TODO"))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = cn;
    
                for (var index = 0; index < dgvDetails.Rows.Count - 1; index++)
                {
                    cmd.CommandText = Qry2;
                    cmd.Parameters.AddWithValue("@Title", txtTITLE.Text);
                    cmd.Parameters.AddWithValue("@Author", txTAUTHOR.Text);
                    cmd.Parameters.AddWithValue("@Barcode", txtBARCODE.Text);
                    cmd.Parameters.AddWithValue("@Copies", txtCOPIES.Text);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                            /* your error handling */
                    }
    
                    cmd.CommandText = Qry1;
                    cmd.Parameters.AddWithValue("@BookYr", txtBOOKYR.Text);
                    cmd.Parameters.AddWithValue("@Edition", txtEDITION.Text);
                    cmd.Parameters.AddWithValue("@Publication", txtPUBLICATION.Text);
                    cmd.Parameters.AddWithValue("@AccNo", txtACCESSNO.Text);
                    cmd.Parameters.AddWithValue("@CallNo", txtCALLNO.Text);
                    cmd.Parameters.AddWithValue("@Category", txtCATEGORY.SelectedItem);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                            /* your error handling */
                    }
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, July 4, 2018 9:01 AM
    Moderator
  • Hi MAHDI SEDAQAT,

    According to your description and related code, it seems that there is a associated field between two table which named ReportId and ReportDetailsNumber when you insert into detail table, I would suggest that you could insert ReportDetailsNumber value into the table.

    Here is a complete sample which use transaction. 

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public partial class Form13 : Form
        {
            private SqlCommand cmd;
            private SqlDataAdapter da;
            private SqlConnection con;
            private DataTable dt;
    
            private const string connstr = @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=NormalTable;Integrated Security=True";
    
            public Form13()
            {
                InitializeComponent();
            }
    
            private void Form13_Load(object sender, EventArgs e)
            {
                BindData();
            }
    
            private void BindData()
            {
                using (con = new SqlConnection(connstr))
                {
    
                    con.Open();
                    cmd = new SqlCommand(@"select CashirReport.ReportId,CashirReport.ReportNumber,CashirReport.ReportDate ,
                CashirReportDetails.SerialNumber,
                CashirReportDetails.RefDetails,CashirReportDetails.Amount from CashirReport
                inner join CashirReportDetails on CashirReport.ReportId=CashirReportDetails.ReportDetailsNumber", con);
                    da = new SqlDataAdapter();
                    da.SelectCommand = cmd;
                    dt = new DataTable();
                    da.Fill(dt);
                }
                this.dgvDetails.DataSource = dt;
            }
    
            private void btnsave_Click(object sender, EventArgs e)
            {
    
                using (con = new SqlConnection(connstr))
                {
                    con.Open();
                    SqlTransaction sqlTran = con.BeginTransaction();
                    cmd = con.CreateCommand();
                    cmd.Transaction = sqlTran;
                    try
                    {
                        cmd.CommandText = @"insert into CashirReport(ReportDate,Total)values(@ReportDate,@Total)";
                        cmd.Parameters.AddWithValue("@ReportDate", SqlDbType.Date).Value = dtDate.Value.Date;
                        cmd.Parameters.AddWithValue("@Total", SqlDbType.Money).Value = txtAmount.Text;
                        cmd.ExecuteNonQuery();
    
                        cmd.Parameters.Clear();
                        cmd.CommandText = "SELECT @@IDENTITY";
    
                        int newID = Convert.ToInt32(cmd.ExecuteScalar());
    
                        for (int i = 0; i < dgvDetails.Rows.Count - 1; i++)
                        {
                            cmd.Parameters.Clear();
                            cmd.CommandText = @"insert into CashirReportDetails(SerialNumber,RefDetails,Amount, ReportDetailsNumber)values(@SerialNumber,@RefDetails,@Amount, @ReportDetailsNumber)";
    
                            cmd.Parameters.AddWithValue("@SerialNumber", dgvDetails.Rows[i].Cells[0].Value);
                            cmd.Parameters.AddWithValue("@RefDetails", dgvDetails.Rows[i].Cells[1].Value);
                            cmd.Parameters.AddWithValue("@Amount", dgvDetails.Rows[i].Cells[2].Value);
                            cmd.Parameters.AddWithValue("@ReportDetailsNumber", newID);
                            cmd.ExecuteNonQuery();
                        }
                        sqlTran.Commit();
                    }
                    catch (Exception ex1)
                    {
    
                        sqlTran.Rollback();
    
                    }
                }
    
                    BindData();
            }
        }
    }
    

    Best regards,

    Zhanglong


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, July 5, 2018 2:40 AM
    Moderator