Insert form data with DataGridView rows to Database using C# and VB.Net in Windows Forms Application

Hi sujayanand,

Will you please explain in more detail about your requirement?

Hi sujayanand,

Do you mean that you have DataGrid and some TextBoxes and ComboBox values in your form and you want to save all the data in database on button click?

If not then please elaborate the term "form value".

Hi sujayanand,

I have created sample code which full-fill your requirement. So please refer the below code and modify as per your requirement.

C#

private void BindProduct()
{
    DataTable dt = new DataTable();
    dt.Columns.Add("SrNo");
    dt.Columns.Add("Product");
    dt.Columns.Add("Rate");
    dt.Columns.Add("Discount");
    dt.Columns.Add("Price");
    DataRow dr = dt.NewRow();
    dataGridView1.DataSource = dt;
}
private void button1_Click(object sender, EventArgs e)
{
    object id;
    string constring = @"Data Source=.\SQL2005;Initial Catalog=test;User id = sa;password=pass@123";
    using (SqlConnection con = new SqlConnection(constring))
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Invoice Values(@Date,@Inovice,@EstimateNo,@Name)" + "SELECT SCOPE_IDENTITY()", con))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = txtDate.Text;
            cmd.Parameters.AddWithValue("@Inovice", txtInvoice.Text);
            cmd.Parameters.AddWithValue("@EstimateNo", txtEstimate.Text);
            cmd.Parameters.AddWithValue("@Name", txtName.Text);
            con.Open();
            id = cmd.ExecuteScalar();
            con.Close();
        }

        foreach (DataGridViewRow row in dataGridView1.Rows)
        {
            if (row.Cells[0].Value != null)
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO InvoiceDetails Values(@CustomerId,@Srno,@Product,@Rate,@Discount,@Price)", con))
                {
                    cmd.Parameters.AddWithValue("@CustomerId", 1);
                    cmd.Parameters.AddWithValue("@Srno", row.Cells[0].Value);
                    cmd.Parameters.AddWithValue("@Product", row.Cells[1].Value);
                    cmd.Parameters.AddWithValue("@Rate", row.Cells[2].Value);
                    cmd.Parameters.AddWithValue("@Discount", row.Cells[3].Value);
                    cmd.Parameters.AddWithValue("@Price", row.Cells[4].Value);
                    cmd.CommandType = CommandType.Text;
                    con.Open();
                    cmd.ExecuteNonQuery();
                    con.Close();
                }
            }
        }
    }

    lblmsg.Text = "Record Inserted Successfully";
}

Vb.Net

Private Sub BindProduct()
	Dim dt As New DataTable()
	dt.Columns.Add("SrNo")
	dt.Columns.Add("Product")
	dt.Columns.Add("Rate")
	dt.Columns.Add("Discount")
	dt.Columns.Add("Price")
	Dim dr As DataRow = dt.NewRow()
	dataGridView1.DataSource = dt
End Sub
Private Sub button1_Click(sender As Object, e As EventArgs)
	Dim id As Object
	Dim constring As String = "Data Source=.\SQL2005;Initial Catalog=test;User id = sa;password=pass@123"
	Using con As New SqlConnection(constring)
		Using cmd As New SqlCommand("INSERT INTO Invoice Values(@Date,@Inovice,@EstimateNo,@Name)" + "SELECT SCOPE_IDENTITY()", con)
			cmd.CommandType = CommandType.Text
			cmd.Parameters.Add("@Date", SqlDbType.DateTime).Value = txtDate.Text
			cmd.Parameters.AddWithValue("@Inovice", txtInvoice.Text)
			cmd.Parameters.AddWithValue("@EstimateNo", txtEstimate.Text)
			cmd.Parameters.AddWithValue("@Name", txtName.Text)
			con.Open()
			id = cmd.ExecuteScalar()
			con.Close()
		End Using

		For Each row As DataGridViewRow In dataGridView1.Rows
			If row.Cells(0).Value IsNot Nothing Then
				Using cmd As New SqlCommand("INSERT INTO InvoiceDetails Values(@CustomerId,@Srno,@Product,@Rate,@Discount,@Price)", con)
					cmd.Parameters.AddWithValue("@CustomerId", 1)
					cmd.Parameters.AddWithValue("@Srno", row.Cells(0).Value)
					cmd.Parameters.AddWithValue("@Product", row.Cells(1).Value)
					cmd.Parameters.AddWithValue("@Rate", row.Cells(2).Value)
					cmd.Parameters.AddWithValue("@Discount", row.Cells(3).Value)
					cmd.Parameters.AddWithValue("@Price", row.Cells(4).Value)
					cmd.CommandType = CommandType.Text
					con.Open()
					cmd.ExecuteNonQuery()
					con.Close()
				End Using
			End If
		Next
	End Using

	lblmsg.Text = "Record Inserted Successfully"
End Sub

SqlTable

CREATE TABLE Invoice(
Id INT IDENTITY PRIMARY KEY NOT NULL
,[Date] DateTIME 
,InvoiceNo INT
,EstimateNo INT
,Name VARCHAR(50)
);

CREATE TABLE InvoiceDetails( 
 Id INT IDENTITY PRIMARY KEY NOT NULL
,CustomerId INT 
,Srno INT
,Product VARCHAR(50)
,Rate INT
,Discount INT
,Price INT
);

Screenshot