[2005] MySQL 'TEXT' field returns a NULL value to ADODB Recordset

[G_Hosa_Phat]

Hello again, all. I've run into an interesting phenomenon, and was hoping that someone here might be able to provide me with some guidance/direction:

I'm building an internal HelpDesk ticketing system that retrieves error details from a custom application we have built here and stores them to a MySQL database. For the purposes of work logs and such, some of the fields in the MySQL database need to have the capacity for fairly large amounts of text data, so I have set them as TEXT instead of VARCHAR.

I connect to the database using an ADODB recordset to retrieve the HelpDesk ticket details, including the data from these TEXT fields. The interesting thing here is, as soon as the recordset is opened, it has the data from these fields. However, the moment it moves on to the next instruction in my application, the TEXT field data seems to drop out of the recordset completely. That is, RS.Fields(X).Value returns the actual content of the field when the recordset is opened, but on the very next instruction, it changes to System.DBNull.

None of the other fields have this problem, so I tried changing the format of the field over to VARCHAR (this is a new application without much data, so I'm not worried about losing anything just yet). Sure enough, when it got the recordset this time, it retained the string value from the field. I went back to my TEXT field format and tried requerying the database just before the instruction where I need this particular field value with RS.Requery(), but it still dropped the data out of the recordset before I could grab it. I even tried setting a separate recordset with JUST that field, but no luck.

So, my question is, is there something I'm missing here? Is there some reason that VB seems to drop the contents of TEXT fields out of the recordset? Is there a way to get it to retain the data?

[kleinma]

can you show some code?

[G_Hosa_Phat]

Absolutely. Here's the whole big nastiness. The problem is the Error_Desc field, which is a TEXT field in the MySQL database. All of the other fields that contain String data for this particular part of the project are VARCHAR (varying sizes) and seem to work just fine.

SqlStr = "SELECT p.Priority_Desc AS 'Priority', s.Status_Desc AS 'Status', "
SqlStr = SqlStr & "h.User_Login AS 'User', l.Workstation AS 'Workstation', "
SqlStr = SqlStr & "l.Title AS 'Title', c.Cat_Desc AS 'Category', "
SqlStr = SqlStr & "sc.SubCat_Desc AS 'SubCategory', l.Closed_Date As 'Closed_Date', "
SqlStr = SqlStr & "r.Res_Desc AS 'Resolution', l.Error_Num AS 'Error_Num', "
SqlStr = SqlStr & "l.Error_Src AS 'Error_Src', l.Error_Desc AS 'Error_Desc', "
SqlStr = SqlSTr & "l.Module AS 'Module', l.Process AS 'Process', l.Ticket_Date AS 'Ticket_Date', "
SqlStr = SqlStr & "l.Resolved_Date AS 'Resolved_Date', l.Account AS 'Account' "
SqlStr = SqlStr & "FROM ((((((ticket_log l INNER JOIN ticket_priority p "
SqlStr = SqlStr & "ON l.Priority = p.Priority_ID) INNER JOIN ticket_status s "
SqlStr = SqlStr & "ON l.Status = s.Status_ID) INNER JOIN ticket_category c "
SqlStr = SqlStr & "ON l.Category = c.Cat_ID) INNER JOIN ticket_subcategory sc "
SqlStr = SqlStr & "ON l.SubCategory = sc.SubCat_ID) INNER JOIN helpdesk_user h "
SqlStr = SqlStr & "ON l.User = h.User_ID) INNER JOIN resolution_code r "
SqlStr = SqlStr & "ON l.Resolved_Code = r.Res_ID) WHERE l.ID = " & TicketNumber
 
RSTicket = HelpDeskCN.Execute(SqlStr) 
' *********************************************************************
' ** After executing the above line, the IDE shows me the String     **
' ** value of RSTicket.Fields("Error_Desc").Value in my Watch window.**
' **                                                                 **
' ** As soon as it moves to the next instruction, the Watch shows    **
' ** the value change to {System.DBNull}                             **
' *********************************************************************
 
Application.DoEvents()
 
If Not RSTicket.EOF Then
    If Not IsDBNull(RSTicket.Fields("Title").Value) Then Title = RSTicket.Fields("Title").Value
    If Not IsDBNull(RSTicket.Fields("User").Value) Then ReportedBy = RSTicket.Fields("User").Value
    If Not IsDBNull(RSTicket.Fields("Workstation").Value) Then ComputerID = RSTicket.Fields("Workstation").Value
    If IsNumeric(RSTicket.Fields("Error_Num").Value) Then ErrorNum = RSTicket.Fields("Error_Num").Value
    If Not IsDBNull(RSTicket.Fields("Error_Src").Value) Then ErrorSrc = RSTicket.Fields("Error_Src").Value
    ' *********************************************************************
    ' ** By the time it gets down here, it truly believes it is a null   **
    ' ** field.  Requerying the recordset doesn't help.  Nor does setting** 
    ' ** up another, completely separate recordset with just this field. **
    ' *********************************************************************
    If Not IsDBNull(RSTicket.Fields("Error_Desc").Value) Then ErrorDesc = RSTicket.Fields("Error_Desc").Value
    If Not IsDBNull(RSTicket.Fields("Module").Value) Then ErrorMod = RSTicket.Fields("Module").Value
    If Not IsDBNull(RSTicket.Fields("Process").Value) Then ErrorProc = RSTicket.Fields("Process").Value
    If IsDate(Format((RSTicket.Fields("Ticket_Date").Value), "MM/dd/yyyy hh:mm:ss")) Then OpenedDate = Format((RSTicket.Fields("Ticket_Date").Value), "MM/dd/yyyy hh:mm:ss")
    If Not IsDBNull(RSTicket.Fields("Priority").Value) Then TicketPriority = RSTicket.Fields("Priority").Value
    If Not IsDBNull(RSTicket.Fields("Status").Value) Then TicketStatus = RSTicket.Fields("Status").Value
    If Not IsDBNull(RSTicket.Fields("Category").Value) Then TicketCategory = RSTicket.Fields("Category").Value
    If Not IsDBNull(RSTicket.Fields("SubCategory").Value) Then TicketSubCategory = RSTicket.Fields("SubCategory").Value
    If Not IsDBNull(RSTicket.Fields("Resolution").Value) Then TicketResolution = RSTicket.Fields("Resolution").Value
    If Assigned Then AssignedTo = RSTicket.Fields("Assigned").Value

[kleinma]

Why are you not using OLEDB ADO.NET???

It looks like you are using old school COM based data connections (ADODB)

It's been some time since I worked with adodb (not since VB6) so I'm a bit hazy when it comes to it.

If you can migrate the code to ADO.NET it may fix it (should be a quick test to perform to do that), otherwise if no one gets you an answer on here, then I would suggest seeing if the MySQL site has any info on it.

[G_Hosa_Phat]

Yeah, I know I should be using ADO.NET connections with data sets and all that fun jazz, but I still have yet to successfully figure that out. Most of the applications I work with are in VB6 using the ADODB connections or (*gasp*) DAO because that's what they were originally designed with. I'm trying to build this new application from the ground up, so it's probably a good time to get my feet wet with ADO.NET, but I kinda fell back on what I "know" just to get the thing built and running.

Guess I need to take another stab at figuring out how to use ADO.NET. *sigh* :P

Still, if there IS a reason that the data from TEXT fields in MySQL drops out of the ADODB recordset like this, I'd definitely be interested in knowing. It might be an interesting bit of trivia somewhere down the road.

[kleinma]

not that you need a dataset necessarily.

You just use an OLEDBConnection, OLEDBCommand, and OLEDBDataReader

a datareader is very much like an ADODB recordset, that is forward only, so its for use when you want to query some data, and then use that data in your code.

There are many tutorials on ADO.NET

and also visit www.connectionstrings.com to get syntax for ADO.NET Connections to a MySQL database.