sanwar_mal_jat - Professional Profile

SQL

--Get duplicate Row

 select ipFROM,ipTO,countrySHORT,countryLONG, count(ipTO) from IPCountry1
 group by ipFROM,ipTO,countrySHORT,countryLONG
 having count(ipTO) >1

SELECT * FROM IPCountry1 WHERE pk NOT IN (SELECT MIN(pk)
FROM IPCountry1 GROUP BY ipFROM,ipTO,countrySHORT,countryLONG )

SELECT * FROM IPCountry1 WHERE pk NOT IN (SELECT MAX(pk)
FROM IPCountry1 GROUP BY ipFROM,ipTO,countrySHORT,countryLONG )


--Delete duplicate Row

delete FROM IPCountry1 WHERE pk NOT IN (SELECT MAX(pk)
FROM IPCountry1 GROUP BY ipFROM,ipTO,countrySHORT,countryLONG )

Thanks & Regards

Sanwar

local:
1) denoted by # symbol.
2) valid for the current connection only.
They are cleared as soon as the curent connection closes.
3)cannot be shared between multiple users.

CREATE TABLE #LocalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

insert into #LocalTempTable values ( 1, 'Abhijit','India');

select * from #LocalTempTable

global:
1)denoted by ## symbol.
2)Available to all the connections once created.
"They are unallocated from the Tempdb when the connection closed where the actual table was created"
3)can be shared betwen multiple users.
CREATE TABLE ##NewGlobalTempTable(
UserID int,
UserName varchar(50), 
UserAddress varchar(150))

insert into ##NewGlobalTempTable values ( 1, 'Abhijit','India');

select * from ##NewGlobalTempTable


Both of then are stored in the tempdb database Temporary Tables Folder

Thanks & Regards

Sanwar

XML

<%@ Page Language="C#" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 <script runat="server">
     public class Country
     {
         public int Value { get; set; }
         public string Name { get; set; }

     }
 // These all methods  You can use in .cs file If u want
    protected void Page_Load(object sender, EventArgs e)
    {

    }

    //Mark your method as static and give it the WebMethod attribute
    //The method has to be declared static. It must also be marked with the WebMethod attribute. You'll probably find that you need to include System.Web.Services
    //using System.Web.Services;
    [System.Web.Services.WebMethod]
    public static string ProcessIT(string name, string address)
    {
        //Here We can write or use Data base Working
        string result = "Welcome Mr. " + name + ". Your address is '" + address + "'.";
        return result;
    }

    [System.Web.Services.WebMethod] // This method May be in Web SERVICE
    public static string DisplayData(string name)
    {//Here We can write or use Data base Working
        if (name == "vikram")
            return DateTime.Now.ToString();
        else
            return "Send Name Not Vikram";
    }



    [System.Web.Services.WebMethod] // This method May be in Web SERVICE
    public static System.Collections.Generic.List<Country> GetCountry()
    {
        System.Collections.Generic.List<Country> listObj = new System.Collections.Generic.List<Country>();
        for (int i = 0; i <= 10; i++)
        {
            Country obj = new Country();
            obj.Value = i;
            obj.Name = "Copuntry " + i;
            listObj.Add(obj);
        }
        return listObj;
    }
</script>

<html>
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
 ----------------------------- This is Page Method------------------------------------
        <%-- Enable Page Methods on your ScriptManager  Set the EnablePageMethods attribute to true --%>
        <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
        </asp:ScriptManager>

<script type="text/javascript">
    function HandleIT() {
        var name = "TestName";
        var address = "TestAdderess";
        PageMethods.ProcessIT(name, address, onSucess, onError);
        function onSucess(result) {
            alert(result);
        }
        function onError(result) {
            alert('Something wrong.');
        }
    }
    </script>
     <br />
        <asp:Button ID="PageMethod" runat="server" Text="PageMethod" OnClientClick="HandleIT(); return false;" />

        <br />


 ----------------------------- This is Ajex Method------------------------------------

 <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.6.2/jquery.min.js" ></script>

  <script type="text/javascript">
      function ShowCurrentTime() {
          var name = "vikram";
          $.ajax({
              type: "POST",
              contentType: "application/json; charset=utf-8",
              url: "Default.aspx/DisplayData",
              data: '{name:"' + name + '"}',
              dataType: "json",
              success: function (data) {
                  alert(data.d); //where data.d is used for deserialzation in javascript
              },
              error: function (result) {
                  alert("Error");
              }
          });
      }
</script>
 <br />
   <asp:Button ID="USEAjex" runat="server" Text="USE Ajex" OnClientClick="ShowCurrentTime(); return false;" />
    <br />
  ----------------------------- This is Ajex Method For Fill Droap down-----------------------------------

  <script type="text/javascript">
      function FillCity() {
          $.ajax({
              type: "POST",
              contentType: "application/json; charset=utf-8",
              url: "Default.aspx/GetCountry",
              data: "{}",
              dataType: "json",
              success: function (data) {
                  FillCities(data.d);
              },
              error: function (result) {
                  alert("Error");
              }
          });
      }


      function FillCities(data) {
          $("#ddlCity").empty();
          $("#ddlCity").append('<option value="0">' + "--Select City--" + '</option>');

          for (var i = 0; i < data.length; i++) {
              $("#ddlCity").append('<option value="' + data[i].Value + '">' + data[i].Name + '</option>');
          }
      }
</script>

  <br />
     <asp:Button ID="Button1" runat="server" Text="FillCity" OnClientClick="FillCity(); return false;" />

      <asp:DropDownList ID="ddlCity" runat="server">
            </asp:DropDownList>
    </div>
    </form>
</body>
</html>

Thanks & Regards

Sanwar

Imports System
Imports System.Web

Public Class MySession
    ' private constructor
    Private Sub New()
    End Sub

    ' Gets the current session.
    Public Shared Function StaticConverter() As MySession
        Return New MySession()
    End Function


    Public Shared ReadOnly Property Current() As MySession
        Get
            Dim session As MySession = DirectCast(HttpContext.Current.Session("__MySession__"), MySession)
            If session Is Nothing Then
                session = New MySession()
                HttpContext.Current.Session("__MySession__") = session
            End If
            Return session
        End Get
    End Property
 

   Public Property VoucherAmount() As Decimal
        Get
            Return _VoucherAmount
        End Get
        Set(ByVal value As Decimal)
            _VoucherAmount = value
        End Set
    End Property
    Private _VoucherAmount As Decimal = 0D

End Class

-----Use in class----------------

MySession.Current.VoucherAmount=10

Thanks & Regards

Sanwar

------Get Image Dimentions Useing an Another server Uploaded Images In VB.NET---------------------------------- 

Dim req As System.Net.WebRequest = System.Net.WebRequest.Create("URL")
                Dim response As System.Net.WebResponse = req.GetResponse()
                Dim stream As IO.Stream = response.GetResponseStream()
                 Dim fullSizeImg As System.Drawing.Image = System.Drawing.Image.FromStream(stream)
                productDesc.imageDimention = fullSizeImg.PhysicalDimension
             '  Dim Height As Integer = fullSizeImg.Height
              '  Dim Width As Integer = fullSizeImg.Width
                 stream.Close()

Thanks & Regards

Sanwar

http://rhinoslider.com/demo-download/[^]

Thanks & Regards

Sanwar

$(".Date").datepicker({
               dateFormat: 'dd.mm.yy',
               changeMonth: true,
               changeYear: true,
               closeText: "X",
               showButtonPanel: true
           });

           var _gotoToday = $.datepicker._gotoToday;
           $.datepicker._gotoToday = function (id) {
               var target = $(id);
               var inst = this._getInst(target[0]);
               _gotoToday.call(this, id);
               $.datepicker._selectDate(id, $.datepicker._formatDate(inst, inst.selectedDay, inst.selectedMonth, inst.selectedYear));
               target.blur();
           }

Thanks & Regards

Sanwar

SQL

-----CTAS – Create Table As SELECT---
 Select   * into NewTable From ExistingTable

--Insert all record one table to another new table and create a new table with it
 Select   * into NewTable From ExistingTable

--Create a table copy to another Without data copy
Select top 0 * into NewTable From ExistingTable

--Create a table copy to another Without data copy
Select top into NewTable From ExistingTable where 1=2

-- Create new table and insert record if ExistingTable  have 20 rows then insert this query only four rows in  NewTable

SELECT TOP (2) PERCENT * INTO NewTable FROM ExistingTable

-- SELECT INTO is minimally logged - inside transaction only
-- SELECT INTO not logged into the transaction log file
-- SELECT INTO table cannot be recovered with transaction log restore
-- SELECT INTO table can only be recovered after full database backup

Thanks & Regards

Sanwar

VB

Convert-- Static
Class Name     Type

Enumerable--   Static
HttpRequest--  Static
HttpResponse-- Static
HttpRuntime--  Static
Math--         Static
SiteMap--      Static


AppDomain --    Sealed
HttpContext --  Sealed
HttpCookie --   Sealed
String --       Sealed
Version --      Sealed

Array --        abstract
Delegate --     abstract
StringComparer--abstract
TimeZone--      abstract
Type--          abstract

Thanks & Regards

Sanwar

System.Xml.XmlDocument xdoc = new System.Xml.XmlDocument();
       xdoc.Load("http://ws1.postcodesoftware.co.uk/lookup.asmx/getAddress?account=2067&password=mnqgtf87&postcode=LS18%204AA");
       System.Xml.XmlNodeList bookList = xdoc.GetElementsByTagName("Address");
      foreach (System.Xml.XmlNode RootPerson in bookList)
       {
          string str1  = RootPerson["Address1"].InnerText;
           string str2 = RootPerson["Address2"].InnerText;
           string str3= RootPerson["Town"].InnerText;
           string str4 = RootPerson["County"].InnerText;

       }

Thanks & Regards

Sanwar

//  using System.Net;
         //using System.IO;
        System.Net.HttpWebRequest r = (System.Net.HttpWebRequest)System.Net.WebRequest.Create("http://ws1.postcodesoftware.co.uk/lookup.asmx/getAddress?account=2067&password=mnqgtf87&postcode=LS18%204AA");
        r.Method = "Get";
        System.Net.HttpWebResponse res = (System.Net.HttpWebResponse)r.GetResponse();
        System.IO.Stream sr = res.GetResponseStream();
        System.IO.StreamReader sre = new System.IO.StreamReader(sr);
        string s = sre.ReadToEnd();

Thanks & Regards

Sanwar

--------------------------------Read and write XML----------------------------------
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.Data.SqlClient;
using System.Xml.Serialization;
using System.Xml;
using System.IO;
using System.Collections.Generic;
---------------------------------------------
Return XML file by web service
---------------------------------------------
[WebMethod]
        public System.Xml.XmlDocument ReturnXml()
        {
            System.Xml.XmlDocument dom = new System.Xml.XmlDocument();

            System.Xml.XmlElement people = dom.CreateElement("People");
            dom.AppendChild(people);          

            #region Personal Details
            System.Xml.XmlElement person = dom.CreateElement("Person");
            people.AppendChild(person);

            System.Xml.XmlElement firstName = dom.CreateElement("FirstName");
            person.AppendChild(firstName);

            System.Xml.XmlElement lastName = dom.CreateElement("LastName");
            person.AppendChild(lastName);

            System.Xml.XmlText _fName = dom.CreateTextNode("Bob");
            System.Xml.XmlText _lName = dom.CreateTextNode("Christo");
            firstName.AppendChild(_fName);
            lastName.AppendChild(_lName);

            System.Xml.XmlElement Country = dom.CreateElement("Country");
            System.Xml.XmlElement City = dom.CreateElement("City");
            person.AppendChild(Country);
            person.AppendChild(City);

            System.Xml.XmlText _Country = dom.CreateTextNode("India");
            System.Xml.XmlText _City = dom.CreateTextNode("Jaipur");

            Country.AppendChild(_Country);
            City.AppendChild(_City);
            #endregion

            return dom;
        }
---------------------------------------------
Get value from xml nodes
---------------------------------------------
 localhost.Service1 web = new localhost.Service1();
        XmlNode docV = web.ReturnXml();

        foreach (XmlNode RootPerson in docV)
        {
            person = new Person();
            person.FirstName = RootPerson["FirstName"].InnerText;
            person.LastName = RootPerson["LastName"].InnerText;
            person.Country = RootPerson["Country"].InnerText;
            person.City = RootPerson["City"].InnerText;
            persons.Add(person);
        }
---------------------------------------------
Genearate new xml file from dataset
---------------------------------------------
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM CustOfCust_Tab", con);
        DataSet ds = new DataSet();
        da.Fill(ds, "CustOfCust_Tab");
        StreamWriter xmlDoc = new StreamWriter(Server.MapPath("~/Testdo.xml"), false);
        ds.WriteXml(xmlDoc);
        xmlDoc.Close();
---------------------------------------------
Read xml file from location
---------------------------------------------
XmlDataDocument xmlReader = new XmlDataDocument();
        xmlReader.DataSet.ReadXml(Server.MapPath("~/Testdo.xml"));
        DataSet ds = new DataSet();
        ds = xmlReader.DataSet;        
---------------------------------------------
Read xml file from location
---------------------------------------------
 XmlDocument xml = new XmlDocument();
        xml.Load(Server.MapPath("~/Testdo.xml"));
        XmlNodeList node = xml.SelectNodes("/NewDataSet/CustOfCust_Tab");
        foreach (XmlNode row in node)
        {
            String name = row["CustOfCustId"].InnerText;
        }
----------------------------------------------

Thanks & Regards

Sanwar

SQL

Create PROC [dbo].[SearchAllTablesData]
(
 @SearchStr nvarchar(100)
)
AS
BEGIN

CREATE TABLE #Results (ColumnName nvarchar(500), ColumnValue nvarchar(3630))
SET NOCOUNT ON
 DECLARE @TableName nvarchar(300), @ColumnName nvarchar(300), @SearchStr2 nvarchar(500)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + ltrim(rtrim(@SearchStr)) + '%','''')
 WHILE @TableName IS NOT NULL
 BEGIN
  SET @ColumnName = ''
  SET @TableName =
  (
   SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
   FROM  INFORMATION_SCHEMA.TABLES
   WHERE   TABLE_TYPE = 'BASE TABLE'
    AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
    AND OBJECTPROPERTY(
      OBJECT_ID(
       QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
        ), 'IsMSShipped'
             ) = 0
  )
 WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
  BEGIN
   SET @ColumnName =
   (
    SELECT MIN(QUOTENAME(COLUMN_NAME))
    FROM  INFORMATION_SCHEMA.COLUMNS
    WHERE   TABLE_SCHEMA = PARSENAME(@TableName, 2)
     AND TABLE_NAME = PARSENAME(@TableName, 1)
     AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
     AND QUOTENAME(COLUMN_NAME) > @ColumnName
   )
 IF @ColumnName IS NOT NULL
   BEGIN
    INSERT INTO #Results
    EXEC
    (
     'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
     FROM ' + @TableName + ' (NOLOCK) ' +
     ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
    )
   END
  END
 END

 SELECT ColumnName, ColumnValue FROM #Results
END

Thanks & Regards

Sanwar

protected void Page_Load(object sender, EventArgs e)
 {
     if ((!string.IsNullOrEmpty(Request["ImagePath"])) && (!string.IsNullOrEmpty(Request["Height"])) && (!string.IsNullOrEmpty(Request["Width"])))
     {
         string Path = Convert.ToString(Request["ImagePath"]);
         int ImageHeight = Convert.ToInt32(Request["Height"]);
         int ImageWidth = Convert.ToInt32(Request["Width"]);
         SaveBannerImage(Path, ImageHeight, ImageWidth);
     }
 }
 // http://Servername/ImageResize/ImageResize.aspx?Width=200&Height=250&ImagePath=http://Servername/ImageResize/ImagesResizeRUN/test.jpg
 private void SaveBannerImage(string Path, int ImageHeight, int ImageWidth)
 {
     string ImageName = Path.Remove(0, Path.LastIndexOf("/") + 1);
     string StringPath = Path.Substring(0, Path.LastIndexOf("/"));
     string TempPath = "ImagesResizeRUN//Temp//";// Real File Store Location
     string TempPathActual = "ImagesResizeRUN//"; // File Store when taking any action
     string filepath=HttpContext.Current.Server.MapPath(TempPath + ImageName);
     FileStream fs = new FileStream(@filepath, FileMode.Open, FileAccess.Read);
     string ImageNameNew = System.IO.Path.GetFileNameWithoutExtension(ImageName)+ System.IO.Path.GetExtension(ImageName);
     string destination = HttpContext.Current.Server.MapPath(TempPathActual) + ImageNameNew;
     System.Drawing.Image initImage = System.Drawing.Image.FromStream(fs, true);
     double templateRate = double.Parse(ImageWidth.ToString()) / ImageHeight;
     double initRate = double.Parse(initImage.Width.ToString()) / initImage.Height;
     System.Drawing.Image templateImage = new System.Drawing.Bitmap(ImageWidth, ImageHeight);
     System.Drawing.Graphics templateG = System.Drawing.Graphics.FromImage(templateImage);
     templateG.InterpolationMode = System.Drawing.Drawing2D.InterpolationMode.High;
     templateG.SmoothingMode = System.Drawing.Drawing2D.SmoothingMode.HighQuality;
     templateG.Clear(Color.White);
     templateG.DrawImage(initImage, new System.Drawing.Rectangle(0, 0, ImageWidth, ImageHeight), new System.Drawing.Rectangle(0, 0, initImage.Width, initImage.Height), System.Drawing.GraphicsUnit.Pixel);
     templateImage.Save(destination, System.Drawing.Imaging.ImageFormat.Jpeg);
     Response.Redirect(Path);
}

Thanks & Regards

Sanwar

XML

<asp:ImageButton validatingroup="NameOfGroup" ID="btnsubmitSECU" CausesValidation="true" ImageUrl="~/images/btnsaveholding.png" runat="server" OnClick="btnsubmit_Click"   OnClientClick="if(Page_IsValid) return ValidDate(this);" ></asp:ImageButton>

Thanks & Regards

Sanwar

XML

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <script src="http://ajax.aspnetcdn.com/ajax/jquery/jquery-1.8.0.js" type="text/javascript"></script>
    <script src="http://ajax.aspnetcdn.com/ajax/jquery.ui/1.8.22/jquery-ui.js"></script>
    <script type="text/javascript">
        $(window).load(function () {

    updateIndex = function (e, ui) {
        $('td.index', ui.item.parent()).each(function (i) {
            $(this).html(i + 1);
        });
        $('input.index', ui.item.parent()).each(function (i) {
            $(this).val(i + 1);
        });
    };

            $("#sort tbody").sortable({
                stop: updateIndex,
                cursor: 'crosshair'
            }).disableSelection();
        }); //]]>

    </script>
    <title></title>
</head>
<body>

        <table id="sort" class="drag_drop_grid">
            <thead>
                <tr>
                    <th>
                        No.
                    </th>
                    <th>
                        Title
                    </th>
                    <th>
                        Price
                    </th>

                    <th>
                        Ranking Text box
                    </th>
                </tr>
            </thead>
            <tr style="display: table-row;">
                <td class="index">
                    1
                </td>
                <td>
                    shirt
                </td>
                <td>
                    1000
                </td>
                <td>
                  <input type="text" value="1" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    2
                </td>
                <td>
                    Jeans
                </td>
                <td>
                    1500
                </td>
                <td>
                  <input type="text" value="2" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    3
                </td>
                <td>
                    Trousers
                </td>
                <td>
                   1800
                </td>
                <td>
                  <input type="text" value="3" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    4
                </td>
                <td>
                    Tie
                </td>
                <td>
                    200
                </td>
                <td>
                   <input type="text" value="4" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    5
                </td>
                <td>
                    Cap
                </td>
                <td>
                    800
                </td>
                <td>
                <input type="text" value="5" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    6
                </td>
                <td>
                    Hat
                </td>
                <td>
                   1000
                </td>
                <td>
                  <input type="text" value="6" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    7
                </td>
                <td>
                    Scarf
                </td>
                <td>
                   280
                </td>
                 <td>
                  <input type="text" value="7" class="index" />
                </td>
            </tr>
            <tr style="display: table-row;">
                <td class="index">
                    8
                </td>
                <td>
                    Belt
                </td>
                <td>
                   300
                </td>
                 <td>
                  <input type="text" value="8" class="index" />
                </td>
            </tr>
        </table>

</body>
</html>

Thanks & Regards

Sanwar

droapdownID.SelectedValue = droapdownID.Items.FindByText(item["dataThose select"].Text).Value;

and normal is

drpround.Items.FindByText("Database or other given text").Selected = true;

Thanks & Regards

Sanwar

modified 3-Jan-13 4:51am.

SQL

Declare @InvoiceId nvarchar(50)
Declare @Orderid bigint
select @InvoiceId = 'Ex2832'
DECLARE @DynamicColumn VARCHAR(MAX)
DECLARE @Rate VARCHAR(MAX)
DECLARE @CRate VARCHAR(MAX)
DECLARE @Karat VARCHAR(MAX)
DECLARE @Query VARCHAR(MAX)

--SELECT @DynamicColumn = COALESCE( @DynamicColumn + ',[' + cast(purity as varchar) + 'KWeight]', '[' + cast(purity as varchar)+ 'KWeight]' )FROM (select Distinct purity from InvoiceDetails Where InvoiceId =@InvoiceId) as A
SELECT @Rate = COALESCE( @Rate + ',[' + cast(purity as varchar) + 'KRate]', '[' + cast(purity as varchar)+ 'KRate]' )FROM (select Distinct purity from InvoiceDetails Where InvoiceId =@InvoiceId) as B
SELECT @CRate = COALESCE( @CRate + ',case when  ([' + cast(purity as varchar) + 'KRate]) IS null   then  (select max([' + cast(purity as varchar) + 'KRate]) from PivotData pivot (max ( GoldPrice) for  RValue in (['+cast(purity as varchar) + 'KRate]) ) as pv) else ['+ cast(purity as varchar) +'KRate]end  as ['+ cast(purity as varchar) +'KRate]',
'case when  ([' + cast(purity as varchar) + 'KRate]) IS null   then  (select max([' + cast(purity as varchar) + 'KRate]) from PivotData pivot (max ( GoldPrice) for  RValue in (['+cast(purity as varchar) + 'KRate]) ) as pv) else ['+ cast(purity as varchar) +'KRate]end  as ['+ cast(purity as varchar) +'KRate]' )FROM (select Distinct purity from InvoiceDetails Where InvoiceId =@InvoiceId) as C

set @Query = 'With PivotData as ( SELECT ShapeName,
InvoiceDetails.Purity,

cast(InvoiceDetails.Purity as varchar(10)) + ''KRate'' as RValue,

(select Rate from GoldPrice Where Purity=InvoiceDetails.Purity) as ''GoldPrice''
FROM Invoice
INNER JOIN   InvoiceDetails ON Invoice.InvoiceId  =InvoiceDetails.InvoiceId
Where Invoice.InvoiceId ='''+@InvoiceId+''' )

select ShapeName,Purity,'+@CRate+' from PivotData
pivot (MAX(GoldPrice) for RValue in ( '+@Rate+')) as pvt'
 print @Query
Execute(@Query)


Result is 

ShapeName	          Purity	14KRate	18KRate	22KRate
Gold Caps 14K	              14	29.35	37.73	46.12
Faceted Gold Beads 18K	      18	29.35	37.73	46.12
Three Piece Set	              18	29.35	37.73	46.12
EarRings	              22	29.35	37.73	46.12

Thanks & Regards

Sanwar

The COALESCE function in SQL returns the first non-NULL expression among its arguments.

It is the same as the following CASE statement:

SELECT CASE ("column_name")
  WHEN "exp1 is not NULL" THEN "expr1"
  WHEN "exp2 is not NULL" THEN "expr2"
  ...
  [ELSE "NULL"]
  END
FROM "table_name"


 
For examples, say we have the following table,

Table Contact_Info

Name 	   Business_Phone  	Cell_Phone 	Home_Phone
Jeff 	    531-2531 	        622-7813 	565-9901
Laura 	     NULL 	        772-5588 	312-4088
Peter 	     NULL 	          NULL 	        594-7477

and we want to find out the best way to contact each person according to the following rules:

1. If a person has a business phone, use the business phone number.

2. If a person does not have a business phone and has a cell phone, use the cell phone number.

3. If a person does not have a business phone, does not have a cell phone, and has a home phone, use the home phone number.

We can use the COALESCE function to achieve our goal:

SELECT Name, COALESCE(Business_Phone, Cell_Phone, Home_Phone) Contact_Phone
FROM Contact_Info;

Result:

Name 	Contact_Phone
Jeff 	531-2531
Laura 	772-5588
Peter 	594-7477


Read more: http://www.1keydata.com/sql/sql-coalesce.html#ixzz2AOG0fR5Z

Thanks & Regards

Sanwar

XML

Check data  exist or not in data base, using Generic handler call by j-query and check all validation work proper

Script function (Check data exist or not):--
<script language="javascript" type="text/javascript">
        $(function () {
            $("#<% =TxtUserName1.ClientID %>").change(function () {
                //debugger;
                var val = $("#<% =lblid.ClientID %>").text();


                if (($("#<% =ddlUserRole.ClientID %>").val()) != "0") {
                    $.post("GenericHandler_ClassExisting.ashx", { UserName: $("#<% =TxtUserName1.ClientID %>").val(), RoleId: $("#<% =ddlUserRole.ClientID %>").val(), UserId: $("#<% =lblid.ClientID %>").text() }, function (output) {

                        if (output == 0) {
                            $("#dv_Ajax_Response").html("User name already exist!");
                            $("#dv_Ajax_Response").css("color", "red");
                        }
                        else {
                            $("#dv_Ajax_Response").html("User name still not available");
                            $("#dv_Ajax_Response").css('color', 'green');
                        }
                    });
                }
            });
        });

    </script>
    <script language="javascript" type="text/javascript">
        $(function () {
            $("#<% =ddlUserRole.ClientID %>").change(function () {
                //debugger;
                var val = $("#<% =lblid.ClientID %>").text();

                if (($("#<% =TxtUserName1.ClientID %>").val()) != "") {
                    //if (($("#<% =TxtUserName1.ClientID %>").val()) != "User Name")
                    $.post("GenericHandler_ClassExisting.ashx", { UserName: $("#<% =TxtUserName1.ClientID %>").val(), RoleId: $("#<% =ddlUserRole.ClientID %>").val(), UserId: $("#<% =lblid.ClientID %>").text() }, function (output) {

                        if (output == 0) {
                            $("#dv_Ajax_Response").html("User name already exist!");
                            $("#dv_Ajax_Response").css("color", "red");
                        }
                        else {
                            $("#dv_Ajax_Response").html("User name still not available");
                            $("#dv_Ajax_Response").css('color', 'green');
                        }
                    });
                }
            });
        });

    </script>
    Script function (Check data Validate or not):--
    <script language="javascript" type="text/javascript">

        $(document).ready(function () {
            $("#<%=BtnSaveAndUpdate.ClientID %>").click(function () {

                return $("#commentForm").validation();

            });
        });

        $.fn.validation = function () {
            var blnIsError = true;
            $("#<% =TxtUserName1.ClientID %>", this).each(function () {
                if ($("#dv_Ajax_Response").html() == "User name already exist!") {
                    // $("#<% =TxtUserName1.ClientID %>").css("background", "url('../_Images/input_bg1.png') no-repeat scroll 0 0 ");  // For image change regarding
                    blnIsError = false;
                }
            });

            $("#<% =TxtUserName1.ClientID %>").bind("keyup", function () {
                if ($(this).val() != '') {
                    // $("#<% =TxtUserName1.ClientID %>").css("background", "url('../_Images/input_bg.png') no-repeat scroll 0 0 ");  // For image change regarding
                }
            });
            return blnIsError;
        };

    </script>

    Page code:--

      <asp:Label   ID="lblid" runat="server" style="display:none"> </asp:Label>

 <asp:DropDownList ID="ddlUserRole" runat="server" Width="155px"  >  </asp:DropDownList>

 <asp:TextBox ID="TxtUserName1" runat="server" Width="150px"></asp:TextBox>
 <asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ErrorMessage="Please enter user name."
  ForeColor="Red" ControlToValidate="TxtUserName1" ValidationGroup="a"></asp:RequiredFieldValidator>
 <div id="dv_Ajax_Response" style="font-weight: bold" />

 <asp:ImageButton ID="BtnSaveAndUpdate"  runat="server" ValidationGroup="a" OnClick="BtnSaveAndUpdate_Click" />

Thanks & Regards

Sanwar

if i m send an url -->
http://localhost:3211/foldername/fileName/Query1/Query2/Query3

There is foldername and fileName compulsory but other like Query1 , Query2 , Query3 not compulsory or url like -->
http://localhost:3211/foldername/fileName/Query1 or
http://localhost:3211/foldername/fileName/Query1/Query2 or
http://localhost:3211/foldername/fileName/Query1/Query2/Query3

It will be matched properly and work perfect.

Current Url Get it -->

C#

string Current_URL = Request.ServerVariables["PATH_INFO"];

Use this regex match-->

Regex rewrite_regex = new Regex("(^/(?<foldername>[^/]+)/(?<fileName>[^/]+))*(/(?<Query1>[^/]+))? *(/(?<Query2>[^/]+))? *(/(?<Query3>[^/]+))?", RegexOptions.IgnoreCase);

And get result -->

C#

Match match_rewrite = rewrite_regex.Match(Current_URL);
string CurrentFolderName = string.Empty;
            string returnURL = string.Empty;
            string Query1 = string.Empty;
            string Query2 = string.Empty;
            string Query3 = string.Empty;
             CurrentFolderName = match_rewrite.Groups["foldername"].Value.ToString();
            FileActualName = match_rewrite.Groups["fileName"].Value.ToString();
            Query1 = match_rewrite.Groups["Query1"].Value.ToString();
            Query2 = match_rewrite.Groups["Query2"].Value.ToString();
            Query3 = match_rewrite.Groups["Query3"].Value.ToString();

Thanks & Regards

Sanwar

<link href="<%# ResolveUrl("~") %>Styles/Site.css" rel="stylesheet" type="text/css" />

<script src="<%# ResolveUrl("~") %>Scripts/ClsCookies.js" type="text/javascript" ></script>

In page link in master page

<a href='<%= Page.ResolveUrl("~")%>Masters/Merchant'>Merchant</a>

Thanks & Regards

Sanwar

App key like

<appsettings>
<add key="PagePath" value="http://localhost:3007">

</appSettings>

And import in page Using this

<%$ AppSettings:PagePath %>

and

System.Configuration.ConfigurationManager.AppSettings["PagePath"].ToString()

and another one is create a function in global.asax file like-

C#

public static string GetImageDirectory()
       {
         string  _rootpath = System.Configuration.ConfigurationManager.AppSettings["PagePath"].ToString();
           return _rootpath;
       }

and then access it .

<%= String.Format("{0}/_Images/btn-edit.jpg", YourApplicationRootNameSpace.Global.GetImageDirectory())%>

Thanks & Regards

Sanwar

<script type="text/javascript">
    $(document).ready(function () {
          var url = location.href.substring(location.href.lastIndexOf("/") + 1);
          var page = url.substring(0, url.lastIndexOf("."));
          var query = url.substring(url.indexOf("?") + 1);
          query = query.substring(query.indexOf("=") + 1);

       if ((query == "" || query == undefined) && (page == "" || page == undefined)) {
              $('.ligreenbg').addClass(" ");
              return;
          }
          if (page == "PageO") {

              $('.ord').addClass('ligreenbg');
          }
          else if ((page == "pg1") || (page == "pg2") || (page == "pg3") || (page == "pg4") || (page == "pg5") || (page == "pg6") || (page == "pg7") || (page == "pg8") || (page == "pg9") || (page == "pg10") || (page == "pg11")) {

              $('.mast').addClass('ligreenbg');
           }
          else if ((page == "pgg1") || (page == "pgg2") || (page == "pgg3")) {

              $('.pro').addClass('ligreenbg');
           }
          else if (page == "user") {

              $('.use').addClass('ligreenbg');
            }

      });
  </script>

XML

<ul id="css3menu1">
                           <li class="Submenu ord" ><a href='<%= Page.ResolveUrl("~")%>AspxPages/PageO.aspx'
                               target="_parent">PageO</a></li>
                           <li class="topmenu mast"><a href="#"><span>Menu1</span></a>
                               <ul id="Submenu" class="topmenu1"> 
                                   <li><a href='<%= Page.ResolveUrl("~")%>AspxPages/pg1.aspx'>pg1</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg2.aspx'>pg2</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg3.aspx'>pg3</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg4.aspx'>pg4</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg5.aspx'>pg5</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg6.aspx'>pg6</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg7.aspx'>pg7</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg8.aspx'>pg8</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg9.aspx'>pg9</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg10.aspx'>pg10</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>Masters/pg11.aspx'>pg11</a></li>
                               </ul>
                           </li>
                           <li class="topmenu pro"><a href="#"><span>Menu2</span></a>
                               <ul id="Submenu">
                                   <li><a href='<%= Page.ResolveUrl("~")%>AspxPages/pgg1.aspx'>pgg1;/a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>AspxPages/pgg2.aspx'>pgg2</a></li>
                                   <li><a href='<%= Page.ResolveUrl("~")%>AspxPages/pgg3.aspx'>pgg3</a></li>
                               </ul>
                           </li>
                           <li class="toplast use"><a href="#"><span>Menu3</span></a>
                               <ul id="Submenu">
                                   <li><a href='<%= Page.ResolveUrl("~")%>Account/user.aspx'>user</a></li>
                               </ul>
                           </li>
                       </ul>

Thanks & Regards

Sanwar

<a href='<%= Page.ResolveUrl("~")%>AspxPages/ItemUploadForm.aspx'>Tally Upload</a>

Thanks & Regards

Sanwar

sanwar_mal_jat - Professional Profile

Summary

Reputation

Privileges