NIHAR'S BLOG

How to Format Date/Time Sometimes I can't find a better way to present something then through a series of code/exmaples, so, without much addoo: - Microsoft SQL Server T-SQL date and datetime formats - Date time formats - mssql datetime  - MSSQL getdate returns current system date and time in standard internal format SELECT   convert ( varchar ,   getdate (),  100 )   - mon dd yyyy hh:mmAM (or PM)                                          - Oct  2 2008 11:01AM           SELECT   convert ( varchar ,   getdate (),  101 )   - mm/dd/yyyy  -  10/02/2008                   SELECT   c...

Key Code backspace 8 tab 9 enter 13 shift 16 ctrl ...

ORA-00568 maximum number of interrupt handlers exceeded     Action : Reduce the number of registered interrupt handlers. 00570-00599: SQL*Connect Opening and Reading Files Messages The messages for this topic are described elsewhere in the Oracle8 Error Messages, Release 8.0.3 error message set. 00600-00639: Oracle Exceptions Messages This section lists messages generated when an internal exception is generated within Oracle. Cause : User specified too many ^c handlers     Action : Remove some old handlers.

ORA-01258 unable to delete temporary file string Cause : A DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES or ALTER DATABASE TEMPFILE DROP INCLUDING DATAFILES operation was not able to delete a temporary file in the database.     Action : Subsequent errors describe the operating system error that prevented the file deletion. Fix the problem, if possible, and manually purge the file.

ORA-12235 TNS:Failure to redirect to destination   Cause : This error is reported by an interchange which fails to redirect a connection to another interchange along the path to the destination.     Action : Report the problem to your Network Administrator so that he may fix the problem because its server problem .

 ASPX : <asp:gridview id = "Gridview1" runat = "server" allowpaging = "true" onpageindexchanging = " GridView1_PageIndexChanging " > </asp:gridview> C# : protected void Page_Load(object sender, EventArgs e)   {             if (!IsPostBack)             {                              Gridview1.PageIndex = Int32.MaxValue;                 bindGridData();                           }    } protected void gvJobCard_RowDataBound(object sender, GridViewRowEventArgs e) {      ...

HOW TO DISPLAY ALL ROW DATA IN SINGLE STRING : SELECT Stuff(   (SELECT N', ' + Name FROM [TableName]  FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') Output : ACBCK12..1212sid... EX: SELECT Stuff(   (SELECT N', ' + Name FROM Test FOR XML PATH(''),TYPE)   .value('text()[1]','nvarchar(max)'),1,2,N'') output : Nihar, Rohan, Rohan, , Sw, Sw

COUNT(SomeColumn) will only return the count of rows that contain non-null values for SomeColumn.  COUNT(*) and COUNT('Foo') will return the total number of rows in the table. Other else no such big difference between them.....  

    //Create the data set and table DataSet ds = new DataSet ( "New_DataSet" ); DataTable dt = new DataTable ( "New_DataTable" ); //Set the locale for each ds . Locale = System . Threading . Thread . CurrentThread . CurrentCulture ; dt . Locale = System . Threading . Thread . CurrentThread . CurrentCulture ; //Open a DB connection (in this example with OleDB) OleDbConnection con = new OleDbConnection ( dbConnectionString ); con . Open (); //Create a query and fill the data table with the data from the DB string sql = "SELECT Whatever FROM MyDBTable;" ; OleDbCommand cmd = new OleDbCommand ( sql , con ); OleDbDataAdapter adptr = new OleDbDataAdapter (); adptr . SelectCommand = cmd ; adptr . Fill ( dt ); con . Close (); //Add the table to the data set ds . Tables . Add ( dt ); //Here's the easy part. Create the Excel worksheet from the data set ExcelLibrary . DataSetHelper . CreateWorkbook ( "MyExcelFile...

Try following query select * from [TestApp].[dbo].[Info] where '|pune      |SDF       |sfd       |dgd       |' like '%|' + adds + '|%' Result : f_name    adds           city           state            id             zip nihar        pune          pune          mah           1             23232     FSDF       SDF           SFS   ...

I n SQL SERVER 2008 : There are two ways to describe :   1)  Here one more solution found with help StackOverflow site I like the answer that attempts to do the translate, however, while using the code it doesn't like columns that are not VARCHAR type such as BIGINT or DATETIME. I needed something similar today so I took the time to modify it more to my liking. It is also now encapsulated in a function which is the closest thing I could find to just typing describe as oracle handles it. I may still be missing a few data types in my case statement but this works for everything I tried it on. It also orders by ordinal position. this could be expanded on to include primary key columns easily as well. Step1: Create Function in Database as CREATE FUNCTION dbo.describe (@TABLENAME varchar(50)) returns table as RETURN ( SELECT TOP 1000 column_name AS [ColumnName],        IS_NULLABLE AS [IsNullable],        DA...

Types : Inline Table-Valued  (For reducing complex logic or big logic ) Multi-statement Table-valued. (For reducing complex logic or big logic ) Scalar valued function      (mostly for beginners) How do I create and use a Scalar User-Defined Function? A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value. Below is an example that is based in the data found in the NorthWind Customers Table.   CREATE FUNCTION [dbo].[whichContinent] ( @country nvarchar(30) ) RETURNS nvarchar(30) AS BEGIN declare @result nvarchar(20) select @result=case @country when 'A' then 'AMERICA' when 'B' then 'BELGAON' when 'I' then 'INDIA' else 'Unknown' end return @result END    ...

    What is Unicode character string :           In sql server , unicode character string starts with "N"  i.e nvarchar,nchar etc        Main purpose to use it... it acceptss universal character string and "N" stands for NATIONAL   LANGUAGE.         When we are executing query in sql sevrer with parameters , sql server displays list of values preceded with N 'Hello'  etc               @p_id = 6,         @p_desc = N'sas',         @p_msg = @p_msg OUTPUT           More details

Autopostback is the mechanism, by which the page will be posted back to the server automatically based on some events in the web controls. In some of the web controls, the property called auto post back, which if set to true, will send the request to the server when an event happens in the control. For e.g  ASPX :   <td align="center" colspan="3">                                                 <asp:TextBox ID="txtID" runat="server" ontextchanged="txtID_TextChanged" AutoPostBack="true"></asp:TextBox>                                      ...

The error String or binary data would be truncated. The statement has been terminated.   occurs when we try to insert or update data which is passed having length greater than which is specified in the database. For Example Column Name      Data Type Description              varchar(50) But we given more than 50 characters its will be truncated. happy coding!!  

All except blanks: Like '%' Starting with A: Like 'A%' Or could do this: Like 'a%' A somewhere in the field: Like '%A%' Or could do this: Like '%a%' One character an A or B or D: Like '[A,B,D]' Or could do this: Like '[a,b,d]' One character A through C as the first character: Like '[A-C]%' Or could do this: Like '[a-c]%' A through C as the 1st character and A through H as the 2nd character: Like '[A-C][A-H]%' Or could do this: Like '[a-c][a-h]%' Starting with Sm, ending with th, and anything for the 3rd character: Like 'SM?TH' Or could do this: Like 'sm?th' Digit for the 1st character: Like '#%' Or could do this: Like '[0-9]%' Not in a range of letters: Like '[!a-c]' Not start with a range of letters: Like '[!a-c]%' Not start with a number: Or could do this: Like '[!0-9]%'

Strange , no need to go XSD for simple reports... We can do it directly from crystal reports..... setting How to write sql statement in CRYSTAL REPORTS : Go to Field Explorer -> Database Field -> Set Database expert In history your database server (any) ->  ADD Command Enter your query and click finish or OK Suppose you want to edit your sql statement Again that rightclick on command "Database Expert" -> on right handwindow your command name is visible . Double click on it or right click  Editor will be visible Again OK Its simple some more advance thing in new crystal report version ... Happy Coding

-          Error occur at time of master page loading -          Remove commented text ß <asp….> à   etc. from that page -          Give space between <asp: content> starting tag and content   Try it... It works...