rhaak
asked on
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!
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Leon
ASKER
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?
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?
>Is that typical?
No, not at all. Those two lines should give you identical results.
No, not at all. Those two lines should give you identical results.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
ASKER