Technical Corner: 2014

Saturday, December 13, 2014

http://therealratchets.com/Tineke.php

Sunday, November 9, 2014

Classic asp database connection

<!DOCTYPE html>

<head>

</head>

<body>

'Dim cnn

'Set cnn = Server.CreateObject("ADODB.Connection")

'cnn.open "PROVIDER=SQLOLEDB;DATA SOURCE=SHIVALISAANVI;UID=ituser;PWD=123456;DATABASE=AdventureWorks2008R2 "

'Dim conn

'Set conn = Server.CreateObject("ADODB.Connection")

'conn.Open "Provider=SQLOLEDB; Data Source = (SHIVALISAANVI); Initial Catalog = AdventureWorks2008R2; User Id = ituser; Password=123456"

'If conn.errors.count = 0 Then

' Response.Write "Connected OK"

'End If

'declare the variables

Dim Connection

Dim Recordset

Dim SQL

'declare the SQL statement that will query the database

SQL = "SELECT TOP 10 * FROM [AdventureWorks2008R2].[Person].[Person]"

'create an instance of the ADO connection and recordset objects

Set Connection = Server.CreateObject("ADODB.Connection")

Set Recordset = Server.CreateObject("ADODB.Recordset")

'open the connection to the database

Connection.Open "PROVIDER=SQLOLEDB;DATA SOURCE=SHIVALISAANVI;UID=ituser;PWD=123456;DATABASE=AdventureWorks2008R2 "

'Open the recordset object executing the SQL statement and return records

Recordset.Open SQL,Connection

'first of all determine whether there are any records

If Recordset.EOF Then

Response.Write("No records returned.")

Else

'if there are records then loop through the fields

Do While NOT Recordset.Eof

Response.write Recordset("FirstName") &" "

Response.write Recordset("MiddleName")&" "

Response.write Recordset("LastName")&" "

Response.write "<br>"

Recordset.MoveNext

Loop

End If

'close the connection and recordset objects to free up resources

Recordset.Close

Set Recordset=nothing

Connection.Close

Set Connection=nothing

</body>

</html>

Thursday, November 6, 2014

Fwd: Process Oracle Visual Studios Connection

1. Install Oracle Client 32 Bit

2. in VS 2010 -> View -> Server Explorer

3. Right Click on Data Connection -> Add Connection ->

in server name -> //OracleServerName:PortNo/InstenceName

//dc01xxxxxxx03.xxxxxx.ccc:80903/hixxjd

user id and password

4. Test connection

gridview -> use director-> Create connection string

and SQL statement

SELECT * FROM DevUSER(Schema Name).[Table Name] WHERE ROWNUM <= 100

Wednesday, October 1, 2014

Javascript enable/disable 32

about:config

In the address bar, type "about:config" (with no quotes), and press Enter.
Click "I'll be careful, I promise"
In the search bar, search for "javascript.enabled" (with no quotes).
Right click the result named "javascript.enabled" and click "Toggle". JavaScript is now disabled.

https://support.mozilla.org/en-US/questions/967344

Sunday, September 7, 2014

gtalk

Here is a download directly from Google: http://dl.google.com/googletalk/googletalk-setup.exe

--
Thanks
Shweta Sharma
http://www.svdeals.com

Tuesday, September 2, 2014

Find Column name in SQL serverTable

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name = 'billclasscode'
ORDER BY schema_name, table_name;

Monday, September 1, 2014

Update join 2 table SQL _SERVER

select lipqoh,TRQT,liitm,itm,lilocn,locn
--UPDATE proddta.f41021 SET lipqoh =trqt
from proddta.f41021
INNER JOIN zdev.dbo.temp_pqoh on liITM=itm and rtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and lilocn=locn
where rtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and liITM=itm and lilocn=locn and
rtrim(ltrim(limcu))=rtrim(ltrim(mcu)) and lipqoh<>trqt

Sunday, August 31, 2014

If and Like with Stored Proc

USE [AdventureWorks2008R2]

/****** Object: StoredProcedure [dbo].[SP_countbyfirstName] Script Date: 08/31/2014 08:49:39 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER proc [dbo].[SP_countbyfirstName]

@firstname varchar(50)

Declare @cntno Int

select @cntno= COUNT(*)from Person.Person where FirstName like @firstname +'%'

If (@cntno=95)

begin

select * from Person.Person where FirstName like @firstname +'%'

end

If (@cntno<>95)

begin

select * from Person.Person where FirstName like @firstname +'%'

end

Substring,Left,right

SELECT LastName,firstname, SUBSTRING(LastName, 4, 4) as Initial

FROM Person.Person

WHERE LastName like 'Barl%'

ORDER BY LastName;

select * from Person.Person

--SUBSTRING ( expression ,start , length )

Select AddressLine1,SpatialLocation,SUBSTRING(cast(SpatialLocation as varchar(50)), 4, 4) as Address

from Person.Address

select PostalCode from Person.Address

SELECT firstName,lastname,RIGHT(FirstName, 4) AS 'First Name'

FROM Person.Person

SELECT firstName,lastname,left(FirstName, 2) AS 'First Name'

FROM Person.Person

SELECT firstName,lastname,SUBSTRING(FirstName,1,2) AS 'First Name'

FROM Person.Person

Saturday, August 30, 2014

IIS Wiki

Internet Information Services (IIS, formerly Internet Information Server) is an extensible web server created byMicrosoft for use with Windows NT family.^[2] IIS supports HTTP, HTTPS, FTP, FTPS, SMTP and NNTP. It has been an integral part of the Windows NT family since Windows NT 4.0, though it may be absent from some editions (e.g. Windows XP Home edition). IIS is not turned on by default when Windows is installed. The IIS Manager is accessed through the Microsoft Management Console or Administrative Tools in the Control Panel.

http://en.wikipedia.org/wiki/Internet_Information_Services

Most Commonly Used Functions in SQL Server 2005/2008

http://www.codeproject.com/Articles/38998/Most-Commonly-Used-Functions-in-SQL-Server

Friday, August 29, 2014

JavaScript Tutorial

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

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

<h1>My First JavaScript</h1>

<p>Click Date to display current day, date, and time.</p>

<button type="button" onclick="ImageClick()">ReDirect</button>

<asp:TextBox ID="txtPagingGoto" Width="215px" runat="server"></asp:TextBox>

<div>

<asp:TextBox ID="txtFirstName" runat="server" OnClick ="ValidateInput()" ></asp:TextBox>

</div>

</form>

function myFunction() {

document.getElementById("demo").innerHTML = Date();

}

function ImageClick() {

window.location.href("http://www.google.com/");

}

function ValidateInput() {

var validationMessage = "";

var isValid = true;

// keep as many as you want

var inputControls = ['<%= txtFirstName.ClientID %>', '<%= txtFirstName.ClientID %>'];

for (var i = 0; i < inputControls.length; i++) {

if (document.getElementById(inputControls[i]).value = '') {

// update the message

isValid = false;

}

if (!isValid)

alert(validationMessage);

return false;

}

</script>

</body>

</html>

Monday, August 18, 2014

Linked Servers SQL Server

Configure a linked server to enable the SQL Server Database Engine to execute commands against OLE DB data sources outside of the instance of SQL Server. Typically linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle. Many types OLE DB data sources can be configured as linked servers, including Microsoft Access and Excel. Linked servers offer the following advantages:

http://msdn.microsoft.com/en-us/library/ms188279.aspx

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

http://msdn.microsoft.com/en-us/library/ms151198.aspx

Sunday, August 17, 2014

SQL Server Interview Question

http://www.mssqltips.com/sql-server-tip-category/46/interview-questions-dba/

--
Rajeev Kumar
949-378-6094

SQL Profiler download

https://sites.google.com/site/sqlprofiler/

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

Friday, May 16, 2014

Optional parameter SQL_Server Where

COALESCE

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

Thursday, March 27, 2014

12 Period get values

declare @Period int, @Period_01 int,@Period_02 int,@Period_03 int,@Period_04 int,
@Period_05 int,@Period_06 int,@Period_07 int,@Period_08 int,@Period_09 int,@Period_10 int,@Period_11 int,@Period_12 int

declare @fy int
set @fy=14
set @Period=3

set @Period_12=@Period
if @Period-1=0 set @Period=12+@Period set @Period_11=@Period-1
if(@Period-2=0) set @Period=12+@Period set @Period_10=@Period-2
if(@Period-3=0) set @Period=12+@Period set @Period_09=@Period-3
if(@Period-4=0) set @Period=12+@Period set @Period_08=@Period-4
if(@Period-5=0) set @Period=12+@Period set @Period_07=@Period-5
if(@Period-6=0) set @Period=12+@Period set @Period_06=@Period-6
if(@Period-7=0) set @Period=12+@Period set @Period_05=@Period-7
if(@Period-8=0) set @Period=12+@Period set @Period_04=@Period-8
if(@Period-9=0) set @Period=12+@Period set @Period_03=@Period-9
if(@Period-10=0) set @Period=12+@Period set @Period_02=@Period-10
if(@Period-11=0) set @Period=12+@Period set @Period_01=@Period-11

print @Period_12
print @Period_11
print @Period_10
print @Period_09
print @Period_08
print @Period_07
print @Period_06
print @Period_05
print @Period_04
print @Period_03
print @Period_02
print @Period_01