I have a problem accessing data on a MySQL database server sitting on a LINUX box; I'm working with VB6 and Remote Data Object v2.0.

The task is this: upon opening a form, you make a connection to the MySQL database and retrieve some field values. You take the values and stuff them into some form controls like text boxes, and if a field is a null, make sure you use an empty string to stuff the text box with so the application doesn't fall over. Simple enough.

All of this works nicely until I come to a field called "notes" which is of type text in the MySQL database. Running the code results in the good old "run-time error 94 - invalid use of null" message.

This is my subroutine, and all I'm concerned with at this point is successfully getting the contents of "notes" stuffed into a text box:



Private Sub Form_Load()
Dim cnMySql As New rdoConnection
Dim rdoQry As New rdoQuery
Dim rdoRS As rdoResultset

cnMySql.CursorDriver = rdUseOdbc
cnMySql.Connect = "dsn=TAS"
cnMySql.EstablishConnection


With rdoQry
.Name = "getNotes"
.SQL = "SELECT name, notes FROM tbHotels WHERE name = '" & frmMain.cbName.Text & "'"
.RowsetSize = 1
Set .ActiveConnection = cnMySql
Set rdoRS = .OpenResultset(rdOpenKeyset, rdConcurRowVer)
End With


With rdoRS
If .RowCount > 0 Then
If IsNull(!notes) Then
Me.Text1.Text = ""
Else
Me.Text1.Text = !notes
End If
End If
End With

'Close record set & close connection to the database
rdoRS.Close
cnMySql.Close

End Sub



Now as you can see, we test !notes for a null and do the right thing:

With rdoRS
If .RowCount > 0 Then
If IsNull(!notes) Then
Me.Text1.Text = ""
Else
Me.Text1.Text = !notes
End If
End If
End With



Now if !notes has nothing in it, ie it is null, then the first condition executes ( Me.Text1.Text = "" ) and we see an empty box, as we should.

Now the problem is when !notes has SOMETHING in it. You'd expect that the second condition would execute (Me.Text1.Text = !notes ), and it does, but yet we get the "run-time error 94 - invalid use of null" message and it all comes to a crashing halt.

So what gives? Here's something interesting:
If I set a breakpoint on the line If IsNull(!notes) Then and inspect the value of !notes when it's being pulled in from a record that I know isn't empty by doing a mouse-over on the variable name, I see that there is indeed text there. Now if I move the mouse away and then back on the name again, it reads a value of NULL. If I inspect any other field value pulled out of the record, they're all there and behave.

The only difference is that they're of different data types.

So what I figure is happening is: you read !notes out of the database (let's assume that it's not an empty field - it's actually got some text in it we want to get at), and when you look at it the first time to test the IsNull(!notes) condition, it comes back with the appropriate response that yes, it's got stuff there, and so we're going to execute (Me.Text1.Text = !notes ). Now what I think happens is that by the time we look at !notes a second time, to perform the (Me.Text1.Text = !notes ) business, it's somehow lost its contents and been turned into a NULL. As by this time we're already in the (Me.Text1.Text = !notes ) section of the if/then loop, naturally it tries to stuff a NULL into a text box and of course then it all comes to a screaming halt.

There's nothing else running anywhere in the program that could affect the !notes field, so what's happening?

It looks like you can only access the variable one time, even though it's only in read-only mode, and then it gets nuked.

Earlier, I mentioned fields of other data types behaved perfectly with the same bit of code, used to stuff other boxes on the form. Now, if I change !notes to type varchar, for example, the problem goes away and it's all perfect! In fact, the problem only occurs when !notes is of type text, medium text, long text. If you just keep it as char or varchar then no problem. Seems anything like text of 65k size up make this problem occur.

Now I can't afford to have !notes limited to just 255 characters, so I need to make it of type text, but how the hell do I get around this vanishing act?

Can anyone tell me what's going on? I'm about to call my therapist.