Problem with NULL Text field but being stored as (Memo) in MYSQL database

------_=_NextPart_001_01C5B864.981E2A22
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Dear Sir/Madam;

I downloaded a MYSQL 4.1.14 database, created a table with fields of =
Char, Varchar, Text, Date and Double as data types. I developed a small =
VB 6.0 application to write data into the table and it works fine. I =
captured 5 records. On 3 records I did not enter any data in the Text =
field datatype. I have also found out that this Text field data is being =
stored as (Memo) when I open the table in datasheet. I have gone further =
and tried to retrieve the records using a recordset and assign the =
values from the recordset into a field say Text1 on a form. This works =
fine if the Text field stored as (Memo) has value. In cases where it as =
NULL, I get this error mesage from my small VB application - =
"Multiple-step OLE DB operation generated errors. Check each OLE DB =
status value, if available. No work was done."=20

In my VB application the code is like this:

"If NOT ISNULL(rs!Rep) then Text1.text =3D rs!Rep." The (NOT ISNULL) I =
am using it to test if there is any value.=20

I have tried to use "If len(trim(rs!Rep)) > 0 then Text1.text =3D =
rs!Rep" and it does not work either - same error.

I have checked my Data Source Name and the Login is fine, Connect =
Options I did not select any option and Advanced I selected the first =
top 3 options.

So I don't know where I am going wrong if you can help please because I =
have a project waiting for this break-through.

Thanks.


------_=_NextPart_001_01C5B864.981E2A22--

RE: Problem with NULL Text field but being stored as (Memo) in MYSQL database

I use a routine like this to filter data from recordsets into control. I
don't think I have ever used a memo field though.

Public Function BlankNull(V As Variant) As String
' For generating strings to put in controls
If IsNull(V) Then
BlankNull =3D ""
Else
On Error Resume Next
BlankNull =3D CStr(V)
If Err.Number =3D 3021 Then BlankNull =3D ""
End If
End Function

I also use a routine like the following to interact with the database
and in particular it dumps out the entire error collection of the
connection object when things go wrong. This often helps find the root
cause of those multi step errors. I don't use things like data controls
etc. because they hide too much of the detail from you, making it almost
impossible to resolve some errors because they are buried in MS code. I
also recommend using VBMySQLDirect instead of ADODB. The routine below
was actually working with an MS SQL Server but I use a similar approach
with MySQL via VBMySQLDirect.

Public Function RunSQL(SQL As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
Dim Cmd As String
Dim Msg As String
Dim E As ADODB.Error
=20
On Local Error GoTo RunSQL_Err
=20
Cmd =3D UCase(Left(SQL, 4))
If Cmd =3D "SELE" Or Cmd =3D "SHOW" Then
' SELECT or SHOW can return a recordset
Set rs =3D New ADODB.Recordset
rs.Open SQL, gCon, adOpenStatic, adLockReadOnly, adCmdText
Set RunSQL =3D rs
Else
' An action query, don't worry about recordset
gCon.Execute SQL, gRecordsAffected, adCmdText
End If

RunSQL_Exit:
Exit Function
=20
RunSQL_Err:
If gCon.Errors.Count <> 0 Then
Msg =3D "SQL Error " & CStr(Err.Number) & ": " & Err.Description
For Each E In gCon.Errors
Msg =3D Msg & vbCrLf & E.Description
Next E
gCon.Errors.Clear
Msg =3D Msg & vbCrLf & "SQL: " & SQL
MsgBox Msg, , "RunSQL"
Else
MsgBox "Error " & CStr(Err.Number) & ": " & Err.Description, ,
"RunSQL"
End If
Resume RunSQL_Exit
End Function

John B.
-----Original Message-----
From: Shepherd Madziwa [mailto:SMadziwa@psmi.co.zw]=20
Sent: Tuesday, 13 September 2005 10:41 PM
To: myodbc@lists.mysql.com
Subject: Problem with NULL Text field but being stored as (Memo) in
MYSQL database

Dear Sir/Madam;

I downloaded a MYSQL 4.1.14 database, created a table with fields of
Char, Varchar, Text, Date and Double as data types. I developed a small
VB 6.0 application to write data into the table and it works fine. I
captured 5 records. On 3 records I did not enter any data in the Text
field datatype. I have also found out that this Text field data is being
stored as (Memo) when I open the table in datasheet. I have gone further
and tried to retrieve the records using a recordset and assign the
values from the recordset into a field say Text1 on a form. This works
fine if the Text field stored as (Memo) has value. In cases where it as
NULL, I get this error mesage from my small VB application -
"Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done."=20

In my VB application the code is like this:

"If NOT ISNULL(rs!Rep) then Text1.text =3D rs!Rep." The (NOT ISNULL) I =
am
using it to test if there is any value.=20

I have tried to use "If len(trim(rs!Rep)) > 0 then Text1.text =3D =
rs!Rep"
and it does not work either - same error.

I have checked my Data Source Name and the Login is fine, Connect
Options I did not select any option and Advanced I selected the first
top 3 options.

So I don't know where I am going wrong if you can help please because I
have a project waiting for this break-through.

Thanks.


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Re: Problem with NULL Text field but being stored as (Memo) in MYSQLdatabase

Shepherd Madziwa wrote:

>Dear Sir/Madam;
>
>I downloaded a MYSQL 4.1.14 database, created a table with fields of Char, Varchar, Text, Date and Double as data types. I developed a small VB 6.0 application to write data into the table and it works fine. I captured 5 records. On 3 records I did not enter any data in the Text field datatype. I have also found out that this Text field data is being stored as (Memo) when I open the table in datasheet. I have gone further and tried to retrieve the records using a recordset and assign the values from the recordset into a field say Text1 on a form. This works fine if the Text field stored as (Memo) has value. In cases where it as NULL, I get this error mesage from my small VB application - "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."
>
>In my VB application the code is like this:
>
>"If NOT ISNULL(rs!Rep) then Text1.text = rs!Rep." The (NOT ISNULL) I am using it to test if there is any value.
>
>I have tried to use "If len(trim(rs!Rep)) > 0 then Text1.text = rs!Rep" and it does not work either - same error.
>
>I have checked my Data Source Name and the Login is fine, Connect Options I did not select any option and Advanced I selected the first top 3 options.
>
>So I don't know where I am going wrong if you can help please because I have a project waiting for this break-through.
>
>Thanks.
>
>
>
>
You'll probably need to send more info than that to get help. Try
sending your table definition, from:

show create table table_name

.... replace table_name with the name of your table.

--
Daniel Kasak
IT Developer
NUS Consulting Group
Level 5, 77 Pacific Highway
North Sydney, NSW, Australia 2060
T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989
email: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au

--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org