CODEWITHSUNDEEP
javasql-serversql-server-2000
Syed Muhammad Mubashir
Syed Muhammad Mubashir

Reputation: 1016

Error converting data type varchar to numeric

I am getting an error in the following query:

insert into ProductStone ( Pieces, Weight, CutChg, LotID, 
                           CatID, OrnID, StoneID, ShadeID, CutID, ChgType, SetID) 
 values (  '10',  '0.3',  'null',  '601',  'H',  '101',  
           'S',  '0',  '13',  'null',  '4' ).

Problem is that I am creating my query at runtime . Please tell how I can check whether the value is null or not at run time.

Part of my code which I use to build dynamic query at runtime is as follows. I changed my previous code but in some cases it is working and some cases not working with null values.

  if ((updatedData.elementAt(r).get(c).get(1)==null) ||
      (updatedData.elementAt(r).get(c).get(1).equals("")))
  {
      updatedData.elementAt(r).get(c).set(1,"0");
  }
  else if (!(updatedData.elementAt(r).get(c).get(1).toString().equalsIgnoreCase("null") )) 
  {
      updatedData.elementAt(r).get(c).set(1,"0");   
  }

Upvotes: 0

Views: 725

Answers (2)

Syed Muhammad Mubashir
Syed Muhammad Mubashir

Reputation: 1016

My problem is being solved by putting two validations

//First
if ((updatedData.elementAt(r).get(c).get(1).toString().equals("null"))) 
{
updatedData.elementAt(r).get(c).set(1,"0");   
}
//Second
if (!(updatedData.elementAt(r).get(c).get(1).toString().equals("null"))) 
{
updateQuery +=updatedData.elementAt(r).get(c).get(0)+"='"+updatedData.elementAt(r).get(c).get(1)+"', " ;
insertColValsStr += " '"+updatedData.elementAt(r).get(c).get(1)+"') ";        
}
else
{
updateQuery += updatedData.elementAt(r).get(c).get(0)+"=null " ;
insertColValsStr += " null ) " ;       
}

Upvotes: 0

gmhk
gmhk

Reputation: 15940

if((updatedData.elementAt(r).get(c).get(1)==null) || (updatedData.elementAt(r).get(c).get(1).equals("")))
           {
             updatedData.elementAt(r).get(c).set(1,null);
           }

Change that piece of code and check...

One more point 

insert into ProductStone ( Pieces, Weight, CutChg, LotID, 
CatID, OrnID, StoneID, ShadeID, CutID, ChgType, SetID) 
 values (  '10',  '0.3',  'null',  '601',  'H',  '101',  
'S',  '0',  '13',  'null',  '4' ).

When ever you inser the null dont put that in the quotes, convert that to the following statement and check 

insert into ProductStone ( Pieces, Weight, CutChg, LotID, 
CatID, OrnID, StoneID, ShadeID, CutID, ChgType, SetID) 
 values (  '10',  '0.3',  null,  '601',  'H',  '101',  
'S',  '0',  '13',  null,  '4' ).

Upvotes: 1

Related Questions