I want to Insert Multiple Row from Table To SqlServer but Having a Problem

Question

0.00/5 (No votes)

See more:

When I Insert the data the Last Low Insert Two Time.( suppose I insert 3 row then my code inserted 4 row in data base last row insert Two Time).

My code........

C#

 protected void btngo_Click(object sender, EventArgs e)
    {
        try
        {
            lblerror.Visible = false;
            divform.Visible = true;
            btnsave.Enabled = true;
            tblinstallment.Visible = true;
            int projectid = Convert.ToInt32(ddlproject.SelectedValue);
          
           
            ViewState["Installment"] = txtinstallment.Text.Trim();
            createtable();
        }
        catch (Exception ex) { }
    }
    protected void btnsave_Click(object sender, EventArgs e)
    {
        try
        {
            lblerror.Visible = false;
            ViewState["ItemName"] = null;
            #region DataTable

            if (dttrue == true)
            {
                table = new DataTable("Installment");

                DataColumn column1 = new DataColumn();
                column1.DataType = System.Type.GetType("System.String");
                column1.Caption = "ItemName";
                column1.ColumnName = "ItemName";

                DataColumn column2 = new DataColumn();
                column2.DataType = System.Type.GetType("System.String");
                column2.Caption = "Quantity";
                column2.ColumnName = "Quantity";

                DataColumn column3 = new DataColumn();
                column3.DataType = System.Type.GetType("System.String");
                column3.Caption = "UOM";
                column3.ColumnName = "UOM";

                DataColumn column4 = new DataColumn();
                column4.DataType = System.Type.GetType("System.String");
                column4.Caption = "Description";
                column4.ColumnName = "Description";




                table.Columns.Add(column1);
                table.Columns.Add(column2);
                table.Columns.Add(column3);
                table.Columns.Add(column4);

                dttrue = false;
            }
            #endregion
            DataRow row;
            int count = int.Parse(ViewState["Installment"].ToString());
            for (int i = 1; i <= count; i++)
            {
                DropDownList textdays;
                int uom = 0;
                int ItemName = 0;
                
                DropDownList textbp = (DropDownList)tblinstallment.FindControl("txtbasic" + i.ToString());

              

                if (textbp.SelectedIndex != 0)
                {
                    ItemName = Convert.ToInt32(textbp.SelectedIndex);
                }
                else
                {
                    
                    lblerror.Text = "Please select Item Name";
                    lblerror.Visible = true;
                    lblerror.ForeColor = Color.Red;
                    return;
                }



                ViewState["ItemName"] = ItemName;
                TotalBasicPrice = 0;
                TotalOtherCharges = 0;
                TotalPLC = 0;
                TotalIFM = 0;
                TotalDiscountAdjust = 0;
        


                textdays = (DropDownList)tblinstallment.FindControl("txtdays" + i.ToString());
                if (textdays.SelectedIndex != 0)
                {
                    uom = Convert.ToInt32(textdays.SelectedIndex);
                }
                else
                {

                    lblerror.Text = "Please select UOM";
                    lblerror.Visible = true;
                    lblerror.ForeColor = Color.Red;
                    return;
                }
            
                TextBox textIFM = (TextBox)tblinstallment.FindControl("txtifm" + i.ToString());
             
                quantity = textIFM.Text.Trim();

                TextBox textinststage = (TextBox)tblinstallment.FindControl("txtpaymentdesc" + i.ToString());
                string Description = textinststage.Text.Trim();

                                

                

               
                row = table.NewRow();
                row["ItemName"] = ItemName;
                row["UOM"] = uom;
                row["Quantity"] = quantity;
                row["Description"] = Description;
              
                table.Rows.Add(row);
            }
            ViewState["ItemName"] = null;
            DataSet ds = new DataSet();
            ds.Tables.Add(table);
            ViewState["Table"] = ds;

            TotalBasicPrice = 0;
            TotalOtherCharges = 0;
            TotalPLC = 0;
            TotalIFM = 0;
            TotalDiscountAdjust = 0;

              
           
            SaveRequisition();
            //Response.Redirect("~/Purchase/Requisition.aspx");
        }
        catch (Exception ex)
        {
            lblerror.Text = ex.ToString();
            lblerror.Visible = true;
            lblerror.ForeColor = Color.Red;
        }
      }
public void SaveRequisition()
    {
        try
        {
            if (ViewState["Table"] != null)
            {
                ds = (DataSet)ViewState["Table"];
                int count = ds.Tables[0].Rows.Count;

                objConnection = commonfunctions.GetConnection();
                if (objConnection.State != ConnectionState.Open)
                {
                    objConnection.Open();
                }
                myTransaction = objConnection.BeginTransaction();
                myCommand = new SqlCommand("sp_insert_Requisition", objConnection, myTransaction);
                myCommand.CommandType = CommandType.StoredProcedure;

                for (int i = 0; i < count; i++)
                {
                    myCommand.Parameters.Clear();

                    SqlParameter param1 = new SqlParameter("@projectid", SqlDbType.Int, 4);
                    param1.Value = Convert.ToInt32(ddlproject.SelectedValue);
                    myCommand.Parameters.Add(param1);

                    SqlParameter param2 = new SqlParameter("@DeptId", SqlDbType.Int, 4);
                    param2.Value = Convert.ToInt32(ddlpaymentplan.SelectedValue);
                    myCommand.Parameters.Add(param2);



                    SqlParameter param3 = new SqlParameter("@ItemName", SqlDbType.Int, 4);
                    param3.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["ItemName"].ToString());
                    myCommand.Parameters.Add(param3);

                    SqlParameter param4 = new SqlParameter("@Quantity", SqlDbType.Decimal, 9);
                    param4.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["Quantity"].ToString());
                    myCommand.Parameters.Add(param4);

                    SqlParameter param5 = new SqlParameter("@UOM", SqlDbType.Int, 4);
                    param5.Value = Convert.ToDecimal(ds.Tables[0].Rows[i]["UOM"].ToString());
                    myCommand.Parameters.Add(param5);

                    SqlParameter param6 = new SqlParameter("@description", SqlDbType.VarChar, 200);
                    param6.Value = (ds.Tables[0].Rows[i]["Description"].ToString());
                    myCommand.Parameters.Add(param6);

                    SqlParameter param7 = new SqlParameter("@Submitedby", SqlDbType.VarChar, 50);
                    param7.Value = TextBox1.Text;
                    myCommand.Parameters.Add(param7);

                    SqlParameter param8 = new SqlParameter("@Approvedby", SqlDbType.VarChar, 50);
                    param8.Value = TextBox2.Text;
                    myCommand.Parameters.Add(param8);

                    myCommand.ExecuteNonQuery();
                  
                }

                myCommand.ExecuteNonQuery();
                myTransaction.Commit();
                myCommand.Dispose();
                lblerror.Text = "Data save successfully"; ;
                lblerror.ForeColor = Color.Blue;
                lblerror.Visible = true;
                divform.Visible = false;
                txtinstallment.Text = "";
                ViewState["Table"] = null;
                objConnection.Close();
                ddlproject.SelectedIndex = 0;
                ddlpaymentplan.SelectedIndex = 0;

               
             

                //bindGrid();

             
                           
             
               
            }
        }
        catch (Exception ex)
        {
            btnsave.Enabled = true;
            myTransaction.Rollback();
            objConnection.Close();
            lblerror.Text = ex.Message;
            lblerror.Visible = true;
        }
    }

Posted 4-Mar-14 1:05am

Parmendra choudhary

Updated 4-Mar-14 1:50am

v3

Add a Solution

2 solutions

Solution 2

Use like this

make sure that datatable column name and table column name should be same

SqlConnection Con = new SqlConnection(@"Connectionstring");
Con.Open();
DataRow[] rowArray = dtright.Select();// dtright is datatable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Con))
{
bulkCopy.DestinationTableName = "dbo.FileUpload";
try
{
bulkCopy.WriteToServer(rowArray);
MessageBox.Show("Successfully inserted...!");
}
catch (Exception ex)
{
MessageBox.Show("Operation faild");
}
}
Con.Close();

Posted 4-Mar-14 1:12am

Amalraj Ramesh

Updated 4-Mar-14 1:21am

v3

Comments

Parmendra choudhary 4-Mar-14 7:38am

thanks for your answer but please will you you help me in my own code, I thinks Its not big problem Its miner

Amalraj Ramesh 4-Mar-14 12:54pm

see below

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Amalraj Ramesh · Accepted Answer · 2014-03-04T06:47:00

try this

protected void btngo_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
divform.Visible = true;
btnsave.Enabled = true;
tblinstallment.Visible = true;
int projectid = Convert.ToInt32(ddlproject.SelectedValue);

ViewState["Installment"] = txtinstallment.Text.Trim();
createtable();
}
catch (Exception ex) { }
}
protected void btnsave_Click(object sender, EventArgs e)
{
try
{
lblerror.Visible = false;
ViewState["ItemName"] = null;
#region DataTable

if (dttrue == true)
{
table = new DataTable("Installment");

DataColumn column1 = new DataColumn();
column1.DataType = System.Type.GetType("System.String");
column1.Caption = "ItemName";
column1.ColumnName = "ItemName";

DataColumn column2 = new DataColumn();
column2.DataType = System.Type.GetType("System.String");
column2.Caption = "Quantity";
column2.ColumnName = "Quantity";

DataColumn column3 = new DataColumn();
column3.DataType = System.Type.GetType("System.String");
column3.Caption = "UOM";
column3.ColumnName = "UOM";

DataColumn column4 = new DataColumn();
column4.DataType = System.Type.GetType("System.String");
column4.Caption = "Description";
column4.ColumnName = "Description";

table.Columns.Add(column1);
table.Columns.Add(column2);
table.Columns.Add(column3);
table.Columns.Add(column4);

dttrue = false;
}
#endregion
DataRow row;
int count = int.Parse(ViewState["Installment"].ToString());
for (int i = 1; i <= count; i++)
{
DropDownList textdays;
int uom = 0;
int ItemName = 0;

DropDownList textbp = (DropDownList)tblinstallment.FindControl("txtbasic" + i.ToString());

if (textbp.SelectedIndex != 0)
{
ItemName = Convert.ToInt32(textbp.SelectedIndex);
}
else
{

lblerror.Text = "Please select Item Name";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}

ViewState["ItemName"] = ItemName;
TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;

textdays = (DropDownList)tblinstallment.FindControl("txtdays" + i.ToString());
if (textdays.SelectedIndex != 0)
{
uom = Convert.ToInt32(textdays.SelectedIndex);
}
else
{

lblerror.Text = "Please select UOM";
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
return;
}

TextBox textIFM = (TextBox)tblinstallment.FindControl("txtifm" + i.ToString());

quantity = textIFM.Text.Trim();

TextBox textinststage = (TextBox)tblinstallment.FindControl("txtpaymentdesc" + i.ToString());
string Description = textinststage.Text.Trim();

row = table.NewRow();
row["ItemName"] = ItemName;
row["UOM"] = uom;
row["Quantity"] = quantity;
row["Description"] = Description;

table.Rows.Add(row);
}
ViewState["ItemName"] = null;
DataSet ds = new DataSet();
ds.Tables.Add(table);
ViewState["Table"] = ds;

TotalBasicPrice = 0;
TotalOtherCharges = 0;
TotalPLC = 0;
TotalIFM = 0;
TotalDiscountAdjust = 0;

SaveRequisition(table);
//Response.Redirect("~/Purchase/Requisition.aspx");
}
catch (Exception ex)
{
lblerror.Text = ex.ToString();
lblerror.Visible = true;
lblerror.ForeColor = Color.Red;
}
}
public void SaveRequisition(DataTable dt)
{
try
{
if (ViewState["Table"] != null)
{
ds = (DataSet)ViewState["Table"];
int count = ds.Tables[0].Rows.Count;

objConnection = commonfunctions.GetConnection();
if (objConnection.State != ConnectionState.Open)
{
objConnection.Open();
}
DataRow[] rowArray = dtright.Select();// dtright is datatable
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(Con))
{
bulkCopy.DestinationTableName = "YourTableName";
try
{
bulkCopy.WriteToServer(rowArray);
lblerror.Text = "Data save successfully"; ;
lblerror.ForeColor = Color.Blue;
lblerror.Visible = true;
divform.Visible = false;
txtinstallment.Text = "";
ViewState["Table"] = null;
objConnection.Close();
ddlproject.SelectedIndex = 0;
ddlpaymentplan.SelectedIndex = 0;
}
catch (Exception ex)
{
btnsave.Enabled = true;
myTransaction.Rollback();
objConnection.Close();
lblerror.Text = ex.Message;
lblerror.Visible = true;
}
}
}
}
}