472,364 Members | 2,124 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

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

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

Nov 21 '05 #1
10 3176
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

Nov 21 '05 #2
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,


Nov 21 '05 #3
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?
Nov 21 '05 #4

Tomasz Tybusz wrote:
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?


Thanks for the suggestion but it did not work.

Phin

Nov 21 '05 #5
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

"Phin" <mr******@yahoo.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
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,

Nov 21 '05 #6
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

Nov 21 '05 #7
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

Nov 21 '05 #8
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

Nov 21 '05 #9
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


Nov 21 '05 #10
hi there,
I set the "crystalReportViewer.ReportSource" to a dataset by using
"rptCR.SetDataSource(ds)" but my report is blank!!
how to I connect my returned DS to fields on the report??
how do i put fields on the report???

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 21 '05 #11

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Phin | last post by:
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...
1
by: RJN | last post by:
Hi I'm new to VS.Net Crystal Report. I'm using data sets as the source for crystal report. I've a dataset which has 2 data tables. The first table's schema is like this Ptid String Prid...
1
by: mike11d11 | last post by:
For some reason I'm getting no data populating in my crystal report. I built my crystal report off my dataset in my project, and I fill the dataset when opening the main form, and count the...
11
by: =?Utf-8?B?cmtibmFpcg==?= | last post by:
How can I stop receiving this message while calling a crystal report? "The report you requested requires further information." Thanks
3
by: muddasirmunir | last post by:
i am using vb6 and crystal report 10 i want to ask solution of one proble which i am facing in making report. first , in my programe i ask the user to give paramerter (range) of the data...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and credentials and received a successful connection...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Rahul1995seven | last post by:
Introduction: In the realm of programming languages, Python has emerged as a powerhouse. With its simplicity, versatility, and robustness, Python has gained popularity among beginners and experts...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.