Monthly Archives: October 2011

ASP classic like code behind on the page

<% @Page Language="VB" %><%@ Import Namespace="System.Xml" %><script runat="server">    Protected Sub btnsubmit_Click(sender As Object, e As EventArgs)        lblErrorMessage.Text = string.Empty        Dim errorString As String = String.Empty    Dim userId As String = frmUserName.Text.Trim()    Dim password As String = frmPassword.Text.Trim()    Dim status As String = String.Empty    Dim message As String = String.Empty    'bool serviceWorkedProperly = false;    Try        Dim xmlStream As New XmlDocument()        Dim root As XElement        Dim WEBSERVICE As String = string.Format("https://test.com.service.asmx/CheckNamePwd?name={0}&password={1}", userId, password)        xmlStream.Load(WEBSERVICE)        root = XElement.Load(WEBSERVICE)        status = root.Value        Dim ErrorCodes As String() = New String(6) {}        ErrorCodes(0) = "Authenticated"        ErrorCodes(1) = "Username not found"        '"Credentials not found"        ErrorCodes(2) = "Password is incorrect"        ErrorCodes(3) = "Credentials not found"        ErrorCodes(4) = "Expired or inactive"        ErrorCodes(5) = "Unknown Exception"        ErrorCodes(6) = "ValidationError"        errorString = Array.Find(ErrorCodes, Function(element) element.Equals(status))        If errorString Is Nothing Then            errorString = "errorstring is empty"        End If    Catch ex As Exception        errorString = ex.Message    End Try        lblErrorMessage.Visible = True        lblErrorMessage.Text = errorStringEnd Sub</script><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server">    <title>Untitled Page</title></head><body>    <form id="form1" runat="server">    <div>        <asp:Label ID="lblErrorMessage" runat="server" Visible="false" />        <br /><br />        Username: <asp:TextBox ID="frmUserName" runat="server" />                            <asp:RequiredFieldValidator ID="valfrmUserName" runat="server" ErrorMessage="* Username Required" ControlToValidate="frmUserName" />        <br />        Password: <asp:TextBox ID="frmPassword" runat="server" TextMode="Password" />        <asp:RequiredFieldValidator ID="valfrmPassword" runat="server" ErrorMessage="* Password Required" ControlToValidate="frmPassword" />        <asp:Button ID="btnsubmit" runat="server" Text="Login" OnClick="btnsubmit_Click" />    </div>    </form></body></html>

SQL string occurrences

Function

CREATE FUNCTION [dbo].[StringOccurrence]( @StringInput VARCHAR(8000), @StringSearch VARCHAR(100) )RETURNS INTBEGIN    RETURN (LEN(@StringInput) -             LEN(REPLACE(@StringInput, @StringSearch, ''))) /            LEN(@StringSearch)ENDGO

Usage

SELECT * FROM CTE_EmailsWHERE  dbo.StringOccurrence(Email, '@') = 1

 

ASP.NET Access Page variables from User Control

BasePage class

namespace Classes{    public class BasePage : System.Web.UI.Page    {        public string CurrentBusinessPartnerID = "testId";    }}

BaseUserControl class

namespace Classes{    public class BaseUserControl : System.Web.UI.UserControl    {        private string _currentBusinessPartnerID = null;        public BaseUserControl()        {                   }        public string CurrentBusinessPartnerID        {            get            {                BasePage currentPage = (BasePage) this.Page;                                if (currentPage != null)                {                    currentPage = (BasePage)HttpContext.Current.Handler;                }                _currentBusinessPartnerID = currentPage.CurrentBusinessPartnerID;                return _currentBusinessPartnerID;            }        }    }}

User Control

// insteadpublic partial class UtilityMenu : System.Web.UI.UserControl// use thispublic partial class UtilityMenu : Classes.BaseUserControl{    protected void Page_Load(object sender, EventArgs e)    {        litTest.Text = CurrentMeter.StreetAddress;    }}

C# Simple CSV Excel export

protected void btnDownload_Click(object sender, EventArgs e){    StringBuilder strResponse = new StringBuilder();    //Heading    strResponse.AppendLine(string.Format("{0},{1}", "EndDate", "KWH Usage"));    //For Each Data Point    strResponse.AppendLine(string.Format("{0},{1}", "2011-10-10", "11.11"));    strResponse.AppendLine(string.Format("{0},{1}", "2011-10-11", "11.11"));    HttpResponse Export = HttpContext.Current.Response;    Export.Clear();    Export.ContentType = "application/octet-stream";    Export.AddHeader("Content-Length", strResponse.Length.ToString());    Export.AddHeader("Content-Disposition", "attachment; filename=CSVFile.csv");    System.Text.Encoding enc = System.Text.Encoding.UTF8;    byte[] fileBytes = enc.GetBytes(strResponse.ToString());    Export.OutputStream.Write(fileBytes, 0, fileBytes.Length);    Export.Flush();    Export.Close();}

See simple class on Stack Overflow

SQL Server: Calculate Summary and Column Summary

This post shows how to show a summary of similar rows as well as column summary for SQL Server table data. Suppose you want to generate the total of a column and display it at the end of the result set. You can use at least two methods as shown below:

Consider the following data:

sql-server-summary

declare @t table(name varchar(100), amount decimal(12,2))
insert into @t
select ‘test1’, 1000 union all
select ‘test1’, 2000 union all
select ‘test1’, 3000 union all
select ‘test2’, 1500 union all
select ‘test3’, 600 union all
select ‘test3’, 1800
If you want to want to sum the amount for each name and also show the total of all names
at the end, use any of the following methods
METHOD 1: Using ROLLUP
select coalesce(name,’Total’) as name, SUM(amount) as amount from @t
group by name
with rollup

METHOD 2: Using UNIONALL
select name, SUM(amount) as amount from @t
group by name
union all
select ‘Total’,SUM(amount) from @t

OUTPUT
sql server total subtotal

Creating Charts with the Google Chart API

I’ve always wondered how the phrase “A picture is worth a thousand words” came about. I like to think that it was coined by some mid-level manager viewing a sales figures report that consisted of metrics from the past 1,000 days. After scanning this long list of numbers, he found, at the bottom of the page, a line chart that summarized the numbers, and uttered that now well-known adage. Charts and graphs provide a succinct synopsis of large amounts of data. With charts a person can quickly spot trends, compare different resultsets, or recognize patterns.