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("");
}
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("");
Load_Record();
}
catch (Exception)
{
Response.Write("");
}
}
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
Loading
Jignesh TrivediPosted Jul 16, 2012, 6:58 AM
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.
SenthilkumarPosted Jul 17, 2012, 2:34 AM
when you have the column data type is numeric then it allows only integer and where you can't insert any other data type.
sathish kumarPosted Jul 16, 2012, 7:07 AM
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 kumarPosted Jul 16, 2012, 6:16 AM
Thanks for ur support...
Jignesh TrivediPosted Jul 16, 2012, 6:11 AM
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 JayaramanPosted Jul 16, 2012, 5:50 AM
sathish kumarPosted Jul 16, 2012, 5:48 AM
But still getting error as
Error 150 Cannot implicitly convert type 'string' to 'bool'
sathish kumarPosted Jul 16, 2012, 5:47 AM
But still getting error as
Cannot implicitly convert type 'string' to 'bool'
Santhosh Kumar JayaramanPosted Jul 16, 2012, 5:30 AM
Jignesh TrivediPosted Jul 16, 2012, 5:27 AM
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 kumarPosted Jul 16, 2012, 5:02 AM
protected void Load_Record()
{
SqlItemMaster.SelectCommand = "SELECT ITEMCODE,ITEMDESCRIPTION,BRAND,ITEMGROUP,ITEMUNIT,PURCHASERATE,MANUFACTURER,PURCHASEMRP,OPENINGQTY,OPENINGVALUE,REORDERLEVEL,MINSTOCKQTY,MAXSTOCKQTY,VATPERCENT 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("");
Load_Record();
}
catch (Exception)
{
Response.Write("");
}
}
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 TrivediPosted Jul 16, 2012, 4:50 AM
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 kumarPosted Jul 16, 2012, 3:54 AM
Jignesh TrivediPosted Jul 16, 2012, 3:51 AM
I think you got error due to Arithmetic overflow of Numeric field.
So suggesting check you value which updated to Numeric Filed like PURCHASERATE, PURCHASEMRP,OPENINGQTY,OPENINGVALUE,REORDERLEVEL,MINSTOCKQTY,MAXSTOCKQTY.
This error occured example :
your data type is numeric(12,2) and you trying to update like 52222222330.00
hope this will help you.
sathish kumarPosted Jul 16, 2012, 3:22 AM
The Error i am getting is : Error converting data type varchar to numeric
Santhosh Kumar JayaramanPosted Jul 16, 2012, 2:07 AM