VB6, MySQL, and disappearing data...

[NotExcessive]

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.

[brucevde]

The Text datatype is a blob-type field. The Text field stores a pointer to the actual location of the data (which is located elsewhere within the database).

ADO accesses this data in "chunks" each time the blob field is referenced. I am not sure of the default chunk size 4k, 8k or ???. It may be provider dependant.

Assume the data is 2k and the default chunk is 4k

If IsNull(!notes) then '--> ADO will get the first 4k of data to see if it is Null.
Else
  Text1.Text = !notes '-->ADO will get the next chunk starting at byte 4k+1 
'- however all data was returned in the call to IsNull, so the second chunk is null.

But in your case, there is no need to check for IsNull on Text data

If IsNull(!notes) Then
  Me.Text1.Text = ""
Else
  Me.Text1.Text = !notes
End If
 
'Can be replaced with
 
Text1.Text = "" & !notes

Or use the Fields GetChunk method to control the size
Text1.Text = "" & rs.Fields("Notes").GetChunk(1000000)
returns up to 1million characters (or bytes)

[NotExcessive]

OK thanks - I've changed it to Text1.Text = "" & !notes and that's done the trick, but I still don't understand why it bumps the pointer every time I access the variable. I should be able to reference any variable any time I like. I don't play in VB at all apart from getting involved in this one project so I'll just accept that it bumps the pointer for whatever the reason and get on with it.

[dee-u]

Textboxes would only accept strings hence the...

Text1.Text = "" & !notes

would mean that if notes is null then it would be appended with "" and as such the textbox would accept it...

[NotExcessive]

Yes that I know but my problem was the pointer was being bumped every time I accessed it, and I still don't know why.

[Al42]

Yes that I know but my problem was the pointer was being bumped every time I accessed it, and I still don't know why.

Because you're looking at a transient pointer, not static text.

[NotExcessive]

OK, so if I wanted to reference the variable repeatedly, how would I do that? I'm used to assembly and C where if I reference a pointer it doesn't bump unless I want it to.

[brucevde]

We are getting the terminology confused. The "Pointer" I mentioned is not like a C pointer, its more like the current record of a recordset.

I.E. If you do several MoveNext, you must issue a MoveFirst to start at the beginning.

ADO does not reset the "pointer" until you reference another field. This should work

If IsNull(!notes) Then
  Me.Text1.Text = ""
  Me.Text2.Text  = !Name
Else
  Me.Text2.Text = !Name
  Me.Text1.Text = !notes
End If

[NotExcessive]

This is what's thrown me. That's basically the same code I have:

If IsNull (!notes) Then
Me.Text1.Text = ""
Else
Me.Text1.Text = !notes
End If

which doesn't work because !notes returns a valid value on the first reference and a null on the second reference.

[brucevde]

The code is not the same.

!Name is used after the first reference to !Notes but before the second reference. ADO assumes you are finished with !Notes and will reset its "pointer". The second call to !Notes will now load the data into the text box.

[NotExcessive]

So if I read this right, I've got to go and do a "dummy" read of something else before coming back to read it again, otherwise the pointer doesn't get reset?