Friday, 29 June 2012

DATETIME FORMAT IN SQL SERVER

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 convert(varchar, getdate(), 102) - yyyy.mm.dd - 2008.10.02           
SELECT convert(varchar, getdate(), 103) - dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) - dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) - dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) - dd mon yyyy
SELECT convert(varchar, getdate(), 107) - mon dd, yyyy
SELECT convert(varchar, getdate(), 108) - hh:mm:ss
SELECT convert(varchar, getdate(), 109) - mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        - Oct  2 2008 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) - mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) - yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) - yyyymmdd
SELECT convert(varchar, getdate(), 113) - dd mon yyyy hh:mm:ss:mmm
                                        - 02 Oct 2008 11:02:07:577     
SELECT convert(varchar, getdate(), 114) - hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) - yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) - yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) - yyyy-mm-ddThh:mm:ss.mmm
                                        - 2008-10-02T10:52:47.513
- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), -/-, - -) - yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 - yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          - mon yyyy


Thursday, 28 June 2012

JAVASCRIPT KEY CODES

Key Code
backspace 8
tab 9
enter 13
shift 16
ctrl 17
alt 18
pause/break 19
caps lock 20
escape 27
page up 33
page down 34
end 35
home 36
left arrow 37
up arrow 38
right arrow 39
down arrow 40
insert 45
delete 46
0 48
1 49
2 50
3 51
4 52
5 53
6 54
7 55
8 56
9 57
a 65
b 66
c 67
d 68
 
Key Code
e 69
f 70
g 71
h 72
i 73
j 74
k 75
l 76
m 77
n 78
o 79
p 80
q 81
r 82
s 83
t 84
u 85
v 86
w 87
x 88
y 89
z 90
left window key 91
right window key 92
select key 93
numpad 0 96
numpad 1 97
numpad 2 98
numpad 3 99
numpad 4 100
numpad 5 101
numpad 6 102
numpad 7 103
 
Key Code
numpad 8 104
numpad 9 105
multiply 106
add 107
subtract 109
decimal point 110
divide 111
f1 112
f2 113
f3 114
f4 115
f5 116
f6 117
f7 118
f8 119
f9 120
f10 121
f11 122
f12 123
num lock 144
scroll lock 145
semi-colon 186
equal sign 187
comma 188
dash 189
period 190
forward slash 191
grave accent 192
open bracket 219
back slash 220
close braket 221
single quote 222

Wednesday, 27 June 2012

ORA-00568 maximum number of interrupt handlers exceeded

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

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.

Failure to redirect to destination

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 .

Monday, 25 June 2012

HOW TO DISPLAY LAST PAGE OF GRID VIEW ON PAGE LOADING

 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)
{           
           Gridview1.PageIndex = gvJobCard.PageCount - 1;           
}

protected void gvJobCard_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
            gvJobCard.PageIndex = e.NewPageIndex;
            this.bindGridData();
 }


Reply if any query !!
Happy Coding :)

Friday, 22 June 2012

How to display all row data in single string

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

select count(*) vs count(1) in sql server database

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.....  

Wednesday, 20 June 2012

how to store data into excel file using C# and asp.net

 
 
//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.xls", ds);
 
 
Note:Need to attach Microsoft.Excel.*.dll  to project....

parameterize a query containing an IN clause with a variable number of arguments

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           SF            SDF           S        
sf             sfd           sfs           sfd                fsf           sfds     
sf             sfd           sfs           sfd                fsf           sfds     
gddg         dgd           dgd           dg               dtge          dgf      
gddg         dgd           dgd           dg               dtge          dgf      










Tuesday, 19 June 2012

how to describe table in sql server 2008

In 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],
       DATA_TYPE + '(' + CASE
                                    WHEN DATA_TYPE = 'varchar' or DATA_TYPE = 'char' THEN
                                      CASE
                                        WHEN Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5)) = -1 THEN 'Max'
                                        ELSE Cast(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(5))
                                      END
                                    WHEN DATA_TYPE = 'decimal' or DATA_TYPE = 'numeric' THEN
                                      Cast(NUMERIC_PRECISION AS VARCHAR(5))+', '+Cast(NUMERIC_SCALE AS VARCHAR(5))
                                    WHEN DATA_TYPE = 'bigint' or DATA_TYPE = 'int' THEN
                                      Cast(NUMERIC_PRECISION AS VARCHAR(5))
                                    ELSE ''
                                  END + ')' AS [DataType]
FROM   INFORMATION_SCHEMA.Columns
WHERE  table_name = @TABLENAME
order by ordinal_Position
);
GO

Step 2:  Create Sample Table

once you create the function here is a sample table that I used
create table dbo.yourtable
(columna bigint,
 columnb int,
 columnc datetime,
 columnd varchar(100),
 columne char(10),
 columnf bit,
 columng numeric(10,2),
 columnh decimal(10,2)
 )


Step 3:  Execute function by following query :

select * from describe (TableNme);


3) exec sp_columns [TABLE_NAME]

Ref : StackOverflow
 

Function in SQL Server (How to use function in sql server 2005 / 2008 )

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
 
 

Execution :

 
1)  create table test1(Country nvarchar(15),cont as ([dbo].[whichContinent](country)))
 
2)  insert into test1(country) values ('I')
 
3)  select * from test1
 
 
I refer following link ...
... 





More info..

 


Monday, 18 June 2012

Unicode in SQL SERVER

    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

Saturday, 16 June 2012

What is AUTOPOSTBACK in ASP.NET

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>
                                            </td>



<td align="center" colspan="3">
                                                <asp:TextBox ID="txtDesc" runat="server"></asp:TextBox>
                                            </td>




C# : 
  

        protected void txtID_TextChanged(object sender, EventArgs e)
        {
            BusinessLayer.Test test = new BusinessLayer.Test();
            txtDesc.Text = test.getDesc(txtID.Text); 
        }




 Java script  

method with name __doPostBack (eventtarget, eventargument)
b. Two Hidden variables with name __EVENTTARGET and __EVENTARGUMENT
c. OnChange JavaScript event to the control

 


IsPostBack

When we discuss about autopostback, we should have an understanding of the IsPostBack property of Page class. IsPostBack property is used by the Page to determine whether the page is posted back from the client. If IsPostBack property is false, then the page is loading for the first time, and if false, then the request is because of some event generated by web controls.
IsPostBack is used when we want to load some information when the page loads, for e.g. if we want to load some information from the database and show in the data grid in a page for the first time, then we can load and bind the grid in the page_load when IsPostBack property is false.


try it.. 
Now it send text back to server because AutoPostBack =true 

So ... If it false ...you can check ,.wt happens...
Happy Coding





Monday, 11 June 2012

String or binary data would be truncated. The statement has been terminated

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!!
 

Friday, 1 June 2012

LIKE statment in SQL

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]%'

How to write sql query in Crystal reports

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

the page contains markup that is not valid when attached to master page


-         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...