Solved

vb6 User Defined Type Issue

Posted on 2014-04-26
12
261 Views
Last Modified: 2014-04-26
I have an application that uses vb classic with a sql server 2008 back end and it is sending an interesting error.  I use it to time races and, in some races, I get an error if the .Email field is null but in others I don't.  Any help?

Here is where the error occurs:
        With PartEntry
            .PartID = PartData(0, i)
            .Race = PartData(1, i)
            .FirstName = PartData(2, i)
            .LastName = PartData(3, i)
            .Bib = PartData(4, i)
            .Gender = PartData(5, i)
            .Age = PartData(6, i)
            .AgeGrp = PartData(7, i)
            .DOB = PartData(8, i)
            .Email = PartData(9, i)
            .City = PartData(10, i)
            .State = PartData(11, i)
        End With

Open in new window


Here is where the type is defined:
Public Type PartEntry
    PartID As String * 5
    Race As String * 8
    FirstName As String * 10
    LastName As String * 12
    Bib As String * 4
    Gender As String * 3
    Age As String * 3
    AgeGrp As String * 11
    DOB As String * 10
    Email As String * 30
    City As String * 10
    State As String * 2
End Type

Open in new window

0
Comment
Question by:Bob Schneider
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 144 total points
Comment Utility
> I get an error if the .Email field is null
you have to check if the value is null before assigning it to your typed variable, and assign empty instead of null, for example
0
 

Author Comment

by:Bob Schneider
Comment Utility
Ok but why is it only a problem in some cases.  Is it possible that the when it doesn't throw an error the field is "empty" but not null?

I didn't know user-defined strings could not be null?
0
 

Author Comment

by:Bob Schneider
Comment Utility
BTW, how do you assign "empty"
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 144 total points
Comment Utility
this would be the line:
.Email = iif( isnull(PartData(9, i)), Empty, PartData(9, i))

Empty is a keyword that will assign an empty string ("") to string typed variables, 0 to numerical data types ...

and yes, I presume that some are actually not really NULL, but empty string.
you could solve this also at the query level, returning only non-null values.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
Comment Utility
It looks to me like you posted code that you created just for this thread rather than what's in your app because you can't assign values to a type (user or built-in type). That would be like trying to do String = "MyString" so I assume that what you really have is something like


Public Public Type PE
    PartID As String * 5
    Race As String * 8
    FirstName As String * 10
    LastName As String * 12
    Bib As String * 4
    Gender As String * 3
    Age As String * 3
    AgeGrp As String * 11
    DOB As String * 10
    Email As String * 30
    City As String * 10
    State As String * 2
End Type
Public PartEntry As PE

Open in new window

Note the changes in lines 1 and 15 above.

        With PartEntry
            .PartID = PartData(0, i)
            .Race = PartData(1, i)
            .FirstName = PartData(2, i)
            .LastName = PartData(3, i)
            .Bib = PartData(4, i)
            .Gender = PartData(5, i)
            .Age = PartData(6, i)
            .AgeGrp = PartData(7, i)
            .DOB = PartData(8, i)
            .Email = PartData(9, i)
            .City = PartData(10, i)
            .State = PartData(11, i)
        End With

Open in new window


In addition to that I have a few questions.
1) What error do you get?
2) Does you application require fixed-length records?
3) Can you reproduce the error consistently?
0
 

Author Comment

by:Bob Schneider
Comment Utility
Right.  I just posted what I thought would be the concern.  The utility is working well except for the null thing.

Is iif a built-in vb6 function?
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
Comment Utility
Yes but an inefficient one
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
Comment Utility
And what about my 3 questions?
0
 

Author Comment

by:Bob Schneider
Comment Utility
1) Invalid use of Null
2) The app does not require fixed length strings
3) The error occurs every time I try to use the type to populate a multi-column list box with  user data...some data will be empty for some rows (email address, city, state, etc).

Am I correct in understanding that I need to replace nulls with emptys as indicated above or is there another approach?
0
 
LVL 45

Accepted Solution

by:
aikimark earned 71 total points
Comment Utility
Change your assignment to this.  It should always work, even if PartData(9, i) is Null.
.Email = vbNullstring & PartData(9, i)

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
Comment Utility
It may not help your problem but if you don't need fixed length strings then try this which has the advantage that you won;t get truncated values if your data happens to be longer than any of your type's subfields..

Public Type PartEntry
    PartID As String 
    Race As String
    FirstName As String
    LastName As String
    Bib As String 
    Gender As String
    Age As String 
    AgeGrp As String
    DOB As String
    Email As String 
    City As String 
    State As String
End Type

Open in new window

0
 

Author Closing Comment

by:Bob Schneider
Comment Utility
Good discussion.  Thanks
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now