Posts

Showing posts from May, 2012

What is difference between Primary Key and Unique key?

Q: What is difference between Primary Key and Unique key, What are the different types of key used in sql server and what is its use what is rollup and cude functions? Ans : A UNIQUE constraint is similar to PRIMARY key, but you can have more than one UNIQUE constraint per table. When you declare a UNIQUE constraint, SQL Server creates a UNIQUE index to speed up the process of searching for duplicates. In this case the index defaults to NONCLUSTERED index, because you can have only one CLUSTERED index per table. * The number of UNIQUE constraints per table is limited by the number of indexes on the table. * Contrary to PRIMARY key UNIQUE constraints can accept NULL but just once. If the constraint is defined in a combination of fields, then every field can accept NULL and can have some values on them, as long as the combination values is unique.

How to insert system date in SQL Server 2008

First create table : 1) create table Test(id int identity(1,1),name varchar(20),V_DATE datetime)   use getDate()  function :  insert into  Test(name,vDate)  values('Sw',getDate()) select * from Test Output : 1    Nihar     NULL 2    Rohan    NULL 3    Rohan    NULL 4    Sw         2012-05-30 15:57:35.230 Different Date & time Fornat read this : select id,name,CONVERT(varchar,vDate,104) from Test   --Date : 30.05.2012 select id,name,CONVERT(varchar,vDate,1) from Test       --Date : 05/30/12 select id,name,CONVERT(varchar,vDate,2) from Test       --Date : 12.05.30 select id,name,CONVERT(varchar,vDate,3) from Test        --Date : 30/05/12 select id,name,CONVERT(varchar,vDate,4) from Test        --Date : 30.05.12 select id,name,CONVERT(varchar,vDate,0) from Test        --Date : May 30 2012  3:57PM select id,name,CONVERT(varchar,vDate,5) from Test        --Date : 30-05-12 select id,name,CONVERT(varchar,vDate,6) from Test          --Date : 30 May 12 select id,

SQL Server 2008 Data types

Today I'll will mention the Microsoft SQL Server 2008 Fileds data types, you need to choose the right data type for the database fileds for more speed efficency, and i think the most important thing the fundamental of the database itself. Let's begin: char: the char datatype is ifxed in length, if you enter fewer that than the number of character defined the remaining length will be space filled to the right; use this data type when the column dat is to be of fixed length, which tends to be the case foe customer IDs and bank account IDs. nchar: the nchar type is exactly like char, but will hold characters in unicode formate rather than ANSI; infact SQL Server allocates double the space internally, so unless there is a need in your database to hold this type of character, it's easier to stick with ANSI. Note: ANSI character sets only hold up to 256 char. Unicode character sets only hold up to 65,536 chr. varchar: the varchar data type holds a

ALTER TABLE STATEMENTS

SQL ALTER TABLE Syntax Add a column in a table , use the following syntax:         ALTER TABLE table_name         ADD column_name datatype   Delete a column in a table , use the following syntax (notice that some database systems don't allow deleting a column):          ALTER TABLE table_name         DROP COLUMN column_name   Change the data type of a column in a table , use the following syntax: 1) My SQL / SQL Server / MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype   2) Oracle: ALTER TABLE table_name MODIFY column_name datatype     You can try it...  Hopes it will helps you

Allow Only Numbers in a Textbox

It can be possible using .net and javascript .Net :         You have asp control to validate you web part. JavaScript :            In this , you need to write code in <Script> Tag like <script language= "JavaScript" > function onlyNumbers(evt) { var e = event || evt; var charCode = e.which || e.keyCode; if (charCode > 31 && (charCode < 48 || charCode > 57)) return false; return true; } </script>

Debugging tricks with Visual Studio

Introduction Debugging is important of solving,correcting methods in VS. Some trick anf facts about debugging .MY friend send me this link ,you have refer  following link. It will help you .  Debugging is a major part of the development lifecycle. Sometimes challenging, sometimes puzzling, sometimes annoying, one for sure - it is unavoidable for any not-so-trivial program. The progress of debugging tools over the last years has made many debugging tasks much easier and less time-consuming. This article summarizes ten debugging tricks and techniques that can save you a lot of time when using Visual Studio. http://www.codeproject.com/Articles/359801/10plus-powerful-debugging-tricks-with-Visual-Studi

Database session using V$SESSION in Oracle

V$SESSION This view lists session information for each current session. Column Datatype Description SADDR RAW(4 | 8) Session address SID NUMBER Session identifier SERIAL# NUMBER Session serial number. Used to uniquely identify a session's objects. Guarantees that session-level commands are applied to the correct session objects if the session ends and another session begins with the same session ID. AUDSID NUMBER Auditing session ID PADDR RAW(4 | 8) Address of the process that owns the session USER# NUMBER Oracle user identifier USERNAME VARCHAR2(30) Oracle username COMMAND NUMBER Command in progress (last statement parsed); for a list of values, see Table 7-5 . These values also appear in the AUDIT_ACTIONS table. OWNERID NUMBER The column contents are invalid if the value is 2147483644 . Otherwise, this column contains the identifier of the user who owns the migratable session. For operations using Parallel Slaves, inte

@@transcount in sql server

Returns the number of BEGIN TRANSACTION statements that have occurred on the current connection . For ex : PRINT @@TRANCOUNT -- The BEGIN TRAN statement will increment the -- transaction count by 1. BEGIN TRAN PRINT @@TRANCOUNT BEGIN TRAN PRINT @@TRANCOUNT -- The COMMIT statement will decrement the transaction count by 1. COMMIT PRINT @@TRANCOUNT COMMIT PRINT @@TRANCOUNT --Results 0 1 2 1 0 Ex2 :- >   Step 1: create table a(m varchar ( max ), i int ) go   Step2 : declare @cnt int --Explicit transaction begin tran set @cnt=@@trancount insert into a select 'inside' , @@trancount insert into a select 'inside cnt' , @cnt commit tran set @cnt=@@trancount --Implicit transcation insert into a select 'outside' , @@trancount insert into a select 'outside cnt' , @cnt     Step 3 :   select * from a   Output : inside    2 inside cnt             1 outside 2 outside cnt

Rowcount (@@rowcount) in Sqlserver

@@rowcount  returns number of rows were after affteced after executing sql query/statement. If the number of rows is more than 2 billion, use ROWCOUNT_BIG Syntax :: @@rowcount Example : 1)  Here it will shows you warning like (Here i  have only) update [TestApp].[dbo].[UpdatedProducts] set [ProductID]=4 where [ProductID] =6 if @@rowcount=0 PRINT 'Warning: No rows were updated'; GO Output : (0 row(s) affected) Warning: No rows were updated 2) here my productId=5 exist in table , so it shows update [TestApp].[dbo].[UpdatedProducts] set [ProductID]=4 where [ProductID] =6 if @@rowcount=0 PRINT 'Warning: No rows were updated'; GO Output : (1 row(s) affected)

EXCEPT OR NOT EXIST IN ORACLE / SQL SERVER

Query : SELECT [Id] , [Title] , [Director] , [boxOfficeTotal] FROM [TestApp] . [dbo] . [MovieDT1] where boxOfficeTotal > 3000 Except SELECT , [Title] , [Director] , [boxOfficeTotal] FROM [TestApp] . [dbo] . [MovieDT1] where boxOfficeTotal > 4000 Here Output of ths query like Value between More than 3000 but less than 4000 1)  Except or Not Exist  replaces  use of NOT IN(...) Instead of using NOT IN in statment query , you can use EXCEPT or NOT EXIST  2) For better performance ,it works more better than left join or NOT IN

How to Import Excel sheet data to Oracle

Without Toad or an tool : Save the file as .csv file, then load the file into oracle using sqlLoader.Multiple sheets does not matter how the records are listed matters and how that should go into the db matters. Now from TOAD go to: 1. Database-->Import-->Table Data 2. Select the proper schema and Table name where data should be inserted 3. Click "Execute Wizard" and specify the type of file to be imported 4. Select "Excel File (.xls)" and click next 5. Give the path of the file, and again click next

ROWNUM IN ORACLE

nOTE : [ reference :http://blog.lishman.com/2008/03/rownum.html]  ROWNUM is an Oracle pseudo column which numbers the rows in a result set. SELECT rownum, table_name FROM user_tables; ROWNUM TABLE_NAME ------------- ----------------- 1 EMP 2 DEPT 3 BONUS 4 SALGRADE 5 DUMMY 5 rows selected Here is a summary of how ROWNUM can be used. Limiting Rows ROWNUM can be used to limit the number of rows returned by a query in a similar way to LIMIT in Postgres and MySql, TOP in SQL Server and FETCH FIRST in DB2. SELECT rownum, table_name FROM user_tables WHERE rownum <=3; ROWNUM TABLE_NAME ------------- ----------------- 1 EMP 2 DEPT 3 BONUS 3 rows selected ROWNUM with DML The use of ROWNUM is not restricted to select statements. It can be used with DML statements

How to set equal width of columns in Gridview

Suppose you are binding data field with some templates like checkbox,radiobutton,textbox etc... Then it will works using < Headerstyle width="6%"> E'g : ...// Some your grridview code <asp:gridview> ....// Some data fields .. ..  <asp:TemplateField Visible="true" HeaderText="Disagree">                         <HeaderStyle Width="2%"/>                             <ItemTemplate>                                 <asp:RadioButton ID="rdbtn3" runat="server" GroupName="rdGroup" OnLoad="RadioButtonGroup_load" />                             </ItemTemplate>                         </asp:TemplateField>  .... </Asp:gridview> If stll have problem, then post your code in comment... But it will helps you Happy Coding  :) :)

How to use @@Identity or IDENTITY ?

Syntax IDENTITY [ ( seed , increment ) ] Here,   seed :-  this is for you starting point of number.Means from which number you want to start table data increment (e'g  1,2, -1 ,0 ,100,1001  etc) increment :-   You can set increment value as per you want (1,2,3...) how much difference you want between two numbers Note : Identity column 'i' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable. step 1 :   Create table in database create table #CheckIdentity(i Int Identity,j Int) step 2 : Insert Values in that database insert #CheckIdentity select 1 step 3:  Display Values from table select * from #CheckIdentity Output : --------- I        J ---------- 1       1 2       1 3       1 Reference :  http://www.simple-talk.com/sql/t-sql-programming/identity-columns/ Link help you lot

How to enable script on browsers

Enable you Javascript of Browsers !! IF you don't have idea , follow the steps On browser : Tools -> Internet Options Security -> Click on " Custom level " tab Scroll down , thr you will get the Scripting tabs Enable that close browser , then again check it

Multiple radiobutton without radiobuttonlist in gridview

storing Multiple selected radiobutton's of gridview?  ASPX : < asp : GridView ID = "gvSurvey" runat = "server" CellPadding = "4"   OnRowDataBound = "gvSurvey_RowDataBound" BorderWidth = "2" AutoGenerateColumns = "False" EmptyDataText = "No data Available" GridLines = "None" HorizontalAlign = "Center" ForeColor = "#333333" Font - Names = "Verdana" ShowFooter = "True" > < RowStyle BackColor = "White" /> < Columns > < asp : BoundField DataField = "HEADER" HeaderText = "Description" ItemStyle - HorizontalAlign = "Left" FooterStyle - HorizontalAlign = "Left" > < ItemStyle HorizontalAlign = "Left" /> </ asp : BoundField > < asp : TemplateField Visible = "true" HeaderText = "Strongly Disagree" > < ItemTempla

The result of selection formula must be a boolean in Crystal Report

You're attempting to return a date in a selection formula (Whether it is a Record, Group, or Saved Data selection formula) and CR doesn't know how to handle that situation. It must be a boolean because selection formulas essentially tell the report to only include data where the formula evaluates to TRUE. ... Instead of  that ,you can directly click "Use Editor" ..  Without selection or group formula you will get all type operators. Hope this will help you !!

Multiple Radiobuttons in gridview

ASPX :   <asp:TemplateField Visible="true" HeaderText ="Strongly Disagree">                                                     <ItemTemplate>                                                            <%-- <asp:RadioButton ID="rdbtn1" runat ="server" />--%>                                                            <input name="MyRadioButton1" type ="radio" value = "'<%# Eval("SQTN_NO") %>'" />                                                     </ItemTemplate>                                                 </asp:TemplateField>                                                 <asp:TemplateField Visible="true" HeaderText ="Agree">                                                     <ItemTemplate>                                                              <%-- <asp:RadioButton ID="rdbtn2" runat ="

The debugger cannot continue running the process.Process was terminated

While debugging your code step by step , you will find the line , from where error redirecting. If you are using  " ToString() " anywhere in that file ,please remove that . Instead of the ,you can use Value / Text . It works fine. ............ If you were not used ToString() any where in program , then reload project copy by removing completely.

Test you ODP.NET connection with following program

using System; using Oracle.DataAccess.Client; namespace NoOraClient {     class Program     {         static void Main(string[] args)         {             //You need to enter a valid Oracle connection string, below is the format             string connectionString = "user id=USERID;password=PASSWORD;" +                 "data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=IPorSERVERNAME)" +                 "(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ValidSID)))";             using (OracleConnection connection = new OracleConnection())             {                 connection.ConnectionString = connectionString;                 try                 {                     connection.Open();                     Console.WriteLine("Connection Successful!");                     Console.ReadLine();  // stops the console from closing until you hit the ENTER key                 }                 catch (OracleException ex)                 {                  

Method Hiding

Method Hiding: In some situation we may want to have a method in the base class which can be implemented in the derived class independent of the method in the base class i.e we may want to have its new version altogether In the following code i add a method Hello() in the base class and in the derived class give a completely new definition to the same method by preceding it with 'new' keyword using System; using System.Collections.Generic; using System.Text; namespace ConsoleApplication1 {            public class VirtualDemo             {                   public virtual double Area( double r)                   {                         return r * r;                   }                    public void Hello()                   {                         Console .WriteLine( "Hello in Base Class" );                   }             }          public class A : VirtualDemo          {                public override doubl