Archive

Archive for February, 2012

SQL Function to Convert Number to Character

February 28, 2012 1 comment

Hi all,

try this example to use SQL Function to Convert Number to Character


--create the following function
CREATE FUNCTION dbo.Convert_Num_ToWords
(
 @Number Numeric (38, 0) -- Input number with as many as 18 digits

) RETURNS VARCHAR(8000)
/*
* Converts a integer number as large as 34 digits into the
* equivalent words. The first letter is capitalized.
*
* Attribution: Based on NumberToWords by Srinivas Sampath
* as revised by Nick Barclay
*
* Example:
select dbo.udf_Num_ToWords (1234567890) + CHAR(10)
 + dbo.udf_Num_ToWords (0) + CHAR(10)
 + dbo.udf_Num_ToWords (123) + CHAR(10)
select dbo.udf_Num_ToWords(76543210987654321098765432109876543210)

DECLARE @i numeric (38,0)
SET @i = 0
WHILE @I <= 1000 BEGIN
 PRINT convert (char(5), @i)
 + convert(varchar(255), dbo.udf_Num_ToWords(@i))
 SET @I = @i + 1
END
*
* Published as the T-SQL UDF of the Week Vol 2 #9 2/17/03
****************************************************************/
AS BEGIN

DECLARE @inputNumber VARCHAR(38)
DECLARE @NumbersTable TABLE (number CHAR(2), word VARCHAR(10))
DECLARE @outputString VARCHAR(8000)
DECLARE @length INT
DECLARE @counter INT
DECLARE @loops INT
DECLARE @position INT
DECLARE @chunk CHAR(3) -- for chunks of 3 numbers
DECLARE @tensones CHAR(2)
DECLARE @hundreds CHAR(1)
DECLARE @tens CHAR(1)
DECLARE @ones CHAR(1)

IF @Number = 0 Return 'Zero'

-- initialize the variables
SELECT @inputNumber = CONVERT(varchar(38), @Number)
 , @outputString = ''
 , @counter = 1
SELECT @length = LEN(@inputNumber)
 , @position = LEN(@inputNumber) - 2
 , @loops = LEN(@inputNumber)/3

-- make sure there is an extra loop added for the remaining numbers
IF LEN(@inputNumber) % 3 <> 0 SET @loops = @loops + 1

-- insert data for the numbers and words
INSERT INTO @NumbersTable SELECT '00', ''
 UNION ALL SELECT '01', 'one' UNION ALL SELECT '02', 'two'
 UNION ALL SELECT '03', 'three' UNION ALL SELECT '04', 'four'
 UNION ALL SELECT '05', 'five' UNION ALL SELECT '06', 'six'
 UNION ALL SELECT '07', 'seven' UNION ALL SELECT '08', 'eight'
 UNION ALL SELECT '09', 'nine' UNION ALL SELECT '10', 'ten'
 UNION ALL SELECT '11', 'eleven' UNION ALL SELECT '12', 'twelve'
 UNION ALL SELECT '13', 'thirteen' UNION ALL SELECT '14', 'fourteen'
 UNION ALL SELECT '15', 'fifteen' UNION ALL SELECT '16', 'sixteen'
 UNION ALL SELECT '17', 'seventeen' UNION ALL SELECT '18', 'eighteen'
 UNION ALL SELECT '19', 'nineteen' UNION ALL SELECT '20', 'twenty'
 UNION ALL SELECT '30', 'thirty' UNION ALL SELECT '40', 'forty'
 UNION ALL SELECT '50', 'fifty' UNION ALL SELECT '60', 'sixty'
 UNION ALL SELECT '70', 'seventy' UNION ALL SELECT '80', 'eighty'
 UNION ALL SELECT '90', 'ninety'

WHILE @counter <= @loops BEGIN

 -- get chunks of 3 numbers at a time, padded with leading zeros
 SET @chunk = RIGHT('000' + SUBSTRING(@inputNumber, @position, 3), 3)

 IF @chunk <> '000' BEGIN
 SELECT @tensones = SUBSTRING(@chunk, 2, 2)
 , @hundreds = SUBSTRING(@chunk, 1, 1)
 , @tens = SUBSTRING(@chunk, 2, 1)
 , @ones = SUBSTRING(@chunk, 3, 1)

 -- If twenty or less, use the word directly from @NumbersTable
 IF CONVERT(INT, @tensones) <= 20 OR @Ones='0' BEGIN
 SET @outputString = (SELECT word
 FROM @NumbersTable
 WHERE @tensones = number)
 + CASE @counter WHEN 1 THEN '' -- No name
 WHEN 2 THEN ' thousand ' WHEN 3 THEN ' thousand '
 WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
 WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
 WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
 WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
 WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
 ELSE '' END
 + @outputString
 END
 ELSE BEGIN -- break down the ones and the tens separately

 SET @outputString = ' '
 + (SELECT word
 FROM @NumbersTable
 WHERE @tens + '0' = number)
 + '-'
 + (SELECT word
 FROM @NumbersTable
 WHERE '0'+ @ones = number)
 + CASE @counter WHEN 1 THEN '' -- No name
 WHEN 2 THEN ' thousand ' WHEN 3 THEN ' million '
 WHEN 4 THEN ' billion ' WHEN 5 THEN ' trillion '
 WHEN 6 THEN ' quadrillion ' WHEN 7 THEN ' quintillion '
 WHEN 8 THEN ' sextillion ' WHEN 9 THEN ' septillion '
 WHEN 10 THEN ' octillion ' WHEN 11 THEN ' nonillion '
 WHEN 12 THEN ' decillion ' WHEN 13 THEN ' undecillion '
 ELSE '' END
 + @outputString
 END

 -- now get the hundreds
 IF @hundreds <> '0' BEGIN
 SET @outputString = (SELECT word FROM @NumbersTable WHERE '0' + @hundreds = number) + ' hundred ' + @outputString
 END
 END

 SELECT @counter = @counter + 1
 , @position = @position - 3

END

-- Remove any double spaces
SET @outputString = LTRIM(RTRIM(REPLACE(@outputString, ' ', ' ')))
SET @outputstring = UPPER(LEFT(@outputstring, 1)) + SUBSTRING(@outputstring, 2, 8000)

RETURN @outputString -- return the result
END

Now to use this Function

select dbo.Convert_Num_ToWords(5800000)

Hope this helps

Good Luck

Advertisements
Categories: SQL Server

JQuery to Select CheckBoxes in Checkboxlist

February 21, 2012 Leave a comment

Hi all ,

try this example to use JQuery to Select CheckBoxes in Checkboxlist


<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
 <script src="http://ajax.aspnetcdn.com/ajax/jQuery/jquery-1.6.js" type="text/javascript"></script>
 <script type="text/javascript">
 $(document).ready(function () {
 $($('#<%=values.ClientID %>').val().split(",")).each(function (i, item) {
 $("table[id*=CheckBoxList1] input[value=" + $.trim(item) + "]").attr('checked', true);
 });
 });
</script>
</head>
<body>
 <form id="form1" runat="server">
 <asp:HiddenField ID="values" runat="server" Value="1, 2, 4" />
 <asp:CheckBoxList ID="CheckBoxList1" runat="server">
 <asp:ListItem Text="Text1" Value="1"></asp:ListItem>
 <asp:ListItem Text="Text2" Value="2"></asp:ListItem>
 <asp:ListItem Text="Text3" Value="3"></asp:ListItem>
 <asp:ListItem Text="Text4" Value="4"></asp:ListItem>
 </asp:CheckBoxList>
 </form>
</body>
</html>

Hope this helps

Good Luck.

Categories: ASP.Net, Javascript, Jquery

Export ReportViwer to Excel,PDF,and Image from Code

February 14, 2012 Leave a comment

Hi all,

try this example to export MS ReportViwer to Excel and PDF and Image from Code

In this example i will use Categories table in Northwind Database

1) Open VS2008 and create a new website

2) Right click on Solution Explorer and Add new DataSet and name it “NorthwindDS.xsd” and from Server Explorer drag Categories table into Dataset

3) Right click on Solution Explorer and Add new it then select Report and rename it to “CategoryReport.rdlc” and you can design your report which data to display in your report

4) Add new Web Page and from Toolbox drag and Drop ReportViwer and three buttons for export (Excel,Pdf,Image)

5) Now we need to display data from Categories table and display it on Reportviewer so add this code


private void FillReport()
 {
 try
 {
 NorthwindDS _ds = new NorthwindDS();
 NorthwindDSTableAdapters.ProductsTableAdapter _ProductsTableAdapter = new NorthwindDSTableAdapters.ProductsTableAdapter();
 Microsoft.Reporting.WebForms.ReportDataSource rds;
 _ProductsTableAdapter.Fill(_ds.Categories);
 rds = new Microsoft.Reporting.WebForms.ReportDataSource("NorthwindDS_Categories", _ds.Categories);
 ReportViewer1.LocalReport.ReportPath = "Report/ProductReport.rdlc";
ReportViewer1.LocalReport.DataSources.Clear();
 ReportViewer1.LocalReport.DataSources.Add(rds);
 ReportViewer1.LocalReport.Refresh();
 }
 catch
 {

}
 }

so will add this method in Page Load


protected void Page_Load(object sender, EventArgs e)
 {
 if (!IsPostBack)
 {
 FillReport();
 }
 }

Now we need to create method for Report Export to Excel,Pdf,Image


public enum ReportType
 {
 PDF,
 Excel,
 Image
 }

private void ExportReport(string fileName,string reporttype)
 {
 // Variables
 Warning[] warnings;
 string[] streamIds;
 string mimeType = string.Empty;
 string encoding = string.Empty;
 string extension = string.Empty;
 byte[] bytes = ReportViewer1.LocalReport.Render(reporttype, null, out mimeType, out encoding, out extension, out streamIds, out warnings);
 // Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
 Response.Buffer = true;
 Response.Clear();
 Response.ContentType = mimeType;
 Response.AddHeader("content-disposition", "attachment; filename=" + fileName + "." + extension);
 Response.BinaryWrite(bytes); // create the file
 Response.Flush(); // send it to the client to download
 }

In Button Click we can call this method


protected void btn_Pdf_Click(object sender, EventArgs e)
 {
 ExportReport("PDF", ReportType.PDF.ToString());
 }
 protected void btn_Excel_Click(object sender, EventArgs e)
 {
 ExportReport("Excel", ReportType.Excel.ToString());
 }
 protected void btn_Image_Click(object sender, EventArgs e)
 {
 ExportReport("Image", ReportType.Image.ToString());
 }

Now Browse your website

Hope this helps

Good Luck

 

Categories: ASP.Net

Use Yahoo Weather Forecast with ASP.Net

February 9, 2012 12 comments

Hi all,

try this example to Use Yahoo Weather Forecast with ASP.Net

1) Open VS2008 and create new website

2) Add new Class and name it “WeatherItem.cs”


using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

public class WeatherItem
{
 #region Prob
 public string DayName { get; set; }
 public System.DateTime Day { get; set; }
 public int Low { get; set; }
 public int High { get; set; }
 public string Forcast { get; set; }
 public string Code { get; set; }
 public string ImageUrl { get; set; }
 public string LocalImageUrl { get; set; }
 protected string LocalImagePath = "Images/Weather/";
 #endregion

public WeatherItem()
 {
 //
 // TODO: Add constructor logic here
 //
 }

public WeatherItem(string localImagePath)
 {
 LocalImagePath = localImagePath;
 }
 public void YahooForcast(XElement forcastItem)
 {
 DayName = forcastItem.Attribute("day").Value;
 Day = Convert.ToDateTime(forcastItem.Attribute("date").Value);
 Low = Convert.ToInt32(forcastItem.Attribute("low").Value);
 High = Convert.ToInt32(forcastItem.Attribute("high").Value);
 Forcast = forcastItem.Attribute("text").Value;
 Code = forcastItem.Attribute("code").Value;
 ImageUrl = "http://l.yimg.com/a/i/us/we/52/" + Code + ".gif";
 LocalImageUrl = LocalImagePath + Code + ".gif";
 }
}

3) Add new User Control and name it “YahooWeather.ascx” and from Toolbox drag DataList and Label Control


<%@ Control Language="C#" AutoEventWireup="true" CodeFile="YahooWeather.ascx.cs" Inherits="YahooWeather" %>
<style type="text/css">
 .wthrTitle { font-family: Tahoma; font-size: 14px; width: 370px; color: White; background: blue; border-top: 1px solid navy; text-align: center; }
 .wthrContainer { text-align: center; border: 1px; float: left; }
 .wthrDayTitle { font-family: Tahoma; font-size: 14px; width: 100%; color: White; background: blue; border-bottom: 1px solid navy; border-top: 1px solid navy; }
 .wthrForcast { font-family: Tahoma; font-size: 12px; color: blue; }
 .wthrTemp { font-family: Tahoma; font-size: 10px; color: blue; }
 .wthrClear { clear: left; }
 </style>
<div class="wthrTitle"><asp:Label ID="descriptionLabel" runat="server" Text='' /></div>
<asp:DataList ID="DL_Weather" runat="server" RepeatDirection="Horizontal"
 CellPadding="2" CellSpacing="2">
 <ItemTemplate>
 <div class="wthrContainer">
 <div class="wthrDayTitle"><asp:Label ID="dayLabel" runat="server" Text='<%# Eval("DayName") %>' /></div><br />
 <asp:Image ID="forcastImage" runat="server" ImageUrl='<%# Eval("ImageUrl") %>' /><br />
 <asp:Label ID="forcastLabel" runat="server" Text='<%# Eval("Forcast") %>' CssClass="wthrForcast" /><br />
 <div class="wthrForcast"><asp:Label ID="highLabel" runat="server" Text='<%# Eval("High") %>' />° -
 <asp:Label ID="lowLabel" runat="server" Text='<%# Eval("Low") %>' />°</div><br />
 </div>
 </ItemTemplate>
</asp:DataList>
<br class="wthrClear" />
<asp:HiddenField ID="zipcodeHidden" runat="server" />

4) In code behind add the following code :-


using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Xml;
using System.Xml.Linq;
using System.Net;
using System.Collections.Generic;

public partial class YahooWeather : System.Web.UI.UserControl
{

#region "Properties"
 public string ZipCode
 {
 get { return zipcodeHidden.Value; }
 set { zipcodeHidden.Value = value; }
 }
 #endregion

 protected void Page_Load(object sender, EventArgs e)
 {
 GetWeather(ZipCode);
 }

 protected void GetWeather(string zipCode)
 {
 try
 {
 XDocument xmlData = XDocument.Load("http://xml.weather.yahoo.com/forecastrss/" + zipCode + "_f.xml");
 List<WeatherItem> _list = new List<WeatherItem>();
 string _description = null;
 System.DateTime _date;
 foreach (XElement _row in xmlData.Root.Elements("channel"))
 {
 foreach (XElement _field in _row.Elements())
 {
 if ((_field.Name.ToString() == "description"))
 {
 // cherry pick the desired data at this level.
 _description = _field.Value;
 descriptionLabel.Text = _description;
 }
 if ((_field.Name.ToString() == "lastBuildDate"))
 {
 string _dateString = _field.Value.Substring(5);
 _date = DateTime.Parse(_dateString.Substring(0, _dateString.Length - 4));
 descriptionLabel.Text = _description + " at " + _date.ToShortTimeString();
 }
 if (_field.Name.ToString() == "item")
 {
 foreach (XElement _itm in _field.Elements())
 {
 if (_itm.Name.LocalName == "forecast")
 {
 WeatherItem _wi = new WeatherItem();
 _wi.YahooForcast(_itm);
 _list.Add(_wi);
 }
 }
 }
 }
 }
 if (_list.Count > 0)
 {
 _list[0].DayName = "Today";
 }
 DL_Weather.DataSource = _list;
 DL_Weather.DataBind();
 }
 catch (Exception e)
 {

 }
 }
}

5) Add new Web Page and drag the YahooWeather.ascx UserControl inside it and browse your page

<uc1:YahooWeather ID="YahooWeather1" runat="server" ZipCode="39827" />

Hope this helps

Good Luck

 

Categories: ASP.Net