Storing Multiple Languages in Sequel Server 2000

• To store data in different languagae we need to set the datatype to nchar, nvarchar or ntext .
• Once we set datatype of the required field as above datatype we get a property in the bottom grid for properties named "COLLATION".
• Select Collation
• Then select Windows Collation
• Select the language you want to store

SQL FAQs

What are the all different types of Joins in SQL Server 2000, Anybody can explain each join with definition..
Different Types Of JOINS:

     Most of the joins you will come across are based on
equality, with the equijoin being the most dominant.
 In this chapter you learned about equijoins; there are
other types of joins you must become familiar with, most
notably the self-join, the nonequijoin, and the outer join.

Equijoin or Inner Join (Equality) : Traditional comma-
separated join or ANSI JOIN syntax (including optional
INNER keyword).
Natural Join (Equality) : NATURAL JOIN keyword.
Cross-Join or Cartesian Product (No join condition):
Traditional comma-separated with the missing join condition
in the WHERE clause or CROSS JOIN keyword.
Self-Join (Equality): Equijoin or Inner Join.
Outer Join (left, right, full):(Equality and extending the
result set): "Complex Joins" OUTER JOIN keywords or outer
join operator(+).
Non-Equijoin (Nonequality of values): "Complex Joins"
Traditional comma-separated join or ANSI join syntax with
the ON clause.
The join criteria is not based on equality.

What are the all different types of Joins in SQL Server
2000, Anybody can explain each join with definition.
In SQL Server 2000 we have three types of joins
1. Inner join
2. Outer Join
3. Cross Join
1. Inner Join: Inner Join is the default type of join, it
will producesses the result set, which contains matched
rows only.
syntax: select * from table1table2

2. Outer Join: Outer join produces the results, which
contains matched rows and unmatched rows.
here we have three types of joins,
1.Left Outer Join 2.Right Outer Join 3.Full Outer Join
Left Outer Join: Left Outer Join producesses the results,
which contains all the rows from Left table and matched
rows from Right Table.
syntax: select * from table1table2

Right Outer Join: Right Outer Join producesses the
resultset, which contains all the rows from right table and
matched rows from left table.
syntax:select * from table1table2

Full Outer Join: Full Outer Join producesses the resultset,
which contains all the rows from left table and all the
rows from right table.
syntax:select * from table1table2

3.Cross Join: A join without having any condition is known
as Cross Join, in cross join every row in first table is
joins with every row in second table.
syntax: select * from table1table2

Self Join: A join joins withitself is called self join
working with self joins we use Alias tables.

What is the STUFF function and how does it differ from the
REPLACE function?
STUFF - Delete a specified length of characters and insert
another set of characters at a specified starting point.
For example: SELECT STUFF('asdfghjkl', 3, 5, 'XYZ')
          Go
here the result set is:

          asXYZkl

REPLACE - Replace all occurrences of the second given string
expression in the first string expression with a third
expression.
For Example: SELECT REPLACE('Abhay', 'a', 'KKT')
Here the result set is:
KKTbhKKTy



Can you link only other SQL Servers or any database servers
such as Oracle?
We can link any server provided we have the OLE-DB provider
from Microsoft to allow a link. For Oracle we have a OLE-DB
provider for oracle that microsoft provides to add it as a

What does it mean to have quoted_identifier on? What are the
implications of having it off?

when QUOTED_IDENTIFIER option is specified as ON ,the
identifiers are placed with in double quotation and
constants are placed with in single quotation.
when QUOTED_IDENTIFIER is OFF,the identifiers are not placed
with in double quotation and we must follow all T-SQL  rules
for the identifiers.
linked server to the sql server group.

What is RAID and what are different types of RAID 
configurations?
RAID stands for Redundant Array of Inexpensive Disks, used
to provide fault tolerance to database servers. There are
six RAID levels 0 through 5 offering different levels of
performance, fault tolerance.

Difference between Function and Procedure-in general?
Function can return at most one value at a time while
procedure can return more than one value at a time You can
use function within Sql Statement and you cannot use stored
procedure with in Sql Statement

wht is normalization?can u explain me in detail?
Normalization is the process of efficiently organizing data
in a database. There are two goals of the normalization
process: eliminating redundant data and ensuring data
dependencies make sense

For more FAQs log on to : http://www.allinterview.com/showanswers/36608.html

Content Management System

A content management system (CMS) is a system used to manage the content of a website. Typically, a CMS consists of two elements: the content management application (CMA) and the content delivery application (CDA). The CMA element allows the content manager or author, who may not know Hypertext Markup Language (HTML), to manage the creation, modification, and removal of content from a Web site without needing the expertise of a Webmaster. The CDA element uses and compiles that information to update the Web site. The features of a CMS system vary, but most include Web-based publishing, format management, revision control, and indexing, search, and retrieval.

The Web-based publishing feature allows individuals to use a template or a set of templates approved by the organization, as well as wizards and other tools to create or modify Web content. The format management feature allows documents including legacy electronic documents and scanned paper documents to be formatted into HTML or Portable Document Format (PDF) for the Web site. The revision control feature allows content to be updated to a newer version or restored to a previous version. Revision control also tracks any changes made to files by individuals. An additional feature is indexing, search, and retrieval. A CMS system indexes all data within an organization. Individuals can then search for data using keywords, which the CMS system retrieves.

A CMS system may also provide tools for one-to-one marketing. One-to-one marketing is the ability of a Web site to tailor its content and advertising to a user's specific characteristics using information provided by the user or gathered by the site (for example, a particular user's page sequence pattern). For example, if you visit a search engine and search for "digital camera," the advertising banners will advertise businesses that sell digital cameras instead of businesses that sell garden products.

Two factors must be considered before an organization decides to invest in a CMS. First, an organization's size and geographic dispersion must be considered especially if an organization is spread out over several countries. For these organizations, the transition to CMS is more difficult. Secondly, the diversity of the electronic data forms used within an organization must be considered. If an organization uses text documents, graphics, video, audio, and diagrams to convey information, the content will be more difficult to manage.

Dropdown in Datagrid

Let’s first take a look at the ASPX markup to create the grid.

We have a DropDownList in the header declared as HeaderDropDown, and a DropDownList in the Item template declared as ItemDropDown. Notice we set the AutoPostBack property to true. Setting AutoPostBack to true allows the form to post back to the server and raise an event each time the user changes a selection in the DropDownList control. We also assign an event handler for the SelectedIndexChanged event. Notice we are sharing the same event handler (DropDown_SelectedIndexChanged) for all DropDownList controls. We will see later in the code how we can still identity the exact control firing the event.

Before we look at the SelectedIndexChanged event handlers, let’s think about populating the drop down controls with data for the user to select. When the DataGrid binds to a data source it will create a DropDownList control to place in the header row, and also a DropDownList for each row of data rendered. We need to catch when ASP.NET creates these controls so we can populate them with data for the user to select from. The best place to do this is during the ItemDataBound event. You can see in the ASPX above we are handling this event using the DataGrid1_ItemDataBound method, shown below.

protected void DataGrid1_ItemDataBound(object sender, DataGridItemEventArgs e)

{

   if(e.Item.ItemType == ListItemType.AlternatingItem ||

      e.Item.ItemType == ListItemType.Item)

   {

      string[] options = { "Option1", "Option2", "Option3" };

      DropDownList list = (DropDownList)e.Item.FindControl("ItemDropDown");

      list.DataSource = options;

      list.DataBind();

   }

   else if(e.Item.ItemType == ListItemType.Header)

   {

      string[] options = { "OptionA", "OptionB", "OptionC" };

      DropDownList list = (DropDownList)e.Item.FindControl("HeaderDropDown");

      list.DataSource = options;

      list.DataBind();

   }

}

ItemDataBound occurs after a DataGridItem is bound to the grid. The event fires for each row, including the header and the footer rows. We know if the item belongs to the header, the footer, or one of the items by checking the Item.ItemType property of the event. Rows of data will always have a ListItemType of Item or AlternatingItem.

We use the FindControl method to obtain a reference to the DropDownList control for each row. We specify the control to find by name. For the header remember we specified a name of “HeaderDropDown”, while rows with data will have a name of “ItemDropDown”.

We have an array of strings to represent data for the DropDownList control to bind against. Note that this event will fire for each row of the grid, so you’ll want to make sure this method performs well. You would not want to perform a database query each time the event fires. In this example, the DropDownList control in the header binds against a different set of strings than the DropDownList controls in the item rows.

Whenever the user modifies any of the DropDownList selections in our grid, the DropDown_SelectedIndexChanged event will fire. Remember, we assigned the same event handler for all of our lists, and we set the AutoPostBack property to true so a new selection should post back to the server and raise the event immediately. The event handler is shown below.

protected void DropDown_SelectedIndexChanged(object sender, EventArgs e)

{

   DropDownList list = (DropDownList)sender;

   TableCell cell = list.Parent as TableCell;

   DataGridItem item = cell.Parent as DataGridItem;

   int index = item.ItemIndex;

   string content = item.Cells[0].Text;

   Response.Write(

         String.Format("Row {0} contains {1}", index, content)

      );

}

First, notice the sender parameter will be the DropDownList control modified by the user. By casting the object reference to a DropDownList we get a reference to the modified control and can see what the user has selected.

Secondly, notice the DropDownList control’s parent will be a TableCell of the grid. The parent of the TableCell will be a DataGridItem. Once we have a reference to the DataGridItem, we can see which row was selected by using the ItemIndex property. (Note: ItemIndex will be -1 for the header row). With the DataGridItem we can also inspect the values of other cells in the row.

The following image shows the form in action just after we made a new selection to the list in Cheryl's row.

Once you know the control invoking the event you can find almost any other piece of information you need about the grid or the underlying data source by getting to the DataGridItem object and inspecting the ItemIndex property or the collection of TableCells. With this information in hand, adding controls to the rows of your DataGrid should become a straightforward process.

Pop Up in a Datagrid

Creating a Popup Window Details Grid in a DataGrid

This articles topic came from the suggestion of a true DotNetJunkie. He originally sent an email to us asking for an example illustrating how to make a HyperLinkColumn in a DataGrid spawn events that would pop up a new window with details of the row that the user clicked on. Before we could anwser his email he had already emailed us back explaining that he had found a way to do it and suggested a tutorial of his discovery. So, here it is! As with most of our articles, this simplifies the task, but easy examples of coding techniques is what gives developers ideas for more complex senerios.

This example contains two WebForms and one external style sheet (All code is included in the download) - The first WebForm contains a DataGrid with a list of products from the Northwind database and a hyperlink that states "SeeDetails". Once this link is clicked the JavaScript Window.Open method is used to open a new window. Within the URL is a Query String parameter of the ProductID of the product the user wants the details for. In the second WebForm there is another DataGrid that shows the user all the details for the chosen product. The stylesheet is used just because its cleaner to use than inline styles. So lets take a look at WebForm1.aspx and WebForm1.aspx.cs

WebForm1.aspx

Create a datagrid .In the datagrid under columns section create bound columns and then create a hyperlink where datatextformatstring=”Show Details…”
DataTextField and DataNavigateURLField will be the primary key of your database
DataNavigateUrlFormatString="javascript:varwin=window.open('WebForm2.aspx?ProductID={0}',null,'width=692,height=25');" HeaderText="See Details" HeaderStyle-CssClass="HEADERSTYLE" ItemStyle-CssClass="ITEMSTYLEHYPERLINK" />


WebForm1.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient ;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace HowTos.DataGrid.PopupWindow
{

public class WebForm1 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;

#region User Defined Code

private void Page_Load(object sender, System.EventArgs e)
{

if ( ! this.IsPostBack )
this.BindData();

}

protected void BindData()
{

SqlCommand cmd = new SqlCommand( "SELECT TOP 10 ProductID, ProductName FROM Products", con("Server=LocalHost; DataBase=Northwind; TRUSTED_CONNECTION=TRUE"));
this.DataGrid1.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
this.DataGrid1.DataBind();

}

protected SqlConnection con(System.String ConnectionString )
{

SqlConnection c = new SqlConnection( ConnectionString );
c.Open();
return c;

}

#endregion

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)
{

InitializeComponent();
base.OnInit(e);

}

private void InitializeComponent()
{

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

}
}

There isn't really anything out of the ordinary on here except for the details of DataNavigateUrlFormatString You'll notice that I actually have JavaScript window.open directly in it (Note: I could have just as easily created an external .js file or within the WebForm - I used it inline for simplicity. This JavaScript code should look familiar to all so I won't go into discussion about it. Essentially, it will open a new browser with the page WebForm2.aspx with a query string parameter ProductID. This value is the ProductID from our data source. So let's look at WebForm2.aspx and WebForm2.aspx.cs

WebForm2.aspx

In the second webform create a datagrid and then create a javascript based hyperlink
(a href="JavaScript:window.close()">close window<)

WebForm2.aspx.cs
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient ;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;

namespace HowTos.DataGrid.PopupWindow
{

public class WebForm2 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;

#region User Defined Code

private void Page_Load(object sender, System.EventArgs e)
{
if ( ! this.IsPostBack )
this.BindData();
}

protected void BindData()
{
SqlCommand cmd = new SqlCommand( "SELECT * FROM Products WHERE ProductID = @ProductID", con("Server=LocalHost; DataBase=Northwind; TRUSTED_CONNECTION=TRUE"));
cmd.Parameters.Add(new SqlParameter("@ProductID", SqlDbType.VarChar, 200));
cmd.Parameters["@ProductID"].Value = Request["ProductID"].ToString();
this.DataGrid1.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection);
this.DataGrid1.DataBind();
}

protected SqlConnection con(System.String ConnectionString )
{

SqlConnection c = new SqlConnection( ConnectionString );
c.Open();
return c;

}

#endregion

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)
{

InitializeComponent();
base.OnInit(e);

}

private void InitializeComponent()
{

this.Load += new System.EventHandler(this.Page_Load);

}

#endregion

}
}
WebForm2.aspx is also quite simple.
The only object that resides on the page is a DataGrid which is bound to a SqlDataReader. The Reader gets the data for the product based on the query string parameter of the ProductID value. Let's quickly look at the CSS (Cascading Style Sheet) file and then below that contains a figure illustrating WebForm1.aspx when it is first rendered:

StyleSheet1.css
/* Style Sheet */
BODY
{
margin-left: 0;
margin-top:10;
}
.HEADERSTYLE
{
background-color: #3a6ea5;
color: #FFFFFF;
font-weight:bold;
}

.ITEMSTYLEDEFAULT
{
background-color: #C0C0C0;
color: #000000;
font-weight: bold;
}

.ITEMSTYLEHYPERLINK {
background-color: #C0C0C0;
color: #000000;
font-weight: bold;
}

A:LINK
{
color: #000000;
}

A:VISITED
{
color: #000000;
}

A:HOVER
{
color: #3a6ea5;
}

The hyperlink for details is in the far right row. The following figure contains an image of WebForm2.aspx with the details:

Now it's time to make fun from what we have learnt

We will make an application that makes us guess a number between 1 and 10 and making the computer as the jury. The computer will speak out whether the number we have input is too high, too low or correct. Firstly, create a new Website on ASP.NET web developer and build a simple interface like below.

Also, include a label, name it as lblGuessedNumber and make it as invisible.

This will store a randomly generated number.

Add the Speech Object Library Object as reference.

To generate a random number, we make use of the Random class. On Page_Load event we place the following code snippet.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles

Try

If Not IsPostBack() Then

Dim number As Integer

Dim rnd As New Random() ' Random number generator

number = rnd.Next(1, 10) ' Generates random number between 1 and 10

' Storing the random number in the hidden label

lblGuessedNumber.Text = number
End If

Catch ex As Exception

Throw ex

End Try

End Sub

Notice that the code is placed inside the Not IsPostBack() if statement as we do not want to generate a random number on every postback. Now, to validate the number that the user has input, we will make use of the following code snippet in the button's click event.

Protected Sub btnGuess_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnGuess.Click

Try

' Variables declaration

Dim voice As New SpVoice

Dim userNumber As Integer

Dim computerNumber As Integer ' Parse the numbers and store then in integer variables

userNumber = Integer.Parse(txtNumber.Text)

computerNumber = Integer.Parse(lblGuessedNumber.Text) ' Speak the verdict

If (userNumber <>

voice.Speak(txtNumber.Text & " is too low!")

ElseIf (userNumber > computerNumber) Then

voice.Speak(txtNumber.Text & " is too high!")

Else

voice.Speak("Cool man! You guessed the correct number!")

End If

Catch ex As Exception

Throw ex

End Try

End Sub

Make ASP.Net Speak Typed Text

Let's start with Microsoft's Speech API (SAPI)

SAPI has a text-to-speech engine (TTS) that we will make use of. In short, SAPI takes text as input and output an audible speech using TTS engine. By the way, every machine with Windows XP has SAPI and TTS.

SAPI is a COM component that needs to be referenced in the project.

Coding Voice Application

First of all, open Microsoft Visual Web Developer and create a new Website. Use the Visual Basic as language. Add a reference to your project. Website > Add Reference > COM > Microsoft Speech Object Library.
Now throw in a text box and a button on your webpage. It may look as below.

The scenario we want to do is to make the computer speak what has been written in the textbox by clicking on the "Speak" button. Double-click on the button to add some code for the button. To access the DLL function, we have to import the namespace SpeechLib as below:

Imports SpeechLib

The next step is to create a SpVoice object.

Dim voice As New SpVoice()

And finally, you can make the computer speak for you!

voice.Speak(txtSpeech.Text)

Below is the complete little code snippet.

Imports SpeechLib

Partial Class _Default

Inherits System.Web.UI.Page

Protected Sub btnSpeak_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnSpeak.Click

Try
Dim voice As New SpVoice() ' Creates the voice object

voice.Speak(txtSpeech.Text) ' Speak whatever is written in the textbox

Catch ex As Exception

Throw ex

End Try

End Sub

End Class

Now, you can play around with the voice object by exploring the different methods and properties that the voice object can expose.

To speed up the rate at which the speaker talks, we can do:
voice.Rate = 10