Producing Crystal Reports from inside VB.NET 2005

[ribbons]

Can anyone point me toward examples, code, information, etc. relating to producing Crystal Reports from inside VB.NET framework where the information on the report is filtered from the database by using drop down boxes? I have already found code examples on MSDN for this purpose, but apparently there is some differences in the syntax of the selection formula between the example and what I want to do as I have not been able to follow their examples and make this work. Also, I have not been able to find any information in CR or .NET books that speak to this, either, even though this seems a very simple usage to me.

I've posted a question on this to this forum previously without much response, so I am assuming that what I am trying to do is not a functionality much used. Any help or ideas at this point would be wonderful.

 

[ribbons]

Okay, I've partly answered my own question, except for a small part.

Can anyone tell me what is wrong with this code:

Dim selectionFormula As String = "{LabNumber.OldLabNumber}= '" _
        & labnumber _
        & "'" _
        & " AND{GermReadings.GermFinishedDate}= '" _
        & dategermfinished _
        & "'"

This is used to filter data to be displayed on a Crystal Report. I know that something about the syntax where the two {Table.Field} expressions are, probably either the AND or the quotes, but I have been unable to find out just what it wrong. labnumber and dategermfinished are the selections from two drop down boxes.

Thanks for you help.

 

[rjoubert]

I believe you have your line continuation characters (_) and your ampersands (&) reversed. Try...

Dim selectionFormula As String = "{LabNumber.OldLabNumber}= '" & _
        labnumber & _
        "'" & _
        " AND{GermReadings.GermFinishedDate}= '" & _
        dategermfinished & _
        "'"

 

[rjoubert]

And you also need a space after the "AND" in your formula.

AND {GermReadings.GermFinishedDate}...

 

[ribbons]

Hi rjoubert,

Thanks for helping.

Unfortunately, that didn't work either. The error message reads:

Error in formula <Record Selection>
'{LabNumber.OldLabNumber}=" AND {GermReadings.GermFinishedDate}='" 
A timedate is required here

The original example of code in my original post came from an MSDN example for Vb.NET. I am trying to adapt this to create this selection string to fill a report by having the user make selections from drop down boxes. I have been unable to find a single reference either on the internet or in a book that explains EXACTLY how to format these strings as far as the double quote, single quotes and spaces are concerned for VB.NET. I have done this in VB 6 quite easily, but apparently there has been a change in formatting.

The following portion of the code works beautifully:

Dim selectionFormula As String = "{LabNumber.OldLabNumber}= '" _
        & labnumber _
        & "'"

This represents one drop down box (labnumber is a private constant and is set: labnumber = cmolabnumber.text under a button click event.

I get into trouble when I try to add the second drop down box: dategermfinished. I don't know how to join the two as the AND and it's accompanying punctuation don't seem to be working. Any help is greatly appreciated. I have been working on this for days.

ribbons

 

[rjoubert]

Try wrapping the # symbol around your date value...

Dim selectionFormula As String = "{LabNumber.OldLabNumber}= '" & _
        labnumber & _
        "'" & _
        " AND {GermReadings.GermFinishedDate}= #" & _
        dategermfinished & _
        "#"

 

[ribbons]

I'm sorry, it didn't like that either. The message was:

The timedate literal ## was not understood

What do the "'" and the #" mean? Is the "'" providing a space in the string or is it a closure character?

Can I use SQL to create stings such as this in VB.NET? This is for a desktop application, not web based.

ribbons

 

[ribbons]

An update . . . I've been able to make this work using all fields that are TEXT fields, so apparently this has something to do with formatting for the date in the code. Help?

 

[rjoubert]

Have you verified that your variable dategermfinished has a valid date value?

 

[rjoubert]

Also, what database are you querying? Is it Access, SQL Server, Oracle?

 

[rjoubert]

You would use the single quote (') when you are querying against a string field. You use the pound symbol (#) to query against a date/time value. These may be different depending on which database you're querying, but these at least work for Access.

WHERE MyStringFieldName = 'testing'

WHERE MyDateFieldName = #01/01/2001#

 

[ribbons]

Hi rjoubert,

I am using an Access database. And the date field is Id'd as a date/time field in Access. Is this formatting for this selection formula Crystal Syntax or Access format or something that pertains to VB? Where would I get a list of t his?


I'll give your suggestions another try this afternoon and post back if it works. I just left Barnes and Noble and I can't find any reference in any book to this type of sytax. Weird.

ribbons

 

[rjoubert]

I normally write applications that work with an Access DB. Whenever I need to write custom SQL in my code, I create the query in Access and copy the SQL that Access creates behind the query. I then modify it to use any variables I may be using in the code.

 

[ribbons]

So, I can use a SQL string for record selection versus the record selection syntax I'm presently working on? Using SQL is how I have programmed in VB 6 in the past.

 

[rjoubert]

No what I'm saying is you can go into Access and create an example query and view the SQL behind it. For example, let's say you create a query that pulls data from one table, using a date and string parameter (like you are passing in from your vb.net app). Create the query in Access and use sample data as your selection criteria.

Let's say this is the SQL that Access creates for that query...
SELECT * FROM Table1 WHERE Field1 = 'This is a test' AND Field2 = #09/07/2007#

Copy that SQL statement and paste into your code, then replace the example parameters with your variables.

selectionformula = "SELECT * FROM Table1 WHERE Field1 = '" & labnumber & "' AND Field2 = #" & dategermfinished & "#

 

[ribbons]

Yes, that answers my question. I wasn't sure if I could use the "SELECT * . . . ." and so forth FORMAT in VB.NET at all, or just duplicate the same format of the record selection formulas found in a stand alone CR XI (which is what I'm using to develop the reports).

That's what I wanted to know. Sorry to be so ignorant about this. Our programmer quit cold turkey in the middle of a huge and complex laboratory Database and reporting program without which the lab will grind to a halt. So, they're stuck with me, sometimes programmer with limited experience until the powers that be decide to fill the position which might be MONTHS.

Thanks so much

ribbons