Invalid Use of Null error because value from MySQL Server doesn't last

I am getting an Invalid use of Null error at the following line in the attached code from a VB6 application:
sAddress = MyRS!Address
However, I do have a check for Null.  I put a stop point in the code, and the first time I hilight MyRS!Address it contains a value.  I then move the mouse away and move it back without stepping through any code, and now MyRS!Address is Null.  The same thing must be happening when application is run; at the IsNull statement MyRS!Address is not Null, but the the second time it is referenced a few lines later the value is Null.  Any suggestions as to why this is happening???  Could there be a problem at the MySQL server?

Thanks in advance!
Dim MyDB As ADODB.Connection
Set MyDB = New ADODB.Connection

MyDB.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserver;DATABASE=mydatabase;UID=myuid;PWD=mypwd"
MyDB.Open

Dim MyRS As ADODB.Recordset
Set MyRS = New ADODB.Recordset

SQLPARM = "Select * from USERS Where id = 5"

Set MyRS = MyDB.Execute(SQLPARM)

If Not MyRS.EOF And Not MyRS.BOF Then
	If IsNull(MyRS!Address) Then
		sAddress = ""
	Else
		sAddress = MyRS!Address
	End If
End If
                           
MyRS.Close
Set MyRS.ActiveConnection = Nothing

Open in new window

rhaakAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

leonstrykerCommented:
Replace this:

        If IsNull(MyRS!Address) Then
                sAddress = "" 
        Else
                sAddress = MyRS!Address
        End If

With this:

sAddress = MyRS!Address  & ""

Leon
0
rhaakAuthor Commented:
Much better!!!  Is it typical that you can only reference values once before it is lost?
0
leonstrykerCommented:
I do not think you lost the value. I think some how you are look at the previous value and then it switches to a NULL value which your code has an issue with, by adding a empty string you take care of the NULL and let you code run as it needs to.

Leon
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

rhaakAuthor Commented:
It does seem to be losing the value after referencing it only once.  If I have these two lines of code in a row I get different results:

MsgBox "Address is " & MyRS!Address
MsgBox "Address is " & MyRS!Address

I will get text for the address in the first message box and the address is blank in the second.  Is that typical?
0
leonstrykerCommented:
>Is that typical?

No, not at all. Those two lines should give you identical results.
0
rhaakAuthor Commented:
That's what I thought, it seems very strange.  Do know what the problem might be?  Could it be something at the server?  I've compared my connection to to many samples and have tried other ways of connecting to the database, but I always get the same issue.
0
KarenAnalyst programmerCommented:
The Execute method returns a forward-only cursor. Why don't you use the Open method? Something like:

    Set MyRS = New ADODB.Recordset
    MyRS.CursorLocation = adUseClient
    MyRS.Open SQLPARM, MyDB, adOpenStatic, adLockReadOnly
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rhaakAuthor Commented:
I had tried using MyRS.Open and was still losing the database values after one reference, but my parameters were different from what you have.  When I used your example above it works perfectly.  Thanks so much!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.