Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Having trouble displaying an SQL 7 'text' field using an ADO .ttx file.

Greetings,

The SQL table I'm reporting from has a 'text' field which contains anywhere from 2KB to 150KB worth of text.  The only rows in the table that will display are those which are 2KB in size.  Thus, I'm guessing the root of this problem is because the field is defined as type 'memo' in the .ttx file.  

I'm creating the .ttx file from within the VB code like this:

Call CreateTTXFiles

Is the 'memo' field the root of my problem?  If so, is Crystal Reports capable of handling the 'text' field and how?

Here is the connection code if this helps:

With ADOcn
        .Provider = "MSDASQL"
        .ConnectionString = ConnectString
        .CursorLocation = adUseClient
        .Open
End With

'Create the recordset for the main report
With ADOText
    .CursorType = adOpenDynamic
    .Open "select LogText from TableX", ADOcn
End With

0
John500
Asked:
John500
  • 13
  • 13
1 Solution
 
jswebyCommented:
Crystal Reports should have no problem handling memo fields, I have reports which print memo fields no problem.

J.
0
 
John500Author Commented:
jsweby,

That's not the point.  The issue is that the memo field is not as powerful as the text field.  The function 'CreateTTXFiles' shouldn't be creating a .ttx file with a memo field, because it's a text field.

I maintain that if this conversion was possible, the report would be running.  Otherwise what's the solution?

0
 
DRRYAN3Commented:
Lets have a look at the TTX file.  

All string types having a length in excess of 254 characters are defined as MEMO types in the data definition files.  Strings with a maximum length of less than 254 characters are defined as type STRING.  These are the only two types available for string data in TTX files.

Remember that you must specify the expected maximum length of the memo type field in the TTX file.  You may have a default value in your TTX file, or a undocumented assumed default of 2KB which you will have to explicitly override.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
DRRYAN3Commented:
0
 
John500Author Commented:
DRRYAN3,

Sounds like you got something there regarding the maximum length, it isn't stated.

-------------------------------------------------

; Field definition file for table: ADORecordset
TableID             string  11   string sample value
LogText             memo         memo sample data

-------------------------------------------------

It's estimated that the largest file will contain about 150,000 characters.  Thus, would this be correct:

LogText             memo 150000        memo sample data







0
 
DRRYAN3Commented:
Does adding the length parameter correct the problem with the report?
0
 
DRRYAN3Commented:
Oh, that was a question.

It appears that your syntax is correct.
0
 
John500Author Commented:
DRRYAN3,

I made the change but still no luck.  What else is a possible show stopper here??
0
 
DRRYAN3Commented:
Substitute a BLOB type for the MEMO type and omit the length spec.

Have you confirmed that the LogText field is populated correctly in the ADO recordset where the length exceeds 2K?
0
 
John500Author Commented:
DRRYAN3,

I'll try the 'BLOB' type.  But how do I confirm the LogText field is populated in the ADO recordset where the length exceeds 2K?

0
 
DRRYAN3Commented:
You'll probably have to write some code to step through the recordset.  Either examine the length of the field, or drop a bound text or rtf field onto a form and step through the recordset to make sure your fields are not being truncated for some reason.

Because you are passing a recordset to CR, I think it is important to know if you are getting good data into that recordset, or if the problem is in CR somewhere.
0
 
John500Author Commented:
DRRYAN3,

I say I boost this question up 50 points and then you can help with this extra code that you are talking about.  What do you think?

I'm aware of operations like this:

MsgBox ADOLog.RecordCount

Wouldn't you agree this is sufficient to prove the data is in the record set?
0
 
DRRYAN3Commented:
OK on points if you can wait until Monday.  

The code you mention is not sufficient.  It only shows there is data, but not the length of the data.

This ought to let you examine the contents being returned in the recordset:

public cn as new adodb.connection
public rs as new adodb.recordset
public cnstring as string
public strSQL as string

public sub mydbtest()
  cnstring = "Provider=SQLOLEDB;Password=password;Persist Security Info=True;" & _
             "User ID=userid;Initial Catalog=database;Data Source=servername;"
  cn.CursorLocation = adUseClient
  cn.open cnString
  strSQL = "SELECT LogText FROM TableX"
  rs.open strSQL, cn, adOpenDynamic, adLockOptimistic
  do while not rs.EOF
    ' Assuming a textbox1 on an open form
    textbox1.multiline = true
    textbox1.text = rs!LogText
    msgbox "Hit OK"
  done
end sub


Put this code on a form, add a textbox and resize the textbox to fill the form.  Run code to verify that contents of SQL table are being returned via ADO.

Have a good weekend.
0
 
John500Author Commented:
DRRYAN3,

Sounds good, my feelings exactly!  Ok Monday it is...

0
 
John500Author Commented:
DRRYAN3,

I put the code you gave on a form in a separate project.  When I run this thing I'm getting:

Compiler Error:  Variable not defined

on - 'textbox1'

... of the following code:

do while not rs.EOF
   ' Assuming a textbox1 on an open form
   textbox1.multiline = true
   textbox1.text = rs!LogText
   msgbox "Hit OK"
 done

I'm guessing I don't have some Reference included, yes/no?
0
 
DRRYAN3Commented:
When you created your new project you should haved added a reference to the ActiveX Data Objects Library.  I think you have done this, though, because by the time you get to the block of code in question, your recordset is already open.

The error you are getting, if on the textbox1.multiline, means that you either did not drop a textbox control on your form or that its name is not "textbox1" (or you are running an older version of VB that does not have that property for textboxes).
0
 
John500Author Commented:
DRRYAN3,

As you mentioned, I:

1) did reference the ActiveData Ojbects Library 2.5
2) did add a textbox control on the form
3) am running version 6.0 (sp 4) of Visual Studio

However, if I click the textbox object on the form and then view its properties, the properties box says I'm viewing:

Text1 Textbox

Keep in mind that this is a compiler error, so although the error occurs beyong the connection code, no recordset was actually opened.

Got any other ideas??


0
 
DRRYAN3Commented:
In the code I provided, wherever you see "textbox1", replace it with "text1".  Your text box has a different name.
0
 
John500Author Commented:
DRRYAN3 ,

Ok, some progress, but now I'm getting:

Compiler Error:  Can't assign to read-only property

on - '.MultiLine'

... of the following code:

do while not rs.EOF
  ' Assuming a textbox1 on an open form
  textbox1.multiline = true
  textbox1.text = rs!LogText
  msgbox "Hit OK"
Loop

What do you think here?

0
 
DRRYAN3Commented:
Set that property at design time by Viewing the Properties Page and finding Multiline.  Change the False to True.  I really shouldn't provide code examples on a Friday afternoon!
0
 
John500Author Commented:
DRRYAN3,

Using the code you gave me, I was able to get Form1 to display the Log text.  I used type 'BLOB' and 'MEMO' and both display the text within Form1 but not in the Crystal Report Viewer.  I get only the report title in the Report Viewer.

Knowing this, what then could be hindering the text from displaying in the Report Viewer?

Thanks.

0
 
DRRYAN3Commented:
Well you learn something everyday - I finally got it to work in VB6 with CR8.0

1.  On the file menu, choose options.  Uncheck the boxes labeled Translate DOS Strings and Translate DOS Memos

2.  Your TTX file MUST use a memo type without a size designator.  Mine looked like this (between 2 lines)

-----
UID     Number          11
LogText     Memo          My_Memo
-----

3. Verifying the database did not always put the memo field back into the list of available fields - if you find this to be the case, close CR and restart CR and then verify database.  Your LogText should be in the list of available fields.

4. When you add the LogText field to your report, make sure that you have checked the CanGrow box for that text field.

5. The code I used to select and display the report follows:

Private Sub Command1_Click()
  Dim rs As New ADODB.Recordset
  Dim strsql As String
  Dim mystring As String
  Dim i As Integer
  Dim crAp As New CRAXDRT.Application
  Dim rpt As CRAXDRT.Report
  Set rpt = crAp.OpenReport("l:\reports\forecast\report2.rpt")
  rpt.Database.Tables(1).SetDataSource rs, 3
  strsql = "select UID, LogText from testtab"
  rs.Open strsql, cnForecasts, adOpenDynamic, adLockOptimistic
  CRViewer1.ReportSource = rpt
  CRViewer1.ViewReport
  rs.Close
  Set rs = Nothing
End Sub

Where CRViewer1 is placed on its own form.

This worked where the LogText field was of length 0, 1, 100, 30000 and 150000.
0
 
John500Author Commented:
DRRYAN3,

I made the changes you mentioned above but still no luck.  However, the format properties for the CR text object I deselected the 'Keep Together' option and was able to get the logs to display.  

Even though I now get about 75 % of the larger logs to display, the reports still aren't displaying properly.  How can I get the data to fill the entire page.  As it is now, the first page is full but every succeeding page takes up only about half of the page.

I have 'Can Grow' selected and the max number is set to
'0' or no limit.  What enables text to fill the whole page, every page?
0
 
DRRYAN3Commented:
Not having the same problems here

Field Format
  Suppress is not checked
  Horiz Align = default
  Keep Object Together is not checked
  Close Border on Page Break is checked
  Can Grow is checked
  Max Lines = 0
  Text Rotation = 0
  Suppress if duplicated is not checked

Border, Font, Paragraph Formatting, Hyperlink tabs all at default values

Details Section Format
  Free Form Placement is checked
  Keep together is checked
  All others are unchecked.
0
 
John500Author Commented:
DRRYAN3,

Thanks for the comparison info.  I'm running the same stuff as you (i.e. VB 6.0 CR 8.0).  The only thing I noticed was different from yours is that I have no option for 'Free Form Placement' under the Details format section.

Could that be an option I need to include as a reference in order to that kind of functionality?  The entire text field still isn't displaying for the larger rows.

If you have no other suggestions, I'll close this out and fool around with it...
0
 
DRRYAN3Commented:
I think you are very close to getting what you want and the last bit will be the formatting options inside the report designer.

Good luck
0
 
John500Author Commented:
Yes, thanks!!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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