HELP on Changing Crystal Report sql query at run time via vs.net.

[Phin]

I need your HELP!

I've seen all the posts on using Crystal Reports within vs.net (vb.net)
and changing a SQL query at runtime. When I tried to pass in a dataset
into the crystal report at runtime, the report still showed the results
from the default query (from within the Crystal Report).

Then I tried the XSD solution where you define a dataset (that mataches
the database and the Crystal Report) and have the Crystal Report use
this. All the examples that use the XSD solution use one table. I have
four tables and when I fill up my dataset and pass it to the report,
the report comes up blank (even though there is data in the dataset).
Man, I wish there just was a sqlquery property that I could change!

I've tried the following and all bring back a blank report:

---------
strSQL 'a query that joins four tables and bring back results from all
four tables

Dim da As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter(strSQL, strConnectionString)

Attempt 1.
Dim ds As New System.data.DataSet
da.Fill(ds)
SomeCrystalReport.SetDataSource(ds)

Attempt 2.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")
SomeCrystalReport.SetDataSource(ds)

Attempt 3.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

Dim i As Integer = 0
While i < SomeCrystalReport.Database.Tables.Count
SomeCrystalReport.Database.Tables(i).SetDataSource (ds.Tables(i))
i = i + 1
End While

Attempt 4.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table4"))
Attempt 5.
Dim ds As New Some_XSD_dataset
da.Fill(ds.Tables("Table1"))
da.Fill(ds.Tables("Table2"))
da.Fill(ds.Tables("Table3"))
da.Fill(ds.Tables("Table4"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table2").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table3").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table4").SetDat aSource(ds.Tables("Table4"))

-------------
Please post a complete solution (or links) on how to change a crystal
report sql query at runtime via vs.net. Your help is appreciated!

All the best,

Phin

[Bernie Yaeger]

Hi Phin,

You're going about it in a way that I would not. I do what you need all the
time, and here's how: I delete a table (using an sql command obj) that I
recycle, let's call it ttable. I then recreate ttable using a 'select into'
clause. I then run the report. The report always opens ttable, so it
doesn't care that the data is always different.

HTH,

Bernie Yaeger

"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I need your HELP!

I've seen all the posts on using Crystal Reports within vs.net (vb.net)
and changing a SQL query at runtime. When I tried to pass in a dataset
into the crystal report at runtime, the report still showed the results
from the default query (from within the Crystal Report).

Then I tried the XSD solution where you define a dataset (that mataches
the database and the Crystal Report) and have the Crystal Report use
this. All the examples that use the XSD solution use one table. I have
four tables and when I fill up my dataset and pass it to the report,
the report comes up blank (even though there is data in the dataset).
Man, I wish there just was a sqlquery property that I could change!

I've tried the following and all bring back a blank report:

---------
strSQL 'a query that joins four tables and bring back results from all
four tables

Dim da As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter(strSQL, strConnectionString)

Attempt 1.
Dim ds As New System.data.DataSet
da.Fill(ds)
SomeCrystalReport.SetDataSource(ds)

Attempt 2.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")
SomeCrystalReport.SetDataSource(ds)

Attempt 3.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

Dim i As Integer = 0
While i < SomeCrystalReport.Database.Tables.Count
SomeCrystalReport.Database.Tables(i).SetDataSource (ds.Tables(i))
i = i + 1
End While

Attempt 4.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table4"))
Attempt 5.
Dim ds As New Some_XSD_dataset
da.Fill(ds.Tables("Table1"))
da.Fill(ds.Tables("Table2"))
da.Fill(ds.Tables("Table3"))
da.Fill(ds.Tables("Table4"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table2").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table3").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table4").SetDat aSource(ds.Tables("Table4"))

-------------
Please post a complete solution (or links) on how to change a crystal
report sql query at runtime via vs.net. Your help is appreciated!

All the best,

Phin

[Phin]

Bernie,

Thank you for the quick response! I am not sure what you mean to delete
table (which table? in the report?) and recycle. Please your post the
code on how to do this. I've just seen the xsd solution and your way
sounds easier. I apprecaite your help!

Thanks!

Phin

Bernie Yaeger wrote:

Hi Phin,

You're going about it in a way that I would not. I do what you need all the time, and here's how: I delete a table (using an sql command obj) that I recycle, let's call it ttable. I then recreate ttable using a 'select into' clause. I then run the report. The report always opens ttable, so it doesn't care that the data is always different.

HTH,

[Tomasz Tybusz]

Phin napisa³(a):

I need your HELP!

I've seen all the posts on using Crystal Reports within vs.net (vb.net)
and changing a SQL query at runtime. When I tried to pass in a dataset
into the crystal report at runtime, the report still showed the results
from the default query (from within the Crystal Report).

Did You try to use Refresh method?

[Phin]

Thanks Bernie, Fang, et al for your help!

I was crunched for time, so I just used the Craxdrt (interop) dll with
VB.net like I would have done in the pre-VB.NET days (and pass the
query via a sqlquery property)!

What it takes to update a query in Crystal Reports in .NET starts
looking like a Rube Goldberg invention:
http://www.rube-goldberg.com/

Don't get me wrong, I love .NET (and have used it since the beta back
in the 2000). I just wished they would have a sqlquery property exposed
or at least make Microsoft Reporting Services easier to implement (in a
production environment) and convert over to (hopefully with RS 2.0 and
SQL server 2005), so I can leave Crystal and its ambiguous error
messages and over-priced licensing behind!

I feel better now that I got that off my chest!

: )

Phin

[Bernie Yaeger]

Hi Phin,

I know how you feel, but I'm afraid we're not going to see Reporting
Services robust enough for a production environment for at least 2 years.
So we still have to live with the difficulties that Crystal imposes.

Regards,

Bernie

"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@l41g2000cwc.googlegr oups.com...

Thanks Bernie, Fang, et al for your help!

I was crunched for time, so I just used the Craxdrt (interop) dll with
VB.net like I would have done in the pre-VB.NET days (and pass the
query via a sqlquery property)!

What it takes to update a query in Crystal Reports in .NET starts
looking like a Rube Goldberg invention:
http://www.rube-goldberg.com/

Don't get me wrong, I love .NET (and have used it since the beta back
in the 2000). I just wished they would have a sqlquery property exposed
or at least make Microsoft Reporting Services easier to implement (in a
production environment) and convert over to (hopefully with RS 2.0 and
SQL server 2005), so I can leave Crystal and its ambiguous error
messages and over-priced licensing behind!

I feel better now that I got that off my chest!

: )

Phin

[Scott Wallace]

Are you saving data in the report? You can check the .HasSavedData
property. If you do, it will show and ignore your datasource.

"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I need your HELP!

I've seen all the posts on using Crystal Reports within vs.net (vb.net)
and changing a SQL query at runtime. When I tried to pass in a dataset
into the crystal report at runtime, the report still showed the results
from the default query (from within the Crystal Report).

Then I tried the XSD solution where you define a dataset (that mataches
the database and the Crystal Report) and have the Crystal Report use
this. All the examples that use the XSD solution use one table. I have
four tables and when I fill up my dataset and pass it to the report,
the report comes up blank (even though there is data in the dataset).
Man, I wish there just was a sqlquery property that I could change!

I've tried the following and all bring back a blank report:

---------
strSQL 'a query that joins four tables and bring back results from all
four tables

Dim da As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter(strSQL, strConnectionString)

Attempt 1.
Dim ds As New System.data.DataSet
da.Fill(ds)
SomeCrystalReport.SetDataSource(ds)

Attempt 2.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")
SomeCrystalReport.SetDataSource(ds)

Attempt 3.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

Dim i As Integer = 0
While i < SomeCrystalReport.Database.Tables.Count
SomeCrystalReport.Database.Tables(i).SetDataSource (ds.Tables(i))
i = i + 1
End While

Attempt 4.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table4"))
Attempt 5.
Dim ds As New Some_XSD_dataset
da.Fill(ds.Tables("Table1"))
da.Fill(ds.Tables("Table2"))
da.Fill(ds.Tables("Table3"))
da.Fill(ds.Tables("Table4"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table2").SetDat aSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table3").SetDat aSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table4").SetDat aSource(ds.Tables("Table4"))

-------------
Please post a complete solution (or links) on how to change a crystal
report sql query at runtime via vs.net. Your help is appreciated!

All the best,

Phin

[Brian Bischof]

I would like to chime in here if you don't mind. Yes, there is definitely a
problem with the CR.NET API. It needs to be fleshed out more. Things are
going to be much better in CR.NET 2005. Secondly, re Reporting Services, I
love how everyone thinks this is a free tool that is easy to use. Have you
read the RS newsgroups and the problems people are having? That might shed
some light on where RS is at in development. And it's only free when you
read the marketing brochures. Put it on a production server and it won't be
long before you are buying a second server and another SQL license to boot.
Want to use ASP.NET Forms Authentication? That will be another $20K, thank
you. Trust me, MS didn't become the world's largest software company by
giving away its software for free. Otherwise we would be getting MS Office
cds in the mail right next to the AOL cds. Also, CR Server is much cheaper
now that RS is in the marketplace (competition has a way of doing that...).
So keep dreaming about RS and how great it is, but until you go through the
pain of getting around its bugs and limited feature set ("Just write more
custom data extensions!") then stick with Crystal.

Brian
"Scott Wallace" <sc***********@astyles.com> wrote in message
news:Oe**************@tk2msftngp13.phx.gbl...

Are you saving data in the report? You can check the .HasSavedData
property. If you do, it will show and ignore your datasource.

"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I need your HELP!

I've seen all the posts on using Crystal Reports within vs.net (vb.net)
and changing a SQL query at runtime. When I tried to pass in a dataset
into the crystal report at runtime, the report still showed the results
from the default query (from within the Crystal Report).

Then I tried the XSD solution where you define a dataset (that mataches
the database and the Crystal Report) and have the Crystal Report use
this. All the examples that use the XSD solution use one table. I have
four tables and when I fill up my dataset and pass it to the report,
the report comes up blank (even though there is data in the dataset).
Man, I wish there just was a sqlquery property that I could change!

I've tried the following and all bring back a blank report:

---------
strSQL 'a query that joins four tables and bring back results from all
four tables

Dim da As SqlClient.SqlDataAdapter = New
SqlClient.SqlDataAdapter(strSQL, strConnectionString)

Attempt 1.
Dim ds As New System.data.DataSet
da.Fill(ds)
SomeCrystalReport.SetDataSource(ds)

Attempt 2.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")
SomeCrystalReport.SetDataSource(ds)

Attempt 3.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

Dim i As Integer = 0
While i < SomeCrystalReport.Database.Tables.Count
SomeCrystalReport.Database.Tables(i).SetDataSource (ds.Tables(i))
i = i + 1
End While

Attempt 4.
Dim ds As New System.data.DataSet
da.Fill(ds, "Table1")
da.Fill(ds, "Table2")
da.Fill(ds, "Table3")
da.Fill(ds, "Table4")

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"
))
SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table2"
))
SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table3"
))
SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table4"
))

Attempt 5.
Dim ds As New Some_XSD_dataset
da.Fill(ds.Tables("Table1"))
da.Fill(ds.Tables("Table2"))
da.Fill(ds.Tables("Table3"))
da.Fill(ds.Tables("Table4"))

SomeCrystalReport.Database.Tables("Table1").SetDat aSource(ds.Tables("Table1"
))
SomeCrystalReport.Database.Tables("Table2").SetDat aSource(ds.Tables("Table2"
))
SomeCrystalReport.Database.Tables("Table3").SetDat aSource(ds.Tables("Table3"
))
SomeCrystalReport.Database.Tables("Table4").SetDat aSource(ds.Tables("Table4"
))
-------------
Please post a complete solution (or links) on how to change a crystal
report sql query at runtime via vs.net. Your help is appreciated!

All the best,

Phin