Archive for December, 2009

how to convert datetime format in SQL Server 2005

datetime data type in database seems to irritate programmers quite often. One of the simple solution is to keep everything in its own state; save datetime in database format and show or get datetime to or from users as they require. SQL Server built-in function convert does both kind of conversions, i.e. datetime to varchar and vice versa. In the following code snippets, I have used “dd/mm/yyyy” datetime style and the code for this one is 103, you can collect code for your chosen datetime style from Date and Time Styles.


CREATE FUNCTION udf_DateTo_Save (@inputDate as varchar(10))
returns datetime
as
BEGIN
  declare
@outputDate datetime
  select
@outputDate = convert(datetime,@inputDate,103)
  return @outputDate
END


This user defined function dbo.udp_DateTo_Save takes varchar as input convert it into datetime. Please follow that convert function requires datetime style code as parameter.


CREATE FUNCTION udf_DateTo_Show(@inputDate as datetime)
returns varchar(10)
as
BEGIN
  declare
@outputDate varchar(10)
  select @outputDate = convert(varchar,@inputDate,103)
  return @outputDate
END


dbo.udp_DateTo_Show function does the opposite.


SELECT dbo.udf_DateTo_Save(’01/01/2010′) as DBDate, dbo.udf_DateTo_Show(getdate()) as UIDate



Use those two user defined functions while interacting with database and avoid complexity with datetime.

Thanks
A Rahim Khan

Advertisements

how to use SQL Server Stored Procedure in C#, VB6

Using Stored Procedure to insert data is sometimes proved worthy in case of robust applications. More appropriately, it is at least better than creating a long SQL insert query string for database interaction. However, using Stored Procedure in C# is very simple and effective as explained below.


CREATE PROCEDURE udp_InsertGuest
@guest_Name varchar50),
@guest_Address varchar(50),
@guest_PhoneNumber varchar(50),
@guest_VisitPurpose varchar(50),
@guest_Id int output
as
BEGIN
  insert into
t_Guest
 (guest_Name,guest_Address,guest_PhoneNumber,
  guest_VisitPurpose,guest_EntryDateAndTime)
  values
 (@guest_Name,@guest_Addresss,@guest_PhoneNumber,
  @guest_VisitPurpose, getdate ())
  set @guest_Id = SCOPE_IDENTITY ()
END


First of all, I have written Stored Procedure udp_InsertGuest to insert data in t_Guest table that returns identity of last inserted row as well.


sqlConnection = DbConnector.GetConnection;
sqlConnection.Open();
sqlCommand = new SqlCommand(“udp_InsertGuest”,sqlConnection);
sqlCommand.CommandType = CommandType.StoredProcedure;


Here, I have created sqlCommand object of SqlCommand class using Stored Procedure and Database Connection as parameters as well as assigned Command Type to it.


sqlCommand.Parameters.Add(“@guest_Name”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Name”].Value = guestObj.Name;

sqlCommand.Parameters.Add(“@guest_Address”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_Address”].Value = guestObj.Address;

sqlCommand.Parameters.Add(“@guest_PhoneNumber”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_PhoneNumber”].Value = guestObj.Phone;

sqlCommand.Parameters.Add(“@guest_VisitPurpose”, SqlDbType.VarChar, 50);
sqlCommand.Parameters[“@guest_VisitPurpose”].Value = guestObj.VisitCause;

sqlCommand.Parameters.Add(“@guest_Id”, SqlDbType.Int);
sqlCommand.Parameters[“@guest_Id”].Direction = ParameterDirection.Output;


This code snippet shows how to add parameters and assign values in sqlCommand object. Please follow the output parameter requires a direction assignment.


sqlCommand.ExecuteNonQuery();
int guestId = (int)sqlCommand.Parameters[“@guest_Id”].Value;


Finally, after sqlCommand’s execution, it’s quite easy to retrieve the value of output parameter.



Dim guestId As Integer

'Creating Command
Dim command As ADODB.command
Set command = New ADODB.command
command.CommandType = adCmdStoredProc
command.ActiveConnection = ProjectConnection
command.CommandText = "udp_InsertGuest"

'Adding Input Parameters
command.Parameters.Append command.CreateParameter("guest_Name", adVarChar, adParamInput, 50, guestName)
command.Parameters.Append command.CreateParameter("guest_Address", adVarChar, adParamInput, 50, guestAddress)
command.Parameters.Append command.CreateParameter("guest_PhoneNumber", adVarChar, adParamInput, 50, guestPhoneNumber)
command.Parameters.Append command.CreateParameter("guest_VisitPurpose", adVarChar, adParamInput, 50, guestVisitPurpose)

'Adding Output Parameters
command.Parameters.Append command.CreateParameter("guest_id", adInteger, adParamOutput)

'Executing Command
command.Execute

'Retrieving Output Parameters
guestId = command("guest_Id")


This code snippet shows how to use stored procedure with VB6.

Thanks
A Rahim Khan

Maintaining Scroll Position of page after autopostback or failed submission in ASP.NET

This is a small but quite handy trick to send user back where he was in web page after unsuccessful submission or auto post back of the page. However, doing this programmatically will cost you a lot. What ASP.NET developed page does is that it remembers scroll position at time of submission and returns user the at exactly the same scroll position.

Just set MaintainScrollPositionOnPostBack property to “true” at the very beginning of your web page and you will see the ASP.NET’s efficiency.

Thanks
A Rahim Khan

Treating Enter key as TAB in UI with C#, VB6

Sometimes, End User of robust application prefers pressing Enter to Tab Key while navigate through various input fields.

To implement this, you can use this KeyPress event in each Form.

Don’t forget to set KeyPreview property to True. Now, whenever you press enter key while navigating in Form, it will be treated as Tab and you will be navigated to next Input field.

This is all the same for Visual Basic 6 except coding syntax.

Thanks
A Rahim Khan

Database BackUp & Restore Commands in SQL Server 2005

Sometimes, Database backup and restore using SQL Command is proved handy.

First of all, taking backup of any specific database to your desired location is very easy using this simple SQL command.

Moreover, you can retrieve logical name from Database backup file.

Finally, this code snippet shows how to restore database and save database files in default location or even in your desired location.

Thanks
A Rahim Khan

How to Enable/Configure sa User in SQL Server 2005

First of all, SQL Server Log In is to be enabled.
# Right click on sa or other user you want to enable and click on Properties.

# In the left Pane select General, change and confirm Password in Right Pane for User if it requires.

# Select Status in Left Pane and check Enable under Login in Right Pane.

# Press OK and restart the SQL Server.

Thanks
A Rahim Khan