push data to crystal report asp.net

I am attempting to use the push model for a crystal report from a asp.net project.
I am having two problems. First when I attempt to use a sql command I am able to create the report
but when I attempt to push the data to it I get a login failed error. When I correct this I get the original
data that was used to format the report not the modified data that I am attempting to push to it.
Here is the code.

Sub BindReport2()
Dim oRpt As New CrystalReport1

Dim myConnection As New SqlClient.SqlConnection
myConnection.ConnectionString = "Server=WOODS1;Database=WEB_TEST;User ID=WebRep;Password=webrep;Trusted_Connection=False"
Dim MyCommand As New SqlClient.SqlCommand
MyCommand.Connection = myConnection
MyCommand.CommandText = "select fname, lname, address1, city, state " & _
"from T1, T2 " & _
"where T1.empid = T2.empid and state = 'New York' "

MyCommand.CommandType = CommandType.Text
Dim MyDA As New SqlClient.SqlDataAdapter
MyDA.SelectCommand = MyCommand
Dim myDS As New DataSet
'This is our DataSet created at Design Time
MyDA.Fill(myDS, "Command")
'You have to use the same name as that of your Dataset that you created during design time

'oRpt.Database.Tables.Item("Command").SetDataSource(myDS)
' This is the Crystal Report file created at Design Time
oRpt.SetDataSource(myDS)
' Set the SetDataSource property of the Report to the Dataset
CrystalReportViewer1.ReportSource = oRpt
'oRpt.SetDatabaseLogon("user","password","server","database")
oRpt.SetDatabaseLogon("WebRep", "webrep", "WOODS1", "web_test")
CrystalReportViewer1.RefreshReport()

' Set the Crystal Report Viewer's property to the oRpt Report object that we created
End Sub

My Second issue is similar though involves using a dataset. I am able to push the data to the report if
my dataset only contains one table if it contains more than one I get no data returned to the report.
Here is the code.

Sub BindRep()
Dim oRpt As New CrystalReport2
Dim myConnection As New SqlClient.SqlConnection
myConnection.ConnectionString = "Server=WOODS1;Database=WEB_TEST;User ID=WebRep;Password=webrep;Trusted_Connection=False"

Dim strSQL As String = "select fname, lname, address1, city, state " & _
"from T1, T2 " & _
"where T1.empid = T2.empid and state = 'New York' "

Dim objAdapter As New SqlClient.SqlDataAdapter(strSQL, myConnection)

Dim objDataSet As New Dataset1
objAdapter.Fill(objDataSet, "T1, T2")

oRpt.SetDataSource(objDataSet)
' Set the SetDataSource property of the Report to the Dataset
CrystalReportViewer1.ReportSource = oRpt
End Sub

I have been able with the above code using a dataset that contains only one table to push the correct data
to the report. Any help on this issue whould be greatly appreciated.I dunno what database you're using, but I would have thought your problem occurs becuse of your SQL syntax/structure, where's your:
AS T1 INNER JOIN T2 ON (T1.empid = T2.empid )
???

If you don't join them on at least one field, the SQL parser can't resolve the WHERE clause.

Davesql

Pulling reports from Reporting server

I have reports posted to my server and need to edit them. My issue is the original rdl has been "misplaced". I do not have a model built for this server so I cannot use the built in report builder. Is there a way to "download" from the reporting server?Yes. Using Report Manager, If you click on the "properties" of the report and go to the "general" tab, there is an "edit" button right below "Report Definition". This should prompt an edit/save dialog box that will let you download the report if you have the appropriate permissions.

Pulling data from 2 tables, 1 with possibly multiple records

I'm trying to create a view which pulls data from the following 2 tables:
CREATE TABLE PC (
PCName varchar(50) NOT NULL,
Make varchar(50),
Model varchar(50),
SerialNumber varchar(50)
PRIMARY KEY
(PCName)
)
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
'OptiPlex GX1', '12345')
INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
'PowerEdge 6450', '23456')
CREATE TABLE CPU (
PCName varchar(50) NOT NULL REFERENCES PC(PCName),
Row int NOT NULL,
Type varchar(50),
Speed int
PRIMARY KEY
(PCName, Row)
)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
III', 1000)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium III
Xeon', 700)
INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium III
Xeon', 700)
The CPU table holds an entry for each CPU in the PC. Now I want the view to
retrieve 1 row per PC and look like this...
PCName Make Model SerialNumber Type
Speed NumberOfCPUs
========================================
==============================
TEST1 Dell OptiPlex GX1 12345 Pentium III
1000 1
TEST2 Dell PowerEdge 6450 23456 Pentium III
Xeon 1200 4
... where NumberOfCPUs is the max(Row) for each particular PC.
Any ideas? Unfortunately we are stuck with the table structure as is (from
a 3rd party).
ThanksJim
Thanks for posting DDL
See , if this helps you
SELECT PC.PCName,Make,Model,SerialNumber,
Type, Speed,NumberOfCPUs FROM PC JOIN
(
SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
GROUP BY PCName,Type,Speed
) AS Der ON PC.PCName=Der.PCName
"Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
> I'm trying to create a view which pulls data from the following 2 tables:
> CREATE TABLE PC (
> PCName varchar(50) NOT NULL,
> Make varchar(50),
> Model varchar(50),
> SerialNumber varchar(50)
> PRIMARY KEY
> (PCName)
> )
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST1', 'Dell',
> 'OptiPlex GX1', '12345')
> INSERT INTO PC(PCName, Make, Model, SerialNumber) values ('TEST2', 'Dell',
> 'PowerEdge 6450', '23456')
> CREATE TABLE CPU (
> PCName varchar(50) NOT NULL REFERENCES PC(PCName),
> Row int NOT NULL,
> Type varchar(50),
> Speed int
> PRIMARY KEY
> (PCName, Row)
> )
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST1', 1, 'Pentium
> III', 1000)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 1, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 2, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 3, 'Pentium
> III Xeon', 700)
> INSERT INTO CPU (PCName, Row, Type, Speed) values ('TEST2', 4, 'Pentium
> III Xeon', 700)
>
> The CPU table holds an entry for each CPU in the PC. Now I want the view
> to retrieve 1 row per PC and look like this...
> PCName Make Model SerialNumber Type Speed
> NumberOfCPUs
> ========================================
==============================
> TEST1 Dell OptiPlex GX1 12345 Pentium
> III 1000 1
> TEST2 Dell PowerEdge 6450 23456 Pentium III
> Xeon 1200 4
>
> ... where NumberOfCPUs is the max(Row) for each particular PC.
> Any ideas? Unfortunately we are stuck with the table structure as is
> (from a 3rd party).
> Thanks
>|||Yes, that certainly does. Thank you very much.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uMIH0gmvFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Jim
> Thanks for posting DDL
> See , if this helps you
>
> SELECT PC.PCName,Make,Model,SerialNumber,
> Type, Speed,NumberOfCPUs FROM PC JOIN
> (
> SELECT MAX(Row)NumberOfCPUs,PCName,Type,Speed FROM CPU
> GROUP BY PCName,Type,Speed
> ) AS Der ON PC.PCName=Der.PCName
>
> "Jim Coyne" <REcoyneMO_jimVE@.hoMEtmail.com> wrote in message
> news:usUSwQlvFHA.2072@.TK2MSFTNGP14.phx.gbl...
>

publishing downloading report model

Hi friends

is it possible download a report model (.smdl) from a report server?

and also how to publish a report model?

i know how to do this from report manager page but i need to do this programmatically (from C#).

any ideas please. Thanks.

any ideas dudes ?

Thanks for ur help.

|||

publish model using SOAP method CreateModel, then bind it to a data source using SetItemDataSources

|||

Thank you very much Lev.i'll use that method to upload.

BTW i cant find any method to download a model mate ?

any ideas?

Thanks for ur help again.

|||GetModelDefinition|||

Thanks for that Lev. thats what am looking for.

BTW is there any sample code on these methods ?

|||Hi Lev
thanks for that . i finally able to upload a report model using "createModel" but after uploading i still need to set its data source manually from report manager page to get it work!

as you know "createModel" has parameter called "property[]" ,can i use it to set data source ? if so how can i please ?

or you said i could use "setItemDatasources" but i cant figure out how to use this one ?

can you help ?
Thanks
|||BTW Lev
how can i overwrite the report model if already exists?
at the moment am getting following exception

System.Web.Services.Protocols.SoapException: The item '/Models/copyofORM' already exists.

Thanks.|||

Here is some sample code that may give you some ideas:

http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_script_model

|||

prk wrote:

how can i overwrite the report model if already exists?
at the moment am getting following exception

System.Web.Services.Protocols.SoapException: The item '/Models/copyofORM' already exists.

It is not possible to override model with different model. You have to delete existing model first (note, that this will break all reports using that model)

|||

Lev Semenets - MSFT wrote:

It is not possible to override model with different model. You have to delete existing model first (note, that this will break all reports using that model)

Lev

actually am not overwriting with different model. first i download what already there ,make few changes and re upload the model.

when we upload a model from report manager page ,as you know, there is an option to overwrite , i wonder if we can do same thing programmatically ?

|||

Lev Semenets - MSFT wrote:

Here is some sample code that may give you some ideas:

http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_script_model

Thats wonderful Lev.

will go thru all examples here. Thanks for ur time and help :)

publishing downloading report model

Hi friends

is it possible download a report model (.smdl) from a report server?

and also how to publish a report model?

i know how to do this from report manager page but i need to do this programmatically (from C#).

any ideas please. Thanks.

any ideas dudes ?

Thanks for ur help.

|||

publish model using SOAP method CreateModel, then bind it to a data source using SetItemDataSources

|||

Thank you very much Lev.i'll use that method to upload.

BTW i cant find any method to download a model mate ?

any ideas?

Thanks for ur help again.

|||GetModelDefinition|||

Thanks for that Lev. thats what am looking for.

BTW is there any sample code on these methods ?

|||Hi Lev
thanks for that . i finally able to upload a report model using "createModel" but after uploading i still need to set its data source manually from report manager page to get it work!

as you know "createModel" has parameter called "property[]" ,can i use it to set data source ? if so how can i please ?

or you said i could use "setItemDatasources" but i cant figure out how to use this one ?

can you help ?
Thanks
|||BTW Lev
how can i overwrite the report model if already exists?
at the moment am getting following exception

System.Web.Services.Protocols.SoapException: The item '/Models/copyofORM' already exists.

Thanks.|||

Here is some sample code that may give you some ideas:

http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_script_model

|||

prk wrote:

how can i overwrite the report model if already exists?
at the moment am getting following exception

System.Web.Services.Protocols.SoapException: The item '/Models/copyofORM' already exists.

It is not possible to override model with different model. You have to delete existing model first (note, that this will break all reports using that model)

|||

Lev Semenets - MSFT wrote:

It is not possible to override model with different model. You have to delete existing model first (note, that this will break all reports using that model)

Lev

actually am not overwriting with different model. first i download what already there ,make few changes and re upload the model.

when we upload a model from report manager page ,as you know, there is an option to overwrite , i wonder if we can do same thing programmatically ?

|||

Lev Semenets - MSFT wrote:

Here is some sample code that may give you some ideas:

http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_script_model

Thats wonderful Lev.

will go thru all examples here. Thanks for ur time and help :)