Thursday, January 16, 2014

Merge Two Rows in SQL

Let's create a table:

Create Table School (
StudentId int,
Course varchar(50),
Semester varchar(10),
Primary Key(StudentId, Semester));

Insert into School Values(1234, 'Math', 'Fall');
Insert into School Values (1234, 'Science', 'Spring');






Both rows are for a student with ID:1234. If you would like to combine these rows into one, you could do by different ways: 1.) do max for each column with Group by clause, 2.) do the inner join with same table, 3.) rank by semester and then pivot based on the rank. If  you have one row data and another row null then you can do max and group by with common Id.

I am going to show you the third one here: Rank by semester and then pivot on the rank:

Select StudentId,
Max(Case when rk = 1 then Course end) as Course1,
Max(Case when rk = 1 then Semester end) as Semester1,
Max(Case when rk = 2 then Course end) as Course2,
Max(Case when rk = 2 then Semester end) as Semester2
   From
  (Select StudentId, Course, Semester,
      Row_Number() Over
          (Partition by StudentId Order by Semester) as rk
From School) as A
Group by StudentId;

Output:





Happy Programming!!

Monday, November 25, 2013

Dynamic jQuery calls from code behind VB.NET


Simple Function call: You can call the jQuery from code behind with the help of ClientScript.RegisterStartupScript():
  
ClientScript.RegisterStartupScript(this.GetType(), 
           "blah", "YourJsFunction();", true);
Assign via the control: If you have multiple controls on a page and need to make an individual jQuery call based on condition (not for every time; let say after the button click)
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" 
                                        runat="Server">
       
    <asp:Literal runat="server" ID="ltlJsFadeCode"></asp:Literal>
     
    Result: 
     <asp:Label runat="server" ID="lblResult"></asp:Label>
     
    Id:     
     <asp:TextBox runat="server" ID="tbId"></asp:TextBox>
     
    Save Button: 
     <asp:Button runat="server" ID="btnSave" Text="Save" />
</asp:Content>

Code behind:
       
        Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As 
                               System.EventArgs) Handles btnSave.Click
          ltlJsFadeCode.Text = JsFadeCode(lblResult.ClientID)
          lblResult.Text = "<span style=""font-weight:bold;color: #0C0;"">
                                          Saved</span>"
        End Sub 

        Protected Function JsFadeCode(ByVal clientId As String) As String
        Dim sb As New StringBuilder
        sb.Append(vbNewLine)
        sb.Append("<scr")
        sb.Append(vbNewLine)
        sb.Append("$(document).ready(function() {")
        sb.Append(vbNewLine)
        sb.Append("$('#")
        sb.Append(clientId)
        sb.Append("').animate({ opacity: 1.0 }, 3000).fadeOut('slow');")
        sb.Append(vbNewLine)
        sb.Append("});")
        sb.Append(vbNewLine)
        sb.Append("</scr"
        sb.Append("ipt>")
        sb.Append(vbNewLine)
        sb.Append(vbNewLine)
        Return sb.ToString
    End Function
Hence, the literal control ltlJsFadeCode behaves like jQuery ready function:
<script type=""text/javascript">
   $(document).ready(function(){
     $('#lblResult').animate({ opacity: 1.0 }, 3000).
                                fadeOut('slow');
       };
<script>
Happy Coding!!

Tuesday, April 23, 2013

Draw line/image using mouse and save/display on/from database (MSSQL/VB.NET)

HTML5 Canvas element is a drawing API which can be used for rendering graphs, game graphics, to draw line/image etc. Canvas is a rectangle area which can be added to our HTML5 page. To get the image data URL of the canvas, we can use the toDatURL() method of the canvas object which converts the canvas drawing into a 64 bit encoded PNG/JPEG/BITMAP URL.

PaintingApplicationTest.aspx

<%@ Page Language="VB" AutoEventWireup="false"  
CodeFile="PaintingApplicationTest.aspx.vb"
    Inherits="PaintingApplicationTest" %>

<!DOCTYPE html />
<html lang="en">
<head>
    <title>Paint Testing App</title>
      

<script type="text/javascript" 
           src="http://ajax.aspnetcdn.com/ajax/jQuery/             jquery-1.6.1.min.js"></script>
    <script type="text/javascript">


  // setup our test canvas
  // and a simple drawing function
   window.onload = function () {

   var bMouseIsDown = false;

   var oCanvas = document.getElementById("thecanvas");
        var oCtx = oCanvas.getContext("2d");

   var iWidth = oCanvas.width;
   var iHeight = oCanvas.height;

   oCanvas.onmousedown = function (e) {
     bMouseIsDown = true;

     iLastX = e.clientX - oCanvas.offsetLeft + 
              (window.pageXOffset || 
               document.body.scrollLeft || 
               document.documentElement.scrollLeft);
     iLastY = e.clientY - oCanvas.offsetTop + 
               (window.pageYOffset || 
                document.body.scrollTop || 
                document.documentElement.scrollTop);
            } 


      oCanvas.onmouseup = function () {
                bMouseIsDown = false;
                iLastX = -1;
                iLastY = -1;
            }


      oCanvas.onmousemove = function (e) {
          if (bMouseIsDown) {
          var iX = e.clientX - oCanvas.offsetLeft +                    (window.pageXOffset || 
                 document.body.scrollLeft || 
                 document.documentElement.scrollLeft);
          var iY = e.clientY - oCanvas.offsetTop + 
                 (window.pageYOffset || 
                  document.body.scrollTop || 
                  document.documentElement.scrollTop);
                    
                    oCtx.moveTo(iLastX, iLastY);
                    oCtx.lineTo(iX, iY);
                    oCtx.stroke();
                    iLastX = iX;
                    iLastY = iY;
                }
            }
        }
</script>
<script type="text/javascript">


// Send the canvas image to the server.
$(function () {
$("#btnSave").click(function () {
var image = document.getElementById("thecanvas").toDataURL("image/png");
 image = image.replace('data:image/png;base64,', '');
  $.ajax({
     type: 'POST',
      url: 'PaintingApplicationTest.aspx/UploadImage',
      data: '{ "imageData" : "' + image + '" }',
      contentType: 'application/json; charset=utf-8',
      dataType: 'json',
        success: function (msg) {
           alert('Image saved successfully !');
                  }
                });
            });
        });
    </script>
</head>
<body>
  <form id="form1" runat="server">
  <canvas width="400" height="200" 
   style="border: 1px solid black;" id="thecanvas"></canvas>  

<input type="button" id="btnSave" 
        name="btnSave" value="Save" />

<asp:Button runat="server" ID="btnShow" 
    Text="Show Saved Image" OnClick="btnShow_Click" />

<asp:Image ID="Image1" style="width:200px" Runat="server" />
 </form>
</body>
</html>

PaintingApplicationTest.aspx.cs

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports System.Web.Script.Services
Imports System.Web.Services
Imports System.Data.SqlClient
Imports System.Data

<ScriptService()> _
Partial Public Class PaintingApplicationTest
    Inherits System.Web.UI.Page
    <WebMethod()> _
 
Public Shared Sub UploadImage(imageData As String)

Dim constr As String = System.Web.Configuration.
            WebConfigurationManager.
            ConnectionStrings("SqlServerReadOnly").
            ConnectionString
Dim con As New SqlConnection(constr)
        Dim cmd As New SqlCommand()
        cmd.Connection = con
//make sure you use SP or Parameterized query
Dim sql As String = "INSERT INTO HTML5CanvasImage
                    (imageData) VALUES('"
                    imageData + "')"
        cmd.CommandText = sql
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub


Protected Sub btnShow_Click(sender As Object, 
                 e As EventArgs)

Dim constr As String = System.Web.Configuration.
              WebConfigurationManager.
              ConnectionStrings("SqlServerReadOnly").
              ConnectionString

Dim sql As String = "SELECT ImageData FROM 
                     HTML5CanvasImage"
Dim cmd As SqlCommand = New SqlCommand(sql, 
                        New SqlConnection(constr))
        cmd.Connection.Open()

Dim imgString As String = cmd.ExecuteScalar().
                          ToString()
Image1.Attributes("src") = "data:image/png;base64," 
                          + imgString
cmd.Connection.Close()

 End Sub
End Class

Output:

Draw and save the image:
 

Display saved image from SQL database:
More information about HTML5:
Click here
Click here
Click here


Happy Coding!!

Wednesday, February 6, 2013

Disable Dates in Calendar Extender & Calendar Control

CalenderExtenders don't have OnDayRender Event, It is specially for Calendar Control. For CalenderExtender we can use some JS function:

CalenderExtenders:
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<!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></title>

   <script type="text/javascript">

     function checkDate(sender, args) {

       // 6 days        
       var ms = new Date().getTime() + 6 * 86400000; 

        var weekFar = new Date(ms);

          if (sender._selectedDate < weekFar) {           

              //getTime() gives milliseconds  
              //and 86400000 is the number of 
              //milliseconds in a day. 
             alert("You can't select a day 
                     earlier than a week!");

 
           // set the date back to a week from today
            var ms1 = new Date().getTime() 
                         + 7 * 86400000; 

            var weekFar1 = new Date(ms1);
            sender._selectedDate = weekFar1; 
            sender._textbox.set_Value(sender.
                 _selectedDate.format(sender._format))

          }

      }

    </script>
</head>

<body>

    <form id="form1" runat="server">

     <asp:ToolkitScriptManager 
        ID="ToolkitScriptManager1" runat="server">
                </asp:ToolkitScriptManager> 

  

         <asp:TextBox ID="txtDateNeeded
              runat="server" Width="5em">
              </asp:TextBox>   

          <asp:CalendarExtender   
             ID="CalendarExtender1
             OnClientDateSelectionChanged="checkDate"
             TargetControlID="txtDateNeeded"  
             runat="server"/>

</form></body></html>

Calendar Control:
<asp:Calendar ID="Calendar1" runat="server"
      Caption="" CaptionAlign="Top" CellPadding="2"
      CellSpacing="2" ShowTitle="true"
      OnDayRender="Calendar1_DayRender
      Font-Names="verdana"
      Font-Size="Small">
      <DayHeaderStyle BackColor="CadetBlue" 
      HorizontalAlign="center
      VerticalAlign="Middle" />
      <WeekendDayStyle BackColor="gray">
      </WeekendDayStyle>
      <SelectedDayStyle BackColor="BurlyWood"
      ForeColor="DarkBlue" Font-Names="Calibri"
      Font-Bold="true" />
</asp:Calendar>

'Code behind
'calendar disable certain dates
Protected Sub Calendar1_DayRender(sender As Object, e As DayRenderEventArgs)

 If e.Day.Date <= DateTime.Now.AddDays(6) Then
       'disable the date a week from today
       e.Cell.Enabled = False
       e.Day.IsSelectable = False
       e.Cell.ToolTip = "This date is not available"
   End If
End Sub