Tuesday, September 25, 2012

Get Difference Between Two Rows


 
 
Employee table have EmployeeID, and his salary for each month
 
EmpID
EmpName
Salary
month
1
Sally
2000
2
1
Sally
2500
3
2
Noor
3200
1
2
Noor
3250
2
 
 
to get difference between salary for EmpID = 1 for month 2 , 3

 

SELECT empName as name , salary  - (
                                                                  SELECT  salary
                                                                  FROM Employee
                                                                  WHERE month = 2
                                                               ) as Diff_Salary

from Employee 
 
where month = 3
 
and   EmpID  =  1
 
 

the Result
 


 





 

Tuesday, September 18, 2012

DataGridView keypress Event Not Working

 
 
 
In this example i want to create Keypress Event in Column named 'Name' to allow letters  and backspace character
 

 
 
here DataGridView.EditingControlShowing event handler adds a handler for a
 
DataGridViewTextBoxEditingControl event. The editing control is cast to a TextBox to handle the
 
 
 
 

 
KeyPress Event Code
 


EditingControlShowing Event Code
 
 
 
 
After that the Keypress event must be work fine
 
 
 

Friday, September 14, 2012

SQL Server 2008 Automatic job

 
Automatic job to Backup DB in ' SQL Server 2008 '
 
 
1- Open sql server Managment Studio and connect with Account in SYSAdmin Group like 'sa'
 
 
2- make sure the SQL Server Agent in Start mode
 
 
3- go to Managment --> Maintenance Plan --> new Maintenance Plane and set Name for this Plan
 


 
4- select Calender Icon from Top Right side
 

5- configer time/date and if you want to repeat set Schedual type Recurring
 
and Frequency --> Occurs (Weekly,Daily ...)
and Daily Frequency --> Occurs Once At (time you want)
 
 
6- Tool Box in left side --> Drag Backup Database Task --> Drop in Yellow Space in right side
 
 
7- Double Click In Back Up Database Task Box And Set The Configration in below window
 
  • in Database(s) Field Select your DB you want to backup
  • in Folder Field Choose Location You want
  • then OK
 
8- now you can Save  and test it Manualy if you want by right click in maintenance plan you created now and select Execute , go to folder you select and chick if file .bak created or not.
 
 
 if you want to Delete Old Backup File like older than 1 month you must do that
 
1- Tool Box in left side --> Drag Maintenance Cleanup Task --> Drop in Yellow Space in right side
 
 
2- Double Click In Maintenance Cleanup Task Box And Set The Configration in below window
  • in Folder Field Choose the same Location in backup task
  • in File extension type the same extension in backup task
  • then OK
 
3- now Save
 

Thursday, September 13, 2012

Database 'msdb' cannot be opened


Database 'msdb' cannot be opened
 


Sql Server 2008 retrieve this Error when opened
 
"Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)"






I found 2 solution to solve it

Loggin with 'Sa' account

1st Slolution - (Not Worked With me)




 
1- open new Query

2 - Typing

2.1 EXEC sp_resetstatus 'DB_Name';
Details :(sp_resetstatus turns off the suspect flag on a database. This procedure updates the mode and status columns of the named database in sys.databases. Also note that only logins having sysadmin priveleges can perform this )
 

2.2 ALTER DATABASE DB_Name SET EMERGENCY
Details :(Once the database is set to EMERGENCY mode it becomes a READ_ONLYcopy and only members of sysadmin fixed server roles have privileges to access it. )


2.3 DBCC checkdb('DB_Name')
Details :(Check the integrity among all the objects)

 

2.4 ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
 Details :(Set the database to single user mode)


2.5 DBCC CheckDB ('DB_Name', REPAIR_ALLOW_DATA_LOSS)
Details :(Repair the errors)2.6 ALTER DATABASE DB_Name SET MULTI_USER
Details :(Set the database to multi user mode, so that it can now be accessed by others) 

 
2nd Solution  (Worked with me)


1- open sql server Managment Studio
2- in  object Explorer --> the opened connection item --> rightclick --> Stop


2- open  Control Panel -->Administrative Tools-->Services
 
3- in Sql Server (MSSQLSERVER) item --> right click --> Stop

 

4- Open C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

5- Move MSDBData.mdf & MSDBlog.ldf to any other place



6- then Copy this Files Agin from New Place and put it in older Place

 C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

7-  in opened connection in object Explorer --> rightclick --> Start

 


 8- then Refresh DataBase .

9- then you can Detach the MSDB File



Wednesday, May 16, 2012

MDI Form in C#

What is MDI Form?


MDI Form is a parent form, it has child forms


Create Use MDI Form

Form form1 = new Form();
Form1.ISMDIContainer = true ;

 OR

Add new Item --> MDI Parent Form  // this form has full Menu Bar and Status Bar

Create Child Forms

 in MDIForm load function

Form child1 = new Form();

child1.WindowState = FormWindowState.Maximized;


child1.MdiParent = this;

Panel2.Controls.Add(child1);     //open child form on panel in parent form

child1.Show();



if all child is opened in MDI Form , so to  preview specific one use

child1.BringToFront();