Solved

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

Posted on 2001-06-29
27
461 Views
Last Modified: 2008-02-01
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
Comment
Question by:John500
  • 13
  • 13
27 Comments
 
LVL 4

Expert Comment

by:jsweby
ID: 6238241
Crystal Reports should have no problem handling memo fields, I have reports which print memo fields no problem.

J.
0
 

Author Comment

by:John500
ID: 6238300
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6238706
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6238729
0
 

Author Comment

by:John500
ID: 6238966
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6238973
Does adding the length parameter correct the problem with the report?
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6239073
Oh, that was a question.

It appears that your syntax is correct.
0
 

Author Comment

by:John500
ID: 6239148
DRRYAN3,

I made the change but still no luck.  What else is a possible show stopper here??
0
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6239178
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
 

Author Comment

by:John500
ID: 6239221
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6239288
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
 

Author Comment

by:John500
ID: 6239421
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6239604
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:John500
ID: 6241229
DRRYAN3,

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

0
 

Author Comment

by:John500
ID: 6245008
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6245170
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
 

Author Comment

by:John500
ID: 6245234
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
 
LVL 12

Expert Comment

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

Author Comment

by:John500
ID: 6245495
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6245523
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
 

Author Comment

by:John500
ID: 6246140
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6246330
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
 

Author Comment

by:John500
ID: 6248706
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
 
LVL 12

Expert Comment

by:DRRYAN3
ID: 6249413
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
 

Author Comment

by:John500
ID: 6249467
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
 
LVL 12

Accepted Solution

by:
DRRYAN3 earned 105 total points
ID: 6249571
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
 

Author Comment

by:John500
ID: 6249621
Yes, thanks!!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now