SQL Char Data Types and Casting to int

mountainyman

VB.net
I have a stored procedure which I use to populate a datagridview.
The stored procedure returns a temprary table.
In order to get around a difficulty I was having of the stored procedure returning a large number of zeros that I don't want to show I changed the data type of the fields in the temptable from int to char.

But now I cannot assess them as numbers even when I go

CType(dgvmygrid.Item("NumberColumn", iCurrentRow).Value, Integer) <> 0 

so Ctype Integer is doing nothing.

Any ideas?

MM

nialo

why not just compare to zero as a char? since your using a char..

dgvmygrid.Item("NumberColumn", iCurrentRow).Value != "0"

Delirium

Just wondering, why aren't you filtering out the records in the stored procedure? You are pulling records across that you don't need.

Personally, the stored procedure would be the first place I'd consider changing. Also, is there any special reason you changed the field to a char?

mountainyman

Koth:
I'm not filtering them out. I need to format records that have a value in 'NumberCoumn' One way and format records that don't another way.

I still need to display all records.

Nialo:
The zeros get dropped if I change the datatype on the stored procedure to a char. I have tried doing a compasion with "Is Nothing"

But that hasn't worked.

Delirium

mountainyman wrote: »

Koth:
I'm not filtering them out. I need to format records that have a value in 'NumberCoumn' One way and format records that don't another way.

I still need to display all records.

Ah, understand now:) Have you tried doing a CASE statement to format the data in the stored procedure? Or is the logic too complex to apply in the stored procedure?

stephenlane80

If the field in SQL server is Int and it has a value of zero, then SQL Server treats this field as null. When you try to cast a null value to a char you are converting a null int to a null char

you could do this if you want a char '0' returned in your results.

select a, b, c, CASE(int_field)
WHEN null then '0'
ELSE cast(int _field as char)
END
from table

Ginger

Do an Int32.TryParse which returns a bool

Or

Int32.Parse, returns an int.

mountainyman

Hi StepenLane,

The issue is not the null values.
My Table has 100 records.
'NumberColumn' contains 7 values.

If the datatype for number column is an int then the 93 nulls appear in my vb application as zeroes.

If the If the datatype for number column is a char then the 93 nulls appear in my vb application as nothing. I can't see them.
However the 7 values in NumberCoulumn do come across.

Now I would have thought that regardless of the datatype in SQL I could cast the values to any type I want.

Ginger

stephenlane80 wrote: »

If the field in SQL server is Int and it has a value of zero, then SQL Server treats this field as null. When you try to cast a null value to a char you are converting a null int to a null char

Eh??? 0 is not null.. NULL == NULL ..

0 is cast as '0' .. I dont know where you came up with this one.. but I would love to see the source..

nialo

Create Procedure [dbo].[sp_getInfo]
(
@ID		int = NULL, 
@name		VARCHAR(50) = NULL
) 
AS
select NumberColumn, ID, Name -- NumberColumn is an int
From temptable 
where ( id = @ID Or @ID = 0) 
AND (name like  '%'+@name+'%' or (@name IS NULL or @name ='') )

protected void gridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
   if (e.Row.RowType == DataControlRowType.DataRow)
   {
           int temp = Convert.ToInt32(DataBinder.Eval(e.Row.DataItem, "NumberColumn"));
           if(temp == 0)
           {
              e.Row.Cells[0].Text = ""; // assumes cell zero is the numberColumn in the grid. can format how you like from here... 
           }
   }
}

amen

If the field in SQL server is Int and it has a value of zero, then SQL Server treats this field as null

so wrong. 0 is 0 it is never null

now if you put a value such as 0123 in to an INT field it will save as 123 whereas if you put 0123 into a char field it will save as 0123

stephenlane80

Ginger wrote: »

Eh??? 0 is not null.. NULL == NULL ..

null and zero are interchangeable in ANSI C : section 8.1 im sure i heard the same for MSSQL somewhere


note sure what you mean by "0 is cast as '0'", i was suggesting that he convert the zero int values to a char of '0', then cast the other non-zero ints to chars with the case function

Amen:
"so wrong. 0 is 0 it is never null" see above example with C

by the sounds of what is going on i think there are nulls getting passed into the VB application, you could try this to see if zeros get transfered:

select ISNULL(NumberColumn,0), ID, Name -- NumberColumn is an int
From temptable 
where ( id = @ID Or @ID = 0) 
AND (name like  '%'+@name+'%' or (@name IS NULL or @name ='') )

Ginger

In ANSI C maybe but not MSSQL .. by ISO SQL Standard a 0 in an INT field is not considered NULL...

If you converted the entire field from INT to CHAR(1) you would only get NULL values on any field that didnt have a value ... 0 would be considered a value.

So if you returned your SP.. you would see that a NULL value would be returned. In .NET if you have a (int)dr["field"] where it is null, it would possibly throw a NullException or assign a default value to the variable which is .... 0

Which is possibly where you are getting it from.

stephenlane80

Ginger wrote: »

If you converted the entire field from INT to CHAR(1) you would only get NULL values on any field that didnt have a value ... 0 would be considered a value.

hmm yea, i remember hearing in PL-SQL that an empty string was the same as null, prob the same with T-SQL


He should give a go to my last post and see if it passes zeros instead of blanks to the application

Ginger

An empty string technically isnt NULL either... Depends on DBMS implementation..


For example if you saved '' (no value) it wouldnt be saved as NULL in the database. If you used something DbNull (.NET) it would save it as NULL.

mountainyman

Null was explained to me as a closed box. Maybe there is a value in the box and maybe not. We don't open the box we deal with it in some way.

In practise null is nothing though.

kayos

mountainyman wrote: »

Null was explained to me as a closed box. Maybe there is a value in the box and maybe not. We don't open the box we deal with it in some way.

In practise null is nothing though.

The whole box comparision sounds more like quantum physics

Ginger

Null is a higgs boson in disguise

mountainyman

Null is a dead cat which is why vb turns into a STINKING zero