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
Monday, December 28, 2009
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
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
Next in Default.vb.aspx
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 :)
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\
Then, go to your xampp directory and find httpd.conf. Open it through notepad and find this line
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
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
Remove the comment by removing symbol ";"
Restart apache. This is tutorial not include code to bind ldap in PHP (login through AD)
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
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
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
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
You should get a result in the label
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!
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!
Thursday, June 18, 2009
Convert string type to unique identifier [VB / C#]
We always want to insert back unique identifier that we have query. Usually we will put it inside session or string. So this is how to convert a string to a unique identifier.
And of course the string must in correct format which is
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Here is a sample:
And of course the string must in correct format which is
xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx
Here is a sample:
VB
--
Dim ApplicationId As Guid = New Guid(Session("SampleString").ToString())
C#
--
Guid ApplicationId = new Guid(Session["SampleString"].ToString());
Sunday, June 7, 2009
Show Success or Fail message in .Net (with the most stupid way but works!)
Okay, I been looking around the web but cannot find the most suitable code for my code. FYI, my page is pure using postback, no ajax.
My objective is to display Success or Fail message certain operation is perform
and this is what i am going to achieve
Yup, it is a simple task. But i still cannot find a proper to do it in AJAX .net page (with updatepanel). In only works in native postback page
First, create a stylesheet.css and put this classes
Then, create a C# page name called ShowSuccessFail.aspx and paste this code .aspx code
Then in ShowSuccessFail.cs.aspx copy paste this code
Try to run it, it should work.
My objective is to display Success or Fail message certain operation is perform
and this is what i am going to achieve
Yup, it is a simple task. But i still cannot find a proper to do it in AJAX .net page (with updatepanel). In only works in native postback page
First, create a stylesheet.css and put this classes
.LoadSuccess
{
text-align: center;
vertical-align: middle;
position: absolute;
z-index: 3000;
left: 400px;
top: 300px;
width: 214px;
height: 55px;
background-color: #ffffff;
border-right: #006cff 1px dashed;
border-top: #006cff 1px dashed;
font-weight: bold;
border-left: #006cff 1px dashed;
border-bottom: #006cff 1px dashed;
font-family: Calibri, arial;
font-size: 20px;
color: Green;
filter: alpha (opacity=100);
}
.LoadFail
{
text-align: center;
vertical-align: middle;
position: absolute;
z-index: 3000;
left: 400px;
top: 300px;
width: 214px;
height: 55px;
background-color: Yellow;
border-right: Red 1px dashed;
border-top: Red 1px dashed;
font-weight: bold;
border-left: Red 1px dashed;
border-bottom: Red 1px dashed;
font-family: Calibri, arial;
font-size: 20px;
color: Red;
filter: alpha (opacity=100);
}
Then, create a C# page name called ShowSuccessFail.aspx and paste this code .aspx code
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="ShowSuccessFail.aspx.cs" Inherits="ShowSuccessFail" %>
<!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>
<link rel="stylesheet" type="text/css" href="stylesheet.css" />
<script type='text/javascript'>
function FadeOutSuccess()
{
document.getElementById('divSuccess').style.visibility='hidden';
}
function FadeOutFail()
{
document.getElementById('divFail').style.visibility='hidden';
}
function ShowSuccess()
{
document.getElementById('divSuccess').style.visibility='visible';
setTimeout('FadeOutSuccess()',10000);
}
function ShowFail()
{
document.getElementById('divFail').style.visibility='visible';
setTimeout('FadeOutFail()',10000);
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="divSuccess" class="LoadSuccess" style="visibility: hidden;" runat="server">
Success</div>
<div id="divFail" class="LoadFail" style="visibility: hidden;" runat="server">
Fail!</div>
<asp:Label ID="lblScriptCall" runat="server"></asp:Label><br />
<br />
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Show Success" />
<asp:Button ID="Button2" runat="server" Text="Show Fail" OnClick="Button2_Click" />
<asp:Button ID="Button3" runat="server" Text="Show Nothing" />
</div>
</form>
</body>
</html>
Then in ShowSuccessFail.cs.aspx copy paste this code
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
public partial class ShowSuccessFail : System.Web.UI.Page
{
String callSuccess = "<script type='text/javascript'>ShowSuccess();</script>";
String callFail = "<script type='text/javascript'>ShowFail();</script>";
protected void Page_Load(object sender, EventArgs e)
{
lblScriptCall.Text = "";
}
protected void Button1_Click(object sender, EventArgs e)
{
lblScriptCall.Text = callSuccess;
}
protected void Button2_Click(object sender, EventArgs e)
{
lblScriptCall.Text = callFail;
}
}
Try to run it, it should work.
Saturday, June 6, 2009
Email configuration for .net
Before you can send email through asp.net application, you need to do some configuration in web.config
Here is the things that you need to include in web.config
You need to change <smtp from="foo@foo.com"> to any value you like. It will be used as a default "FROM:" when you send email.
While for the host, you need to ask your administrator what is the smtp address to send emails
On the next post, I will show you how to send emails through .net application.
Here is the things that you need to include in web.config
<system.web>
</system.web>
<system.net>
<mailSettings>
<smtp from="foo@foo.com">
<network host="127.0.0.1" port="25"/>
</smtp>
</mailSettings>
</system.net>
You need to change <smtp from="foo@foo.com"> to any value you like. It will be used as a default "FROM:" when you send email.
While for the host, you need to ask your administrator what is the smtp address to send emails
On the next post, I will show you how to send emails through .net application.
Monday, June 1, 2009
Ajax Toolkit for framework 2.0
I have this dificulties to find Ajax Toolkit for .Net Framework 2.0 since they have release updates only for .Net 3.5
At first, i think they had totally removed the link for download.
But later on i realize that they have include the link but may be i am not aware of it.
So this is the link.
Ajax Toolkit for Framework 2.0
Look for AjaxControlToolkit.zip
*Note: You need Ajax Extension 1.0 for .net 2.0 framework. This is because it need Scriptmanager or UpdatePanel in order to run certain toolkit. You can download it here
At first, i think they had totally removed the link for download.
But later on i realize that they have include the link but may be i am not aware of it.
So this is the link.
Ajax Toolkit for Framework 2.0
Look for AjaxControlToolkit.zip
*Note: You need Ajax Extension 1.0 for .net 2.0 framework. This is because it need Scriptmanager or UpdatePanel in order to run certain toolkit. You can download it here
Saturday, April 18, 2009
Add numbering in gridview [VB / C#]
I also have the same problem when i first using the gridview. Gridview is a very powerful control (we do not have to code table like in PHP). So, after doing a research on several web resources, I've found this method. With this method you can add numbering in your gridview. Insert this code in your page inside the gridview codes (.aspx)
VB
--
C#
--
VB
--
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%#Container.DataItemIndex + 1 & "."%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
C#
--
<Columns>
<asp:TemplateField>
<ItemTemplate>
<%#Container.DataItemIndex + 1 + "."%>
</ItemTemplate>
</asp:TemplateField>
</Columns>
Get Date and Time separately from SQL
Situation: You have a column in your table type DateTime. But you just want to view the date or time only. This is how it can be done.
SQL query
will return result in format dd/mm/yy
will return result in format hh:mm:ss
Here are list of format which you can refer:
SQL query
Select convert(varchar, [your column date], 3)
from [your table name]
will return result in format dd/mm/yy
Select select convert(varchar, [your column date], 8 )
from [your table name]
will return result in format hh:mm:ss
Here are list of format which you can refer:
| Date Formats | ||
|---|---|---|
| Format No. | SQL Query | Output |
| 1 | select convert(varchar, [your column date], 1) from [your table name] | 12/30/08 |
| 2 | select convert(varchar, [your column date], 2) from [your table name] | 08.12.30 |
| 3 | select convert(varchar, [your column date], 3) from [your table name] | 30/12/08 |
| 4 | select convert(varchar, [your column date], 4) from [your table name] | 30.12.08 |
| 5 | select convert(varchar, [your column date], 5) from [your table name] | 30-12-08 |
| 6 | select convert(varchar, [your column date], 6) from [your table name] | 30 Dec 08 |
| 7 | select convert(varchar, [your column date], 7) from [your table name] | Dec 30, 08 |
| 10 | select convert(varchar, [your column date], 10) from [your table name] | 12-30-08 |
| 11 | select convert(varchar, [your column date], 11) from [your table name] | 08/12/30 |
| 101 | select convert(varchar, [your column date], 101) from [your table name] | 12/30/2008 |
| 102 | select convert(varchar, [your column date], 102) from [your table name] | 2008.12.30 |
| 103 | select convert(varchar, [your column date], 103) from [your table name] | 30/12/2008 |
| 104 | select convert(varchar, [your column date], 104) from [your table name] | 30.12.2008 |
| 105 | select convert(varchar, [your column date], 105) from [your table name] | 30-12-2008 |
| 106 | select convert(varchar, [your column date], 106) from [your table name] | 30 Dec 2008 |
| 107 | select convert(varchar, [your column date], 107) from [your table name] | Dec 30, 2008 |
| 110 | select convert(varchar, [your column date], 110) from [your table name] | 12-30-2008 |
| 111 | select convert(varchar, [your column date], 111) from [your table name] | 2008/12/30 |
| Time Formats | ||
|---|---|---|
| Format No. | SQL Query | Output |
| 8 or 108 | select convert(varchar, [your column date], 8 ) from [your table name] | 00:40:50 |
| 9 or 109 | select convert(varchar, [your column date], 9) from [your table name] | Dec 30 2006 12:40:50:840AM |
| 14 or 114 | select convert(varchar, [your column date], 14) from [your table name] | 00:40:50:840 |
How to use dictionary in [VB / C#]
This is how to declare, add / insert and read / view dictionary in C# and VB .net. But you have include library for reference
I prefer using dictionary rather than array. Dictionary is suitable to be used when we know its data type
VB
'In header
Imports System.Collections.Generic
'In your codes section
Dim items As Dictionary(Of String, String) = New Dictionary(Of String, String)()
items.Add("The Key", "The Value")
For Each item As KeyValuePair(Of String, String) In items
Label1.Text = Label1.Text & "Key: " & item.Key.ToString() & ", Display: " & item.Value.ToString() & "
"
Next
C#
// In header
Using System.Collections.Generic;
// In your codes section
Dictionary<string, string> items = new Dictionary<string, string>();
items.Add("The Key", "The Value");
foreach (KeyValuePair<string, string> item in items)
{
Label1.Text = Label1.Text + "Key: " + item.Key.ToString() + ", Display: " + item.Value.ToString() + "<br>";
}
I prefer using dictionary rather than array. Dictionary is suitable to be used when we know its data type
Subscribe to:
Posts (Atom)
