Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Replace nulls with empty strings from DataReader

Status
Not open for further replies.
upplepop

upplepop

IS-IT--Management
Jun 1, 2002
173
0
0
US
I am working on a program to manipulate data from a MS SQL 2000 database. However, I am having problems differentiating between nulls and empty strings, both of which I find in my database. I would like to covert all the nulls returned by my SQL query into blank strings to make data checking easier later on.

Here is the code that gathers the data:
Code: 
Dim cmd As New SqlCommand("Select * From ClientTable", IntakeConnection)
Dim IntakeData As SqlDataReader = cmd.ExecuteReader()

I have a bunch of If-Then statements to act if the data is not null. For example:
Code: 
If IntakeData.Item("SSN") <> "" Then XML.WriteElementString("soc_sec_no", IntakeData.Item("SSN"))

But if the SSN field is null instead of blank, I get errors. I would prefer not to check for nulls & blanks each time; but instead coverting them all to blanks.
 
Sort by date Sort by votes
I would suggest using the Sql Server IsNull function in your query then you don't have to deal with it at all in your code. Good Luck!
Code: 
Dim sql As String = _
    "Select *, ISNULL(soc_sec_no, '') As SSN2 From ClientTable"
Dim cmd As New SqlCommand(sql, IntakeConnection)
Dim IntakeData As SqlDataReader = cmd.ExecuteReader()



Have a great day!

j2consulting@yahoo.com
 
Upvote 0 Downvote
But doesn't that mean I have to have statements for every field in that table, like first & last name, dob, etc? I have over 100 fields in that table.
 
Upvote 0 Downvote
In my first response, I assumed that most of your columns would not allow Nulls. If you have access to QueryAnalyzer, try exec sp_columns 'YourTableName' and check to see which columns in the table are nullable. Those are the only ones you need to worry about.

If you do the above and click on the ColumnName header, you can then ctrl+C to copy the entire column. Paste it into Excel and then use formulas to create fairly quickly the selection list you need for your query. You can copy and paste directly from Excel back into QueryAnalyzer. If you already know all of this, please accept my apologies!

Have a great day!

j2consulting@yahoo.com
 
Upvote 0 Downvote
You have a good suggestion SBendBuckeye, but most of the over 100 columns will allow nulls, and I would rather not clutter up the code with all of those SqlCommands.

I was thinking either there was a way to run the SqlDataReader through a sequence to replace nulls with empty strings
OR...
Write my if-then statements to check for nulls as well.

In plain English:
If SSN is not "" or if SSN is not null then xml.WriteElementString SSN.

Anyone care to convert that to VB (I have a problem with the "is not null" part).
 
Upvote 0 Downvote
Can you still do myfield + "" in .Net? I do it all the time in VB6. e. g.
Code: 
If IntakeData.Item("SSN") + "" <> "" Then XML.WriteElementString("soc_sec_no", IntakeData.Item("SSN"))

Bob
 
Upvote 0 Downvote
You have a good suggestion SBendBuckeye, but most of the over 100 columns will allow nulls, and I would rather not clutter up the code with all of those SqlCommands.
Click to expand...
It has to be done somewhere.

Either in the SQL query (both SELECTs and INSERT/UPDATES), or in your VB code (if column is null, substitute String.Empty; or if string is empty, substitute DBNull.Value; depending on the direction).

So you just have to pick one that is less work for you to maintain. There is nothing that will do it for you automatically.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
 
Upvote 0 Downvote
And even with a hundred fields I would prefer to type them all (or copy paste them from somewhere. And it would be a little bit faster on the server (depends how busy he is).

And perhaps it will make you leave out the fields you don't need.

I know all this will have you work more, but we don't care.

There is also a isdbNull function so

if isdbnull(blabla) = true then
'don't
else
'do
endif

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]
 
Upvote 0 Downvote
No, you can do your solution, Bob. But it only works for SELECTs, and only for columns with string data (CHAR, VARCHAR, TEXT, etc). For INSERTS and UPDATES, you still have to deal with the problem. As well as for SELECT columns that are ints or datetimes.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first
 
Upvote 0 Downvote
Oh, ok, well of course all that is true, Chip. I really only use it when I'm populating text boxes from a database's char fields. Thanks for the elaboration.

Bob
 
Upvote 0 Downvote
I have tried this code:

Code: 
If IntakeData.Item("Middle_Name") <> "" Or IsDBNull(IntakeData.Item("Middle_Name")) = False Then IntakeXML.WriteElementString("middle_name", IntakeData.Item("Middle_Name"))

But I get this error message:

An unhandled exception of type 'System.InvalidCastException' occurred in microsoft.visualbasic.dll

Additional information: Operator is not valid for type 'DBNull' and string "".
Click to expand...

The field is from MS SQL 2000 in the 'char' data type. Any ideas why I'm getting this message?
 
Upvote 0 Downvote
It's saying that you can't compare a DBNull and an empty string. This will happen in your code if IntakeData.Item("Middle_Name") is null. In that case, the first condition boils down to
Code: 
If DBNull <> ""
which will throw the error you're seeing. In effect, you're trying to cast DBNull to a string to compare it with "". That would be an invalid cast exception.

Now, your logic needs a bit of tuning. You're saying "if my field is not blank or my field is not null then do something." This will ALWAYS be true! If your field is a blank string, it isn't null, if youre field is null, it isn't a blank string.

What you want is "If my field isn't my field isn't a blank string, AND my field isn't null, then do something." So:
Code: 
If not IsDBNull(IntakeData.Item("Middle_Name")) AndAlso IntakeData.Item("Middle_Name")) <> "" Then 
   IntakeXML.WriteElementString("middle_name", IntakeData.Item("Middle_Name"))
End If

You need to use AndAlso, becuase otherwise you will evaluate both conditions even if the first one isn't met, and throw the same error. AndAlso is a "short circuiting" operator.

HTH

Bob
 
Upvote 0 Downvote
Bob, AndAlso, says evalueate second condition if first one is true, so this will generate the same error. OrElse on the other hand says evalute the second conditoiin if the first one is false - therefore code should be:

Code: 
If Not IsDBNull(IntakeData.Item("Middle_Name")) OrElse IntakeData.Item("Middle_Name")) <> "" Then 
   IntakeXML.WriteElementString("middle_name", IntakeData.Item("Middle_Name"))
End If

Hope this helps.
 
Upvote 0 Downvote
You probably have the same keyboard I had last week.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]
 
Upvote 0 Downvote
I have great respect for your capabilities, earthandfire, so I went and read up on it again. I'm going to say that this is a case of Quandoque bonus dormitat Homerus (sometimes even the good Homer nods). Please show us if I'm mistaken, though.

My position is that AndAlso differs from AND in that it will NOT evaluate the second condition if the first one is NOT true. AND already does evaluate the second condition if the first is true, so you're suggesting that AndAlso works just like AND. So, that's why they call AndAlso a "short circuit" operator, much like the reverse of OrElse. It fixes an annoying problem that comes up in VB6, when one of the evaluated conditions can result in an error if the first condition isn't met.

So, I'm standing by my code suggestion for the present. :)

Best regards,

Bob
 
Upvote 0 Downvote
Bob, for the result of And or AndAlso to be true, both expressions must be true:

Code: 
[b]And[/b]
Expr1    Expr2 Evaluated
=====    ===============
False    Yes
True     Yes

[b]AndAlso[/b]
Expr1    Expr2 Evaluated
=====    ===============
False    No - final result cannot be true so there is no point
True     Yes


If not IsDBNull(IntakeData.Item("Middle_Name")) AndAlso IntakeData.Item("Middle_Name")) <> "" Then

not IsDBNull(IntakeData.Item("Middle_Name")) = True
IntakeData.Item("Middle_Name")) <> "" must be evaluated to determine whether or not the whole expression is true


Bob, I got as far as typing all that out and then ...

If not IsDBNull(IntakeData.Item("Middle_Name"))

you put not in without telling me [smile] - I noticed it last night, it just didn't register.

You are right, and I'm wrong [blush]
 
Upvote 0 Downvote
<you put not in without telling me

Well, I'm a sneaky guy. :)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

sal21
  • Locked
  • Question
Replace , with ; in txt file
Replies
7
Views
83
muaricewayne
muaricewayne
2009luca
  • Locked
  • Question
GET date from classname
Replies
2
Views
73
2009luca
2009luca
2009luca
  • Locked
  • Question
TWO table with the same classname 1
Replies
3
Views
59
Andrzejek
Andrzejek
2009luca
  • Locked
  • Question
graph with access database - im on vb6 2
Replies
10
Views
130
strongm
strongm
2009luca
  • Locked
  • Question
GET splitted value from node and sub node in treeview
Replies
1
Views
72
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top