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

  • Thread starter Thread starter Phin
  • Start date Start date
P

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").SetDataSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table1").SetDataSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table1").SetDataSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table1").SetDataSource(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").SetDataSource(ds.Tables("Table1"))

SomeCrystalReport.Database.Tables("Table2").SetDataSource(ds.Tables("Table2"))

SomeCrystalReport.Database.Tables("Table3").SetDataSource(ds.Tables("Table3"))

SomeCrystalReport.Database.Tables("Table4").SetDataSource(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
 
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
 
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
 
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).
Click to expand...

Did You try to use Refresh method?
 
Hi Phin,



Below is an example of what I do. In this example, I am creating a table to
contain the receiveable info on a group of customers.



Steps

1. The user has selected them from a listbox. The string
'toarraystring' contains the account #s to include in the table.

2. I then delete an sp (sp_dropsp_passarray kills the sp called
'sp_passarray')

3. I then create the procedure sp_passarray anew - you will note that
inside this procedure I check to see if table 'a_rsumtable' exists; if it
does, this sp will first kill it. The sp then goes on to 'select ...
data ... into a_rsumtable', which creates a_rsumtable with the appropriate
data in it

4. I then create the sp

5. I then execute the sp



Now a_rsumtable exists with fresh data in it and when the report is called,
it opens a_rsumtable - it doesn't care that it has different data each time
it opens it.



Let me know if you have any questions.



Regards,



Bernie



Dim toarraystring As String

Dim acount As Integer = 0

For i = 0 To toarray.Length - 1

acount += 1

toarraystring += Chr(39) & Trim(toarray(i)) & Chr(39) & ","

If i <> toarray.Length - 1 Then 'ie, it isn't the last item

If acount > 20 Then

acount = 0

toarraystring += vbCrLf

End If

End If

Next

toarraystring = Mid(toarraystring, 1, toarraystring.Length - 1) &
")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_dropsp_passarray", oconn)'drop the sp

dcmd.CommandType = CommandType.StoredProcedure

Try

dcmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try

Dim creationstring As String

creationstring = "CREATE PROCEDURE sp_passarray AS " _

& "if exists (select * from information_schema.tables where
table_name = " _

& "'a_rsumtable')" & vbCrLf & "drop table a_rsumtable" & vbCrLf _

& "select imcacct, pubcode, invnum, inv_dt, brname, " _

& "(case when inv_dt + 31 > getdate() then balance else 0 end) as
under31, " _

& "(case when inv_dt + 61 > getdate() and inv_dt + 31 <= getdate()
then balance else 0 end) as over30, " _

& "(case when inv_dt + 91 > getdate() and inv_dt + 61 <= getdate()
then balance else 0 end) as over60, " _

& "(case when inv_dt + 121 > getdate() and inv_dt + 91 <= getdate()
then balance else 0 end) as over90, " _

& "(case when inv_dt + 121 <= getdate() then balance else 0 end) as
over120" _

& " into a_rsumtable from a_r where imcacct" _

& " in (" & toarraystring & vbCrLf _

& "order by pubcode, imcacct"

sqladapt.SelectCommand = New SqlCommand(creationstring, oconn)

Try

sqladapt.SelectCommand.ExecuteNonQuery() ' create the sp

Catch ex As Exception

MessageBox.Show(ex.Message)

End Try

Dim ocmd As New SqlCommand

ocmd = New SqlCommand("sp_passarray", oconn) ' execute the sp

ocmd.CommandType = CommandType.StoredProcedure

Try

ocmd.ExecuteNonQuery()

Catch ex As Exception

MessageBox.Show(ex.message)

End Try
 
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
 
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
 
Are you saving data in the report? You can check the .HasSavedData
property. If you do, it will show and ignore your datasource.
 
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
 
Back
Top