Enable User Instances in SQL Server

in VS when using wizard to create database in SQL server Express edition sometimes it will show

Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

solution :
Open the SQL Server Management Studio Express. This is the downloadable program in the same site where you downloaded the SQL Server 2005 express used to manage SQL Server 2005 Express.
In the query editor type this text: exec sp_configure 'user instances enabled', 1.
Then type: Reconfigure.
Then restart the SQL Server database.
<pre name="code" class="Sql">

Optimizing SQL Server Clustered Indexes

When deciding on whether to create a clustered or non-clustered index, it is often helpful to know what the estimated size of the clustered index will be. Sometimes, the size of a clustered index on a particular column or columns may be very large, leading to database size bloat and increased disk I/O. 

Clustered index values often repeat many times in a table's non-clustered storage structures, and a large clustered index value can unnecessarily increase the physical size of a non-clustered index. This increases disk I/O and reduces performance when non-clustered indexes are accessed.

Because of this, ideally a clustered index should be based on a single column (not multiple columns) that is as narrow as possible. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Servers overall performance.

When you create a clustered index, try to create it as a UNIQUE clustered index, not a non-unique clustered index. The reason for this is that while SQL Server will allow you to create a non-unique clustered index, under the surface, SQL Server will make it unique for you by adding a 4-byte "uniqueifer" to the index key to guarantee uniqueness. This only serves to increase the size of the key, which increases disk I/O, which reduces performance. If you specify that your clustered index is UNIQUE when it is created, you will prevent this unnecessary overhead.

Happy query....

A little missunderstood in GROUP BY ln SQL SERVER

for example i tried to view how much news posted grouping by week in year 2009

     SELECT 
        DATEPART(WK,DatePosted) AS WeekNumber, 
        COUNT(NewsID) AS NewsPosts
     FROM News
     WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
     ORDER BY WeekNumber

you will get the warning message

error , Line 5
Column 'News.DatePosted' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

what's wrong? yup you need to add the group by
so we are going to add the group by

    SELECT
        DATEPART(WK,DatePosted) AS WeekNumber,
        COUNT(NewsID) AS NewsPosts
    FROM News
    WHERE DATEPART(YYYY, dbo.News.DatePosted) = 2009
    GROUP BY WeekNumber
    ORDER BY WeekNumber

still error? try to use the column instead, it's because sql server group by only regonise the column in group by not alias (like in My SQL)

JQuery Free E book

Better Interaction Design and Web Development with Simple JavaScript Techniques

• An introduction to jQuery that requires minimal programming experience
  
• Detailed solutions to specific client-side problems
  
• For web designers to create interactive elements for their designs
  
• For developers to create the best user interface for their web applications
  
• Packed with great examples, code, and clear explanations
  
• Revised and updated version of the first book to help you learn jQuery
  

Dowload (pdf)

Angka Acak di VB.NET

Random class udah ada di  .NET Framework class library
di constructornya, ada dua override method. ada yang minta input ada yang gak..

Random class punya tiga public method yaitu : Next, NextBytes, and NextDouble.

Next method mengembalikan angka acak biasa ,
NextBytes mengembalikan array yang berisi angka acak,
NextDouble mengembalikan angka acak untuk nilai desimal

contoh mengembalikan angka acak biasa:

Dim num As Integer = random.Next()

contoh mengembalikan angka acak < 1000.

Dim num As Integer = random.Next(1000)

angka acak diantara:

Private Function RandomNumber(min As Integer, max As Integer) As Integer
   Dim random As New Random()
   Return random.Next(min, max)
End Function 

buat string acak :

Private Function RandomString(size As Integer, lowerCase As Boolean) As String
   Dim builder As New StringBuilder()
   Dim random As New Random()
   Dim ch As Char
   Dim i As Integer
   For i = 0 To size - 1
      ch = Convert.ToChar(Convert.ToInt32((26 * random.NextDouble() + 65)))
      builder.Append(ch)
   Next
   i If lowerCase Then
      Return builder.ToString().ToLower()
   End If
   Return builder.ToString()
End Function 

kombinasi fungsi angka dan string acak diatas (example:password generator)

Public Function GetPassword() As String
   Dim builder As New StringBuilder()
   builder.Append(RandomString(4, True))
   builder.Append(RandomInt(1000, 9999))
   builder.Append(RandomString(2, False))
   Return builder.ToString()
End Function 'GetPassword 

>

Count Character Occurrences Function

CREATE FUNCTION [dbo].[ufn_CountChar] ( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

DECLARE @vInputLength        INT
DECLARE @vIndex              INT
DECLARE @vCount              INT

SET @vCount = 0
SET @vIndex = 1
SET @vInputLength = LEN(@pInput)

WHILE @vIndex <= @vInputLength
BEGIN
IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar
SET @vCount = @vCount + 1

SET @vIndex = @vIndex + 1
END

RETURN @vCount

END
GO

Description

As can be seen from the user-defined function, it loops through each character in the input string (WHILE @vIndex <= @vInputLength) and compares the string characer against the input search characer (IF SUBSTRING(@pInput, @vIndex, 1) = @pSearchChar). If they are the same, the counter is incremented by 1 (SET @vCount = @vCount + 1).

Second Variant

Here's another way of determining the number of times a certain character occur in a given string without the use of a loop.

CREATE FUNCTION [dbo].[ufn_CountChar] 
( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInput, @pSearchChar, '')))

END
GO

Description

This method is more efficient than the previous version primarily because it doesn't use a loop to determine the number of times the search character occurs in the given string. What it does is replace from the input string the search character with an empty character. It then subtracts the length of the resulting string from the length of the original string to determine the number of times the search character occurs in the input string.

To further illustrate the method performed by the user-defined function, let's say you have a string value of "The quick brown fox jumps over the lazy dog." and you want to determine the number of times the letter "o" occur in the string. The first step it does is to replace the search character, in this case the letter "o", with an empty character/string. So the resulting string after the REPLACE will be "The quick brwn fx jumps ver the lazy dg." Now we subtract the length of this resulting string, which is 40, from the length of the original input string, which is 44, and that gives us 4, which is the number of "o"'s in the given string value.

Third Variant

The previous version of the Count Character Occurrence user-defined function is not case-sensitive. If we want to count the number of "t"'s in lower-case from the same string value above, it will give us a value of 2 instead of just a return value of 1 because it will count the first "t" even if it is in upper-case. To make the user-defined function case-sensitive, it has to be modified as follows:

CREATE FUNCTION [dbo].[ufn_CountChar] 
( @pInput VARCHAR(1000), @pSearchChar CHAR(1) )
RETURNS INT
BEGIN

RETURN (LEN(@pInput) - LEN(REPLACE(@pInputCOLLATE SQL_Latin1_General_Cp1_CS_AS, 
@pSearchChar COLLATE SQL_Latin1_General_Cp1_CS_AS, '')))

END
GO

Description

This version of the Count Character Occurrence user-defined function is the same as the previous version but with a small modification to make it case-sensitive. The only difference is the adding of the "COLLATE SQL_Latin1_General_Cp1_CS_AS" clause to both the input string and the search character. By changing the collation of both the input string and the search character to SQL_Latin1_General_Cp1_CS_AS, which is case-sensitive and accent-sensitive (CS_AS), the REPLACE function will only replace from the input string the search character that matches it exactly, including the case.

source:http://www.sql-server-helper.com/functions/count-character.aspx

view two or more column into one column - merubah dua atau lebih kolom menjadi satu kolom

merubah dua atau lebih kolom menjadi satu kolom, kalo pake MySQL kita dapat memakai fungsi coalesce...
untuk SQL server ini caranya, .
script SQLnya kayak gini

pertama kita buat tabel1

Code:

create table table1 (kolom1 int, kolom2 varchar(50)) 
Go 
  

trus kita isi datanya

Code:

insert table1 
select 1,'satu'  union all 
select 1,'dua'   union all 
select 1,'tiga'  union all 
select 2,'empat' union all 
select 2,'lima'  union all 
select 3,'enam'  union all 
select 3,'tujuh' union all 
select 3,'delapan' 
go

isi tabel1 jadi seperti ini

Kolom1	Kolom2
1	satu
1	dua
1	tiga
2	empat
2	lima
3	enam
3	tujuh
3	delapam

nah kita buat fungsi buat groupingnya

Code:

create function dbo.Group_Concat(@kolom1 int) 
returns varchar(5000)  
--outputnya yang akan jadi kolom2 
as begin    
declare @out varchar(5000) 
--mengabungkan isi kolom2 berdasarkan kriteria kolom1    
select   @out = coalesce(@out + ',' + kolom2, kolom2)   
from   table1    
where   kolom1 = @kolom1    
return @out 
end 

fungsi diatas akan mengrouping nilai kolom2 berdasarkan input yang dimasukin(kolom1)
kalo dipanggil fungsinya... misalnya

Call Group_Concat(1)

maka returnnya = "satu,dua,tiga"
nah sekarang kita buat query untuk nampilin bukan hanya satu input tapi semua nilai dari kolom1
yang itu dengan mengisi parameter untuk fungsi Group_concat dari sub query

querynya :

Code:

select kolom1, dbo.Group_Concat(kolom1) kolom2 
from   
(select   kolom1    from   table1    group by kolom1 ) tempTable 

dan outputnya akan menjadi

Kolom1	Kolom2
1	satu, dua, tiga
2	empat, lima
3	enam, tujuh, delapan

Gw nyobainnya pake Ms SQL server 2005.... seharusnya bekerja untuk semua versi SQl server

Debugging ASP classic with Visual Studio 2005

1. Classic ASP debugging only works with IIS. It does not work with the VS Development Web Server
2. In VS 2005 you have to attach to the ASP worker process (dllhost.exe in IIS 5).

Here is how to make ASP debugging work:

1. Enable ASP debugging on the server. (I also added DEBUG verb to the asp extension, but I am not sure if it is required).
2. Open classic ASP in VS 2005.
3. Set breakpoint.
4. View page in browser or run without debugging.
5. Debug | Attach to Process
6. Select show process from all user
6. Locate IIS ASP worker process (dllhost.exe on IIS5) which exposes x86 and Script and attach as Script.  usually the user is you computer name ex: (MYPC/IWAPT_MYPC)

I tried on Windows XP SP3 prof running IIS 5 and it worked for me.

The Top 20 replies by programmers when their programs do not work

The Top 20 replies by programmers when their programs do not work:


20. "That's weird..."
19. "It's never done that before."
18. "It worked yesterday."
17. "How is that possible?"
16. "It must be a hardware problem."
15. "What did you type in wrong to get it to crash?"
14. "There is something funky in your data."
13. "I haven't touched that module in weeks!"
12. "You must have the wrong version."
11. "It's just some unlucky coincidence."
10. "I can't test everything!"
9. "THIS can't be the source of THAT."
8. "It works, but it hasn't been tested."
7. "Somebody must have changed my code."
6. "Did you check for a virus on your system?"
5. "Even though it doesn't work, how does it feel?
4. "You can't use that version on your system."
3. "Why do you want to do it that way?"
2. "Where were you when the program blew up?"

And the Number One reply by programmers when their programs don't work:
1. "It works on my machine."

from : http://www.thehumorarchives.com/joke/Top_20_programers_excuses

DATE TIME

FormatDateTime(now() ,[dd] [mmmm] [yyyy] [hh]:[nn]:[ss] [AM/PM] )

I want...

I likes my coffee black as hell, my music loud, and my code work....
and no bugs please...

naming convention

penamaan variabel pada saat coding emang bikin puyeng.... saran terbaik ialah menambahakan awalan untuk mengenali tipe variabel yang kita deklarasikan..

Prefix Notation
Integer	i
Long	lng
Single	sgl
Double	dbl
String	str
Boolean	bol
Object	obj

 penggunaannya simpel kalo ingin menamakan variabel string jadi = strName.
owkeh.. semoga berguna
hepi koding...

GooFram???

As soon as Wolfram Alpha launched as a computational knowledge engine, avid searchers hacked up tools to combine its results with standard Google searches. The Goofram site is a clean-looking site that does all that mashing for you.

That's just about all the main Goofram site provides, although with the two search sites' results fit well enough to the page that it almost looks like a natural app. If the idea of broad search reach appeals to Firefox users, they can install a Goofram Firefox add-on that automatically brings Google searches back to Goofram. If you just want Goofram for occasional searching, the site also offers a link for installing the site as one of your Firefox quick search box options. One thing we've learned from using Goofram—Wolfram's suggested searches for "lifehacker" aren't exactly, um, cordial. Free to use, no sign-up required.

Goofram [via The Red Ferret Journal]

Select the last record from tabel

how to select the last record from the table
there is two option:
1. Select the maximum id

Select id from inventory where id in (select max(id) from inventory )

but it takes a a lot of execute time... coz you must execute two select plan and one where
2. The limit

Select id from inventory order by id Desc limit 1

this the fastest way....

Auto Logon Windows XP

If you set a computer for automatic logon, anyone who can physically gain access to the computer can also gain access to everything that is on the computer, including any network or networks that the computer is connected to. Additionally, if you turn on automatic logon, the password is stored in the registry in plain text. The specific registry key that stores this value is remotely readable by the Authenticated Users group. Therefore, only use this setting if the computer is physically secured and if you make sure that users who you do not trust cannot remotely see the registry.

You can use Registry Editor to add your log on information. To do this, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.
2. Locate the following registry key:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon
3. Using your account name and password, double-click the DefaultUserName entry, type your user name, and then click OK.
4. Double-click the DefaultPassword entry, type your password under the value data box, and then click OK.
   
   If there is no DefaultPassword value, create the value. To do this, follow these steps:
   1. In Registry Editor, click Edit, click New, and then click String Value.
   2. Type DefaultPassword as the value name, and then press ENTER.
   3. Double-click the newly created key, and then type your password in the Value Data box.
   If no DefaultPassword string is specified, Windows XP automatically changes the value of the AutoAdminLogon registry key from 1 (true) to 0 (false) to turn off the AutoAdminLogon feature.
5. Double-click the AutoAdminLogon entry, type 1 in the Value Data box, and then click OK.
   
   If there is no AutoAdminLogon entry, create the entry. To do this, follow these steps:
   1. In Registry Editor, click Edit, click New, and then click String Value.
   2. Type AutoAdminLogon as the value name, and then press ENTER.
   3. Double-click the newly created key, and then type 1 in the Value Data box.
6. Quit Registry Editor.
7. Click Start, click Restart, and then click OK.

After your computer restarts and Windows XP starts, you can log on automatically.

If you want to bypass the automatic logon to log on as a different user, hold down the SHIFT key after you log off or after Windows XP restarts. Note that this procedure applies only to the first logon. To enforce this setting for future logoffs, the administrator must set the following registry key:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Winlogon

Value:ForceAutoLogon
Type: REG_SZ
Data: 1

You can also use turn on automatic logon without editing the registry in Microsoft Windows XP Home Edition and in Microsoft Windows XP Professional on a computer that is not joined to a domain. To do this, follow these steps:

1. Click Start, and then click Run.
2. In the Open box, type control userpasswords2, and then click OK.
   
   Note When users try to display help information in the User Accounts window in Windows XP Home Edition, the help information is not displayed. Additionally, users receive the following error message:
   Cannot find the Drive:\Windows\System32\users.hlp Help file. Check to see that the file exists on your hard disk drive. If it does not exist, you must reinstall it.
3. Clear the "Users must enter a user name and password to use this computer" check box, and then click Apply.
4. In the Automatically Log On window, type the password in the Password box, and then retype the password in the Confirm Password box.
5. Click OK to close the Automatically Log On window, and then click OK to close the User Accounts window.

Bad boys

belakangan ini gw lagi research tool untuk web testing, salah satu yang menarik adalah bad boy. selain berbasis GUI dan tambahan script2, serta variabel yang bisa di link ke database ato excel. simpel digunakan dan free untuk 5 user, masih ngulik-ngulik cara akses APInya melalui javascript...

selain itu ada juga watin untuk create testing di VS.NET... cuman ya harus develop dulu berikut contoh skenario testing untuk halaman login dengan mencoba2 inputan dari array, codingnya masih kasar nih... maklum namanya juga contoh.

Sub Main()
Dim browser As New IE("http://localhost/website/login.asp")
Dim User() As String = {"user", "admin", "test", "testingusers", "1", "2"}
Dim pwd() As String = {"password", "admins", "tester", "testingusers", "1", "2"}
Dim i As Integer

For i = 0 To User.Length - 1
If browser.TextField(Find.ByName("login")).Exists Then
browser.TextField(Find.ByName("user")).TypeText(User(i))
browser.TextField(Find.ByName("password")).TypeText(pwd(i))
browser.Button(Find.ByName("btnLogin")).Click()
Else
MsgBox("User = " & User(i - 1) & vbCrLf & "Password = " & pwd(i - 1))
Exit For
End If
Next
End Sub

wish me luck othree....

happy coding

ASP script untuk mencegah SQL injection

berikut adalah script buat mencegah SQL injection... gw lupa dapat darimana, intinya dia melacak karakter-karakter yang bisa dipakai buat SQL injection. sriptnya menscan input secara keseluruhan baik dari form, URL bahkan cookies. ditulis dalam ASP.

'  Author: Nazim Lala
'
'  This is the include file to use with your asp pages to
'  validate input for SQL injection.

Dim BlackList, ErrorPage, s

'
'  Below is a black list that will block certain SQL commands and
'  sequences used in SQL injection will help with input sanitization
'
'  However this is may not suffice, because:
'  1) These might not cover all the cases (like encoded characters)
'  2) This may disallow legitimate input
'
'  Creating a raw sql query strings by concatenating user input is
'  unsafe programming practice. It is advised that you use parameterized
'  SQL instead. Check http://support.microsoft.com/kb/q164485/ for information
'  on how to do this using ADO from ASP.
'
'  Moreover, you need to also implement a white list for your parameters.
'  For example, if you are expecting input for a zipcode you should create
'  a validation rule that will only allow 5 characters in [0-9].
'

BlackList = Array("--", ";", "/*", "*/", "@@", "@",_
                "char", "nchar", "varchar", "nvarchar",_
                "alter", "begin", "cast", "create", "cursor",_
                "declare", "delete", "drop", "end", "exec",_
                "execute", "fetch", "insert", "kill", "open",_
                "select", "sys", "sysobjects", "syscolumns",_
                "table", "update")


'  Populate the error page you want to redirect to in case the
'  check fails.

ErrorPage = "ErrorPage.asp"

'''''''''''''''''''''''''''''''''''''''''''''''''''            
'  This function does not check for encoded characters
'  since we do not know the form of encoding your application
'  uses. Add the appropriate logic to deal with encoded characters
'  in here
'''''''''''''''''''''''''''''''''''''''''''''''''''
Function CheckStringForSQL(str)
On Error Resume Next

Dim lstr

' If the string is empty, return true
If ( IsEmpty(str) ) Then
CheckStringForSQL = false
Exit Function
ElseIf ( StrComp(str, "") = 0 ) Then
CheckStringForSQL = false
 Exit Function
End If

lstr = LCase(str)

' Check if the string contains any patterns in our
' black list
For Each s in BlackList

If ( InStr (lstr, s) <> 0 ) Then
CheckStringForSQL = true
Exit Function
  End If

Next

CheckStringForSQL = false

End Function


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check forms data
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Form
If ( CheckStringForSQL(Request.Form(s)) ) Then

' Redirect to an error page
Response.Redirect(ErrorPage)

End If
Next

'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check query string
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.QueryString
If ( CheckStringForSQL(Request.QueryString(s)) ) Then

' Redirect to error page
Response.Redirect(ErrorPage)

End If

Next


'''''''''''''''''''''''''''''''''''''''''''''''''''
'  Check cookies
'''''''''''''''''''''''''''''''''''''''''''''''''''

For Each s in Request.Cookies
If ( CheckStringForSQL(Request.Cookies(s)) ) Then

' Redirect to error page
Response.Redirect(ErrorPage)

End If

Next

Automatically Gets Updated Date or Time with Current Time and Date When the Record Is Added or Updated

Ada salah satu cara untuk mensetting secara otomatis agar kolom yang bertipe tanggal akan berubah nilainya jika record tersebut di update di MYSQL. Seperti contoh dibawah ini
ketika membuat table ‘TabelLastVisit’ pada kolom modifyDate filed ( DATETIME) set Attributes-nya ON UPDATE CURRENT_TIMESTAMP dan Default value ke NOW().

lihat syntaxnya dibawah ini:

CREATE TABLE `TabelLastVisit` (
`id` int(2) NOT NULL auto_increment primary key,
`name` varchar(20) NOT NULL,
`modifyDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP)

Jika hanya ingin mencatat perubahan pada saat insert, maka hapus syntax on updatenya menjadi

CREATE TABLE `TabelLastVisit` (
`id` int(2) NOT NULL auto_increment primary key,
`name` varchar(20) NOT NULL,
`modifyDate` timestamp NOT NULL default CURRENT_TIMESTAMP)

Sekarang coba tambahkan record baru.

insert into TabelLastVisit (name) values(' Orang Ganteng') 

hasil eksekusi :

id	name	modifyDate
1	Orang Ganteng	2009-05-01 11:46:09

setelah dieksekusi dapat dilihat bahwa kolom modifydate berisi tanggal pada saat di-insert, jika default on updatenya dipakai, maka pada saat update datanya akan berubah

update TabelLastVisit 
set name='Orang Ganteng banget' 
where name='Orang Ganteng'

id	name	modifyDate
1	Orang Ganteng banget	2009-05-01 11:49:22

kolom modify date juga berubah sesuai dengan waktu pada saat record di update.

Guess Estimate..

hari ini gw disuruh bwt guess estimate, terus terang gw rada bingung mo ngisi berapa.. pertama karena gw masih baru bgt di project kayak gini, dan kedua gw juga belom pernah buat guess estimate. biasanya kemaren2 guess estimate gw dah dibuatin ma tim lead.. gw tinggal terima dan biasanya gw selalu on time bahkan seringkali lebih cepat dari jadwal(narcis mode on).

gw diajarin buat gues estimate dengan tiga variabel... pertama yaitu analisis. ini tahap dimana lo nyusun logika ataupun algoritma atau nyari2 bahan, yang kedua yaitu implementasi. yang tiada laen adalah koding. dan yang terakhir adalah testing...

gw pengennya nyusunnya agak molor... tapi gw takut ntar dikatain males... kalo nyusun terlalu cepat takut sombong... akhirnya gw isi apa adanya... dan akhir hari, gw menyadari gw salah kalkulasi... ada tambahan modul yang harus gw kerjain... mudah2an gak lembur...

hepi koding

Error while trying to run project: Unable to start debugging on the web server.

ini yang gw dapat pas gw coba debuging aplikasi ASP.NET, setelah googling kesana kemari... dengan solusi yang aneh-aneh... akhirnya gw nemuin penyebabnya... ternyata gw menginstal .NET versi 2 keatas sebelum gw menginstal IIS. dengan kata lain, gw menginstal IIS setelah gw menginstal .NET framework. ternyata penyebabnya IIS tidak mengenali .NET framework yang gw install... berdasarkan hasil googling kesana kemari salah satu solusinya adalah

1. klik Start ->All Programs -> Microsoft Visual Studio ->Microsoft Visual Studio Tools ->Microsoft Visual Studio Command Prompt
2. pada command prompt ketik aspnet_regiis.exe" -i

kalo langkah2 diatas masih belom sukses... cobain uninstall .NET Framework dan IIS.. setelah itu coba install IIS dulu baru Install .NET framework...

hepi koding...