for my reference and may be yours

Monday, December 28, 2009

SQL return list of month

Here is another query which i have modified from my previous post.

This query will list out list of months

SELECT Month(GETDATE()) as MonthNum
UNION
SELECT Month(GETDATE()) - 1 as MonthNum
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
ORDER BY MonthNum DESC

and here is some query to convert month number to month name

Convert Month Number to name SQLServerCurry

And here is a full query:

SELECT Month(GETDATE()) as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE()),-1)) as MonthName
UNION
SELECT Month(GETDATE())-1 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-1,-1)) as MonthName
UNION
SELECT Month(GETDATE()) - 2 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-2,-2)) as MonthName
UNION
SELECT Month(GETDATE()) - 3 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-3,-3)) as MonthName
UNION
SELECT Month(GETDATE()) - 4 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-4,-4)) as MonthName
UNION
SELECT Month(GETDATE()) - 5 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-5,-5)) as MonthName
UNION
SELECT Month(GETDATE()) - 6 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-6,-6)) as MonthName
UNION
SELECT Month(GETDATE()) - 7 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-7,-7)) as MonthName
UNION
SELECT Month(GETDATE()) - 8 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-8,-8)) as MonthName
UNION
SELECT Month(GETDATE()) - 9 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-9,-9)) as MonthName
UNION
SELECT Month(GETDATE()) - 10 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-10,-10)) as MonthName
UNION
SELECT Month(GETDATE()) - 11 as MonthNum
,datename(mm,DateAdd(mm,Month(GETDATE())-11,-11)) as MonthName
ORDER BY MonthNum DESC

List of Year for MSSQL

Hi, i am current doing some project and i need to get list of year dynamically.

Meaning, i don't want to recode or update list of year in a drop down list

So, i google and found this article

SQL return list of year - StackOverFlow.com

and i used in store procedure and call it within sqldatasource ;)

create a new view and paste this code

SELECT YEAR(GETDATE()) as YearNum
UNION
SELECT YEAR(GETDATE()) - 1 as YearNum
UNION
SELECT YEAR(GETDATE()) - 2 as YearNum
UNION
SELECT YEAR(GETDATE()) - 3 as YearNum
UNION
SELECT YEAR(GETDATE()) - 4 as YearNum
UNION
SELECT YEAR(GETDATE()) - 5 as YearNum
UNION
SELECT YEAR(GETDATE()) - 6 as YearNum
UNION
SELECT YEAR(GETDATE()) - 7 as YearNum
UNION
SELECT YEAR(GETDATE()) - 8 as YearNum
UNION
SELECT YEAR(GETDATE()) - 9 as YearNum
UNION
SELECT YEAR(GETDATE()) - 10 as YearNum
ORDER BY YearNum DESC

Tuesday, September 15, 2009

Simple Javascript in .Net

There is a bit difference between writing javascript in PHP vs .Net and in .Net you have to register the javascript that you are going to use.

Compare to .Net, in PHP you may declare your javascript code at header page and then used it while you display your page.

Here is how to write a simple Javascript in .Net.

In this sample (in VB.Net), you will create a page with a button inside it. When you press this button it will display a confirmation text

Now, create a new page, for this example, i just create Default.aspx

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

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

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:Button ID="Button1" runat="server" Text="Show Confirmation" />
</form>
</body>
</html>

Next in Default.vb.aspx

Imports System.Web.UI.ClientScriptManager

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

Dim scriptText As String

scriptText = "return confirm('Do you want to submit the page?')"

ClientScript.RegisterOnSubmitStatement(Me.GetType(), "ConfirmSubmit", scriptText)
End Sub
End Class

Please concentrate on "ClientScript", here we register the script on submit event. On the next sample, i will show you how to register a block of javascript codes and we can used it later on :)

Sunday, June 28, 2009

Active Directory Login Setup for XAMPP

Hmm.. using .net, there no much trouble in implementing simple AD login for a project.

But for PHP, there are some files that need to be configure.

First, check this file in your c:\windows\system32\

-libeay32.dll
-ssleay32.dll

Then, go to your xampp directory and find httpd.conf. Open it through notepad and find this line

-LoadModule authnz_ldap_module modules/mod_authnz_ldap.so
-LoadModule ldap_module modules/mod_ldap.so

Make sure you uncomment it. To uncomment it, remove symbol '#'

But wait, there are still work to be done.

In your xampp directory, go to \apache\bin\
Find php.ini, and find line with

extension_dir

Uncomment this by removing symbol ";" and make sure the direcory path for your extension is correct.
For mine, "C:\Projects\xampp\php\ext\"

Go to that directory, and check this file php_ldap.dll is exist

Then find this line

extension=php_ldap.dll

Remove the comment by removing symbol ";"

Restart apache. This is tutorial not include code to bind ldap in PHP (login through AD)

Simple select statement using mysqli [PHP5]

Hi, i used to use PHP4 and it is about 2 year i'm not touch any development in PHP.

But for last week, i just got a project from my boss and it is using PHP. Although i just need to supervised the project but i need to know how to it is going to be done.

So, i will put this code as a quick reference for me

$dbConn = new mysqli('localhost', 'root', '', 'samplestaff');

$sql="select staffno, staffname from staffdetails";

$query01=$dbConn->prepare($sql);
$query01->execute();
$query01->bind_result($staffno, $staffname);

while($query01->fetch()){
echo $staffno.":".$staffname."
";
}

?>

Saturday, June 27, 2009

SQL Parameters - Input, Output, ReturnValue + From store procedure [C#]

Today, i will show you how to write a stored procedure to accept an input, return an output and also return a return value. I used adventureworks database

Here the sql to create a stored procedure

-- ================================================
-- KLIKBLIG.BLOGSPOT.COM
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE klikblig_getProductName
-- Add the parameters for the stored procedure here
@ProductNumber nvarchar(25),
@Name nvarchar(50) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

set @Name = ''

begin try
select @Name = p.name
from production.product p
where p.productnumber like @ProductNumber

if(@Name = '')
return 0
else
return 1
end try

begin catch
return -1
end catch

END
GO

Then, on default.aspx; i create a button, textbox and also a label (all id are default). On the default.cs.aspx paste the following code

using System;
using System.Data;
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.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;

conn.Open();
cmd.Connection = conn;
cmd.CommandText = "klikblig_getProductName";
cmd.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter();

//@ProductNumber - INPUT
param.ParameterName = "@ProductNumber";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 25;
param.Direction = ParameterDirection.Input;

cmd.Parameters.Add(param);
cmd.Parameters["@ProductNumber"].Value = TextBox1.Text.Trim();
param = null;

//@Name - OUTPUT
param = new SqlParameter();
param.ParameterName = "@Name";
param.SqlDbType = SqlDbType.NVarChar;
param.Size = 50;
param.Direction = ParameterDirection.Output;

cmd.Parameters.Add(param);
param = null;

//@ReturnValue - RETURNVALUE
param = new SqlParameter();
param.ParameterName = "@ReturnValue";
param.SqlDbType = SqlDbType.Int;
param.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(param);
param = null;

reader = cmd.ExecuteReader();

reader.Read();

//GET THE RESULT
String name = "";
int returnValue = 0;

name = (String)cmd.Parameters["@Name"].Value;
returnValue = (int)cmd.Parameters["@ReturnValue"].Value;

if (returnValue == 0)
{
Label1.Text = "No Results Found";
}
else if (returnValue == 1)
{
Label1.Text = name;
}
else if (returnValue < 0)
{
Label1.Text = "Error";
}

}
}

You should get a result in the label

Sunday, June 21, 2009

Find reference for project

We always need to add external reference for our project. Usually we require this in order to enhance our project

But sometimes, we tend to forget what we have added into our project. This is how we can check our reference.

In the top menu, select Website -> Start Option

A pop up will appear and choose Reference

Happy Programming!

for my reference and may be yours

Monday, December 28, 2009

SQL return list of month

List of Year for MSSQL

Tuesday, September 15, 2009

Simple Javascript in .Net

Sunday, June 28, 2009

Active Directory Login Setup for XAMPP

Simple select statement using mysqli [PHP5]

Saturday, June 27, 2009

SQL Parameters - Input, Output, ReturnValue + From store procedure [C#]

Sunday, June 21, 2009

Find reference for project

Blog Archive

Category

My Facebook Badge

Some Searching

My Favorite Sites

My Favorite Tools

GFX Free Source

Followers