Bind SQL reporting services (Reports) to ASP.NET (Urgent)

Hi all,

Currently I have a project that require me to use reporting services (Reports) together with ASP.net web application.

However, I have the following problem.

I have an asp.net web application. From there, I have a textbox whereby user can type a specific date. The specific date should then be incorporate with the sql reporting services. The report that is being generated will contain data that is on the specific date.

How can I do that?

I try to search through the internet but to no result. Can anyone help out?

Thank you so much.Read a book on Reporting Services ... you can pass everything to Reporting Services through the webservices it exposes ...
Microsoft has some good books about Reporting Services ... Search for William Vaugh ... He's very good

Bind a variable to a SQL data source for labels, textboxes, etc.

I am trying to set a variable to be passed to my asp page but the value is not carrying over. I don't even know if this is supposed to work or not below:

<tdcolspan="3"style="font-size: 10pt; vertical-align: top; color:<%=Div7fontcolor%>; background-color:<%=Div7bgcolor%>; text-align: center; width: 0px;"id="TopBox"runat="server">

The above Div7fontcolor and Div7bgcolor are variables that I set in the VB file as shown below:

Dim obConnectionAs SqlConnection =New SqlConnection("Data Source=localhost\sqlexpress;Initial Catalog=OrgBoard;Integrated Security=True")Dim obCommandAs SqlCommand =New SqlCommand("SELECT Divisions.*, Dept19.*, Dept20.*, Dept21.*, ConfigDisplay.* FROM Divisions CROSS JOIN Dept19 CROSS JOIN Dept20 CROSS JOIN Dept21 CROSS JOIN ConfigDisplay", obConnection)

obConnection.Open()

Dim drAs SqlDataReader = obCommand.ExecuteReader()

dr.Read()

Dim Div7bgcolorAsString = dr("Div7color").ToString().Trim()

Dim Div7fontcolorAsString = dr("Div7textcolor").ToString().Trim()

dr.Close()

obConnection.Close()

The web page runs fine with no errors but the value does not carry over and change the property correctly.

I am able to set the bgColor value of the TopBox within the VB file if I use "TopBox.BgColor = Div7bgcolor" but I can't set other values on my web page like the font color. If I can do this from the VB file then please correct me. Using variables within the page will allow me to set almost any value but I don't even know if what I want is possible. Any help is greatly apprectiated.

Thank you,

Kris

The easiest thing for this is to use Literal controls:

<tdcolspan="3"style="font-size: 10pt; vertical-align: top; color:<asp:Literal ID="Div7fontcolor" runat="server" />; background-color:<asp:Literal ID="Div7bgcolor" runat="server" />; text-align: center; width: 0px;"id="TopBox"runat="server">

....

dr.Read()

Div7bgcolor.Text = dr("Div7color").ToString().Trim()

Div7fontcolor.Text = dr("Div7textcolor").ToString().Trim()

dr.Close()

Bind 2 tables from different database file.

Hi!

I have 2 databases. One is ASPNETDB.MDF and another is PPQ_Data.MDF
ASPNETDB.MDF is generated by ASP.net (VWD 2005) when I use Login control.
PPQ_Data.MDF is created by me. It contains menu and order information of my website.

I would like to connect this 2 databases together so that I can createa GridView table that binds 2 tables, one from ASPNETDB.MDF and anothertable from PPQ_Data.MDF. So, it is kind of displaying nested data.

Is there anybody out there that know how to connect 2 databases together?

Again, my purpose of connecting 2 databases together is to pull out a table from each database and bind the 2 tables together.

thanks in advanced for any advice or articles submitted.

You can use qualified database object name to refer to tables in different databases, and even from different servers (you have to make sure the app can connect to all referenced databse resources). Forexample (I suppose you database name to be c:\ASPNETDB.MDF and c:\PPQ_Data.MDF):

select * from [c:\ASPNETDB.MDF]..table1 , [c:\PPQ_Data.MDF]..table2

For more information, please see this article:

http://msdn.microsoft.com/library/en-us/tsqlref/ts_fa-fz_4ox9.asp?frame=true

|||Ohhw, yeah, thanks for the reply. I almost forget about SQL server online book. thanks!

Binary File

Can someone firecct me to instructions on how to download a Binary file in a asp.net application.

I have a sql 2000 db that has a field that contains files and my users need access to them via my web app.At the end of this article it tells you how to retrieve binary data from the database and then shows you code to actually start the download:

http://www.developer.com/net/asp/article.php/3097661|||That works great thanx.

Bestpractice for ReportingServices 2005

Hello All,

I am working on ReportingServices 2005 and embading the reports in ASP.NET using ReportViewer.

I would like to know Best Practice for this above.

Like Header, Footer, Logo, Report Run time, ReportName, Start and EndDate, Width (on browser and while printing)
PageCount, MasterPages for ReportingServices 2005

Also on the Web Site(ASP.NET with AJAX ), how to navigate to these reports, like Menu or TreeView

Is there any BestPractice or StarterKit for .Net 2.0?

Here are useful links

http://www.microsoft.com/technet/prodtechnol/sql/2005/rsdesign.mspx

http://msdn2.microsoft.com/en-us/library/ms159162.aspx

http://msdn2.microsoft.com/en-us/library/ms170246.aspx

http://msdn2.microsoft.com/en-us/library/ms170246.aspx|||

Thanks for the reply.

Could you please point me to, where I can see live demo/working copy/starter kit to get a feel of the Bestpractice.

Thanks in Advance.

|||

Hi,

Check this link too -

http://www.ssw.com.au/ssw/Standards/Rules/RulesToBetterSQLReportingServices.aspx#underline4HyperlinkAction

best way to write to DB for ASP 2.0 project?

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

Also, add some error handling in the stored procedure after the insert. Something like as follows.

SET @.returnstatus = @.@.error

IF @.returnstatus <> 0
BEGIN
RETURN @.returnstatus
END

|||

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

|||

pbromberg:

From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.

OK, I give. I just read a bunch of articles on the subject of profile provider and none of them really helped me--seemed like they were talking about creating instead of enabling profiles. Where's some good, simple information on activating the built-in provider. The closest I've come is implementing _CreatingUser on the CreateNewUser wizard, and listing the field names from my new table in web.config. If you can educate we where to go from here in order to enable the profile provider, I'd appreciate it.

|||

I just read up on profiles in Walther'sUnleashed book, and came up with the stuff below for web.config. I get Intellisense in the code-behind, which is a good sign, but when I run the page, I can't get it right for "type = " and the book doesn't elaborate on it. Not having the correct entry for the type criteria or omitting it produces an error. If someone can help me on this, I think I'll be in good shape.

One other thing: Should I use a separate table (shown as tblAlumni below) or use one of the standard aspnet tables? I'd prefer the latter to keep things the most simple, but Walther shows an "outside" table. Thanks in advance for any help.

<profile defaultProvider="DNProfileProvider"> <properties> <add name="FirstName" /> <add name="LastName" /> <add name="GradYr" type="integer"/> <add name="Address1" /> <add name="Address2" /> <add name="City" /> <add name="State" /> <add name="Zip" /> <add name="SpouseName" /> <add name="Gender" /> <add name="MaidenName" /> <add name="Phone" /> </properties> <providers> <add name="DNProfileProvider" type="??" connectionStringName="sqlConnection" profileTableName="tblAlumni"/> </providers> </profile>

|||

It depends on how the Profile class is written. The default profile provider has its own table and it can hold any type and number of fields that you define for each profile. However, this data is "opaque" in the database - you cannot easily search on it. If you want to use a custom profile provide as it seems from the snippet you posted, then it may use it's own table. There are some samples for "Table Profile Provider" and "Stored Procedure Profile Provider" that you can use as a model if you want a custom table.

Here is an article with some examples:

http://www.eggheadcafe.com/articles/20060731.asp

|||

muybn:

Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.

@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint

DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)

A new problem has arisen, having to do with database, that I need to resolve first. I make an entry to my form, save it to the database; then the next entry I make throws the error that the same UID can't be written to the database. I believe that, Mr. DB, but since when am I writing the same UID to you? I don't know where to start tracing this, except to show the stored proc again (above), and to describe my process and some of my code that might generate it.

 
Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUser strEmail =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text strUserName =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower strFirstName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text strLastName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text lngGradYr =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text strTeacher =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).TextEnd Sub Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUserDim cmdAs New SqlCommand("sp_CreateUser", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@.UserName", strUserName) cmd.Parameters.AddWithValue("@.Email", strEmail) cmd.Parameters.AddWithValue("@.FirstName", strFirstName) cmd.Parameters.AddWithValue("@.LastName", strLastName) cmd.Parameters.AddWithValue("@.Teacher", strTeacher) cmd.Parameters.AddWithValue("@.GradYr", lngGradYr) Using con con.Open() cmd.ExecuteScalar() con.Close()End Using cmd.Equals(Nothing)End Sub
  

Please let me know any other info you'd need to help me determine what's wrong.

|||

Can you please start a new POST / Thread. By this way we can concentrate your new issue. Since a post is marked as answered, every one will think that your issue is resolved. Hope you understand.

Best way to view reports from a webpage

I can view a report created in SSRS by using a url or by using an Reportviewer control in asp.net 2.0 what is the best way to view reports from a webpage?

you can use the report viewer control in your web page and render the reports throught it for more info read this article

http://www.gotreportviewer.com/

Best way to send email from a C# stored procedure?

I thought I could just copy over some asp.net code like:

System.Web.Mail.MailMessage mailMessage =new System.Web.Mail.MailMessage();

But VS2005 doesn't seem to want me touching System.Web.Mail.

Any ideas?

Thanks,

Allen

OK, I figured out that I need to useSystem.Net.Mail, but here is the next problem- when I create an instance of the SmtpClient object like this:

SmtpClient

client =newSmtpClient("localhost",25);I get this exception:

System.Security.SecurityException: Requestfor the permissionof type'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.Mail.SmtpClient.Initialize() at System.Net.Mail.SmtpClient..ctor(String host, Int32 port)

|||

has anyone managed to solve this one?

am getting the same error...

|||

I was able to get around that error (and a few others) by doing the following:

ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE

I hope this helps.

-Allen Cryer

|||

I was able to get around that error (and a few others) by doing the following:

ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE

I hope this helps.

-Allen Cryer

|||mate,you're a champ.I've looked for the last 24 hours all over.Thank you very much. Works like a charm now.

Best way to insert a DateModified in updates

how do i update the date modified Field in my DB. i was thinking of update trigger? i am using ASP 2 with DetailsView for Editing(Updates in SQL term). i can make a Update SP but think that might not be needed

If you use the SP you can define the date now to a variable. @.datenow = GetDate()|||

In the Update procedure, you can just "SET dateModified=GETDATE()".

In my view, Triggers are a little bad in the respect that they are hidden. If you look at a simple SQL statement, you really can't "trace" the code execution completely if your DB has a whole pile of Triggers firing all the time, which means, things might be happening that the SQL statement writer isn't aware of, and that can cause problems.

Of course, their not overall bad, but my personal preference is to use an SP, unless a Trigger is specifically required or for some odd reason more effecient.

best way to get related tables?

SQL Server 2000 : ASP w/ C# Codebehind 2.0 framework
I'm building an intranet site to query our database. ON the initial
page is a checkbox list of all the user tables in our DB. When a table
is first checked, I want to query the database and disable all
checkboxes representing tables not related to the first table checked.
The way I started to do it was to query
INFORMATION_SCHEMA.TABLE_CONSTRAINTS for contraints which contained the
table name, I then realized that even if I had a list of related tables
(which I could get by parsing the results properly from my I_S.T_C
query) I still wouldn't know which fields were related, which I need to
know in order to build the INNER JOIN part of the site's end query. So,
I suppose my question is, what is the easiest way to find out which
tables are related to a certain table, and on which fields those tables
are joined?
Thanks,
KrBIs this of any help?
SELECT u.column_name as PrimaryColumn,tc.TABLE_NAME AS PrimaryKeyTable,
--tc.CONSTRAINT_NAME AS PrimaryKey,
u2.column_name as ForeignColumn,
--COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on tc.CONSTRAINT_NAME
=u.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON
tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME
=rc1.CONSTRAINT_NAME
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u2 on rc1.CONSTRAINT_NAME
=u2.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||I'll look into it, thanks SQL Menace =)
K|||That is really close to what I need. The only thing is ForeignColumn
and PrimaryColumn are always equal. I can tell it's picking up the
right tables, however. So it looks like u2.column_name is the same as
u.column_name. I'll tinker with it a bit, thanks again, Denis.|||If you run that code in the Northwind DB you will see that the column
names are not always the same, in pubs they are always the same
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Hmm... Interesting. So is there a way to pull the foreign key column
out of this select?|||Here is a little addition
I have specified AND TC2.CONSTRAINT_TYPE ='FOREIGN KEY' just to be
sure
SELECT TC.CONSTRAINT_TYPE,TC2.CONSTRAINT_TYPE,u.column_name as
PrimaryColumn,tc.TABLE_NAME AS PrimaryKeyTable,
--tc.CONSTRAINT_NAME AS PrimaryKey,
u2.column_name as ForeignColumn,
--COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u on tc.CONSTRAINT_NAME
=u.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON
tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME
=rc1.CONSTRAINT_NAME
join INFORMATION_SCHEMA.KEY_COLUMN_USAGE u2 on tc2.CONSTRAINT_NAME
=u2.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
AND TC2.CONSTRAINT_TYPE ='FOREIGN KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Okay... I think I was just being retarded. So it takes PrimaryKey from
PrimaryKeyTable and ForeignKeyTable is the related table, which is
where ForeignKey comes from. I thought, for some reason, it was saying
ForeignKey and PrimaryKey were coming from the same table... You've
surely saved me many hours of head to wall impact, thanks again.
Kyle

Best way to do this

Hi Group,

I need to incoporate reporting services into my ASP.NET application.

Should I:

1. Use the ReportViewer control to map to my Server or

2. Add a reference to the web service?

Which is the 'best' option?

What are advantages, disadvantages, drawbacks of either one?

Thanks!!

Hi,

Check this link out to help you decide which one better to use.

http://www.devx.com/dotnet/Article/30424/1763.

Hth,

Michael Castillones