Solved

vb6 User Defined Type Issue

Posted on 2014-04-26
12
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 144 total points
ID: 40024390
> 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
ID: 40024396
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
ID: 40024397
BTW, how do you assign "empty"
0
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 144 total points
ID: 40024406
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 47

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
ID: 40024477
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
ID: 40024979
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
 
LVL 47

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
ID: 40024983
Yes but an inefficient one
0
 
LVL 47

Assisted Solution

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

Author Comment

by:Bob Schneider
ID: 40024991
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
ID: 40024993
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 47

Assisted Solution

by:Martin Liss
Martin Liss earned 285 total points
ID: 40025076
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
ID: 40025097
Good discussion.  Thanks
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

726 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