Sql Upation error: in sql2008: Error converting data type varchar to numeric

Question

Hi friends i m geeting Error (converting data type varchar to numeric) while updating the table.I have to update the below table based on the condintion

CREATE TABLE [ITEMMASTER](
[ID] [int] NULL,
[ITEMCODE] [varchar](10) NULL,
[ITEMDESCRIPTION] [varchar](50) NULL,
[BRAND] [varchar](50) NULL,
[ITEMGROUP] [varchar](50) NULL,
[ITEMUNIT] [varchar](50) NULL,
[PURCHASERATE] [numeric](12, 2) NULL,
[MANUFACTURER] [varchar](50) NULL,
[PURCHASEMRP] [numeric](12, 2) NULL,
[OPENINGQTY] [numeric](12, 2) NULL,
[OPENINGVALUE] [numeric](12, 2) NULL,
[ISACTIVE] [bit] NULL,
[REORDERLEVEL] [numeric](12, 2) NULL,
[MINSTOCKQTY] [numeric](12, 2) NULL,
[MAXSTOCKQTY] [numeric](12, 2) NULL,
[VATPERCENT] [varchar](50) NULL
) ON [PRIMARY]

if (txtItemCode.Text == "")
{
Response.Write("<script language='javascript'>alert('Entry Not Selected for Modification!!!');</script>");
}
else
{
Update_Record();
}

protected void Update_Record()
{
try
{
string strSQL;
string dbConn = ConfigurationManager.ConnectionStrings["CMC"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(dbConn);
sqlConn.Open();
string PRate = Convert.ToInt32(txtPurchaseRate.Text).ToString();
string MRP = Convert.ToInt32(txtPurchaseMRP.Text).ToString();
string Oqty = Convert.ToInt32(txtOpeningQuantity.Text).ToString();
string Ovalue = Convert.ToInt32(txtOpeningValue.Text).ToString();
string Rlevel = Convert.ToInt32(txtReorderLevel.Text).ToString();
string MinValue = Convert.ToInt32(txtMinStockQty.Text).ToString();
string MaxValue = Convert.ToInt32(txtMaxStockQty.Text).ToString();
string Vat = Convert.ToInt32(txtVAT.Text).ToString();

strSQL = "Update [ITEMMASTER] Set ITEMDESCRIPTION='" + txtItemDescription.Text + "',BRAND='" + DDLBrand.SelectedItem.ToString() + "',ITEMGROUP='" + DDLItemGroup.SelectedItem.ToString() + "',ITEMUNIT='" + DDLItemUnit.SelectedItem.ToString() + "',MANUFACTURER='" + DDLMfr.SelectedItem.ToString() + "',PURCHASERATE='" + PRate + "',PURCHASEMRP='" + MRP + "',OPENINGQTY='" + Oqty + "',OPENINGVALUE='" + Ovalue + "', REORDERLEVEL='" + Rlevel + "',MINSTOCKQTY='" + MinValue + "',MAXSTOCKQTY='" + MaxValue + "',VATPERCENT='" + Vat + "' Where ITEMCODE ='" + txtItemCode.Text + "'";
SqlCommand cmdItemMaster = new SqlCommand(strSQL, sqlConn);
cmdItemMaster.ExecuteNonQuery();
sqlConn.Close();
Response.Write("<script language='javascript'>alert('Data Updated Successfully...');</script>");
Load_Record();

}
catch (Exception)
{
Response.Write("<script language='javascript'>alert(' Entry Not selected for Updation...');</script>");
}
}

But it shows error....if PURCHASERATE, PURCHASEMRP, OPENINGQTY,OPENINGVALUE,REORDERLEVEL,MINSTOCKQTY,MAXSTOCKQTY,VATPERCENT fields are not entered. But i want to update it even the values are not entered.

The Error is : Error converting data type varchar to numeric
Help me please...Its urgent

Jignesh Trivedi · Answer

ohhh.. sorry it is compilation error due to string casting. try..this and let us know the result hope fully this will work for you. strSQL = Update [ITEMMASTER] Set ITEMDESCRIPTION=' + txtItemDescription.Text + 'BRAND=' + DDLBrand.SelectedItem.ToString() + 'ITEMGROUP=' + DDLItemGroup.SelectedItem.ToString() + 'ITEMUNIT=' + DDLItemUnit.SelectedItem.ToString() + 'MANUFACTURER=' + DDLMfr.SelectedItem.ToString() + 'PURCHASERATE=' + (string.IsNullOrEmpty(txtPurchaseRate.Text) ? 0.0 : txtPurchaseRate.Text) + 'PURCHASEMRP=' + (string.IsNullOrEmpty(txtPurchaseMRP.Text) ? 0.0 : txtPurchaseMRP.Text) + 'OPENINGQTY=' + (string.IsNullOrEmpty(txtOpeningQuantity.Text) ? 0.0 : txtOpeningQuantity.Text) + 'OPENINGVALUE=' + (string.IsNullOrEmpty(txtOpeningValue.Text) ? 0.0 : txtOpeningValue.Text) + ' REORDERLEVEL=' + (string.IsNullOrEmpty(txtReorderLevel.Text) ? 0.0 : txtReorderLevel.Text) + 'MINSTOCKQTY=' + (string.IsNullOrEmpty(txtMinStockQty.Text) ? 0.0 : txtMinStockQty.Text) + 'MAXSTOCKQTY=' + (string.IsNullOrEmpty(txtMaxStockQty.Text) ? 0.0 : txtMaxStockQty.Text) + 'VATPERCENT=' + (string.IsNullOrEmpty(txtVAT.Text) ? 0.0 : txtVAT.Text) + ' Where ITEMCODE =' + txtItemCode.Text + '; only adding braket () before string tobe concate. hope this will help you.

Jignesh Trivedi · Answer

hi I think there is problem when you pass data. When your text box is blank then it pass   so it might not be convert to numeric. I mean to say it might txtPurchaseRate.text is blank and SQL Server trying to cast it to numeric. try.. strSQL = Update [ITEMMASTER] Set ITEMDESCRIPTION=' + txtItemDescription.Text + 'BRAND=' + DDLBrand.SelectedItem.ToString() + 'ITEMGROUP=' + DDLItemGroup.SelectedItem.ToString() + 'ITEMUNIT=' + DDLItemUnit.SelectedItem.ToString() + 'MANUFACTURER=' + DDLMfr.SelectedItem.ToString() + 'PURCHASERATE=' + string.IsNullOrEmpty(txtPurchaseRate.Text)? 0.0 : txtPurchaseRate.Text + 'PURCHASEMRP=' + string.IsNullOrEmpty(txtPurchaseMRP.Text)? 0.0 : txtPurchaseMRP.Text + 'OPENINGQTY=' + string.IsNullOrEmpty(txtOpeningQuantity.Text)? 0.0 : txtOpeningQuantity.Text + 'OPENINGVALUE=' + string.IsNullOrEmpty(txtOpeningValue.Text)? 0.0 : txtOpeningValue.Text + ' REORDERLEVEL=' + string.IsNullOrEmpty(txtReorderLevel.Text)? 0.0 : txtReorderLevel.Text + 'MINSTOCKQTY=' + string.IsNullOrEmpty(txtMinStockQty.Text)? 0.0 : txtMinStockQty.Text + 'MAXSTOCKQTY=' + string.IsNullOrEmpty(txtMaxStockQty.Text)? 0.0 : txtMaxStockQty.Text + 'VATPERCENT=' + string.IsNullOrEmpty(txtVAT.Text)? 0.0 : txtVAT.Text + ' Where ITEMCODE =' + txtItemCode.Text + '; hope this will help you.

sathish kumar · Answer

wow it's Great Mr. Jignesh Trivedi.....  Thank u so soooooo Much...its working well now.....Thanks for your help....i m happy... Thank u so much once again..... Thank u all guys....Keep Rocking..................

sathish kumar · Answer

no yaar...i didnt make any changes in ur's....Even i cant understand why this error occurs. I tried so many ways. But nothing works out.... Thanks for ur support...

Jignesh Trivedi · Answer

hi I cannot understand why you got this error? because you have only one field has bit data type and you are not using it in you update statement. are you got error in same code? are you done any change you update query? thx.

Santhosh Kumar Jayaraman · Answer

You are trying to save string into bol field. Can you please check which is the bool column you are tring to update and instead of convert.ToInt32 or convret.tostring just make it as convert.toboolean

sathish kumar · Answer

hi thanks for ur reply... But still getting error as Error 150 Cannot implicitly convert type 'string' to 'bool'

sathish kumar · Answer

hi thanks for ur reply... But still getting error as Cannot implicitly convert type 'string' to 'bool'

Santhosh Kumar Jayaraman · Answer

Jignesh is right. Try his solution. You tried to convert  to int which is incorrect

sathish kumar · Answer

hi ...Thanks for ur reply.... protected void Load_Record() { SqlItemMaster.SelectCommand = SELECT ITEMCODEITEMDESCRIPTIONBRANDITEMGROUPITEMUNITPURCHASERATEMANUFACTURERPURCHASEMRPOPENINGQTYOPENINGVALUEREORDERLEVELMINSTOCKQTYMAXSTOCKQTYVATPERCENT FROM [ITEMMASTER] ORDER BY ITEMCODE ; } protected void Update_Record() { try { string strSQL; string dbConn = ConfigurationManager.ConnectionStrings[CMC].ConnectionString; SqlConnection sqlConn = new SqlConnection(dbConn); sqlConn.Open(); strSQL = Update [ITEMMASTER] Set ITEMDESCRIPTION=' + txtItemDescription.Text + 'BRAND=' + DDLBrand.SelectedItem.ToString() + 'ITEMGROUP=' + DDLItemGroup.SelectedItem.ToString() + 'ITEMUNIT=' + DDLItemUnit.SelectedItem.ToString() + 'MANUFACTURER=' + DDLMfr.SelectedItem.ToString() + 'PURCHASERATE=' +txtPurchaseRate.Text+ 'PURCHASEMRP=' +txtPurchaseMRP.Text+ 'OPENINGQTY=' +txtOpeningQuantity.Text+ 'OPENINGVALUE=' +txtOpeningValue.Text+ ' REORDERLEVEL=' +txtReorderLevel.Text+ 'MINSTOCKQTY=' +txtMinStockQty.Text+ 'MAXSTOCKQTY=' +txtMaxStockQty.Text+ 'VATPERCENT=' +txtVAT.Text+ ' Where ITEMCODE =' + txtItemCode.Text + '; SqlCommand cmdItemMaster = new SqlCommand(strSQL sqlConn); cmdItemMaster.ExecuteNonQuery(); sqlConn.Close(); Response.Write( alert('Data Updated Successfully...'); ); Load_Record(); } catch (Exception) { Response.Write( alert(' Entry Not selected for Updation...'); ); } } sample values are here: 1 101 AB PHYLLINE CAP NULL NULL SUN PHARMA (ARIAN DIV.) NULL NULL NULL 0 NULL NULL NULL 10 2 102 ACECLO PLUS TAB NULL NULL ARISTO PHARMACEUTICALS LTD. NULL NULL NULL 0 NULL NULL NULL 15 3800 3998 PIPZO 4.5 GM INJ Vail 138 NULL ALKEM LABORATORIES LIMITED 3.00 NULL NULL 0 NULL NULL NULL 1

Jignesh Trivedi · Answer

Sathish I got you point. kindly send whole query with value like.. which may you got in debugging.... Update [ITEMMASTER] Set ITEMDESCRIPTION='Item Desc.'BRAND='Test Brand'ITEMGROUP='GRP'ITEMUNIT='.... I think there is data issue which you pass to update query. hope this will help you.

sathish kumar · Answer

actually i m not entering any values in those numeric fields..If i entered means it doesnt throw any error It Updates successfully. If not it shows that error( Error converting data type varchar to numeric)

Jignesh Trivedi · Answer

hi Sathish I think you got error due to Arithmetic overflow of Numeric field. So suggesting check you value which updated to Numeric Filed like PURCHASERATE PURCHASEMRPOPENINGQTYOPENINGVALUEREORDERLEVELMINSTOCKQTYMAXSTOCKQTY. This error occured example : your data type is numeric(122) and you trying to update like 52222222330.00 hope this will help you.

sathish kumar · Answer

hi Santhosh.... The Error i am getting is : Error converting data type varchar to numeric

Santhosh Kumar Jayaraman · Answer

What is the error??

Senthilkumar · Answer

Here the error is related to the type conversion. when you have the column data type is numeric then it allows only integer and where you can't insert any other data type.