The source code in this issue is designed for all versions of Visual Basic.
One of the problems encountered by developers using databases in Visual Basic are Null values. If you try to assign Null values to typed variables such as String or Long you receive a Type Mismatch error. This issue introduces one way around this problem.
Function NullConvert(vDest As Variant, v As Variant) As Variant
The non-null value will be returned as whatever data type was passed into NullConvert by the parameter vDest
Function NullConvertDate(v As Variant) As Date Function NullConvertCurrency(v As Variant) As Currency Function NullConvertLong(v As Variant) As Long Function NullConvertNumber(v As Variant) As Variant Function NullConvertString(v As Variant) As String Function NullConvertBoolean(v As Variant) As Boolean
The non-null value will be returned.
This subroutine does not really do anything useful except demostrate the various ways the NullConvert functions can be called. Note that you must pass the destination variable to the NullConvert routine so that it can return the correct data type.
Sub ProcessDatabase()
Dim DB As Database
Dim RS As Recordset
Dim sString As String
Dim lLong As Long
Dim cCurrency As Currency
' Open database
Set DB = OpenDatabase(App.Path & "\0010.mdb")
' Get recordset for processing
Set RS = DB.OpenRecordset("Select * from [testtable]")
RS.MoveFirst
' loop until we have no more records
While Not RS.EOF
' Data Type Specific Functions
' sString will either be "" or it's value from the database
sString = NullConvertString(RS.Fields("VString").Value)
' lLong and cCurrency will either be 0 or it's value from the database
lLong = NullConvertLong(RS.Fields("VLong").Value)
cCurrency = NullConvertCurrency(RS.Fields("VCurrency").Value)
' Generic NulConvert function
sString = NullConvert(sString, RS.Fields("VString").Value)
lLong = NullConvert(lLong, RS.Fields("VLong").Value)
' This method of calling NullConvert will only work in VB 4 or later
NullConvert cCurrency, RS.Fields("VCurrency").Value
RS.MoveNext
Wend
' Close recordset and database
RS.Close
DB.Close
End Sub
To see the source code for this issue you must be a subscriber to Code of the Week. If you are a subscriber the source code is available at the following address: http://www.codeoftheweek.com/membersonly/bi/0010.html
This is not necessarily the most efficient way to handle Null values. For strings you can simply do something like this:
sDest = "" & RS.Fields("StringField")
These routines attempt to simplify the methods necessary to handle null values.
If you have any questions that you can not seem to figure out, drop us a email at info@codeoftheweek.com and we will do our best to help you out.
That concludes this issue of COTW. We hope you find the source code useful in your development.
The below describes the ways you can supply us some feedback about COTW. We would like to see our members help mold COTW into the best Visual Basic source code resource available. But to do that we need your feedback about what you like and what you do not like about COTW.
We are currently planning for the next several issues of Code of the Week. If you have any suggestions of source code that you would like to see covered, please jump to our feedback page at http://www.codeoftheweek.com/feedback.html and let us know.
If you have any suggestions for topics you would like to see covered or questions about this issue, please email them to info@codeoftheweek.com or use online feedback form at http://www.codeoftheweek.com/feedback.html.
If you have any source code you would like to submit for possible inclusion in COTW, please fill out our online submission form at http://www.codeoftheweek.com/submission.html.
Thank you for trying Code of the Week for Visual Basic.
Your free trial expires after you receive your fourth issue. If you want to continue to receive Code of the Week you can get 52 issues of COTW for only $19.95. This is a full year of Visual Basic source code and information to help with all your development. So don't wait, subscribe now! The quickest way to subscribe is to jump to our online order form at http://www.codeoftheweek.com/order.html
We accept payment by Mastercard, Visa, check or money order. All payments must be in U.S. Dollars. If you are an international customer paying by credit card is usually the easiest method of payment.
If you prefer not to visit our web site:
1. Enclose a note with your email address and name.
2. If you are paying by credit card, make sure you include the credit card number, credit card expiration and name on the credit card and your signature. If you are paying by check, make it payable to C&D Programming Corp.
3. Mail it to (credit card payments can be faxed to (212) 504-7945):
C&D Programming Corp.
PO Box 20128
Floral Park, NY 11002-0128
4. As soon as we receive your payment you will be added to our paid subscriber list.