Re: Null
From: MikeD (nobody_at_nowhere.edu)
Date: 09/04/04
- Next message: Gajendra: "Re: Multiple assignment"
- Previous message: Russ Holsclaw: "Re: Null"
- In reply to: Gary: "Null"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 3 Sep 2004 20:10:44 -0400
"Gary" <Gary@discussions.microsoft.com> wrote in message
news:0378B10A-6932-4537-971E-A77132607371@microsoft.com...
> Hi all, In my vb code I return a recordset from a sql database. This
> recordset contains a variety of fields with varing data types. Some are
> string and some are integers. As I return the recordset I use the Write
> command to output the data to a comma seperated text file.
> My client now wants to import that text file into their own SQL database.
We
> have designed the tables but we have a problem. If the data type in SQL is
> interger and the value is null then when my recordset assign the value to
my
> variable I get an error invalid use of null.
> How can I get the null back into SQL on the clients end.
> I have tried using the print command but this does not solve the problem.
> I hope I have explaind the situation clear enough, if not please let me
know
> and thanks in advance for any help.
You can't assign a Null to anything other than a Variant. If your variable
is declared as anything other than a Variant, you'll get an error if you
attempt to assign a Null value to it (DON'T change the variable's data type
to a Variant just to avoid this, as it'll likely cause more problems).
If the database column allows for null values, then, in your VB code, you
must check for a Null value. Otherwise, you WILL have problems. If the
column's type is a string, you can just append a 0-length string to that and
VB will take care of it For example, let's say you have a column in your
table for a person's middle initial and this column allows for nulls
(because not everybody has, or is going to provide, a middle initial; so, it
makes sense for such a column to allow nulls).
You could write (air code):
Dim MiddleInitial As String
MiddleInitial = MyRecordSet.Fields("MiddleInitial").Value & ""
By appending (or pre-pending) a 0-length string to a Null value, VB does
some behind-the-scenes work. In effect, a 0-length string is assigned to the
string variable. This only works with strings. If the column's data type
is, for example, numeric, then you must use the IsNull function to determine
if the value is Null (and do whatever may be appropriate, possibly just
assigning 0). Many will tell you that even the "shortcut" for string data
types is not advisable because it could be "done away with" at any time;
whereas IsNull will always work.
The point is that you have to write code to handle Null values in some way.
Declaring variables to be Variants is the easy way, but it's FAR from the
best (and I know of nobody that would recommend it).
Mike
- Next message: Gajendra: "Re: Multiple assignment"
- Previous message: Russ Holsclaw: "Re: Null"
- In reply to: Gary: "Null"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
