3

Install Configure And Troubleshooting Sql Server Reporting Services

Posted by amiT jaiN

System Requirements

1. Windows Server 2003 / Windows 2000 Server/ Windows vista / Windows XP/ Windows Server 2008
2. IIS 5.0 or later is required for Microsoft SQL Server 2005 Reporting Services (SSRS) installations.
3. ASP.NET 2.0 is required for Reporting Services. When installing Reporting Services, SQL Server Setup will enable ASP.NET if it is not already enabled.
4. SQL server 2005 with SP1(In case you need to reinstall reporting services its advised to reinstall full SQL server rather then just reporting services).
5. SQL Server Setup requires Microsoft Windows Installer 3.1 or later and Microsoft Data Access Components (MDAC) 2.8 SP1 or later. You can download MDAC 2.8 SP1 from this Microsoft Web site.

Installation

Follow the steps mentioned in the link below
http://msdn.microsoft.com/en-us/library/aa545330.aspx

Run the Reporting Services Configuration tool, connect to the report server instance you've installed, and review the status indicator for each setting to verify that it is configured.


Configuring and troubleshooting reporting services

In browser window type http://localhost/reportserver (http://-computer name-/ReportServer-instance name-).
You can also go and try running report server directly through IIS.

First of all , go to start menu > run , type inetmgr to open IIS Manager
Now click on Web sites > Default Web Site > right click on Reports (Your report directory ) [in my case it's Reports$SQLExpress] ,and select properties




In properties window , go to Directory security tab > Authentication and access control > click on Edit



here Check the checkox saying enable anonymous access > copy the User name from the user name box , which should be entered automatically

And Uncheck it Again , don't apply anything for now , i mean don't allow anonymous access

open browser and type http://localhost/ReportServer or whatever is ur report server url , in my case it's http://localhost/ReportServer$SQLExpress,

login by giving username and password

You should see this window having two tabs , Contents , properties


click on properties tab, Now click on new role assignment

In the Group or user name Text box paste the user name u copied from iis manager , assign whatever roles u want to give

only Content Manager role will do as well

Click on Ok to finish this

Now Again open IIS Manager , and go to properties window of your ReportServer directory

Now Check the CheckBox saying allow anonymous access

Click on apply and this will resolve your issues

Good luck



Other SQL Server articles:
1.Import/Export Excel Data into Sql Server using SqlBulkCopy-ASP.NET
2.Install configure and troubleshooting sql server reporting services 2005
3.Ms sql server bulk insert method to import bulk csv data into database
4.Combine Multiple Records Comma Separated In One Column MSSQL
5.blogger page views post view hit counter to count how many times post has been read
6.ASP.NET articles - Creating rss feed and consuming with custom feed reader using C# and .NET 2.0

Read more >>

 

12

Populate Cascading DropDownList based on selection of other dropdown ASP.NET

Posted by amiT jaiN

There are several situations where we need to populate second or third dropdown list based on selection of first or second dropdwon.

For example populating state DropDown based on selection of Country from Country DropDown list and then population Cities DropDown based on selectedIndex of State

For this we need to write code in code behind in the SelectedIndexChanged Event of respective Dropdown to implement the cascading of DropDowns

The html source of the page goes like this

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<strong>Country : &nbsp;</strong>
<asp:DropDownList ID="ddlCountry" runat="server"
AutoPostBack="True" OnSelectedIndexChanged="ddlCountry_SelectedIndexChanged">
</asp:DropDownList><br />
<br />

<strong>State: &nbsp; &nbsp; &nbsp; </strong>

<asp:DropDownList ID="ddlState" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlState_SelectedIndexChanged">
</asp:DropDownList><br />
<br />

<strong>City: &nbsp; &nbsp; &nbsp;&nbsp; </strong>

<asp:DropDownList ID="ddlCity" runat="server" AutoPostBack="True">
</asp:DropDownList><br />
<br />

<asp:Label ID="lblMsg" runat="server"></asp:Label></div>
</form>
</body>
</html>


And the code behind for this will be
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
FillCountry();
}
}

protected void ddlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
int CountryID = Convert.ToInt32(ddlCountry.SelectedValue.ToString());
FillStates(CountryID);
}
protected void ddlState_SelectedIndexChanged(object sender, EventArgs e)
{
int StateID = Convert.ToInt32(ddlState.SelectedValue.ToString());
FillCities(StateID);
}

private void FillCountry()
{
string strConn = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select CountryID, Country from Country";
DataSet objDs = new DataSet();
SqlDataAdapter dAdapter = new SqlDataAdapter();
dAdapter.SelectCommand = cmd;
con.Open();
dAdapter.Fill(objDs);
con.Close();
if (objDs.Tables[0].Rows.Count > 0)
{
ddlCountry.DataSource = objDs.Tables[0];
ddlCountry.DataTextField = "Country";
ddlCountry.DataValueField = "CountryID";
ddlCountry.DataBind();
ddlCountry.Items.Insert(0, "--Select--");
}
else
{
lblMsg.Text = "No Countries found";
}

}

private void FillStates(int countryID)
{
string strConn = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select StateID, State from State where CountryID =@CountryID";
cmd.Parameters.AddWithValue("@CountryID", countryID);
DataSet objDs = new DataSet();
SqlDataAdapter dAdapter = new SqlDataAdapter();
dAdapter.SelectCommand = cmd;
con.Open();
dAdapter.Fill(objDs);
con.Close();
if (objDs.Tables[0].Rows.Count > 0)
{
ddlState.DataSource = objDs.Tables[0];
ddlState.DataTextField = "State";
ddlState.DataValueField = "StateID";
ddlState.DataBind();
ddlState.Items.Insert(0, "--Select--");
}
else
{
lblMsg.Text = "No states found";
}
}

private void FillCities(int stateID)
{
string strConn = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(strConn);
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select CityID, City from City where StateID =@StateID";
cmd.Parameters.AddWithValue("@StateID", stateID);
DataSet objDs = new DataSet();
SqlDataAdapter dAdapter = new SqlDataAdapter();
dAdapter.SelectCommand = cmd;
con.Open();
dAdapter.Fill(objDs);
con.Close();
if (objDs.Tables[0].Rows.Count > 0)
{
ddlCity.DataSource = objDs.Tables[0];
ddlCity.DataTextField = "City";
ddlCity.DataValueField = "CItyID";
ddlCity.DataBind();
ddlCity.Items.Insert(0, "--Select--");
}
else
{
lblMsg.Text = "No Cities found";
}
}


Hope this helps

You would also like to read
1. Populating dropdown based on the selection of first drop down in DetailsView using FindControl and ItemTemplate

2. Ajax Cascading DropDownList in GridView with databse in ASP.NET


Read more >>

 

13

Create Consuming Rss Feeds In ASP.NET With Custom Feed Reader

Posted by amiT jaiN

In this example i'm going to describe how to create and consume rss feeds for your web application using ASP.NET and C#

First of all we need to create a SQL server database to store and fetch data for feeds.
Create a database and name it RSS and create a table according to image below

And add some data in this table.


Now create a new website in Visual studio and name it RssFeed
Add a new web form and name it Employees.

Go to html source of the page
And add this page directive below the first line on the page

<%@ OutputCache Duration="120" VaryByParam="EmpId" %>


Now go to code behind of Employee page and write this code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Text;
using System.Data.SqlClient;
public partial class Employees : System.Web.UI.Page
{
string strConnection =
ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
// Clear the response buffer contents
Response.Clear();
Response.ContentType = "text/xml";
XmlTextWriter rssFeed = new XmlTextWriter
(Response.OutputStream, Encoding.UTF8);

//writing RSS tags
rssFeed.WriteStartDocument();
rssFeed.WriteStartElement("rss");
rssFeed.WriteAttributeString("version", "2.0");
rssFeed.WriteStartElement("channel");
rssFeed.WriteElementString("title", "Employee Details");
rssFeed.WriteElementString("link", "http://localhost:2923/RssFeed");
rssFeed.WriteElementString("description", "Details of Employees");

// create sql connection and connect to database
SqlConnection con = new SqlConnection(strConnection);
SqlCommand cmd = new SqlCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select * from Employee";
cmd.Connection = con;
con.Open();
SqlDataReader dReader ;
dReader = cmd.ExecuteReader();
while (dReader.Read())
{
rssFeed.WriteStartElement("item");
rssFeed.WriteElementString("title", dReader["FirstName"].ToString()
+ " " +dReader["LastName"].ToString());
rssFeed.WriteElementString("description", dReader["Location"].ToString());
rssFeed.WriteElementString("link",
"http://localhost:2923/RssFeed/Employees.aspx?EmpID=" +
dReader["ID"]);
rssFeed.WriteElementString("pubDate", DateTime.Now.ToString());
rssFeed.WriteEndElement();
}
dReader.Close();
con.Close();
rssFeed.WriteEndElement();
rssFeed.WriteEndElement();
rssFeed.WriteEndDocument();
rssFeed.Flush();
rssFeed.Close();
Response.End();

}

}
}

Save, build and run the project

Creating custom RSS FEED reader

Create a new project and add new web form to it , name it FeedReader.
Add a new web form and name it anything you want, go to html source of the page and add this
inside <form> tag of the form
<table cellpadding="0" cellspacing="0">
<tr>
<td>
<table class="NormalText" runat="server"
id="tblNews" cellpadding="0" cellspacing="0">
</table>
</td>
</tr>
</table>


Now go to code behind of the page and write this code
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Net;
using System.Xml;
using System.IO;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string rss =
"http://localhost:2923/RssFeed/Employees.aspx".ToString();
try
{
FetchRssFeeds(rss);
}
catch (Exception ex)
{
}
}
public void FetchRssFeeds(string rss)
{
// Read the RSS feed
WebRequest rssRequest = WebRequest.Create(rss);
WebResponse rssResponse = rssRequest.GetResponse();

Stream rssStream = rssResponse.GetResponseStream();

// Load XML Document
XmlDocument rssDocument = new XmlDocument();
rssDocument.Load(rssStream);

XmlNodeList rssList = rssDocument.SelectNodes("rss/channel/item");

string title = "";
string link = "";
string description = "";

// Loop through RSS Feed items
for (int i = 0; i < rssList.Count; i++)
{
XmlNode rssDetail;

rssDetail = rssList.Item(i).SelectSingleNode("title");
if (rssDetail != null)
{
title = rssDetail.InnerText;
}
else
{
title = "";
}

rssDetail = rssList.Item(i).SelectSingleNode("link");
if (rssDetail != null)
{
link = rssDetail.InnerText;
}
else
{
link = "";
}

rssDetail = rssList.Item(i).SelectSingleNode("description");
if (rssDetail != null)
{
description = rssDetail.InnerText;
}
else
{
description = "";
}

// Populate the HTML table rows and cells
HtmlTableCell cell = new HtmlTableCell();
cell.InnerHtml = "<b><a href='" + link + "' target='new'>"
+ title + "</a></b>";
HtmlTableRow trow = new HtmlTableRow();
trow.Cells.Add(cell);
tblNews.Rows.Add(trow);
HtmlTableCell cell2 = new HtmlTableCell();
cell2.InnerHtml = "<p align='justify'>" + description + "</p>";
HtmlTableRow trow2 = new HtmlTableRow();
trow2.Cells.Add(cell2);
tblNews.Rows.Add(trow2);
}
}
}


Save, build and run the project
Download the sample code




Other Posts:
C#.NET Articles -Cascading DropDownList Populate dropdown based on selection of other dropdown in ASP.NET

Install configure and troubleshooting sql server reporting services 2005

Highlight gridview row on mouse over using javascript in asp.net and C# c-sharp

Read more >>

 

4

Import CSV Data Into MsSql Server Using Bulk Insert Method

Posted by amiT jaiN

Import CSV Data or File Into MsSql Server Using Sql Bulk Insert Method in Asp.Net.

Some time while developing applications using ms sql server we need to import data into sql server from data files or csv files.

For this we can use bulk insert method to insert data from csv file to sql server .

1. create a csv file with some data in it like


amit,noida,26

sharad,noida,26

Shobhit,Delhi,25

Now run the following script to load data from this csv file into Database

--Temp table to store the data
CREATE TABLE Test
(
Name VARCHAR(50),
City VARCHAR(50),
Age INT
)
GO

--Bulk insert into Test
BULK INSERT Test
FROM 'C:\test.csv'
WITH
(
FIELDTERMINATOR=',',
ROWTERMINATOR = '\n'
)
GO

--Get all data from Test
SELECT *
FROM Test
GO

--Drop the temp table
DROP TABLE Test
GO


Other SQL Server articles:
1.Import/Export Excel Data into Sql Server using SqlBulkCopy-ASP.NET
2.Install configure and troubleshooting sql server reporting services 2005
3.Ms sql server bulk insert method to import bulk csv data into database
4.Combine Multiple Records Comma Separated In One Column MSSQL
6.Disable browser back button functionality using javascript in ASP.NET
7.Method error 500 / 12031 in implementing ajax cascadingdropdown extender
8.Unable to attach binding handle invalid error in visual studio 2005 while debugging
9.The backup set holds a backup of a database other than the existing database-Sql Server Error 3154

Read more >>

 

Subscribe to: Posts (Atom)

Popular Posts

Find More Articles




C#.NET Articles and tutorials,ASP.NET Articles - blog by amiT jaiN