Tuesday, August 04, 2009
You can use SELECT statement to update records through UPDATE statement.
In the SET Clause use the SELECT statement along with desired filters to
update the records.
UPDATE tblDestination
SET tblDestination.col=value
WHERE EXISTS (
SELECT col2.value
FROM tblSource
WHERE tblSource.join_col=tblDestination.join_col
AND tblSource.constraint=value)
In INSERT statement, by replacing the VALUES clause with a SELECT statement
you can get a set of records for INSERT.
Suppose I have a table called table_A and I want to populate it with the ID, Name and
Address of table_B. The statement would look something like this:
INSERT table_A (col_id, col_name, col_address)
SELECT col_id, col_name, col_address
FROM table_B
WHERE col_city = 'karachi'
This will take the records with col_city='karachi' and load them into the
table table_A. I can use any type of SELECT statement here.
It just has to return a record set that matches the columns in the
INSERT statement. The number of columns and their data types must match
(or be implicitly convertible). I can also execute a stored procedure
that returns a record set using the EXEC command in place of the SELECT
statement.
Saturday, February 28, 2009
You can add optional parameters in stored procedures by setting a default value for each parameter that you want to make optional. The default value is typically NULL, but it's not necessary.
Example:
CREATE PROCEDURE dbo.mySP
@firstParam VARCHAR(32) = NULL,
@secondParam INT = NULL
AS
BEGIN
SET NOCOUNT ON
SELECT *
FROM [TableName]
WHERE
Param1 = @firstParam
AND Param2 = @secondParam
END
GO
--
EXEC dbo.mySP @firstParam ='bar', @secondParam =4
EXEC dbo.mySP @firstParam ='bar'
EXEC dbo.mySP @secondParam =4
EXEC dbo.mySP 'bar',4
EXEC dbo.mySP 'bar'
EXEC dbo.mySP
GO
DROP PROCEDURE dbo.mySP
GO
HAS_DBACCESS returns information about whether the user has access to the specified database.
Example:
SELECT HAS_DBACCESS('Northwind');
returns
1 if the user has access to the database
0 if the user does not have access to the database
NULL if the database does not exist
Find all databases that the current user has access to
SELECT [Name] as DatabaseName
FROM master.dbo.sysdatabases
WHERE
ISNULL(HAS_DBACCESS ([Name]),0)=1
ORDER BY [Name]
Thursday, February 19, 2009
I got this joke in an email from my friend and I couldn't stop laughing.
A plain computer illiterate guy rings tech support to report that his computer is faulty.
Tech: What's the problem?
User: There is smoke coming out of the power supply.
Tech: You'll need a new power supply.
User: No, I don't! I just need to change the start up files.
Tech: Sir, the power supply is faulty. You'll need to replace it.
User: No way! Someone told me that I just needed to change the start up and it will fix the problem! All I need is for you to tell me the command.
Tech support::
10 minutes later, the User is still adamant that he is right. The tech is frustrated and fed up.
Tech: Sorry, Sir. We don't normally tell our customers this, but there is an undocumented DOS command that will fix the problem.
User: I knew it!
Tech : Just add the line
LOAD NOSMOKE -U
at the end of the CONFIG.SYS. Let me know how it goes.
10 minutes later.
User : It didn't work. The power supply is still smoking.
Tech : Well, what version of DOS are you using?
User : MS-DOS 6.22 .
Tech : That's your problem there. That version of DOS didn't come with NOSMOKE. Contact Microsoft and ask them for a patch that will give you the file. Let me know how it goes.
1 hour later.
User : I need a new power supply.
Tech support : How did you come to that conclusion?
User : Well, I rang Microsoft and told him about what you said, and he started asking questions about the make of power supply.
Tech: Then what did he say?
--
--
--
User: He told me that my power supply isn't compatible with NOSMOKE.
Wednesday, February 11, 2009
You may use DBCC CHECKIDENT to reseed (reset) the identity column of any table in SQL Server.
Example:
A table has 10 rows with 10 as last identity. The next record will automatically genrate 11, to start the new identity use the follwing T SQL query in query analyzer.
DBCC CHECKIDENT (tableName, reseed, 15)
To set the seed to zero use the following T SQL query in query analyzer.
DBCC CHECKIDENT (tableName, reseed, 0)
Avoid using a value lower than the current value, doing this will violate the uniqueness contrant in case of new entries.
Different Run Commands useful for time saving while using your computer. To invoke the Run box, hold down the Windows key and hit R (or from the Start Menu, choose "Run.") From there type any one of the keywords listed to launch an app: like calc for Calculator or clpbrd for the Clipboard Viewer or freecell for the card game.
|
Program
|
Run Command
|
|
Accessibility Controls
|
access.cpl
|
|
Accessibility Wizard
|
accwiz
|
|
Add Hardware Wizard
|
hdwwiz.cpl
|
|
Add/Remove Programs
|
appwiz.cpl
|
|
Administrative Tools
|
control admintools
|
|
Adobe Acrobat ( if installed )
|
acrobat
|
|
Adobe Distiller ( if installed )
|
acrodist
|
|
Adobe ImageReady ( if installed )
|
imageready
|
|
Adobe Photoshop ( if installed )
|
photoshop
|
|
Automatic Updates
|
wuaucpl.cpl
|
|
Basic Media Player
|
mplay32
|
|
Bluetooth Transfer Wizard
|
fsquirt
|
|
Calculator
|
calc
|
|
Ccleaner ( if installed )
|
ccleaner
|
|
C: Drive
|
c:
|
|
Certificate Manager
|
cdrtmgr.msc
|
|
Character Map
|
charmap
|
|
Check Disk Utility
|
chkdsk
|
|
Clipboard Viewer
|
clipbrd
|
|
Command Prompt
|
cmd
|
|
Command Prompt
|
command
|
|
Component Services
|
dcomcnfg
|
|
Computer Management
|
compmgmt.msc
|
|
Compare Files
|
comp
|
|
Control Panel
|
control
|
|
Create a shared folder Wizard
|
shrpubw
|
|
Date and Time Properties
|
timedate.cpl
|
|
DDE Shares
|
ddeshare
|
|
Device Manager
|
devmgmt.msc
|
|
Direct X Control Panel ( if installed )
|
directx.cpl
|
|
Direct X Troubleshooter
|
dxdiag
|
|
Disk Cleanup Utility
|
cleanmgr
|
|
Disk Defragment
|
dfrg.msc
|
|
Disk Partition Manager
|
diskmgmt.msc
|
|
Display Properties
|
control desktop
|
|
Display Properties
|
desk.cpl
|
|
Display Properties (w/Appearance Tab Preselected )
|
control color
|
|
Dr. Watson System Troubleshooting Utility
|
drwtsn32
|
|
Driver Verifier Utility
|
verifier
|
|
Ethereal ( if installed )
|
ethereal
|
|
Event Viewer
|
eventvwr.msc
|
|
Files and Settings Transfer Tool
|
migwiz
|
|
File Signature Verification Tool
|
sigverif
|
|
Findfast
|
findfast.cpl
|
|
Firefox
|
firefox
|
|
Folders Properties
|
control folders
|
|
Fonts
|
fonts
|
|
Fonts Folder
|
fonts
|
|
Free Cell Card Game
|
freecell
|
|
Game Controllers
|
joy.cpl
|
|
Group Policy Editor ( xp pro )
|
gpedit.msc
|
|
Hearts Card Game
|
mshearts
|
|
Help and Support
|
helpctr
|
|
Hyperterminal
|
hypertrm
|
|
Hotline Client
|
hotlineclient
|
|
Iexpress Wizard
|
iexpress
|
|
Indexing Service
|
ciadv.msc
|
|
Internet Connection Wizard
|
icwonn1
|
|
Internet Properties
|
inetcpl.cpl
|
|
Internet Setup Wizard
|
inetwiz
|
|
IP Configuration (Display Connection Configuration)
|
ipconfig /all
|
|
IP Configuration (Display DNS Cache Contents)
|
ipconfig /displaydns
|
|
IP Configuration (Delete DNS Cache Contents)
|
ipconfig /flushdns
|
|
IP Configuration (Release All Connections)
|
ipconfig /release
|
|
IP Configuration (Renew All Connections)
|
ipconfig /renew
|
|
IP Configuration (Refreshes DHCP & Re-Registers DNS)
|
ipconfig /registerdns
|
|
IP Configuration (Display DHCP Class ID)
|
ipconfig /showclassid
|
|
IP Configuration (Modifies DHCP Class ID)
|
ipconfig /setclassid
|
|
Java Control Panel ( if installed )
|
jpicpl32.cpl
|
|
Java Control Panel ( if installed )
|
javaws
|
|
Keyboard Properties
|
control keyboard
|
|
Local Security Settings
|
secpol.msc
|
|
Local Users and Groups
|
lusrmgr.msc
|
|
Logs You Out of Windows
|
logoff
|
|
Malicious Software Removal Tool
|
mrt
|
|
Microsoft Access ( if installed )
|
access.cpl
|
|
Microsoft Chat
|
winchat
|
|
Microsoft Excel ( if installed )
|
excel
|
|
Microsoft Diskpart
|
diskpart
|
|
Microsoft Frontpage ( if installed )
|
frontpg
|
|
Microsoft Movie Maker
|
moviemk
|
|
Microsoft Management Console
|
mmc
|
|
Microsoft Narrator
|
narrator
|
|
Microsoft Paint
|
mspaint
|
|
Microsoft Powerpoint
|
powerpnt
|
|
Microsoft Word ( if installed )
|
winword
|
|
Microsoft Syncronization Tool
|
mobsync
|
|
Minesweeper Game
|
winmine
|
|
Mouse Properties
|
control mouse
|
|
Mouse Properties
|
main.cpl
|
|
MS-Dos Editor
|
edit
|
|
MS-Dos FTP
|
ftp
|
|
Nero ( if installed )
|
nero
|
|
Netmeeting
|
conf
|
|
Network Connections
|
control netconnections
|
|
Network Connections
|
ncpa.cpl
|
|
Network Setup Wizard
|
netsetup.cpl
|
|
Notepad
|
notepad
|
|
Nview Desktop Manager ( if installed )
|
nvtuicpl.cpl
|
|
Object Packager
|
packager
|
|
ODBC Data Source Administrator
|
odbccp32
|
|
ODBC Data Source Administrator
|
odbccp32.cpl
|
|
On Screen Keyboard
|
osk
|
|
Opens AC3 Filter ( if installed )
|
ac3filter.cpl
|
|
Outlook Express
|
msimn
|
|
Paint
|
pbrush
|
|
Password Properties
|
password.cpl
|
|
Performance Monitor
|
perfmon.msc
|
|
Performance Monitor
|
perfmon
|
|
Phone and Modem Options
|
telephon.cpl
|
|
Phone Dialer
|
dialer
|
|
Pinball Game
|
pinball
|
|
Power Configuration
|
powercfg.cpl
|
|
Printers and Faxes
|
control printers
|
|
Printers Folder
|
printers
|
|
Private Characters Editor
|
eudcedit
|
|
Quicktime ( if installed )
|
quicktime.cpl
|
|
Quicktime Player ( if installed )
|
quicktimeplayer
|
|
Real Player ( if installed )
|
realplay
|
|
Regional Settings
|
intl.cpl
|
|
Registry Editor
|
regedit
|
|
Registry Editor
|
regedit32
|
|
Remote Access Phonebook
|
rasphone
|
|
Remote Desktop
|
mstsc
|
|
Removable Storage
|
ntmsmgr.msc
|
|
Removable Storage Operator Requests
|
ntmsoprq.msc
|
|
Resultant Set of Policy ( xp pro )
|
rsop.msc
|
|
Scanners and Cameras
|
sticpl.cpl
|
|
Scheduled Tasks
|
control schedtasks
|
|
Security Center
|
wscui.cpl
|
|
Services
|
services.msc
|
|
Shared Folders
|
fsmgmt.msc
|
|
Sharing Session
|
rtcshare
|
|
Shuts Down Windows
|
shutdown
|
|
Sounds Recorder
|
sndrec32
|
|
Sounds and Audio
|
mmsys.cpl
|
|
Spider Solitare Card Game
|
spider
|
|
SQL Client Configuration
|
clicongf
|
|
System Configuration Editor
|
sysedit
|
|
System Configuration Utility
|
msconfig
|
|
System File Checker Utility ( Scan Immediately )
|
sfc /scannow
|
|
System File Checker Utility ( Scan Once At Next Boot )
|
sfc /scanonce
|
|
System File Checker Utility ( Scan On Every Boot )
|
sfc /scanboot
|
|
System File Checker Utility ( Return to Default Settings)
|
sfc /revert
|
|
System File Checker Utility ( Purge File Cache )
|
sfc /purgecache
|
|
System File Checker Utility ( Set Cache Size to Size x )
|
sfc /cachesize=x
|
|
System Information
|
msinfo32
|
|
System Properties
|
sysdm.cpl
|
|
Task Manager
|
taskmgr
|
|
TCP Tester
|
tcptest
|
|
Telnet Client
|
telnet
|
|
Tweak UI ( if installed )
|
tweakui
|
|
User Account Management
|
nusrmgr.cpl
|
|
Utility Manager
|
utilman
|
|
Volume Serial Number for C:
|
label
|
|
Volume Control
|
sndvol32
|
|
Windows Address Book
|
wab
|
|
Windows Address Book Import Utility
|
wabmig
|
|
Windows Backup Utility ( if installed )
|
ntbackup
|
|
Windows Explorer
|
explorer
|
|
Windows Firewall
|
firewall.cpl
|
|
Windows Installer Details
|
msiexec
|
|
Windows Magnifier
|
magnify
|
|
Windows Management Infrastructure
|
wmimgmt.msc
|
|
Windows Media Player
|
wmplayer
|
|
Windows Messenger
|
msnsgs
|
|
Windows Picture Import Wizard (Need camera connected)
|
wiaacmgr
|
|
Windows System Security Tool
|
syskey
|
|
Windows Script host settings
|
wscript
|
|
Widnows Update Launches
|
wupdmgr
|
|
Windows Version ( shows your windows version )
|
winver
|
|
Windows XP Tour Wizard
|
tourstart
|
|
Wordpad
|
write
|
|
Zoom Utility
|
igfxzoom
|
Monday, February 02, 2009
Method to retrieve every text inside the stored procedures of a database
SELECT B.Name, *
FROM syscomments A INNER JOIN sysobjects B
ON A.id = B.id
WHERE A.text like '%tblNames%'
You can launch the default browser to display HTML content using the Process.Start method in the System.Diagnostics namespace by specifying the URL as the argument. But I don't recommend this approach because sometimes the browser will launch behind your application.
A more reliable option is to use the Help.ShowHelp method to launch HTML content (even if the content is not on-line help). Call Help.ShowHelp with the active control as argument 1 (or use Form.Active Form). Specify the URL as argument 2, and be sure to include the prefix (file:///, http://, https://, etc).
Sunday, January 25, 2009
It's very important to log any uncaught exceptions that your ASP.NET application generates. Otherwise you may not be aware of serious problems with your website. You can trap exceptions by writing an Application_Error method in Global.asax. Call Server.GetLastError() to get the exception.I recommend logging the following data so that you'll be able to troubleshoot the problem:
| Data |
How to Retrieve |
| Current Time |
DateTime.Now |
| Request IP Address |
Request.UserHostAddress |
| Referring URL (page user was on when the current page was requested) |
Request.UrlReferrer |
| User Agent (browser or spider that issued the request) |
Request.UserAgent |
| User Host Name |
Request.UserHostName |
| All HTTP Headers |
Request.Headers |
| All Form Fields |
Request.Form |
| Exception Details |
ex.GetType(), ex.Message, ex.StackTrace, where ex is the exception returned by Server.GetLastError() |
| Inner Exception Details |
Same as above, if ex.InnerException is not null. |
If you find yourself getting spurious entries in your exception log that you'd like to suppress, I suggest putting regular expressions in your web.config file that match the entries that you're not interested in. When an uncaught exception occurs, generate the log entry, but then see if any of the regular expressions match the log entry. If any do, don't save the log entry.
Sunday, January 18, 2009
Environment.TickCount returns a 32-bit signed integer containing the amount of time in milliseconds that has passed since the last time the computer was started. But this value can overflow for systems that stay up for days at a time. To avoid this overflow problem, you can query the "System Up Time" performance counter:
public TimeSpan SystemUpTime()
{
PerformanceCounter upTime = new PerformanceCounter("System", "System Up Time");
// You've got to call this twice. First time it returns 0 and the second time it returns the real info.
upTime.NextValue();
return TimeSpan.FromSeconds(upTime.NextValue());
}
You need proper privileges to query performance counter.
Sunday, January 11, 2009
First, go to AssemblyInfo.cs and remove all asterisks from the AssemblyVersion:
[assembly: AssemblyVersion("1.0.0.0")]
Otherwise, every time you build your application the registry key will change.
Save configuration values to the registry as follows:
Application.UserAppDataRegistry.SetValue("Value", Value);
Load configuration values to the registry as follows:
try
{
Value = (int) Application.UserAppDataRegistry.GetValue("Value");
}
catch(Exception)
{
}
The GetValue method will throw an exception if the specified value does not exist in the registry.
Friday, January 02, 2009
class HelloWorldApp
{
static void Main()
{
System.Console.WriteLine("Hello, world!");
}
}