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

**Bernie Yaeger** · Nov 21 '05, 12:03 PM

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

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" <mrwoopey@yahoo .com> wrote in message
news:1107903174 .158663.207870@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>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.SqlDa taAdapter = New
> SqlClient.SqlDa taAdapter(strSQ L, strConnectionSt ring)
>
> Attempt 1.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds)
> SomeCrystalRepo rt.SetDataSourc e(ds)
>
> Attempt 2.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
> SomeCrystalRepo rt.SetDataSourc e(ds)
>
> Attempt 3.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
>
> Dim i As Integer = 0
> While i < SomeCrystalRepo rt.Database.Tab les.Count
> SomeCrystalRepo rt.Database.Tab les(i).SetDataS ource(ds.Tables (i))
> i = i + 1
> End While
>
> Attempt 4.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table2 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table3 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table4 "))
>
>
> Attempt 5.
> Dim ds As New Some_XSD_datase t
> da.Fill(ds.Tabl es("Table1"))
> da.Fill(ds.Tabl es("Table2"))
> da.Fill(ds.Tabl es("Table3"))
> da.Fill(ds.Tabl es("Table4"))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table2").S etDataSource(ds .Tables("Table2 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table3").S etDataSource(ds .Tables("Table3 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table4").S etDataSource(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
>[/color]

**Phin** · Nov 21 '05, 12:03 PM

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

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:[color=blue]
> Hi Phin,
>
> You're going about it in a way that I would not. I do what you need[/color]
all the[color=blue]
> time, and here's how: I delete a table (using an sql command obj)[/color]
that I[color=blue]
> recycle, let's call it ttable. I then recreate ttable using a[/color]
'select into'[color=blue]
> clause. I then run the report. The report always opens ttable, so[/color]
it[color=blue]
> doesn't care that the data is always different.
>
> HTH,[/color]

**Tomasz Tybusz** · Nov 21 '05, 12:03 PM

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

Phin napisa³(a):[color=blue]
> 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).[/color]

Did You try to use Refresh method?

**Phin** · Nov 21 '05, 12:07 PM

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

Tomasz Tybusz wrote:[color=blue]
> Phin napisa³(a):[color=green]
> > I need your HELP!
> >
> > I've seen all the posts on using Crystal Reports within vs.net[/color][/color]
(vb.net)[color=blue][color=green]
> > and changing a SQL query at runtime. When I tried to pass in a[/color][/color]
dataset[color=blue][color=green]
> > into the crystal report at runtime, the report still showed the[/color][/color]
results[color=blue][color=green]
> > from the default query (from within the Crystal Report).[/color]
>
> Did You try to use Refresh method?[/color]

Thanks for the suggestion but it did not work.

Phin

**Bernie Yaeger** · Nov 21 '05, 12:07 PM

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

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_pass array 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(toarraystri ng, 1, toarraystring.L ength - 1) &
")"

Dim dcmd As New SqlCommand

dcmd = New SqlCommand("sp_ dropsp_passarra y", oconn)'drop the sp

dcmd.CommandTyp e = CommandType.Sto redProcedure

Try

dcmd.ExecuteNon Query()

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_sch ema.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.Select Command = New SqlCommand(crea tionstring, oconn)

Try

sqladapt.Select Command.Execute NonQuery() ' 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.CommandTyp e = CommandType.Sto redProcedure

Try

ocmd.ExecuteNon Query()

Catch ex As Exception

MessageBox.Show (ex.message)

End Try

"Phin" <mrwoopey@yahoo .com> wrote in message
news:1107928685 .046502.105430@ o13g2000cwo.goo glegroups.com.. .[color=blue]
> 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:[color=green]
>> Hi Phin,
>>
>> You're going about it in a way that I would not. I do what you need[/color]
> all the[color=green]
>> time, and here's how: I delete a table (using an sql command obj)[/color]
> that I[color=green]
>> recycle, let's call it ttable. I then recreate ttable using a[/color]
> 'select into'[color=green]
>> clause. I then run the report. The report always opens ttable, so[/color]
> it[color=green]
>> doesn't care that the data is always different.
>>
>> HTH,[/color]
>[/color]

**Phin** · Nov 21 '05, 12:12 PM

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

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:

Rube Goldberg - biography, general information, art work, art ideas.

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** · Nov 21 '05, 12:12 PM

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

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" <mrwoopey@yahoo .com> wrote in message
news:1108141858 .687577.251380@ l41g2000cwc.goo glegroups.com.. .[color=blue]
> 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
>[/color]

**Scott Wallace** · Nov 21 '05, 12:26 PM

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

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

"Phin" <mrwoopey@yahoo .com> wrote in message
news:1107903174 .158663.207870@ f14g2000cwb.goo glegroups.com.. .[color=blue]
>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.SqlDa taAdapter = New
> SqlClient.SqlDa taAdapter(strSQ L, strConnectionSt ring)
>
> Attempt 1.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds)
> SomeCrystalRepo rt.SetDataSourc e(ds)
>
> Attempt 2.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
> SomeCrystalRepo rt.SetDataSourc e(ds)
>
> Attempt 3.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
>
> Dim i As Integer = 0
> While i < SomeCrystalRepo rt.Database.Tab les.Count
> SomeCrystalRepo rt.Database.Tab les(i).SetDataS ource(ds.Tables (i))
> i = i + 1
> End While
>
> Attempt 4.
> Dim ds As New System.data.Dat aSet
> da.Fill(ds, "Table1")
> da.Fill(ds, "Table2")
> da.Fill(ds, "Table3")
> da.Fill(ds, "Table4")
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table2 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table3 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table4 "))
>
>
> Attempt 5.
> Dim ds As New Some_XSD_datase t
> da.Fill(ds.Tabl es("Table1"))
> da.Fill(ds.Tabl es("Table2"))
> da.Fill(ds.Tabl es("Table3"))
> da.Fill(ds.Tabl es("Table4"))
>
> SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table2").S etDataSource(ds .Tables("Table2 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table3").S etDataSource(ds .Tables("Table3 "))
>
> SomeCrystalRepo rt.Database.Tab les("Table4").S etDataSource(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
>[/color]

**Brian Bischof** · Nov 21 '05, 12:27 PM

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

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" <scott.wallace@ astyles.com> wrote in message
news:OeJyWSEFFH A.3384@tk2msftn gp13.phx.gbl...[color=blue]
> Are you saving data in the report? You can check the .HasSavedData
> property. If you do, it will show and ignore your datasource.
>
> "Phin" <mrwoopey@yahoo .com> wrote in message
> news:1107903174 .158663.207870@ f14g2000cwb.goo glegroups.com.. .[color=green]
> >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.SqlDa taAdapter = New
> > SqlClient.SqlDa taAdapter(strSQ L, strConnectionSt ring)
> >
> > Attempt 1.
> > Dim ds As New System.data.Dat aSet
> > da.Fill(ds)
> > SomeCrystalRepo rt.SetDataSourc e(ds)
> >
> > Attempt 2.
> > Dim ds As New System.data.Dat aSet
> > da.Fill(ds, "Table1")
> > da.Fill(ds, "Table2")
> > da.Fill(ds, "Table3")
> > da.Fill(ds, "Table4")
> > SomeCrystalRepo rt.SetDataSourc e(ds)
> >
> > Attempt 3.
> > Dim ds As New System.data.Dat aSet
> > da.Fill(ds, "Table1")
> > da.Fill(ds, "Table2")
> > da.Fill(ds, "Table3")
> > da.Fill(ds, "Table4")
> >
> > Dim i As Integer = 0
> > While i < SomeCrystalRepo rt.Database.Tab les.Count
> > SomeCrystalRepo rt.Database.Tab les(i).SetDataS ource(ds.Tables (i))
> > i = i + 1
> > End While
> >
> > Attempt 4.
> > Dim ds As New System.data.Dat aSet
> > da.Fill(ds, "Table1")
> > da.Fill(ds, "Table2")
> > da.Fill(ds, "Table3")
> > da.Fill(ds, "Table4")
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table2 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table3 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table4 "
))[color=blue][color=green]
> >
> >
> > Attempt 5.
> > Dim ds As New Some_XSD_datase t
> > da.Fill(ds.Tabl es("Table1"))
> > da.Fill(ds.Tabl es("Table2"))
> > da.Fill(ds.Tabl es("Table3"))
> > da.Fill(ds.Tabl es("Table4"))
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table1").S etDataSource(ds .Tables("Table1 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table2").S etDataSource(ds .Tables("Table2 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table3").S etDataSource(ds .Tables("Table3 "
))[color=blue][color=green]
> >
> >[/color][/color]
SomeCrystalRepo rt.Database.Tab les("Table4").S etDataSource(ds .Tables("Table4 "
))[color=blue][color=green]
> >
> > -------------
> > 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
> >[/color]
>
>[/color]

**oren david** · Nov 21 '05, 12:37 PM

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

hi there,
I set the "crystalReportV iewer.ReportSou rce" to a dataset by using
"rptCR.SetDataS ource(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!

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

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

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment