Не удалось преобразовать значение параметра из строки в Int32.

---

Всем Привет,

При редактировании в моем приложении мне не удается преобразовать значение параметра из строки в Int32. Пожалуйста, помогите мне разобраться с этим.

заранее спасибо

protected void btnEdit_Click(object sender, ImageClickEventArgs e)
    {
        try
        {
            int n = 0;
            int i = grdvwpurorder.Rows.Count;
            int j = grdviewtax.Rows.Count;
            if (i > j)
                n = i;
            else
                n = j;
            int insert = 0;
            SqlCommand cmdDel = new SqlCommand("spDeleteTblStockByStType");
            cmdDel.CommandType = CommandType.StoredProcedure;
            cmdDel.Parameters.Add("@st_type", SqlDbType.VarChar).Value = "G";
            cmdDel.Parameters.Add("@refno", SqlDbType.Int).Value = ddlInvoiceNo.SelectedValue;
            cmdDel.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"].ToString());
            cmdDel.Parameters.Add("@colname", SqlDbType.VarChar).Value = "grnno";
            int delete = ems.Execute_Sql(cmdDel, con);

            float cntQty = 0;
            for (int index = 0; index < n; index++)
            {
                SqlCommand cmd = new SqlCommand("spInsertTblImportPurchase");
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@imppurno", SqlDbType.Int).Value = txtInvoiceNo.Text;
                cmd.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"]);
                cmd.Parameters.Add("@imppurdate", SqlDbType.VarChar).Value = txtInvoiceDate.Text;
                cmd.Parameters.Add("@billno", SqlDbType.VarChar).Value = txtBillNo.Text;
                cmd.Parameters.Add("@billdate", SqlDbType.VarChar).Value = txtBillDate.Text;
                cmd.Parameters.Add("@ordno", SqlDbType.Int).Value = Convert.ToInt16(ddlsupppurord.SelectedValue);
                cmd.Parameters.Add("@orddate", SqlDbType.VarChar).Value = txtsupppodate.Text;
                cmd.Parameters.Add("@quotno", SqlDbType.Int).Value = Convert.ToInt16(txtsuppquotno.Text);
                cmd.Parameters.Add("@quotdate", SqlDbType.VarChar).Value = txtsuppquotdate.Text;
                //cmd.Parameters.Add("@prno", SqlDbType.Int).Value = Convert.ToInt16(txtsuppprno.Text);
                //cmd.Parameters.Add("@prdate", SqlDbType.VarChar).Value = txtsuppprdate.Text;
                cmd.Parameters.Add("@supp_code", SqlDbType.VarChar).Value = txtsuppcode.Text;
                cmd.Parameters.Add("@supp_name", SqlDbType.VarChar).Value = txtsuppname.Text;
                cmd.Parameters.Add("@handleby", SqlDbType.VarChar).Value = ddlHandleBy.Text;
                cmd.Parameters.Add("@approvedby", SqlDbType.VarChar).Value = txtApproved.Text;
                cmd.Parameters.Add("@ModeofDispatchId", SqlDbType.VarChar).Value = rbModeDispatch.SelectedValue;
                cmd.Parameters.Add("@modeofdispatch", SqlDbType.VarChar).Value = rbModeDispatch.SelectedItem.Text;
                //cmd.Parameters.Add("@grossamt", SqlDbType.VarChar).Value = txtGrossAmt.Text;
                //cmd.Parameters.Add("@nettotamt", SqlDbType.Decimal).Value = txtNetAmt.Text;
                //cmd.Parameters.Add("@paymode", SqlDbType.VarChar).Value = ddlPayTerms.SelectedValue;
                cmd.Parameters.Add("@typedby", SqlDbType.VarChar).Value = txtTypedBy.Text;
                //cmd.Parameters.Add("@consignee", SqlDbType.VarChar).Value = txtConsignee.Text;
                cmd.Parameters.Add("@sugamdate", SqlDbType.VarChar).Value = txtesugamdate.Text;
                //cmd.Parameters.Add("@transmode", SqlDbType.VarChar).Value = txtTransMode.Text;
                //cmd.Parameters.Add("@totalbox", SqlDbType.VarChar).Value = txtTotalBox.Text;
                cmd.Parameters.Add("@boxref", SqlDbType.VarChar).Value = txtBoxReference.Text;
                //cmd.Parameters.Add("@rdpermitno", SqlDbType.VarChar).Value = txtRoadNo.Text;
                //cmd.Parameters.Add("@vehicleno", SqlDbType.VarChar).Value = txtVehicleNo.Text;
                cmd.Parameters.Add("@sugamform", SqlDbType.VarChar).Value = txtSugamForm.Text;
                //cmd.Parameters.Add("@delivery", SqlDbType.VarChar).Value = txtDelivery.Text;
                cmd.Parameters.Add("@totalamt", SqlDbType.Decimal).Value = txtFreightCharge.Text;
                cmd.Parameters.Add("@freightchargeby", SqlDbType.VarChar).Value = rbFreight.SelectedValue;
                cmd.Parameters.Add("@filestored", SqlDbType.VarChar).Value = txtfile.Text;
                string multiattach = "";
                if (lstUpload.SelectedIndex >= 0)
                {
                    for (int m = 0; m < lstUpload.Items.Count; m++)
                    {
                        if (lstUpload.Items[m].Selected)
                        {
                            multiattach += lstUpload.Items[m].Value + ",";
                        }
                    }
                }
                if (multiattach != "")
                {

                    cmd.Parameters.Add("@enclosure", SqlDbType.VarChar).Value = multiattach.Substring(0, multiattach.Length - 1);
                }
                string scan = "";
                if (lstAttachMulti.SelectedIndex >= 0)
                {
                    for (int m = 0; m < lstAttachMulti.Items.Count; m++)
                    {
                        if (lstAttachMulti.Items[m].Selected)
                        {
                            scan += lstAttachMulti.Items[m].Value + ",";
                        }
                    }
                }
                if (scan != "")
                {

                    cmd.Parameters.Add("@multiattach", SqlDbType.VarChar).Value = scan.Substring(0, scan.Length - 1);
                }
                cmd.Parameters.Add("@transportname", SqlDbType.VarChar).Value = txtTransportName.Text;

                cmd.Parameters.Add("@transportcharges", SqlDbType.Decimal).Value = txtTransCharges.Text;
                cmd.Parameters.Add("@bankdbtval", SqlDbType.Decimal).Value = txtBankDbtVal.Text;
                cmd.Parameters.Add("@USD", SqlDbType.Decimal).Value = txtUSD.Text;
                cmd.Parameters.Add("@totalcustoms", SqlDbType.Decimal).Value = txttotcust.Text;
                cmd.Parameters.Add("@modvatbenfit", SqlDbType.Decimal).Value = txtmodbenf.Text;
                cmd.Parameters.Add("@claimamt", SqlDbType.Decimal).Value = txtClaim.Text;
                cmd.Parameters.Add("@WareHouseExp", SqlDbType.Decimal).Value = txtWareExp.Text;
                cmd.Parameters.Add("@Currency", SqlDbType.VarChar).Value = ddlInternationalCurrency.SelectedItem.Text;
                cmd.Parameters.Add("@grossweight", SqlDbType.VarChar).Value = txtgrosswt.Text;
                cmd.Parameters.Add("@netweight", SqlDbType.VarChar).Value = txtnetwt.Text;
                cmd.Parameters.Add("@Measurement", SqlDbType.VarChar).Value = txtMeasurement.Text;
                cmd.Parameters.Add("TotInvVal", SqlDbType.Decimal).Value = txtTotInvVal.Text;
                if (rbModeDispatch.SelectedValue == "1")
                {
                    string air;

                    if (ddlAir.SelectedItem.Text == "Others")
                    {

                        air = txtAir.Text;
                        try
                        {
                            SqlCommand cmdAir = new SqlCommand("spGetTblAirCheck");
                            cmdAir.CommandType = CommandType.StoredProcedure;
                            cmdAir.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtAir.Text;
                            int check = Convert.ToInt32(ems.Get_SqlValue(cmdAir, con));
                            if (check == 0)
                            {

                                SqlCommand cmd1 = new SqlCommand("spInsertTblByAir");
                                cmd1.CommandType = CommandType.StoredProcedure;
                                cmd1.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtAir.Text;
                                cmd1.Parameters.Add("@ch", SqlDbType.Int).Value = 1;
                                int success = ems.Execute_Sql(cmd1, con);
                            }
                        }
                        catch (SqlException sqlex)
                        {

                            Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
                        }

                    }
                    else
                    {
                        air = ddlAir.SelectedValue;
                    }
                    cmd.Parameters.Add("@byair", SqlDbType.VarChar).Value = air;
                    cmd.Parameters.Add("@ModeofDispatchDesc", SqlDbType.VarChar).Value = air;
                }


                if (rbModeDispatch.SelectedValue == "2")
                {

                    string ocean;

                    if (ddlOcean.SelectedItem.Text == "Others")
                    {

                        ocean = txtOcean.Text;
                        try
                        {
                            SqlCommand cmdOcean = new SqlCommand("spGetTblOceanCheck");
                            cmdOcean.CommandType = CommandType.StoredProcedure;
                            cmdOcean.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtOcean.Text;
                            int check = Convert.ToInt32(ems.Get_SqlValue(cmdOcean, con));
                            if (check == 0)
                            {

                                SqlCommand cmd1 = new SqlCommand("spInsertTblByOcean");
                                cmd1.CommandType = CommandType.StoredProcedure;
                                cmd1.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtOcean.Text;
                                cmd1.Parameters.Add("@ch", SqlDbType.Int).Value = 1;
                                int success = ems.Execute_Sql(cmd1, con);
                            }
                        }
                        catch (SqlException sqlex)
                        {

                            Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
                        }

                    }
                    else
                    {
                        ocean = ddlOcean.SelectedValue;
                    }
                    cmd.Parameters.Add("@byocean", SqlDbType.VarChar).Value = ocean;
                    cmd.Parameters.Add("@ModeofDispatchDesc", SqlDbType.VarChar).Value = ocean;
                }
                if (rbModeDispatch.SelectedValue == "3")
                {

                    string road;

                    if (ddlRoad.SelectedItem.Text == "Others")
                    {

                        road = txtRoad.Text;
                        try
                        {
                            SqlCommand cmdRoad = new SqlCommand("spGetTblRoadCheck");
                            cmdRoad.CommandType = CommandType.StoredProcedure;
                            cmdRoad.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtRoad.Text;
                            int check = Convert.ToInt32(ems.Get_SqlValue(cmdRoad, con));
                            if (check == 0)
                            {

                                SqlCommand cmd1 = new SqlCommand("spInsertTblByRoad");
                                cmd1.CommandType = CommandType.StoredProcedure;
                                cmd1.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtRoad.Text;
                                cmd1.Parameters.Add("@ch", SqlDbType.Int).Value = 1;
                                int success = ems.Execute_Sql(cmd1, con);
                            }
                        }
                        catch (SqlException sqlex)
                        {

                            Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
                        }

                    }
                    else
                    {
                        road = ddlRoad.SelectedValue;
                    }
                    cmd.Parameters.Add("@byroad", SqlDbType.VarChar).Value = road;
                    cmd.Parameters.Add("@ModeofDispatchDesc", SqlDbType.VarChar).Value = road;
                }

                if (rbModeDispatch.SelectedValue == "4")
                {
                    string train;

                    if (ddlTrain.SelectedItem.Text == "Others")
                    {

                        train = txtTrain.Text;
                        try
                        {
                            SqlCommand cmdTrain = new SqlCommand("spGetTblTrainCheck");
                            cmdTrain.CommandType = CommandType.StoredProcedure;
                            cmdTrain.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtTrain.Text;
                            int check = Convert.ToInt32(ems.Get_SqlValue(cmdTrain, con));
                            if (check == 0)
                            {

                                SqlCommand cmd1 = new SqlCommand("spInsertTblByTrain");
                                cmd1.CommandType = CommandType.StoredProcedure;
                                cmd1.Parameters.Add("@Description", SqlDbType.VarChar).Value = txtTrain.Text;
                                cmd1.Parameters.Add("@ch", SqlDbType.Int).Value = 1;
                                int success = ems.Execute_Sql(cmd1, con);
                            }
                        }
                        catch (SqlException sqlex)
                        {

                            Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
                        }

                    }
                    else
                    {
                        train = ddlTrain.SelectedValue;
                    }
                    cmd.Parameters.Add("@bytrain", SqlDbType.VarChar).Value = train;
                    cmd.Parameters.Add("@ModeofDispatchDesc", SqlDbType.VarChar).Value = train;
                }

                if (index < i)
                {
                    string itemno = Convert.ToString(grdvwpurorder.DataKeys[index].Value);
                    string itemtype = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtcatalogno")).Text;
                    //decimal rate = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtrate")).Text);
                    //decimal value = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtvalue")).Text);
                    //decimal discount = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtdiscount")).Text);
                    //decimal tax = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txttax")).Text);
                    //decimal recqty = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtqty")).Text);
                    decimal stkrate = 0;
                    //decimal stkvalue = 0;
                    //decimal totqty = 0;


                    //cntQty += float.Parse(((TextBox)grdvwpurorder.Rows[index].FindControl("txtpoqty")).Text);
                    cmd.Parameters.Add("@puorditemno", SqlDbType.VarChar).Value = itemno;
                    cmd.Parameters.Add("@catalogno", SqlDbType.VarChar).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtcatalogno")).Text;
                    cmd.Parameters.Add("@serialnos", SqlDbType.VarChar).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtserialno")).Text;
                    cmd.Parameters.Add("@qty", SqlDbType.Float).Value = Convert.ToDouble(((TextBox)grdvwpurorder.Rows[index].FindControl("txtqty")).Text);
                    cmd.Parameters.Add("@ratein$", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtdollar")).Text;
                    cmd.Parameters.Add("@rateinRs", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtRs")).Text;
                    cmd.Parameters.Add("@Frieghtinsur", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtFILC")).Text;
                    cmd.Parameters.Add("@matvalue", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtMatValue")).Text;
                    cmd.Parameters.Add("@customdutyinperc", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtpercent")).Text;
                    cmd.Parameters.Add("@customdutyinamt", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtCustomDutyAmt")).Text;
                    cmd.Parameters.Add("@total", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtvalue")).Text;
                    cmd.Parameters.Add("@txt12", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txt12")).Text;
                    cmd.Parameters.Add("@txtedu2", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtedu2")).Text;
                    cmd.Parameters.Add("@txtshe1", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtshe1")).Text;
                    cmd.Parameters.Add("@txt4", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txt4")).Text;
                    cmd.Parameters.Add("@stockvalue", SqlDbType.Decimal).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txtstockvalue")).Text;
                    cmd.Parameters.Add("@tariffcode", SqlDbType.VarChar).Value = ((TextBox)grdvwpurorder.Rows[index].FindControl("txttariff")).Text;
                    stkrate = Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtstockvalue")).Text) / Convert.ToDecimal(((TextBox)grdvwpurorder.Rows[index].FindControl("txtqty")).Text);
                    cmd.Parameters.Add("@stkrate", SqlDbType.Decimal).Value = stkrate;
                    //cmd.Parameters.Add("@stkvalue", SqlDbType.Decimal).Value = stkvalue;
                    //cmd.Parameters.Add("@discount", SqlDbType.Decimal).Value = discount;
                    //cmd.Parameters.Add("@tax", SqlDbType.Decimal).Value = tax;
                    //cmd.Parameters.Add("@amendinvno", SqlDbType.Int).Value = 0;
                    cmd.Parameters.Add("@checkstatus", SqlDbType.Bit).Value = Convert.ToBoolean(((CheckBox)grdvwpurorder.Rows[index].FindControl("chkItem1")).Checked);
                    cmd.Parameters.Add("@invdet", SqlDbType.Int).Value = 1;
                    cmd.Parameters.Add("@stk", SqlDbType.Int).Value = 1;


                }
                if (index < j)
                {
                    string txitemno = Convert.ToString(grdviewtax.DataKeys[index].Value);
                    cmd.Parameters.Add("@txitemno", SqlDbType.VarChar).Value = txitemno;
                    cmd.Parameters.Add("@taxname", SqlDbType.VarChar).Value = ((DropDownList)grdviewtax.Rows[index].FindControl("ddlShtName")).SelectedValue;
                    cmd.Parameters.Add("@percentage", SqlDbType.Decimal).Value = Convert.ToDecimal(((TextBox)grdviewtax.Rows[index].FindControl("txtPercentage")).Text);
                    cmd.Parameters.Add("@description", SqlDbType.VarChar).Value = ((TextBox)grdviewtax.Rows[index].FindControl("txtDesc")).Text;
                    cmd.Parameters.Add("@formula", SqlDbType.VarChar).Value = ((TextBox)grdviewtax.Rows[index].FindControl("txtFormula")).Text;
                    cmd.Parameters.Add("@invtax", SqlDbType.Int).Value = 1;

                }



                cmd.Parameters.Add("@isactive", SqlDbType.Bit).Value = 1;
                cmd.Parameters.Add("@ch", SqlDbType.Int).Value = 1;



                insert = ems.Execute_Sql(cmd, con);

            }
            if (insert > 0)
            {
                try
                {

                    SqlCommand stcmd = new SqlCommand("spUpdateTblSetUpMaxNo");
                    stcmd.CommandType = CommandType.StoredProcedure;
                    stcmd.Parameters.Add("@colname", SqlDbType.VarChar).Value = "grnno";
                    stcmd.Parameters.Add("@companyid", SqlDbType.Int).Value = Convert.ToInt16(Session["companyid"]);
                    int stchng = ems.Execute_Sql(stcmd, con);
                }
                catch (SqlException sqlex)
                {

                    Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);
                }
                ScriptManager.RegisterStartupScript(this, typeof(string), "openNewWindow", "<script>alert('Record Successfully Modified" + txtInvoiceNo.Text + "')</script>", false);
                Clear();

            }
        }
        catch (SqlException sqlex)
        {
            Logger.Log("Error while Saving data " + sqlex.Message + " Stack Trace: " + sqlex.StackTrace, LogLevel.ERROR);

        }
    }




STORED PROCEDURE::

USE [IAdb14AUG]
GO
/****** Object:  StoredProcedure [dbo].[spInsertTblImportPurchase]    Script Date: 08/31/2012 12:16:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spInsertTblImportPurchase]
@imppurno int,
@companyid int,
@imppurdate varchar(50)=null,
@billno varchar(50)=null,
@billdate varchar(50)=null,
@ordno int=null,
@orddate varchar(50)=null,
@quotno int=null,
@quotdate varchar(50)=null,
@prno int=null,
@prdate varchar(24)=null,
@supp_code varchar(16)=null,
@supp_name varchar(200)=null,
@handleby varchar(20)=null,
@approvedby varchar(20)=null,
@paymode varchar(30)=null,
@typedby varchar(30)=null,
@inwords varchar(50)=null,
@formno varchar(20)=null,
@transmode varchar(30)=null,
@totalbox varchar(40)=null,
@boxref varchar(40)=null,
@rdpermitno varchar(500)=null,
@vehicleno varchar(200)=null,
@sugamform varchar(500)=null,
@sugamdate varchar(50)=null,
@totalamt numeric(14,2)=0,
@freightchargeby varchar(50)=null,
@filestored varchar(150)=null,
@enclosure varchar(500)=null,
@multiattach varchar(500)=null,
@transportname varchar(500)=null,
@receiptno varchar(50)=null,
@receiptdate varchar(50)=null,
@transportcharges numeric(14,2)=null,
@stpercent float=0,
@servicetax numeric(14,2)=0,
@tottransportamt numeric(14,2)=0,
@transportamtinwords varchar(500)=null,
@ModeofDispatchId varchar(12)=null,
@modeofdispatch varchar(20)=null,
@ModeofDispatchDesc varchar(200)=null,
@byair varchar(200)=null,
@byocean varchar(200)=null,
@byroad varchar(200)=null,
@bytrain varchar(200)=null,
@totalcustoms numeric(14,2)=null,
@modvatbenfit numeric(14,2)=null,
@claimamt numeric(14,2)=null,
@bankdbtval numeric(14,2)=null,
@TotInvVal numeric(14,2)=null,
@USD numeric(14,2)=null,
@WareHouseExp numeric(14,2)=null,
@Currency varchar(50)=null,
--@isdone bit=0,
@itemno varchar(10)=null,
@puorditemno varchar(10)=null,
@catalogno varchar(510)=null,
@itemcode varchar(510)=null,
@serialnos varchar(1024)=null,
@qty float=null,
@ratein$ numeric(14,2)=null,
@rateinRs numeric(14,2)=null,
@Frieghtinsur numeric(18, 2)=null,
@matvalue numeric(18, 2)=null,
@customdutyinperc numeric(10, 2)=null,
@customdutyinamt numeric(18, 2)=null,
@total numeric(18, 2)=null,
@txt12 numeric(14,2)=null,
@txtedu2 numeric(14,2)=null,
@txtshe1 numeric(14,2)=null,
@txt4 numeric(14,2)=null,
@stockvalue numeric(14,2)=null,
@grossweight varchar(100)=null,
@netweight varchar(100)=null,
@Measurement varchar(100)=null,
@tariffcode varchar(50)=null,
@stkrate numeric(14,2)=null,
@stkvalue numeric(14,2)=null,
@invdet int=0,
@txitemno varchar(10)=null,
@puordtaxno varchar(10)=null,
@taxname varchar(50)=null,
@percentage numeric(5,3)=null,
@description varchar(25)=null,
@formula varchar(200)=null,
@dtxitemno varchar(10)=null,
@dlitemtype varchar(2048)=null,
@assessvalue numeric(14,2)=0,
@delartaxname varchar(500)=null,
@delarpercent numeric(5,2)=0,
@checkstatus bit=null,
@taxamt numeric(14,2)=0,
@invtax int=0,
@delartax int=0,
@isactive bit,
@ch int,
@stk int=0
as
begin 

declare @id int
declare @getCnt int
declare @getComp int
declare @Error1 int      
declare @Error2 int 
declare @Error3 int
declare @Error4 int
declare @Error5 int
declare @Error6 int
declare @stockno float
declare @indx int
declare @InpString varchar(2048)
declare @strpart varchar(2048)
declare @Inpitem varchar(2048)
declare @stritem varchar(2048)
declare @wrno float
--declare @txitemno int

select @indx=1
select @InpString=@serialnos
select @Inpitem=@catalogno

begin transaction insertinvoicerec

select @getCnt=count(*) from tblImportPurchase where imppurno=@imppurno and companyid=@companyid

if @getCnt=0 and @ch=1
begin
insert into tblImportPurchase(imppurno,companyid,imppurdate,billno,billdate,supppurordno,supppurdate,suppquotno,suppquotdate,prsno,prsdate,suppcode,suppname,handleby,approvedby,isdone,paymode,typedby,inwords,formno,transmode,ModeofDispatch,ModeofDispatchDesc,ModeofDispatchId,byair,byocean,byroad,bytrain,totalbox,boxref,rdpermitno,vehicleno,sugamform,sugamdate,totalamt,freightchargeby,filestored,enclosure,multiattach,transportname,receiptno,receiptdate,transportcharges,stpercent,tottransportamt,transportamtinwords,createdby,createddate,modifiedby,modifieddate,totalcustoms,modvatbenfit,claimamt,bankdbtval,USD,WareHouseExp,Currency,grossweight,netweight,Measurement,TotInvVal)values(@imppurno,@companyid,@imppurdate,@billno,@billdate,@ordno,@orddate,@quotno,@quotdate,@prno,@prdate,@supp_code,@supp_name,@handleby,@approvedby,@isactive,@paymode,@typedby,@inwords,@formno,@transmode,@modeofdispatch,@ModeofDispatchDesc,@ModeofDispatchId,@byair,@byocean,@byroad,@bytrain,@totalbox,@boxref,@rdpermitno,@vehicleno,@sugamform,@sugamdate,@totalamt,@freightchargeby,@filestored,@enclosure,@multiattach, @transportname,@receiptno,@receiptdate,@transportcharges,@stpercent,@tottransportamt,@transportamtinwords,@handleby,convert(varchar,getdate(),103),@handleby,convert(varchar,getdate(),103),@totalcustoms,@modvatbenfit,@claimamt,@bankdbtval,@USD,@WareHouseExp,@Currency,@grossweight,@netweight,@Measurement,@TotInvVal)
select @Error1=@@ERROR
end
else
if @ch=2
begin
update tblImportPurchase set imppurno=@imppurno,companyid=@companyid,imppurdate=@imppurdate,billno=@billno,billdate=@billdate,supppurordno=@ordno,supppurdate=@orddate,suppquotno=@quotno,suppquotdate=@quotdate,prsno=@prno,prsdate=@prdate,suppcode=@supp_code,suppname=@supp_name,handleby=@handleby,approvedby=@approvedby,isdone=@isactive,paymode=@paymode,typedby=@typedby,inwords=@inwords,formno=@formno,transmode=@transmode,ModeofDispatch=@modeofdispatch,ModeofDispatchDesc=@ModeofDispatchDesc,ModeofDispatchId=@ModeofDispatchId,byair=@byair,byocean=@byocean,byroad=@byroad,bytrain=@bytrain,totalbox=@totalbox,boxref=@boxref,rdpermitno=@rdpermitno,vehicleno=@vehicleno,sugamform=@sugamform,sugamdate=@sugamdate,totalamt=@totalamt,freightchargeby=@freightchargeby,filestored=@filestored,enclosure=@enclosure,multiattach=@multiattach,receiptno=@receiptno,receiptdate=@receiptdate,transportname=@transportname,transportcharges=@transportcharges,stpercent=@stpercent,tottransportamt=@tottransportamt,transportamtinwords=@transportamtinwords,modifiedby=@handleby,modifieddate=convert(varchar,getdate(),103),totalcustoms=@totalcustoms,modvatbenfit=@modvatbenfit,claimamt=@claimamt,bankdbtval=@bankdbtval,USD=@USD,WareHouseExp=@WareHouseExp,Currency=@Currency,grossweight=@grossweight,netweight=@netweight,Measurement=@Measurement,TotInvVal=@TotInvVal where imppurno=@imppurno and companyid=@companyid
select @Error1=@@ERROR
end
else
if @ch=3
begin
update tblImportPurchase set imppurno=@imppurno,companyid=@companyid,imppurdate=@imppurdate,billno=@billno,billdate=@billdate,supppurordno=@ordno,supppurdate=@orddate,suppquotno=@quotno,suppquotdate=@quotdate,prsno=@prno,prsdate=@prdate,suppcode=@supp_code,suppname=@supp_name,handleby=@handleby,approvedby=@approvedby,isdone=@isactive,paymode=@paymode,typedby=@typedby,inwords=@inwords,formno=@formno,transmode=@transmode,ModeofDispatch=@modeofdispatch,ModeofDispatchDesc=@ModeofDispatchDesc,ModeofDispatchId=@ModeofDispatchId,totalbox=@totalbox,boxref=@boxref,rdpermitno=@rdpermitno,vehicleno=@vehicleno,sugamform=@sugamform,sugamdate=@sugamdate,filestored=@filestored,enclosure=@enclosure,multiattach=@multiattach,receiptno=@receiptno,receiptdate=@receiptdate,transportname=@transportname,transportcharges=@transportcharges,stpercent=@stpercent,transportamtinwords=@transportamtinwords,modifiedby=@handleby,modifieddate=convert(varchar,getdate(),103) where imppurno=@imppurno and companyid=@companyid
select @Error1=@@ERROR
end


if @invdet=1
begin
select @itemno='I'

select @id=max(convert(int,substring(itemno,2,len(itemno)-1)))+1 from tblImportPurchasedetails

if @id is null
begin
select @itemno=@itemno+'1'
end
else
begin
select @itemno=@itemno+convert(varchar,@id)
end

insert into tblImportPurchasedetails(itemno,companyid,imppurno,supppuorditemno,catalogno,itemcode,serialnos,qty,ratein$,rateinRs,Frieghtinsur,matvalue,customdutyinperc,customdutyinamt,total,checkstatus,txt12,txtedu2,txtshe1,txt4,stockvalue,tariffcode)values(@itemno,@companyid,@imppurno,@puorditemno,@catalogno,@itemcode,@serialnos,@qty,@ratein$,@rateinRs,@Frieghtinsur,@matvalue,@customdutyinperc,@customdutyinamt,@total,@checkstatus,@txt12,@txtedu2,@txtshe1,@txt4,@stockvalue,@tariffcode)
select @Error2=@@ERROR
end
else
if @ch=2
begin
update tblImportPurchasedetails set imppurno=@imppurno,companyid=@companyid,catalogno=@catalogno,itemcode=@itemcode,serialnos=@serialnos,qty=@qty,ratein$=@ratein$,rateinRs=@rateinRs,Frieghtinsur=@Frieghtinsur,matvalue=@matvalue,customdutyinperc=@customdutyinperc,customdutyinamt=@customdutyinamt,total=@total,checkstatus=@checkstatus,txt12=@txt12,txtedu2=@txtedu2,txtshe1=@txtshe1,txt4=@txt4,stockvalue=@stockvalue,tariffcode=@tariffcode where supppuorditemno=@puorditemno and imppurno=@imppurno and companyid=@companyid
select @Error2=@@ERROR
end

if @stk=1
begin
if @serialnos is null 
begin
select @Error5=0
end
else
begin
select @indx=1
While @indx!= 0
Begin
set @indx = charindex(',',@InpString)
if @indx!=0
set @StrPart = left(@InpString,@indx - 1)
else
set @StrPart = @InpString

if(len(@StrPart)>0)
begin
select @wrno=max(wrno)+1 from tblproductwarranty 

if @wrno is null
begin
select @wrno=1
end
insert into tblproductwarranty(wrno,itemtype,companyid,qty,serialno,warrantydate,suppliercode,createdby,createdon,modifiedby,modifiedon,isactive,grnno)values(@wrno,@catalogno,@companyid,@qty,@StrPart,convert(varchar,dateadd(year,1,convert(datetime,@imppurdate,103)),103),@supp_code,@handleby,convert(varchar,getdate(),103),@handleby,convert(varchar,getdate(),103),@isactive,@imppurno)
select @Error5=@@ERROR
end
set @InpString = right(@InpString,len(@InpString) - @indx)
if len(@InpString) = 0 break
End

end


end




if @stk=1
begin
select @indx=1
While @indx!= 0
Begin
set @indx = charindex('~',@Inpitem)
if @indx!=0
set @stritem = left(@Inpitem,@indx - 1)
else
set @stritem = @Inpitem

if(len(@stritem)>0)
begin
select @stockno=max(slno)+1 from tblstock 

if @stockno is null
begin
select @stockno=1
end

insert into tblstock(slno,refno,companyid,st_type,entrydate,suppliercode,catalogno,itemtype,serialnos,qty,rate,[value],createby,createddate,modifiedby,modifieddate)values(@stockno,@imppurno,@companyid,'G',@imppurdate,@supp_code,@catalogno,@stritem,@serialnos,@qty,@stkrate,@stockvalue,@handleby,convert(varchar,getdate(),103),@handleby,convert(varchar,getdate(),103))
select @Error4=@@ERROR
end
set @Inpitem = right(@Inpitem,len(@Inpitem) - @indx)
if len(@Inpitem) = 0 break
End


end
else
if @stk=2
begin
select @indx=1
While @indx!= 0
Begin
set @indx = charindex('~',@Inpitem)
if @indx!=0
set @StrPart = left(@Inpitem,@indx - 1)
else
set @StrPart = @Inpitem

if(len(@StrPart)>0)
begin
select @stockno=max(slno)+1 from tblstock 

if @stockno is null
begin
select @stockno=1
end

insert into tblstock(slno,refno,companyid,st_type,entrydate,suppliercode,catalogno,itemtype,serialnos,qty,rate,[value],createby,createddate,modifiedby,modifieddate)values(@stockno,@imppurno,@companyid,'IP',@imppurdate,@supp_code,@catalogno,@StrPart,@serialnos,@qty,@stkrate,@stkvalue,@handleby,convert(varchar,getdate(),103),@handleby,convert(varchar,getdate(),103))
select @Error4=@@ERROR
end
set @Inpitem = right(@Inpitem,len(@Inpitem) - @indx)
if len(@Inpitem) = 0 break
End
end

if(@invtax=1)
begin

select @txitemno='IT'

select @id=max(convert(int,substring(itemno,3,len(itemno)-2)))+1 from tblImportPurchasetax

if @id is null
begin
select @txitemno=@txitemno+'1'
end
else
begin
select @txitemno=@txitemno+convert(varchar,@id)
end

insert into tblImportPurchasetax(itemno,imppurno,companyid,taxname,percentage,description,formula,isactive)values(@txitemno,@imppurno,@companyid,@taxname,@percentage,@description,@formula,@isactive)
select @Error3=@@ERROR

end

else
if @ch=2 
begin

update tblImportPurchasetax set imppurno=@imppurno,companyid=@companyid,taxname=@taxname,percentage=@percentage,description=@description,formula=@formula,isactive=@isactive where itemno=@txitemno and companyid=@companyid
select @Error3=@@ERROR

end
--if @ch=1 or @ch=2
--begin
--update tblpricelist set price=@rate where st_type='S' and itemtype=@catalogno
--end

if @invdet=0
begin
select @Error2=0
select @Error4=0
end

if @stk=0
begin
select @Error4=0
end

if @invtax=0
begin
select @Error3=0
end

if @delartax=0
begin
select @Error6=0
end

if (@getcnt=1 and @ch=1) or @ch=0 
begin
select @Error1=0
end



if(@Error1 =0 and @Error2 = 0 and @Error3=0 and @Error4=0 and @Error6=0)      
Begin      
COMMIT TRANSACTION insertinvoicerec
End      
      
else      
Begin      
RollBack TRANSACTION insertinvoicerec
End 


end

---