Monday, August 18, 2014

SQL Server Replication


Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote

Sunday, August 17, 2014

SQL Server Interview Question

SQL Profiler download

Launching Performance Monitor

Launching Performance Monitor

You can launch Performance Monitor a few different ways.

Start > Control Panel > Administrative Tools > Performance Monitor or you can launch PerfMon.exe.  Once this is running you will get an interface like the following:


http://www.mssqltips.com/sqlservertutorial/283/performance-monitor/

Saturday, June 21, 2014

Call webpage c# from Excel VBA with parameter

Private Sub btnUserList_Click()
Unload Me
ActiveWorkbook.FollowHyperlink Address:="http://servername/mfuser/login.aspx?z=" & fncWindows_UserID, NewWindow:=True
End Sub

Wednesday, June 18, 2014

Write to active workbook

ActiveWorkbook.Sheets("Calc Proj Flags").Range("B5").Value = "A x B"

Wednesday, June 11, 2014

Invalid Period Number and/or Fisc JDE

Invalid Period Number and/or Fisc
CAUSE . . . .  The period number, fiscal year, and/or century in the
               transaction record is not valid for the transaction date
               and  company.
RESOLUTION. .  Correct the period number, fiscal year, and century to be
               valid for the transaction date and company.

Monday, June 2, 2014

Export Gridview to pdf C#

Step1 : Download itextsharp.dll and paste at bin folder





<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="CS.aspx.cs" Inherits="_Default" %>





    GridView Export To PDF

   

   

            AutoGenerateColumns = "true" Font-Names = "Arial"
        Font-Size = "11pt" AlternatingRowStyle-BackColor = "#C2D69B"
        HeaderStyle-BackColor = "green" AllowPaging ="false"  
        OnPageIndexChanging = "OnPaging" >
     
   

   
     
     
   







using System;
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.Data;
using System.Data.SqlClient;
using System.IO;
using iTextSharp.text;
using iTextSharp.text.pdf;
using iTextSharp.text.html;
using iTextSharp.text.html.simpleparser;
using System.Text;


public partial class _Default : System.Web.UI.Page
{
    int grdvCount = 0;

    protected void Page_Load(object sender, EventArgs e)
    {
        //String strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

        //SqlConnection strConnString = new SqlConnection(ConnectionString);
        SqlConnection con = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter sda = new SqlDataAdapter();
        DataSet ds = new DataSet();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "SP_Plan_ErrorReport_FFSData";
        cmd.Connection = con;
        sda.SelectCommand = cmd;
        try
        {
            con.Open();
            sda.Fill(ds);
            GridView1.EmptyDataText = "No Records Found";
            GridView1.DataSource = ds;
            GridView1.DataBind();
            grdvCount=GridView1.Rows[0].Cells.Count;
            //Response.Write(GridView1.Rows[0].Cells.Count);
          // Response.End();

         
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            con.Dispose();
        }
    }
    protected void btnExportPDF_Click(object sender, EventArgs e)
    {
        //GridView1.AllowPaging = Convert.ToBoolean(rbPaging.SelectedItem.Value);
        GridView1.DataBind();

         //Create a table
        iTextSharp.text.Table table = new iTextSharp.text.Table(grdvCount);
        table.Cellpadding = 5;

        //Set the column widths
        Response.Write(grdvCount);
       // Response.End();


        int[] widths = new int[grdvCount];
        for (int x = 0; x < grdvCount; x++)
        {
            widths[x] = 2500;
            //Response.Write(widths[x]);
            //Response.End();

            string cellText = Server.HtmlDecode(GridView1.HeaderRow.Cells[x].Text);
            iTextSharp.text.Cell cell = new iTextSharp.text.Cell(cellText);
            //cell.BackgroundColor = new Color (System.Drawing.ColorTranslator.FromHtml("#008000"));
            //light blue


            cell.BackgroundColor = new Color(System.Drawing.ColorTranslator.FromHtml("#536895"));
           

            table.AddCell(cell);
        }
        table.SetWidths(widths);

        //Transfer rows from GridView to table
        for (int i = 0; i < GridView1.Rows.Count; i++)
        {
            if (GridView1.Rows[i].RowType == DataControlRowType.DataRow)
            {
                for (int j = 0; j < grdvCount; j++)
                {
                    string cellText = Server.HtmlDecode(GridView1.Rows[i].Cells[j].Text);
                    iTextSharp.text.Cell cell = new iTextSharp.text.Cell(cellText);

                    //Set Color of Alternating row
                    if (i % 2 != 0)
                    {
                        cell.BackgroundColor = new Color(System.Drawing.ColorTranslator.FromHtml("LightBlue"));
                    }
                    table.AddCell(cell);
                }
            }
        }

        //Create the PDF Document
        Document pdfDoc = new Document(PageSize.A0, 10f, 10f, 10f, 0f);
        PdfWriter.GetInstance(pdfDoc, Response.OutputStream);
        pdfDoc.Open();
        pdfDoc.Add(table);
        pdfDoc.Close();
        Response.ContentType = "application/pdf";
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.pdf");
        Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.Write(pdfDoc);
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
        /* Verifies that the control is rendered */
    }
    protected void OnPaging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataBind();
    }


    private string ConnectionString
    {
        get
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;
            return connectionString;
        }
    }
}

Gridview Format in Excel

protected void Button4_Click(object sender, EventArgs e)
    {
        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        using (StringWriter sw = new StringWriter())
        {
            HtmlTextWriter hw = new HtmlTextWriter(sw);

            //To Export all pages
            GridView1.AllowPaging = false;
            this.DataBind();

            GridView1.HeaderRow.BackColor = System.Drawing.Color.White;
            foreach (TableCell cell in GridView1.HeaderRow.Cells)
            {
                cell.BackColor = GridView1.HeaderStyle.BackColor;
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                row.BackColor = System.Drawing.Color.White;
                foreach (TableCell cell in row.Cells)
                {
                    if (row.RowIndex % 2 == 0)
                    {
                        cell.BackColor = GridView1.AlternatingRowStyle.BackColor;
                    }
                    else
                    {
                        cell.BackColor = GridView1.RowStyle.BackColor;
                    }
                    cell.CssClass = "textmode";
                }
            }

            GridView1.RenderControl(hw);

            //style to format numbers to string
            string style = @"";
            Response.Write(style);
            Response.Output.Write(sw.ToString());
            Response.Flush();
            Response.End();
        }
    }

   
}

Saturday, May 31, 2014

COALESCE sql server

alter proc SP_DTV_SecBal
@division varchar(2), @ownerContact varchar(30)
AS
if @division='' begin set @division=null   end
if @ownerContact='' begin set @ownerContact=null   end



delete from Tbl_DTVSecBal
insert into Tbl_DTVSecBal
SELECT  eDAST, CAST(eDAN81 AS INT)eDAN81, LEFT(S.ABALPH, 25),
    ltrim(rtrim(T.WWGNNM)) + ' ' + ltrim(rtrim(T.WWSRNM)),
 '%' ,  CONVERT(MONEY, EDUNCS / 10000),
tg1.GregDay, tg2.GregDay,
    CAST(AICMC2 AS INT), LEFT(P.ABALPH, 25),
    ltrim(rtrim(U.WWGNNM)) + ' ' +  CASE when ltrim(rtrim(U.WWSRNM)) LIKE '%''%' THEN Replace(ltrim(rtrim(U.WWSRNM)), '''', '`') ELSE ltrim(rtrim(U.WWSRNM)) END
,p.abac19 Division,EDGLC,S.ABAT1,EDDCTO
FROM JDE_PRODUCTION.PRODDTA.F5542845 INNER JOIN JDE_PRODUCTION.PRODDTA.F0111 T
ON  EDAN81 = T.WWAN8  INNER JOIN vAB03012
ON  EDAN81 = AIAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0111 U
ON  AICMC2 = U.WWAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0101 S
ON  EDAN81 = S.ABAN8 INNER JOIN JDE_PRODUCTION.PRODDTA.F0101 P
ON  AICMC2 = P.ABAN8 INNER JOIN ZDEV.dbo.tblJulian_Gregorian tg1
ON  EDEFTB = tg1.JulianDay INNER JOIN ZDEV.dbo.tblJulian_Gregorian tg2
ON  EDEFTE=tg2.JulianDay --LEFT JOIN JDE_PRODUCTION.PRODDTA.F4094
WHERE EDAST IN ('DC1','DC2','DC3','DC4','DCNC','DCVP' )
AND (ltrim(rtrim(T.WWGNNM)) + ltrim(rtrim(T.WWSRNM)) != '')
AND (ltrim(rtrim(U.WWGNNM)) + ltrim(rtrim(U.WWSRNM)) != '')

--select * from Tbl_DTVSecBal
select distinct
AllOwnerID = z.AllOwnerID ,z.AlphaName,z.OwnerContact,z.division,
z.Adj,
[Ship To] = z.[Ship To] ,
z.SoldToCompany,
z.SalesPerson,
isnull(z.RateMode, '') RM,
cast(z.CommRate as decimal (15,2)) CommRate ,            
CONVERT(VARCHAR(10),z.Start,101)StartDate ,
CONVERT(VARCHAR(10),z.[End],101)EndDate ,Division,GLC,AT1,OrTY,
cast(v.SecBal as decimal (15,2)) SecBal
FROM Tbl_DTVSecBal z INNER JOIN  dbo.vSBL_SEC v ON z.[Ship To] = v.GBSBL
where z.AllOwnerID<>0
and z.division = COALESCE(@division, division) and  z.OwnerContact= = COALESCE(@OwnerContact,OwnerContact)


GO

Tuesday, May 20, 2014

Tic Tac Toe Game in VB Complete Code....


put button names like Button1 Button2........
and two labels Label1 & Label2



Public Class TicTacToeForm
    Dim random As String = 1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

    End Sub
 

    Public Sub PC()
        If random = 1 And Button1.Enabled = True Then
            Button1.Text = "O"
            Button1.Enabled = False
        End If
        If random = 2 And Button2.Enabled = True Then
            Button2.Text = "O"
            Button2.Enabled = False
        End If
        If random = 3 And Button3.Enabled = True Then
            Button3.Text = "O"
            Button3.Enabled = False
        End If
        If random = 4 And Button4.Enabled = True Then
            Button4.Text = "O"
            Button4.Enabled = False
        End If
        If random = 5 And Button5.Enabled = True Then
            Button5.Text = "O"
            Button5.Enabled = False
        End If
        If random = 6 And Button6.Enabled = True Then
            Button6.Text = "O"
            Button6.Enabled = False
        End If
        If random = 7 And Button7.Enabled = True Then
            Button7.Text = "O"
            Button7.Enabled = False
        End If
        If random = 8 And Button8.Enabled = True Then
            Button8.Text = "O"
            Button8.Enabled = False
        End If
        If random = 9 And Button9.Enabled = True Then
            Button9.Text = "O"
            Button9.Enabled = False
        End If
    End Sub
    Public Sub Options()
        'If user hits 1
        If Label1.Text = 1 Then
            If Button2.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" Or Button6.Text = "X" Then
                hit()
            End If

        End If
        'If user hits 2
        If Label1.Text = 2 Then
            If Button1.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button4.Text = "X" Or Button7.Text = "X" Or Button9.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 3
        If Label1.Text = 3 Then
            If Button1.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 4
        If Label1.Text = 4 Then
            If Button1.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" Or Button3.Text = "X" Or Button8.Text = "X" Or Button9.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 5
        If Label1.Text = 5 Then
            If Button1.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            End If
        End If

        'If user hits 6
        If Label1.Text = 6 Then
            If Button3.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" Or Button2.Text = "X" Or Button7.Text = "X" Or Button8.Text = "X" Then
                hit()
            End If
        End If

        'If user hits 7
        If Label1.Text = 7 Then
            If Button1.Text = "X" And Button4.Enabled = True Then
                With Button4
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button4.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button2.Text = "X" Then
                hit()
            End If
        End If

        'If user hits 8
        If Label1.Text = 8 Then
            If Button2.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button2.Enabled = True Then
                With Button2
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button9.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button9.Enabled = True Then
                With Button9
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button6.Text = "X" Or Button3.Text = "X" Or Button1.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
        'If user hits 9
        If Label1.Text = 9 Then
            If Button6.Text = "X" And Button3.Enabled = True Then
                With Button3
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button3.Text = "X" And Button6.Enabled = True Then
                With Button6
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button5.Text = "X" And Button1.Enabled = True Then
                With Button1
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button1.Text = "X" And Button5.Enabled = True Then
                With Button5
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button8.Text = "X" And Button7.Enabled = True Then
                With Button7
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button7.Text = "X" And Button8.Enabled = True Then
                With Button8
                    .Text = "O"
                    .Enabled = False
                End With
            ElseIf Button2.Text = "X" Or Button4.Text = "X" Then
                hit()
            End If
        End If
    End Sub

    Public Sub CheckIfComputerWins()
        If Button1.Text = "O" And Button2.Text = "O" And Button3.Text = "O" _
        Or Button4.Text = "O" And Button5.Text = "O" And Button6.Text = "O" _
        Or Button7.Text = "O" And Button8.Text = "O" And Button9.Text = "O" _
        Or Button1.Text = "O" And Button4.Text = "O" And Button7.Text = "O" _
        Or Button2.Text = "O" And Button5.Text = "O" And Button8.Text = "O" _
        Or Button3.Text = "O" And Button6.Text = "O" And Button9.Text = "O" _
        Or Button1.Text = "O" And Button5.Text = "O" And Button9.Text = "O" _
        Or Button7.Text = "O" And Button5.Text = "O" And Button3.Text = "O" Then
            MsgBox("Computer Wins")
            Button1.Text = ""
            Button1.Enabled = True
            Button2.Text = ""
            Button2.Enabled = True
            Button3.Text = ""
            Button3.Enabled = True
            Button4.Text = ""
            Button4.Enabled = True
            Button5.Text = ""
            Button5.Enabled = True
            Button6.Text = ""
            Button6.Enabled = True
            Button7.Text = ""
            Button7.Enabled = True
            Button8.Text = ""
            Button8.Enabled = True
            Button9.Text = ""
            Button9.Enabled = True
        ElseIf Button1.Text = "X" And Button2.Text = "X" And Button3.Text = "X" _
        Or Button4.Text = "X" And Button5.Text = "X" And Button6.Text = "X" _
        Or Button7.Text = "X" And Button8.Text = "X" And Button9.Text = "X" _
        Or Button1.Text = "X" And Button4.Text = "X" And Button7.Text = "X" _
        Or Button2.Text = "X" And Button5.Text = "X" And Button8.Text = "X" _
        Or Button3.Text = "X" And Button6.Text = "X" And Button9.Text = "X" _
        Or Button1.Text = "X" And Button5.Text = "X" And Button9.Text = "X" _
        Or Button7.Text = "X" And Button5.Text = "X" And Button3.Text = "X" Then
            MsgBox("Congratulations, You Win")
            Button1.Text = ""
            Button1.Enabled = True
            Button2.Text = ""
            Button2.Enabled = True
            Button3.Text = ""
            Button3.Enabled = True
            Button4.Text = ""
            Button4.Enabled = True
            Button5.Text = ""
            Button5.Enabled = True
            Button6.Text = ""
            Button6.Enabled = True
            Button7.Text = ""
            Button7.Enabled = True
            Button8.Text = ""
            Button8.Enabled = True
            Button9.Text = ""
            Button9.Enabled = True
        End If
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Public Function hit()
        For Each ctl As Control In Me.Controls
            If Label1.Text < 9 Then
                If (ctl.Name.StartsWith("Button" & Label1.Text + 1)) Then
                    Dim btn As Button = DirectCast(ctl, Button)
                    If btn.Enabled = True Then
                        btn.Text = "O"
                        btn.Enabled = False
                    Else
                        Label1.Text = Label1.Text + 1
                    End If
                End If
            Else
                If (ctl.Name.StartsWith("Button1")) Then
                    Dim btn As Button = DirectCast(ctl, Button)
                    If btn.Enabled = True Then
                        btn.Text = "O"
                        btn.Enabled = False
                    Else
                        Label1.Text = Label1.Text + 1
                    End If
                End If
            End If
        Next
        Return Label1.Text

    End Function

    Private Sub Button10_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button10.Click

        Timer1.Stop()

        Button1.Text = "O"

        Label1.Text = 1

        Button1.Enabled = False

        Button10.Enabled = False

    End Sub


    Private Sub Button11_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button11.Click
        Button1.Text = ""
        Button1.Enabled = True
        Button2.Text = ""
        Button2.Enabled = True
        Button3.Text = ""
        Button3.Enabled = True
        Button4.Text = ""
        Button4.Enabled = True
        Button5.Text = ""
        Button5.Enabled = True
        Button6.Text = ""
        Button6.Enabled = True
        Button7.Text = ""
        Button7.Enabled = True
        Button8.Text = ""
        Button8.Enabled = True
        Button9.Text = ""
        Button9.Enabled = True
        Button10.Enabled = True
        Timer1.Start()
    End Sub



    Private Sub Timer1_Tick_1(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        random += 1

        If random = 10 Then

            random = 1

        End If
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button4_Click_1(sender As System.Object, e As System.EventArgs) Handles Button4.Click

        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button5_Click(sender As System.Object, e As System.EventArgs) Handles Button5.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button6_Click(sender As System.Object, e As System.EventArgs) Handles Button6.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub Button9_Click(sender As System.Object, e As System.EventArgs) Handles Button9.Click
        sender.Text = "X"
        sender.Enabled = False
        Timer1.Stop()
        Label1.Text = sender.name
        Label1.Text = Label1.Text.Replace("Button", "")
        If random = Label1.Text Then
            random = random + 1
        End If
        If random > 0 Then
            PC()
            random = 0
        ElseIf random = 0 Then
            Options()
        End If
        Label2.Text = random
        CheckIfComputerWins()
    End Sub

    Private Sub butncode()


    End Sub

End Class

Inspired by: visual-basic-tutorials.com/2162010132827.php


Sunday, May 11, 2014

VB auto Scrollbar move with timer

Public Class Form1
    Shared _timer As Timer
    Shared _list As List(Of String) = New List(Of String)

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
       
        '   HScrollBar1.Value = 50 + Label2.Text




    End Sub

    Private Sub Timer1_Tick(sender As System.Object, e As System.EventArgs) Handles Timer1.Tick
        Label1.Text = TimeOfDay()
        Label2.Text = Now.Second

        HScrollBar1.Value = Label2.Text
       
    End Sub


    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        Timer1.Stop()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Timer1.Start()
    End Sub

   
End Class

Thursday, May 1, 2014

Rolling 12 Months Table header YYYY-MM format

declare @mnth varchar(7),@date as datetime,@vardate as varchar(10), @dateper_01 varchar(7),@period_01 varchar(7),@period_02 varchar(7),@period_03 varchar(7),@period_04 varchar(7)
declare @period_05 varchar(7),@period_06 varchar(7),@period_07 varchar(7),@period_08 varchar(7),@period_09 varchar(7),@period_10 varchar(7),@period_11 varchar(7),@period_12 varchar(7)
declare @Query varchar(4000)

set @mnth='2014-04'
set @vardate=@mnth+'-01'
--select  @vardate
set @date=cast(@vardate as datetime)
set  @period_01=right(convert(varchar,DATEADD(MM,-0,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-0,@date),101),2)
set  @period_02=right(convert(varchar,DATEADD(MM,-1,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-1,@date),101),2)
set  @period_03=right(convert(varchar,DATEADD(MM,-2,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-2,@date),101),2)
set  @period_04=right(convert(varchar,DATEADD(MM,-3,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-3,@date),101),2)
set  @period_05=right(convert(varchar,DATEADD(MM,-4,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-4,@date),101),2)
set  @period_06=right(convert(varchar,DATEADD(MM,-5,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-5,@date),101),2)
set  @period_07=right(convert(varchar,DATEADD(MM,-6,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-6,@date),101),2)
set  @period_08=right(convert(varchar,DATEADD(MM,-7,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-7,@date),101),2)
set  @period_09=right(convert(varchar,DATEADD(MM,-8,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-8,@date),101),2)
set  @period_10=right(convert(varchar,DATEADD(MM,-9,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-9,@date),101),2)
set  @period_11=right(convert(varchar,DATEADD(MM,-10,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-10,@date),101),2)
set  @period_12=right(convert(varchar,DATEADD(MM,-11,@date),101),4)+'-'+left(convert(varchar,DATEADD(MM,-11,@date),101),2)

drop table ##temp

SET @Query =
'CREATE TABLE [dbo].[##temp](
                [bu] [nvarchar](12) NOT NULL,
                [objsub] [nvarchar](20) NOT NULL,
                [obj_desc] [nchar](130) NULL,'
                +'['+@Period_12+'] [float] NULL,'
                +'['+@Period_11+'] [float] NULL,'
                +'['+@Period_10+'] [float] NULL,'
                +'['+@Period_09+'] [float] NULL,'
                +'['+@Period_08+'] [float] NULL,'
                +'['+@Period_07+'] [float] NULL,'
                +'['+@Period_06+'] [float] NULL,'
                +'['+@Period_05+'] [float] NULL,'
                +'['+@Period_04+'] [float] NULL,'
                +'['+@Period_03+'] [float] NULL,'
                +'['+@Period_02+'] [float] NULL,'
                +'['+@Period_01+'] [float] NULL,
) '

EXECUTE (@query)

-- drop table ##temp
select * from ##temp

Friday, April 25, 2014

PIVOT Row into column Month


SELECT * FROM(SELECT FYear,[State],StateCode,LOBName,LOBCode,ProductName,ProductCode,RegionName,RegionCode,[FMonth],Membership
  FROM #temp )t PIVOT (SUM(Membership)
  FOR [FMonth] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12] )) AS pvt