הערה
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות להיכנס או לשנות מדריכי כתובות.
הגישה לדף זה מחייבת הרשאה. באפשרותך לנסות לשנות מדריכי כתובות.
Question
Tuesday, May 29, 2012 10:06 AM
Hello.
I have the followin problem. Opening a table using SqlDataAdapter, numeric columns (sqlDbType = int16, 32, 64) that allow Null value, are set in my dataRows to 0.
I am using Visual Studio 2010 and Sql Server 2008.
Here's my code:
Dim cmd As New SqlCommand
Dim ds As New DataSet
Dim dt As DataTable
Dim da As SqlDataAdapter
command.Connection = My_connection '(connection object previously initialized and opened)
command.CommandText = "Select * From MyTable"
command.CommandType = CommandType.Text
cmd.CommandTimeout = 2000
da = New SqlDataAdapter(cmd)
da.Fill(ds)
Dim dr As DataRow = ds.Tables(0).Rows(0)
When I explore the dr object, all columns smallint, int and bigint, allowing null value, and with no default value, have "0" instead of DbNull.Value.
Any idea? where I am doing wrong?
Walker Boh
All replies (8)
Tuesday, May 29, 2012 1:25 PM ✅Answered
Hi,
if i remember correctly, when you fill a 'untyped' dataset like that, the table is created 'on the fly', without any type information it is not able to create the correct types for each column; they're all strings.
Create a typed dataset (e.g. by dragging a table from server explorer onto your dataset designer window), or create a table, and add the necessary columns :
dim dt as new datatable
dt.Columns.Add(....dt.Columns.Add(....ds.tables.add(dt)
for more details see the example here: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.aspx
Then use that table as argument in the fill method.
Regards, Nico
Tuesday, May 29, 2012 1:04 PM
.Net has no null value, it has nullable data types but those are not used with the dataadapter and the dataset/datatable in fact all columns are objects so strange that you could see direct a value.
So how did you recognise it was an 0 (again it is not a nullable int16 or something like that) smallint, int and bigint are all integer.
Success
Cor
Tuesday, May 29, 2012 1:12 PM
While in debug, moving cursor on "dr" i can explore it and see al values.
Even moving cursor on dr.item("NRTU") i can see tha value of the data
Walker Boh
Tuesday, May 29, 2012 2:13 PM
I can't do that. It is a generic routine. And the application connects to a database or to another, so I don't have the structure available at design time.
Moreover, the columns are created with correct data type: varchar columns are strings, bit columns are boolean, numeric columns are int16, 32, 64....
I get Null in columns that are string, but 0 instead null in numeric columns
Walker Boh
Tuesday, May 29, 2012 2:49 PM
Try the SQL REPLACE function. See the documentation:
http://msdn.microsoft.com/en-us/library/ms186862.aspx
You could use this to replace zero values with nulls, like REPLACE(MyFieldName, 0, NULL) as MyFieldName. The only problem with this is it's not going to be as easy as Select *. You'll have to loop through your data, find each column, then add the Replace syntax to every column you expect a zero to show up in.
Tuesday, May 29, 2012 3:29 PM
Cool, this one works. But it also make null where there really is a 0.
Walker Boh
Tuesday, May 29, 2012 3:36 PM
I think you did something wrong, because I get DBNull Value, also give next time a running sample. This sample from you contains lot of flaws. However, I made it running and it shows that the column contains DBNull.Value.
Success
Cor
Wednesday, May 30, 2012 6:56 AM
I can't do that. It is a generic routine. And the application connects to a database or to another, so I don't have the structure available at design time.
Moreover, the columns are created with correct data type: varchar columns are strings, bit columns are boolean, numeric columns are int16, 32, 64....
I get Null in columns that are string, but 0 instead null in numeric columns
Walker Boh
Did you look at the AllowDBNull and DefaultValue properties of the numeric fields?
Regards, Nico