[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 396
  • Last Modified:

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

0
rhaak
Asked:
rhaak
  • 4
  • 3
2 Solutions
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now