FR.NET truncating custom SQL

pinbotpinbot Texas
edited 4:46PM in FastReport .NET

This may be causing my previous problem.

When I have this custom query stored in the ReportResourceString it is truncating the Custom SQL Query.



select '('+cast(transactiontype as varchar(4))+') '+LedgerDescription as CodeAndDesc,
convert(varchar(7),transactiondate,20) as MonthYear, sum(transactionamount) as Amount
from onlineledgerview
where transactiondate>='2009-01-01'
group by '('+cast(transactiontype as varchar(4))+') '+LedgerDescription,convert(varchar(7),transactiondate,20)



I've tried putting a space before the >= but that didn't help.

I've attached 2 images. I can preview right after designing the report. But if I try to opent the report in VS2005, I get the errors from my previous post. If I exit VS2005 and return to design the report, I get the truncated SQL as shown in the second image.


Bryan


Comments

  • pinbotpinbot Texas
    edited 4:46PM

    Here is the ReportResourceString. Looks like all the SQL is there, its just dropping everything after the > in the custom sql when I recall the report.





    <?xml version="1.0" encoding="utf-8"?>
    <Report ReportInfo.Created="03/05/2009 05:37:02" ReportInfo.Modified="03/05/2009 12:35:17" ReportInfo.CreatorVersion="1.0.146.0">
    <Dictionary>
    <MsSqlDataConnection Name="Connection" ConnectionString="rijcmlqHwzMSgwTFaHpjtG3vsOj8RzkaHGB4c4pZ56fSfbbrFFLwXzS2aNolzDzEwvBCkCCdd4A69eDOKBV0Rbd6pC/765p6puO02uAakpR0jZhiEpMMQK9Y047Tlk4KHFK8peokQVhr5HPM1OqFvj6EkH1G9Q9nCS3nXLNCiRP4Mg7JZvkPuGOLGzlfC1w6cE3OzdSHgaN1kUAU8w1c4h/o/n6vg=="/>
    <MsSqlDataConnection Name="Connection1" ConnectionString="rijcmlqHwzMSgwTFaHpjtG3vsOj8RzkaHGB4c4pZ56fSfbbrFFLwXzS2aNolzDzEwvBCkCCdd4A69eDOKBV0Rbd6pC/765p6puO02uAakpR0jZhiEpMMQK9Y047Tlk4KHFK8peokQVhr5HPM1OqFvj6EkH1G9Q9nCS3nXLNCiRP4Mg7JZvkPuGOLGzlfC1w6cE3OzdS+n6RIW0Rf6+yCdX7a2xV3Q==">
    <TableDataSource Name="Table1" Alias="LedgerQuery" Enabled="true" TableName="Table" SelectCommand="



    select '('+cast(transactiontype as varchar(4))+') '+LedgerDescription as CodeAndDesc,
    convert(varchar(7),transactiondate,20) as MonthYear, sum(transactionamount) as Amount
    from onlineledgerview
    where transactiondate>='2009-01-01'
    group by '('+cast(transactiontype as varchar(4))+') '+LedgerDescription,convert(varchar(7),transactiondate,20)

    ">
    <Column Name="CodeAndDesc" DataType="System.String"/>
    <Column Name="MonthYear" DataType="System.String"/>
    <Column Name="Amount" DataType="System.Decimal"/>
    </TableDataSource>
    </MsSqlDataConnection>
    </Dictionary>
    <ReportPage Name="Page1" Landscape="true" PaperWidth="279.4" PaperHeight="215.9" FirstPageSource="15" OtherPagesSource="15" Guides="357.97,622.57,18.9,248.74,124.52,208.76">
    <PageHeaderBand Name="PageHeader1" Width="980.53" Height="41.92" Guides="9.45,28.35">
    <TextObject Name="Text2" Left="357.97" Top="9.45" Width="264.6" Height="18.9" Border.Lines="Bottom" Text="ALN Billing Codes by Month" HorzAlign="Center" Font="Arial, 12pt, style=Bold"/>
    </PageHeaderBand>
    <DataBand Name="Data1" Top="44.21" Width="980.53" Height="89.17" Guides="9.45,68.13,0,19.56,39.12,58.68">
    <MatrixObject Name="Matrix1" Left="18.9" Top="9.45" Width="229.84" Height="58.68" FixedRows="1" FixedColumns="1" DataSource="Table1">
    <MatrixColumns>
    <Header Expression="[LedgerQuery.MonthYear]"/>
    </MatrixColumns>
    <MatrixRows>
    <Header Expression="[LedgerQuery.CodeAndDesc]"/>
    </MatrixRows>
    <MatrixCells>
    <Cell Expression="[LedgerQuery.Amount]"/>
    </MatrixCells>
    <TableColumn Name="Column1" Width="105.62" AutoSize="true"/>
    <TableColumn Name="Column2" Width="84.24" AutoSize="true"/>
    <TableColumn Name="Column3" Width="39.98" AutoSize="true"/>
    <TableRow Name="Row1" Height="19.56" AutoSize="true">
    <TableCell Name="Cell1" Text="CodeAndDesc" HorzAlign="Center" VertAlign="Center"/>
    <TableCell Name="Cell2" Text="[MonthYear]" HorzAlign="Center" VertAlign="Center"/>
    <TableCell Name="Cell7" Text="Total" HorzAlign="Center" VertAlign="Center"/>
    </TableRow>
    <TableRow Name="Row2" Height="19.56" AutoSize="true">
    <TableCell Name="Cell3" Text="[CodeAndDesc]" HorzAlign="Center" VertAlign="Center"/>
    <TableCell Name="Cell4" Text="[Amount]" HorzAlign="Right" VertAlign="Center"/>
    <TableCell Name="Cell8" HorzAlign="Right" VertAlign="Center"/>
    </TableRow>
    <TableRow Name="Row3" Height="19.56" AutoSize="true">
    <TableCell Name="Cell5" Text="Total" HorzAlign="Center" VertAlign="Center"/>
    <TableCell Name="Cell6" HorzAlign="Right" VertAlign="Center"/>
    <TableCell Name="Cell9" HorzAlign="Right" VertAlign="Center"/>
    </TableRow>
    </MatrixObject>
    </DataBand>
    <PageFooterBand Name="PageFooter1" Top="135.66" Width="980.53" Height="18.9"/>
    </ReportPage>
    </Report>
  • AlexTZAlexTZ
    edited 4:46PM
    I've tried to test it in both WinForms and WebForms, but it works for me, nothing truncated. How can I reproduce this bug?
  • pinbotpinbot Texas
    edited 4:46PM
    OK.

    Just did a simple test to confirm I'm not going crazy.

    1. Open VS2005 and start a new web application.
    2. Drop a Web Report on the default blank page.
    3. Design the Report (don't change any properties of WebReport).
    4. Click on Add Data Source
    5. I already have a connection to a SQL2000 Server DB so Chose That
    6. Click on Add SQL Query
    7. I did a simple "select * from alnaccountledger where transactiondate >'2009-03-01' "
    8. Next all the way through. All the fields show up in the Data Tree. Drop 2 fields in the Data Band.
    8. Preview to make sure it works. It Does.
    9. Close the designer.
    10. Try to run the app in VS 2005. I get an error.
    11. Save the Project.
    12. Exit VS 2005
    12. Reload VS 2005 and then the project.
    13. Design the report again.
    14. In the Data Tree the Connection and Table show up but the Fields are no longer there.
    15. Right click on the table and click "Edit"
    16. Click on Next (through the table name)
    17. The SQL is changed and anything after the >= is missing

    Here is the copy-paste:

    select * from alnaccountledger where transactiondate


    It has truncated the text in the Data tree.





  • pinbotpinbot Texas
    edited 4:46PM


    FR.NET 1.0.146 BTW.

  • pinbotpinbot Texas
    edited 4:46PM

    Also,

    Did you exit visual studio?

    If you just click on Design Report again, the SQL is still there but if you exit VS2005, go back in and reopen your project, that is when it appears to truncate the SQL.

    Bryan
  • AlexTZAlexTZ
    edited 4:46PM
    Bryan,

    Try the latest available FR.Net, I've changed something related to datasources.
  • pinbotpinbot Texas
    edited 4:46PM

    I tried the latest and same problem.

    I've made you a video so you can see the exact steps I'm taking and the issues I'm having.

    I create a report, get some errors, fix them, then get the errors again.

    It's kinda large because I have a 1600x1200 screen resolution.

    http://www.monarchair.com/frproblem.avi

    Let me know after you see it so I can delete the file.


    Thanks

    Bryan




  • AlexTZAlexTZ
    edited 4:46PM
    I've downloaded the video, thanks. You may delete it now.
  • AlexTZAlexTZ
    edited 4:46PM
    I've tried to reproduce these steps in Visual Studio 2008 (unfortunately I don't have Web in my Visual Studio 2005). I've used another db, of course, and the select statement was
    select * from DVDs where ID >= '2'
    The result is ok. I can't check it in VS2005 now, I will do it tomorrow. Could you send me the "Default.aspx" file? I want to look at how the ReportResourceString is stored.
  • pinbotpinbot Texas
    edited 4:46PM
    I've attached it.

    Were you able to see what it is doing? You exited VS2008 and went back in and your query was still there?

    I can't even exit the designer and have it work.

    I had to rename the file default.txt because, surprise surprise, it won't let me upload .aspx files!


    Bryan

  • pinbotpinbot Texas
    edited 4:46PM
    Let's try that again!

    Couldn't upload as an .aspx or .txt



  • AlexTZAlexTZ
    edited 4:46PM
    Well, I've found a lot of differences:
    My VS2008:
... ReportResourceString="...<?xml version="1.0" encoding="utf-8"?> ...

Your file:
... ReportResourceString="...<?xml version="1.0" encoding="utf-8"?> ...

    As you can see, VS2008 handles '<' and '>' correctly by replacing them with < > tags. I will investigate more on this tomorrow.
  • AlexTZAlexTZ
    edited 4:46PM
    I've found the bug in VS2005. I've fixed ReportResourceString, so it works now in both VS2005 and 2008. Open your project, run the designer, correct the SQL and close the designer. Now ReportResourceString is saved in Base64 format and should be ok when you open the designer next time.

    The fixed version will be uploaded in 2-3 hours.
  • pinbotpinbot Texas
    edited 4:46PM

    Thanks for the quick fix!

    Works great now.

    Does it do BASE64 for both VS2005 and VS2008? If I upgrade to 2008 after creating a report will it be compatible?


  • AlexTZAlexTZ
    edited 4:46PM
    It does for both VS2005 & 2008. It will be compatible because the report automatically recognizes the format (utf8 or base64).

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport .NETComment As ...

© Vanilla Keystone Theme 2024