Yash.net

2012-07-01T12:50:00.000-07:00

For common MS-WORD Shortcut keysCode for the Shopping cart without having database its basically for small applications where you have to be fast and you have small no. of items to be stored

source code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Web Cart </title>
</head>
<body>
<form id="form1" runat="server">
<div>
 <asp:Label ID="Label1" runat="server" Height="21px" Text="Demo for Students By YASH"
Width="288px"></asp:Label><br />
<table>
<tr>
<td style="width: 100px">
<asp:DropDownList ID="DropDownList1" runat="server" Width="198px">
<asp:ListItem Value="25.50">LUX</asp:ListItem>
<asp:ListItem Value="45.60">MEDIMAX</asp:ListItem>
<asp:ListItem Value="34.75">CIBACA</asp:ListItem>
<asp:ListItem Value="43.50">CLOSEUP</asp:ListItem>
</asp:DropDownList></td>
<td style="width: 100px">
<asp:Button ID="btnAdd" runat="server" Text="Add to Cart" OnClick="btnAdd_Click" /></td>
</tr>
<tr>
<td colspan="2">
</td>
</tr>
<tr>
<td colspan="2" style="height: 176px">
<asp:GridView ID="GridView1"
ShowFooter="true" DataKeyNames="ProductId"
AutoGenerateColumns="false" runat="server">
<Columns>
<asp:BoundField DataField="Productid" HeaderText="Product Id" />
<asp:BoundField DataField="ProductName" FooterText="Total" HeaderText="Product Name" />
<asp:TemplateField HeaderText="Unit Price" FooterStyle-Font-Bold="True">
<ItemTemplate>
<%# GetUnitPrice(decimal.Parse(Eval("UnitPrice").ToString())).ToString("N2") %>
</ItemTemplate>
<FooterTemplate>
<%# GetTotal().ToString("N2") %>
</FooterTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
</td>
</tr>
</table>

</div>
</form>
</body>
</html>

C# code

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
private void DataTable()
{
DataTable MyDT = new DataTable();
DataRow MyRow;
if (ViewState["DataTable"] == null)
{
MyDT.Columns.Add("Productid", System.Type.GetType("System.Int32"));
MyDT.Columns.Add("ProductName");
MyDT.Columns.Add("UnitPrice", System.Type.GetType("System.Decimal"));
MyRow = MyDT.NewRow();
MyRow[0] = MyDT.Rows.Count + 1;
MyRow[1] = DropDownList1.SelectedItem.Text;
MyRow[2] = DropDownList1.SelectedItem.Value;
MyDT.Rows.Add(MyRow);
}
else
{
MyDT = (DataTable)ViewState["DataTable"];
MyRow = MyDT.NewRow();
MyRow[0] = MyDT.Rows.Count + 1;
MyRow[1] = DropDownList1.SelectedItem.Text;
MyRow[2] = DropDownList1.SelectedItem.Value;
MyDT.Rows.Add(MyRow);
}
ViewState["DataTable"] = MyDT;
GridView1.DataSource = MyDT;
GridView1.DataBind();
}

protected decimal TotalUnitPrice;
protected decimal GetUnitPrice(decimal Price)
{
TotalUnitPrice += Price;
return Price;
}
protected decimal GetTotal()
{
return TotalUnitPrice;
}
protected void btnAdd_Click(object sender, EventArgs e)
{
DataTable();
}
}

For common Ms-Word shortcut keys

Www.skvzm.blogspot.com

How to Create Shopping Cart in asp.net(C#)

2012-07-01T03:44:00.001-07:00

This article discusses how to create an order page and shopping cart page using GridView ASP.NET C# and SQL Server

Create three tables:
Create a web Project
Name of the Server can be anything.
You can name anything to your Database.
Create an Images Folder in the project solution
Add some image file into the Images folder

Rename the Default.aspx web page to OrderPage.aspx
Drag and drop GridView object from the toolbox on to the web form.
Create the following GridView Columns and set the GridView AutoGenerateColumn to false.
Change the Header Text to change the name of the columns in GridView

Where AddToCart is a button, Picture ID, Title and Date Added are text fields and PictureURL is Image field.
GridView will look like this:

When the order page is loaded, all the items have to be loaded to the GridView for the user to select, copy the following code to the load page event:

string Sel = "Select * from ItemTable"; 
SqlConnection Con = new SqlConnection(Cn); 
SqlCommand cmd = new SqlCommand(Sel, Con); 
Con.Open(); 
DataTable dt = new DataTable(); 
dt.Columns.Add(new DataColumn("PictureID", typeof(int))); 
dt.Columns.Add(new DataColumn("PictureURL", typeof(string))); 
dt.Columns.Add(new DataColumn("Title", typeof(string))); 
dt.Columns.Add(new DataColumn("DateAdded", typeof(DateTime))); 
SqlDataReader reader = cmd.ExecuteReader(); 
while (reader.Read()) 
{ 
DataRow dr = dt.NewRow(); 
dr["PictureID"] = Convert.ToInt32(reader["PictureId"]); 
dr["PictureURL"] = ResolveUrl("~/Images/" +reader["PictureURL"]); 
dr["Title"] = reader["Title"]; 
dr["DateAdded"] = reader["DateAdded"]; 
dt.Rows.Add(dr); 
} 
Con.Close(); 
GridView1.DataSource = dt; 
GridView1.DataBind();

Where Cn Is the connection string.

Enter some data to the SQL table:
Build and run the application.
The following result will be displayed:
AddToCart button click event:

Set the AddToCart button property.
Add this to the GridView property using the source page or the HTML page
OnRowCommand="GridView1_RowCommand"

Add these lines of code to the page code behind:

protected void GridView1_RowCommand(object sender, GridViewCommandEventArgs e) 
{ 
if (e.CommandName == "AddToCart") 
{ 
int index = Convert.ToInt32(e.CommandArgument); 
GridViewRow row = GridView1.Rows[index]; 
AddShopCart(row.Cells[1].Text.ToString()); 
} 
}

After you click the AddToCart button, you need to move the item to the shopping cart. To do that, you need to write code to make a data entry to OrderTable, since ItemId is unique. Based on ItemId we insert a data entry to the OrderTable.
Before we make an entry to the table we need order Number, we can get the order number fromControlTable since ControlTable holds the last used order number.

The method to insert selected item to shopping cart will get the last used order number fromControlTable, and after inserting the values to the OrderTable will update the order number in theControlTable.

private void AddShopCart(string ItemId) 
{ 
string ord = OrderNumber(); 
if (ord != "Bad order") 
{ 
int ordernumber = Convert.ToInt32(ord); 
ordernumber += 1; 
SqlConnection C_n = new SqlConnection(Cn); 
SqlCommand cm = new SqlCommand("Insert INTO OrderTable VALUES
 ('" + ordernumber + "', '" + ItemId + "', '" + "101" + 
 "', '" + Convert.ToDateTime("2/19/2007") + "','" + "1" + "')", C_n); 
C_n.Open(); 
SqlDataReader dr = cm.ExecuteReader(); 
C_n.Close(); 
UpdateOrderNumber(ordernumber); 
} 
}

The method to get the last used order number.

private string OrderNumber() 
{ 
SqlConnection Or_Cn = new SqlConnection(Cn); 
SqlCommand Or_Cm = new SqlCommand("Select OrderNumber from ControlTable", Or_Cn); 
Or_Cn.Open(); 
SqlDataReader Or_rd = Or_Cm.ExecuteReader(); 
if (Or_rd.Read()) 
{ 
return Or_rd["OrderNumber"].ToString(); 
} 
else 
{ 
return "Bad order"; 
} 
}

The method to update the order number in the ControlTable:

private void UpdateOrderNumber(int UpdatedNumber) 
{ 
SqlConnection Op_Cn = new SqlConnection(Cn); 
SqlCommand Op_Cm = new SqlCommand
 ("Update ControlTable Set OrderNumber=" + UpdatedNumber, Op_Cn); 
Op_Cn.Open(); 
SqlDataReader Op_rd = Op_Cm.ExecuteReader(); 
Op_Cn.Close(); 
}

Add new page to the project and name it ShoppingCart.aspx.
Drag and drop GridView object from the toolbox on to the web form.
Create the following GridView columns and set the GridView AutoGenerateColumn to false.
Where Delete is a button, Picture ID, Title, Price and Date Added are text fields and PictureURL is Image field.
GridView will look like this:

Under page load event, copy the following code:

string Sel = "Select a.* from ItemTable as a INNER JOIN 
  OrderTable as b ON a.PictureId=b.ItemId"; 
SqlConnection Con = new SqlConnection(Cn); 
SqlCommand cmd = new SqlCommand(Sel, Con); 
Con.Open(); 
DataTable dt = new DataTable(); 
dt.Columns.Add(new DataColumn("PictureID", typeof(int))); 
dt.Columns.Add(new DataColumn("Title", typeof(string))); 
dt.Columns.Add(new DataColumn("Price", typeof(string))); 
dt.Columns.Add(new DataColumn("DateAdded", typeof(DateTime))); 
dt.Columns.Add(new DataColumn("PictureURL", typeof(string))); 
SqlDataReader reader = cmd.ExecuteReader(); 
while (reader.Read()) 
{ 
DataRow dr = dt.NewRow(); 
dr["PictureID"] = Convert.ToInt32(reader["PictureId"]); 
dr["Title"] = reader["Title"]; 
dr["Price"] = reader["Price"]; 
dr["DateAdded"] = reader["DateAdded"]; 
dr["PictureURL"] = ResolveUrl("~/Images/" + reader["PictureURL"]); 
dt.Rows.Add(dr); 
} 
Con.Close(); 
GridView1.DataSource = dt; 
GridView1.DataBind();

How to Create Triggers in Sql server

2012-06-29T13:10:00.000-07:00

CREATE TRIGGER Management.ForCustomers
ON Management.Customers
AFTER INSERT
AS
BEGIN
    INSERT INTO Management.DatabaseOperations
    VALUES(default, N'Customers', SUSER_SNAME(), 
        N'Processed a deposit', GETDATE())
END
GO

How to Create Stored Procedures or What is Stored Procedures in sql server

2012-06-29T13:09:00.000-07:00

We have had an introduction to some types of actions that could be performed on a database. These actions were called functions. A function is used to perform a calculation and produce a regular value based on a data type. In fact, the default formula of a function doesn't allow it to produce a list of values. If you want a function that produces a table, you must create atable-valued function, but you may not want to deal with some of its rules. In reality, you cannot create a regular function that returns a SELECT query. Of course, the alternative is to create a view.

Start the computer and log in
Launch Microsoft SQL Server and click Connect
Open the WattsALoan1.sql file (Watts A Loan)
To create the database and its objects, right-click inside the document and click Execute
Close the Query window
In the Object Explorer, expand the Databases node and expand WattsALoan1
Right-click Database Diagrams -> New Database Diagram
When the message box comes up, read it and click Yes
Right-click Database Diagram and click New Database Diagram
Read the message and click Yes
In the dialog box, double-click each table and, when all tables have been added, click Close
Close the diagram
When asked whether you want to save it, click Yes
Set the name to dgmWattsALoan1
Click OK
In the Object Explorer, right-click WattsALoan1 and click New Query
To see a list of payments made on different loans, type the following:
```
SELECT ALL * FROM Loans.Payments;
GO
```
To see the result, on the main menu, click Query -> Execute
Click inside the top section of the Query window and press Ctrl + A

Creating a Stored Procedure

To create a stored procedure:

In the Object Explorer, expand the database for which you want to create the stored procedure, expand its Programmability node, right-click Stored Procedures, and click New Stored Procedure... A Query window with a skeleton syntax would be displayed. You can then modify that code using the techniques we will learn in this lesson
Open an empty Query window associated with the database for which you want to create the stored procedure and display the Templates Explorer. In the Templates Explorer, expand the Stored Procedure node. Drag Create Stored Procedure and drop it in the Query window
Open an empty Query window associated with the database for which you want to create the stored procedure and enter the necessary code

The simplest syntax to create a stored procedure in Transact-SQL is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
Body of the Procedure

To create a stored procedure, start with the CREATE PROCEDURE expression. You can also useCREATE PROC. Both expressions produce the same result. Like everything in your database, you must name your stored procedure:

The name of a procedure can be any string that follows the rules we reviewed for naming the functions
Refrain from starting the name of a stored procedure with sp_ because it could conflict with some of the stored procedures that already ship with Microsoft SQL Server

When creating a stored procedure, you can precede its name by a schema name. After the name of the procedure, type the AS keyword. The section, group of words, or group of lines after the AS keyword is the body of the stored procedure. It states what you want the procedure to do or what you want it to produce. It is important to keep in mind that there are many other issues related to creating a stored procedure but we will ignore them for now.

Probably the simplest procedure you can create would consist of selecting fields from a table. This is done with the SELECT operator. It uses the techniques we reviewed for data selection. For example, to create a stored procedure that would hold a list of students from a table named Students, you would create the procedure as follows:

Here is an example of creating a stored procedure:

CREATE PROCEDURE Registration.GetStudentIdentification
AS
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Registration.Students
END
GO

Although you can use the * to select all fields of a table, instead, you should always create the list of columns, even if you plan to use all columns of a table.

Besides SELECT operations, in a stored procedure, you can perform any of the database operations we have applied so far. These include creating and maintaining records, etc.

Practical Learning: Creating a Stored Procedure

To start a stored procedure, type the following:

CREATE PROCEDURE Management.ShowInformation
AS
    SELECT ALL * FROM Loans.Types;
    SELECT ALL * FROM Accounts.Customers;
GO

To create the stored procedure, on the main menu, click Query -> Execute

Executing a Stored Procedure

After creating a stored procedure, you must store it as an object in your database. To do this visually, on the SQL Editor toolbar, you can click the Execute button . If the code of the stored procedure is right, it would be created:

Also, a new node for its name would be added to the Stored Procedures section of the database.

To get the results of creating a stored procedure, you must execute it (in other words, to use a stored procedure, you must call it). To execute a stored procedure, you use the EXECUTE keyword followed by the name of the procedure. Although there are some other issues related to executing a stored procedure, for now, we will consider that the simplest syntax to call a procedure is:

EXEC/EXECUTE [SchemaName.]ProcedureName

If you have a stored procedure named GetStudentIdentification, to execute it, you would type:

EXECUTE GetStudentIdentification

You can also precede the name of the procedure with its schema. Here is an example:

EXECUTE Registration.GetStudentIdentification;

You can also precede the name of the schema with the name of the database. Here is an example:

EXECUTE rosh.Registration.GetStudentIdentification;

Practical Learning: Executing a Stored Procedure

Click inside the top section of the Query window and press Ctrl + A to select everything
To execute the stored procedure, type the following
```
EXECUTE Management.ShowInformation;
GO
```
To execute the stored procedure, right-click inside the top section of the Query window and click Execute
Click inside the top section of the Query window and press Ctrl + A

Stored Procedure and Permissions

Introduction

To visually manage the permissions of a stored procedure, in the Object Inspector, right-click it and click Properties. In the left frame, click Permissions. In the right section of the dialog box, get a user name and view the available permissions in the bottom section:

As you can see, a stored procedure does not have many permissions on its own. Still, permissions on stored procedures can get complicated because of the interactions a stored procedure has with the objects it uses.

To start, a developer who has to create one ore more stored procedures must have the CREATE PROCEDURE permission on the database that will own the procedure. Also, he or she must have theALTER permissions. You must also coordinate the permissions of the user on the objects used by the stored procedure.

Executing On Behalf of an Account

If a user is lacking some permissions when executing a stored procedure, he cannot use the result(s) of that procedure. As an alternative, when creating a certain stored procedure, you can specify the user account that will be used to execute the procedure. To give this instruction, before the AS keyword permission, type WITH EXECEUTE AS followed by the user name that will be used. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
WITH EXECUTE AS Option
AS
Body of the Procedure

The options are:

If you want your own account to be used, use WITH EXECUTE AS SELF. This is the default for a stored procedure. In this case, your user name will be stored with the procedure and the databaseengine will call it when the sotred procedure is executed.
If you want the account of the user who is executing the procedure to be used, use WITH EXECUTE AS CALLER. In this case, when the user starts executing the procedure, the database engine will check whether the user has the appropriate permissions. If so, the stored procedure would execute. Otherwise, he will be denied.
If you want the account of the owner of the database to be used, use WITH EXECUTE AS OWNER.
If you want to specify the account that will be used when executing the schema, use WITH EXECUTE AS, followed by the user name. Here is an example:

CREATE USER Patricia FOR LOGIN [Central\pkatts];
GO
CREATE PROCEDURE Registration.GetContactInformation
WITH EXECUTE AS N'Patricia'
AS
SET NOCOUNT ON
    SELECT FirstName, LastName, ParentsNames
    FROM Registration.Students;    
GO

Managing Procedures

Introduction

When a stored procedure executes, the database engine must keep sending messages back and forth between the server and the client. These relentless interactions create overhead on the processing and in most cases are not necessary. To avoid them, after the AS keyword, add a SET NOCOUNT ON expression before starting the body of the stored procedure. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
SET NOCOUNT ON
Body of the Procedure

Here is an example:

CREATE PROCEDURE Registration.GetIdentification
AS
SET NOCOUNT ON
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Registration.Students
END
GO

Although you can create and use a stored procedure without specifying a schema, it is recommended that you always use a schema. In fact, you should always create your own schema in your database and create your stored procedure in it.

Encrypting the Code of a Stored Procedure

As mentioned already, when you create a stored procedure, it becomes an object in the Object Inspector and its code is available. If you want to encrypt that code as it is stored in the database, add a WITH ENCRYPTION expression before the AS keyword. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
WITH ENCRYPTION
AS
Body of the Procedure

Here is an example:

CREATE PROCEDURE Registration.GetStudentIdentification4
WITH ENCRYPTION
AS
SET NOCOUNT ON
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM Registration.Students;    
GO

Modifying a Stored Procedure

As a regular Microsoft SQL Server database object, you can modify a stored procedure without recreating it. To do this:

In the Object Explorer, right-click the procedure and click Modify
In the Object Explorer, right-click the procedure, position the mouse on Script Stored Procedure As -> ALTER To -> New Query Editor Window
Open an empty Query window associated with the database that contains the stored procedure. From the Templates Explorer, expand Stored Procedure. Drag the Alter Stored Procedure node and drop it in the empty Query window

In each case, a skeleton code would be generated for you. You can then edit it to create a new version of your stored procedure. After editing the code, you can execute the SQL statement to update the stored procedure.

In Transact-SQL, the basic formula to modify a stored procedure is:

ALTER PROCEDURE [SchemaName.]ProcedureName
AS
Body of Procedure

Recompiling a Stored Procedure

When you create a stored procedure, it considers the state of the database at that time. This includes the columns and records in the tables, the primary keys, the foreign keys, etc. It is not unusual for a user of the database to change any of these details. After such operations have occurred, the stored procedure may not be updated and may show previous details before the changes. One way you can take care of the stored procedure is to ask the database engine to recompile the stored procedure.

To ask the database engine to check the stored procedure and recompile it the next time it is executed, when creating the procedure, before the AS keyword, add a WITH RECOMPILE expression. The formula to follow is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
AS
WITH RECOMPILE
Body of the Procedure

Here is an example:

CREATE PROCEDURE Registration.GetStudentsByGender
WITH RECOMPILE
AS
    SET NOCOUNT ON
    SELECT FirstName, LastName, DateOfBirth, Gender
    FROM   Students
GO

Deleting a Stored Procedure

One of the characteristics of a stored procedure is that it is treated like an object in its own right. Therefore, after creating it, if you don't need it anymore, you can get rid of it.

There are various types of stored procedures, some of which are considered temporary. Those types of procedures delete themselves when not needed anymore, such as when the person who created the stored procedure disconnects from the database or shuts down the computer. Otherwise, to delete a stored procedure, you can use either the Object Explorer or SQL. As mentioned with tables, even if you create a stored procedure using the Object Explorer, you can delete it using SQL and vice-versa.

To delete a stored procedure in the Object Explorer, after expanding its database, its Programmability, and its Stored Procedure nodes, right-click the stored procedure and click Delete. You can also click it in the Object Explorer to select it and then press Delete. The Delete Object dialog box would come up to let you make a decision.

To delete a stored procedure in SQL, the syntax to use is:

DROP PROCEDURE [SchemaName.]ProcedureName

Of course, you should make sure you are in the right database and also that the ProcedureName exists.

Using Expressions and Functions

One of the advantages of using a stored procedure is that not only can it produce the same expression as we saw for data selection but also it can store such an expression to be recalled any time without having to re-write it (the expression). Based on this, you can create an expression that combines a first and a last name to produce and store a full name. Here is an example:

CREATE PROCEDURE Registration.GetStudentIdentification
AS
BEGIN
    SET NOCOUNT ON
    SELECT FullName = FirstName + N' ' + LastName,
           DateOfBirth, Gender
    FROM Registration.Students
END
GO

A stored procedure can also call a function in its body. To do this, follow the same rules we reviewed forcalling functions during data selection. Here is an example of a stored procedure that calls a function:

CREATE PROCEDURE Registration.GetStudentsAges
AS
BEGIN
    SET NOCOUNT ON
    SELECT FullName = FirstName + N' ' + LastName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Gender
    FROM Registration.Students
END
GO

Here is an example of executing the stored procedure:

EXEC rosh.Registration.GetStudentsAges;
GO

Arguments and Parameters

Introduction

All of the stored procedures we have created and used so far assumed that the values they needed were already in a table of the database. In some cases, you may need to create a stored procedure that involves values that are not part of the database. On such a scenario, for the stored procedure to carry its assignment, you would supply it with one or more values.

An external value that is provided to a stored procedure is called a parameter. When you create a stored procedure, you must also create the parameter if you judge it necessary. When a procedure's creation is equipped with a parameter, it is said that the stored procedure takes an argument. A stored procedure can also take more than one argument.

When you execute a stored procedure that takes one or more arguments, you must provide a value for each argument. In this case, you are said to pass a value for the argument. There are cases when you don't have to provide an argument.

Passing Arguments

To create a stored procedure that takes an argument, type the formula CREATE PROCEDURE orCREATE PROC followed by the name of the procedure, then type the name of the argument starting with @. The parameter is created like a column of a table. That is, a parameter must have a name, a data type and an optional length. Here is the syntax you would use:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
@ParameterName DataType
AS
Body of the Procedure

When implementing the stored procedure, in the body of the procedure, you can define what you want to do with the parameter(s). One way you can use a parameter is to run a query whose statement the user would provide. For example, imagine you want to create a stored procedure that, whenever executed, would be supplied with a gender, then it would display the list of students of that gender. Since you want the user to specify the gender of students to display, you can create a stored procedure that receives the gender. Here is an example:

CREATE PROCEDURE Registration.GetListOfStudentsByGender
       @Gdr NVARCHAR(12)
AS
    SELECT FirstName, LastName,
           DateOfBirth, HomePhone, Gender
    FROM   Students
    WHERE  Gender = @Gdr

Practical Learning: Creating a Stored Procedure

To pass arguments to a stored procedure, type the following in the window:

USE WattsALoan1;
GO

CREATE PROCEDURE Loans.SpecifyCurrentBalance
    @PmtDate date,
    @EmplID int,
    @LaID int,
    @PmtAmt money
AS
BEGIN
    -- Get the amount that was lent to the customer
    DECLARE @AmountOfLoan money;
    SET @AmountOfLoan = (SELECT las.FutureValue
                         FROM Loans.Allocations las
                         WHERE (las.AllocationID = @LaID));

    -- If the customer had already made at least one payment,
    -- get the current balance of the customer's account
    DECLARE @CurrentBalance money;
    SET     @CurrentBalance = (SELECT MIN(pay.Balance)
                               FROM Loans.Payments pay
                               WHERE (pay.AllocationID = @LaID));

    -- If the customer has never made a payment (yet),
    -- to specify the balance, subtract the current payment
    -- from the original amount of the loan
    IF      @CurrentBalance IS NULL
 BEGIN
     INSERT INTO Loans.Payments(PaymentDate, EmployeeID,
     AllocationID, PaymentAmount, Balance)
     VALUES(@PmtDate, @EmplID, @LaID, @PmtAmt,
     @AmountOfLoan - @PmtAmt);
 END
    -- If the customer had already at least one payment,
    -- subtract the current payment from the previous balance
    ELSE
 BEGIN
     INSERT INTO Loans.Payments(PaymentDate, EmployeeID,
           AllocationID, PaymentAmount, Balance)
     VALUES(@PmtDate, @EmplID, @LaID,
     @PmtAmt, @CurrentBalance - @PmtAmt);
 END
END
GO

To create the stored procedure, on the main, click Query -> Execute
Click inside the top section of the Query window and press Ctrl + A

Executing an Argumentative Stored Procedure

As mentioned already, when executing a stored procedure that takes a parameter, make sure you provide a value for the parameter. The syntax used is:

EXEC/EXECUTE [SchemaName.]ProcedureName ParameterValue

If the parameter is Boolean or numeric, make sure you provide the value as 0 or for a Boolean value or another number for the numeric type. If the parameter is a character or a string, type its value in single-quotes. Here is an example:

EXEC rosh.Registration.GetListOfStudentsByGender N'Male';

Here is an example of executing it:

Notice that we could/should have omitted the Gender column in the statement since it would be implied.

Another type of stored procedure can be made to take more than one parameter. In this case, create the parameters in the section before the AS keyword, separated by (a) comma(s). The syntax you would use is:

CREATE PROC/PROCEDURE [SchemaName.]ProcedureName
@ParameterName1 DataType, @ParameterName2 DataType, @ParameterName_n DataType
AS
Body of the Procedure

Here is an example:

USE ROSH;
GO
CREATE PROCEDURE Registration.IdentifyStudentsByState
 @Gdr nvarchar(20),
 @StateOrProvince char(2)
AS
BEGIN
    SELECT FullName = LastName + ', N' + FirstName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Gender
    FROM Registration.Students
    WHERE (Gender = @Gdr) AND (State = @StateOrProvince);
END
GO

When calling a stored procedure that takes more than one parameter, you must still provide a value for each parameter but you have two alternatives. The simplest technique consists of providing a value for each parameter in the exact order they appear in the stored procedure. Here is an example:

USE ROSH;
GO
EXEC rosh.Registration.IdentifyStudentsByState N'Female', N'MD';
GO

This would produce:

Alternatively, you can provide the value for each parameter in the order of your choice. Consider the following stored procedure that takes 3 arguments:

USE ROSH;
GO
CREATE PROCEDURE Registration.IdentifySomeStudents
 @Gdr nvarchar(20),
 @StateOrProvince nchar(2),
 @HomeStatus bit
AS
BEGIN
    SET NOCOUNT ON
    SELECT FullName = LastName + N', ' + FirstName,
           DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
           Gender
    FROM Registration.Students
    WHERE (Gender = @Gdr) AND 
          (State  = @StateOrProvince) AND
          (SingleParentHome = @HomeStatus);
END
GO

When calling this type of procedure, you can type the name of each parameter and assign it the corresponding value. Here is an example:

USE ROSH;
GO
EXEC Registration.IdentifySomeStudents @HomeStatus=1, @StateOrProvince=N'MD', @Gdr=N'Female';
GO

Here is an example of executing the procedure:

Practical Learning: Executing an Argumentative Procedure

To execute the stored procedure, type the following:

USE WattsALoan1;
GO
EXECUTE Loans.SpecifyCurrentBalance N'03/25/2004', 2, 1, 249.08;
GO
EXECUTE Loans.SpecifyCurrentBalance N'01/30/2006', 2, 5, 611.93;
GO
EXECUTE Loans.SpecifyCurrentBalance N'04/20/2004', 1, 1, 249.08;
GO
EXECUTE Loans.SpecifyCurrentBalance N'10/28/2006', 2, 4, 134.38;
GO

To execute, press F5
To see a list of payments made on different loans, type the following:
```
SELECT ALL * FROM Loans.Payments;
GO
```
To see the result, on the main menu, click Query -> Execute
Click inside the top section of the Query window and press Ctrl + A

Default Arguments

Imagine you create a database for a department store and a table that holds the list of items sold in the store:

CREATE TABLE Inventory.Categories
(
    CategoryID int identity(1, 1) primary key,
    Category nvarchar(20) not null
);
GO
INSERT INTO Inventory.Categories(Category)
VALUES(N'Men'), (N'Women'), (N'Boys'), (N'Girls'),(N'Miscellaneous');
GO
CREATE TABLE Inventory.StoreItems
(
    ItemNumber nvarchar(10) primary key,
    CategoryID int foreign key
        references Inventory.Categories(CategoryID),
    ItemName nvarchar(60) not null,
    Size nvarchar(20),
    UnitPrice money
);
INSERT INTO Inventory.StoreItems
VALUES(N'264850', 2, N'Long-Sleeve Jersey Dress', N'Petite', 39.95),
      (N'930405', 4, N'Solid Crewneck Tee', N'Medium', 12.95),
      (N'293004', 1, N'Cotton Comfort Open Bottom Pant', N'XLarge', 17.85),
      (N'924515', 1, N'Hooded Full-Zip Sweatshirt', N'S', 69.95),
      (N'405945', 3, N'Plaid Pinpoint Dress Shirt', N'22 35-36', 35.85),
      (N'294936', 2, N'Cool-Dry Soft Cup Bra', N'36D', 15.55),
      (N'294545', 2, N'Ladies Hooded Sweatshirt', N'Medium', 45.75),
      (N'820465', 2, N'Cotton Knit Blazer', N'M', 295.95),
      (N'294694', 2, N'Denim Blazer - Natural Brown', N'Large', 75.85),
      (N'924094', 3, N'Texture-Striped Pleated Dress Pants', N'44x30', 32.85),
      (N'359405', 3, N'Iron-Free Pleated Khaki Pants', N'32x32', 39.95),
      (N'192004', 3, N'Sunglasses', NULL, 15.85);
GO

ItemNumber	ItemCategoryID	ItemName	ItemSize	UnitPrice
264850	2	Long-Sleeve Jersey Dress	Petite	39.95
930405	4	Solid Crewneck Tee	Medium	12.95
293004	1	Cotton Comfort Open Bottom Pant	XLarge	17.85
924515	1	Hooded Full-Zip Sweatshirt	S	69.95
405945	3	Plaid Pinpoint Dress Shirt	22 35-36	35.85
294936	2	Cool-Dry Soft Cup Bra	36D	15.55
294545	2	Ladies Hooded Sweatshirt	Medium	45.75
820465	2	Cotton Knit Blazer	M	295.95
294694	2	Denim Blazer - Natural Brown	Large	75.85
924094	3	Texture-Striped Pleated Dress Pants	44x30	32.85
359405	3	Iron-Free Pleated Khaki Pants	32x32	39.95
192004	3	Sunglasses		15.85

Imagine you want to create a mechanism of calculating the price of an item after a discount has been applied to it. Such a procedure can be created as follows:

CREATE PROCEDURE Inventory. CalculateNetPrice
@discount Decimal
AS
    SET NOCOUNT ON
    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100)
    FROM StoreItems;
GO

This can be executed as follows:

If you are planning to create a stored procedure that takes an argument and know that the argument will likely have the same value most of the time, you can provide that value as parameter but leave a room for other values of that argument. A value given to an argument is referred to as default. What this implies is that, when the user calls that stored procedure, if the user doesn't provide a value for the argument, the default value would be used.

To create a stored procedure that takes an argument that carries a default value, after declaring the value, on its right side, type = followed by the desired value. Here is an example applied to the above database:

CREATE PROCEDURE Inventory.CalculateDiscountedPrice
@discount decimal = 10.00
AS
    SET NOCOUNT ON
    SELECT ItemName, UnitPrice - (UnitPrice * @discount / 100)
    FROM StoreItems;
GO

When executing a stored procedure that takes a default argument, you don't have to provide a value for the argument if the default value suits you. Based on this, the above stored procedure can be called as follows:

If the default value doesn't apply to your current calculation, you can provide a value for the argument. Here is an example:

On the other hand, you can ask the database engine to get the default value. To do this, pass the argument as DEFAULT. Here is an example:

You can create a stored procedure that takes more than one argument with default values. To provide a default value for each argument, after declaring it, type the desired value to its right side. Here is an example of a stored procedure that takes two arguments, each with a default value:

CREATE PROCEDURE Inventory.CalculateSalePrice
@Discount decimal = 20.00,
@TaxRate  decimal = 7.75
AS
    SET NOCOUNT ON
    SELECT ItemName As [Item Description],
           UnitPrice As [Marked Price],
           UnitPrice * @Discount / 100 As [Discount Amt],
           UnitPrice - (UnitPrice * @Discount / 100) As [After Discount],
           UnitPrice * @TaxRate / 100 As [Tax Amount],
           (UnitPrice * @TaxRate / 100) + UnitPrice - 
           (UnitPrice * @Discount / 100) + (@TaxRate / 100) As [Net Price]
FROM StoreItems;
GO

Here is an example of executing the procedure:

When calling a stored procedure that takes more than one argument and all arguments have default values, you don't need to provide a value for each argument, you can provide a value for only one or some of the arguments. The above procedure can be called with one argument as follows:

EXEC CalculateSalePrice 55.00

In this case, the other argument(s) would use their default value.

We saw that, when calling a stored procedure that takes more than one argument, you didn't have to provide the values of the arguments in the exact order they appeared in the procedure, you just had to type the name of each argument and assign it the desired value. In the same way, if a stored procedure takes more than one argument and some of the arguments have default values, when calling it, you can provide the values in the order of your choice, by typing the name of each argument and assigning it the desired value. Based on this, the above stored procedure can be called with only the value of the second argument as follows:

EXEC CalculateSalePrice @TaxRate = 8.55

In this case, the first argument would use its default value.

How to Create Database and tables in Sql server effictivly

2012-06-29T13:02:00.003-07:00

Create Database and Tables

Store and Fetch Images From Database Using Asp.net

2012-06-29T12:46:00.001-07:00

This article is to designed to show how to store and fetch image from SQL Server Database Using Asp.net

Step 1. Create Database

CREATE DATABASE [Employee]
GO
USE [Employee]
GO
CREATE TABLE EmpDetails
(
empid int IDENTITY NOT NULL,
empname varchar(20),
empimg image
)

** name of the database can be anything or the structure of the table can be altered. But the image field should have the datatype of Image.

------------------------------------------------------------------------------------------------------------
Step 2. Create a new Website and the source code for the page is given below

html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Save Retrieve Images</title>
</head>
<body>
<form id="form1" runat="server">
<div>

<asp:Label ID="lblEmpName" runat="server" Text="Employee Name"></asp:Label>
    
<asp:TextBox ID="txtEName" runat="server"></asp:TextBox>
<br />
<asp:Label ID="lblImage" runat="server" Text="Employee Image"></asp:Label>
    
<asp:FileUpload ID="imgUpload" runat="server" />
<br />
<br />
<asp:Button ID="btnSubmit" runat="server" onclick="btnSubmit_Click"
Text="Submit" />

       &nbsp <asp:Label ID="lblResult" runat="server" ForeColor="#0066FF"></asp:Label>
<br />
<hr />

<asp:Image ID="Image1" style="width:200px" Runat="server" />

</div>
</form>
</body>
</html>

** controls can be altered as per user requirement
------------------------------------------------------------------------------------------------------
Step 3. Code for C# part

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSubmit_Click(object sender, EventArgs e)
{
SqlConnection connection = null;
try
{
FileUpload img = (FileUpload)imgUpload;
Byte[] imgByte = null;
if (img.HasFile && img.PostedFile != null)
{
//To create a PostedFile
HttpPostedFile File = imgUpload.PostedFile;
//Create byte Array with file len
imgByte = new Byte[File.ContentLength];
//force the control to load data in array
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
// Insert the employee name and image into db
//string conn = ConfigurationManager.ConnectionStrings["EmployeeConnString"].ConnectionString;
connection = new SqlConnection("server=YASH;database=Employee;Trusted_connection=true");

connection.Open();
string sql = "INSERT INTO EmpDetails(empname,empimg) VALUES(@enm, @eimg) SELECT @@IDENTITY";
SqlCommand cmd = new SqlCommand(sql, connection);
cmd.Parameters.AddWithValue("@enm", txtEName.Text.Trim());
cmd.Parameters.AddWithValue("@eimg", imgByte);
int id = Convert.ToInt32(cmd.ExecuteScalar());
lblResult.Text = String.Format("Employee ID is {0}", id);
Image1.ImageUrl = "~/ShowImage.ashx?id=" + id;
}
catch
{
lblResult.Text = "There was an error";
}
finally
{
connection.Close();
}

}
}

** of course you have to change the name of the project and name of sql server in my case it is yash

---------------------------------------------------------------------------------------------------------

Step 4. upto the Step 3 everything was done to store the image now for fetching the image you have to add Generic Handler in the project

right click on project>> add new item>> Generic handler

i have named it Showimage you can name it anything as you want

Code for the Handler

<%@ WebHandler Language="C#" Class="ShowImage" %>

using System;

using System.Configuration;

using System.Web;

using System.IO;

using System.Data;

using System.Data.SqlClient;

public class ShowImage : IHttpHandler

{

public void ProcessRequest(HttpContext context)

{

Int32 empno;

if (context.Request.QueryString["id"] != null)

empno = Convert.ToInt32(context.Request.QueryString["id"]);

else

throw new ArgumentException("No parameter specified");

context.Response.ContentType = "image/jpeg";

Stream strm = ShowEmpImage(empno);

byte[] buffer = new byte[4096];

int byteSeq = strm.Read(buffer, 0, 4096);

while (byteSeq > 0)

{

context.Response.OutputStream.Write(buffer, 0, byteSeq);

byteSeq = strm.Read(buffer, 0, 4096);

}

context.Response.BinaryWrite(buffer);

}

public Stream ShowEmpImage(int empno)

{

//string conn = ConfigurationManager.ConnectionStrings["EmployeeConnString"].ConnectionString;

SqlConnection connection = new SqlConnection("server=YASH;database=Employee;Trusted_connection=true");

string sql = "SELECT empimg FROM EmpDetails WHERE empid = @ID";

SqlCommand cmd = new SqlCommand(sql, connection);

cmd.CommandType = CommandType.Text;

cmd.Parameters.AddWithValue("@ID", empno);

connection.Open();

object img = cmd.ExecuteScalar();

try

{

return new MemoryStream((byte[])img);

}

catch

{

return null;

}

finally

{

connection.Close();

}

public bool IsReusable

{

get

{

return false;

}

** You have to change the name of sql server it was "yash" in my case

-----------------------------------------------------------------------------

Step 5: F5

how to add counter to user website using http://www.1freecounter.com

2011-09-08T00:35:00.000-07:00

first go to the website and create code for your website after creating the account of 1freecounter.com and its very its.... then it asks for some data from u...

then its produces code for ur website for your website

such example of such code is give below



<script language="JavaScript">
var data = '&r=' + escape(document.referrer)
   + '&n=' + escape(navigator.userAgent)
   + '&p=' + escape(navigator.userAgent)
   + '&g=' + escape(document.location.href);

if (navigator.userAgent.substring(0,1)>'3')
    data = data + '&sd=' + screen.colorDepth
   + '&sw=' + escape(screen.width+'x'+screen.height);

document.write('<a href="http://www.1freecounter.com/stats.php?i=82908" target=\"_blank\" >');
document.write('<img alt="Free Counter" border=0 hspace=0 '+'vspace=0 src="http://www.1freecounter.com/counter.php?i=82908' + data + '">');
document.write('</a>');
</script>



then u have to copy this code to your website and counter ticks on wid each user comes on the website

code in asp.net to send sms

2011-09-08T00:19:00.001-07:00

using grid view

2011-08-08T12:08:00.001-07:00

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" DataKeyNames="branch_id" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" style="z-index: 100; left: 271px; position: absolute; top: 367px" BackColor="White" BorderColor="#CCCCCC" BorderStyle="None" BorderWidth="1px" CellPadding="4" ForeColor="Black" GridLines="Horizontal">
<Columns>
<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="branch_id" HeaderText="Branch Id" SortExpression="branch_id" />

<asp:BoundField DataField="branchAddress" HeaderText="Branch Address" SortExpression="branchAddress" />

<asp:BoundField DataField="Branch_head" HeaderText="Branch Head" SortExpression="Branch_head" />

<asp:BoundField DataField="branch_manpower" HeaderText="Branch Manpower" SortExpression="branch_manpower" />

</Columns>
<FooterStyle BackColor="#CCCC99" ForeColor="Black" />
<SelectedRowStyle BackColor="#CC3333" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="White" ForeColor="Black" HorizontalAlign="Right" />
<HeaderStyle BackColor="#333333" Font-Bold="True" ForeColor="White" />
</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:web_trackersConnectionString %>"
SelectCommand="SELECT * FROM [branches]" UpdateCommand="update branches set branch_id=@branch_id, branchAddress=@branchAddress, Branch_head=@Branch_head, Branch_manpower=@Branch_manpower where(branch_id=@branch_id)" DeleteCommand ="delete from branches where branch_id=@branch_id"></asp:SqlDataSource>

</div>
</form>
</body>
</html>

delete a record

2011-08-01T20:28:00.001-07:00

SqlCommand cmd = new SqlCommand("delete emp_master where Employee_id='" + TextBox1.Text+ "'", con);
cmd.CommandType = System.Data.CommandType.Text;

con.Open();
cmd.ExecuteNonQuery();
con.Close();
Label18.Text = "Record deleted successfully";

searching

2011-08-01T20:27:00.001-07:00

SqlCommand cmd = new SqlCommand("select * from emp_master where Employee_id='" + TextBox17.Text+ "'", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
{
Label18.Text = "Record not found";
}
else
{
TextBox1.Text = ds.Tables[0].Rows[0]["Employee_id"].ToString();
TextBox2.Text = ds.Tables[0].Rows[0]["Name"].ToString();
TextBox3.Text = ds.Tables[0].Rows[0]["Date_of_Birth"].ToString();
TextBox4.Text = ds.Tables[0].Rows[0]["Date_of_joining"].ToString();
DropDownList1.Text = ds.Tables[0].Rows[0]["Gender"].ToString();
DropDownList2.Text = ds.Tables[0].Rows[0]["Categoryorcaste"].ToString();
TextBox5.Text = ds.Tables[0].Rows[0]["Dept_id"].ToString();
TextBox6.Text = ds.Tables[0].Rows[0]["Designation_id"].ToString();
TextBox7.Text = ds.Tables[0].Rows[0]["Grade_id"].ToString();
TextBox8.Text = ds.Tables[0].Rows[0]["PAN_NO"].ToString();
Label18.Text = "Record Found";

}

display customized welcome msg using sessions

2011-08-01T20:26:00.000-07:00

object sessionObject = Session["adminid"];
if (sessionObject != null)
{
Label3.Text = sessionObject.ToString();
}

new login logic.... courtesies by mr. ankit verma

2011-08-01T05:19:00.000-07:00

SqlConnection con = new SqlConnection("server=ANKIT-PC;user id=sa;password=manager;database=db_dda;");
SqlDataAdapter da = new SqlDataAdapter("select * from adm_login where adm_id='" + TextBox2.Text + "' and adm_pwd='" + TextBox1.Text + "'", con);
con.Open();
DataSet ds=new DataSet();
da.Fill(ds);
if (ds.Tables[0].Rows.Count == 0)
Label3.Text = "Invalid";
else
Label3.Text = "Valid";

Save data to dabase

2011-07-30T12:16:00.003-07:00

try
{
SqlConnection con = new SqlConnection("server=YASH-BA85E3332D\\SQLEXPRESS;database=db_dda;trusted_connection=yes;");

SqlCommand cmd = new SqlCommand("insert into houseing_booking( application_n,f_name,l_name,age,dob,address,phone,housing_scheme_name,house_category,advance_draft_amount,issuing_bank,draft_no,dd_date,date_of_application,bank_ac_number)values (@application_n,@f_name,@l_name,@age,@dob,@address,@phone,@housing_scheme_name,@house_category,@advance_draft_amount,@issuing_bank,@draft_no,@dd_date,@date_of_application,@bank_ac_number)", con);

DataSet ds = new DataSet();

SqlDataAdapter adp;
cmd.CommandType = System.Data.CommandType.Text;
cmd.Parameters.Add("@application_n", SqlDbType.Int).Value = int.Parse(TextBox1.Text);
cmd.Parameters.Add("@f_name", SqlDbType.VarChar).Value = TextBox2.Text;
cmd.Parameters.Add("@l_name", SqlDbType.VarChar).Value = TextBox3.Text;
cmd.Parameters.Add("@age", SqlDbType.Int).Value = int.Parse(TextBox4.Text);
cmd.Parameters.Add("@dob", SqlDbType.DateTime).Value = TextBox5.Text;
cmd.Parameters.Add("@address", SqlDbType.VarChar).Value = TextBox6.Text;
cmd.Parameters.Add("@phone", SqlDbType.Int).Value = int.Parse(TextBox7.Text);
cmd.Parameters.Add("@housing_scheme_name", SqlDbType.VarChar).Value = TextBox8.Text;
cmd.Parameters.Add("@house_category", SqlDbType.VarChar).Value = TextBox9.Text;

cmd.Parameters.Add("@advance_draft_amount", SqlDbType.Int).Value = int.Parse(TextBox10.Text);

cmd.Parameters.Add("@issuing_bank", SqlDbType.VarChar).Value = TextBox11.Text;

cmd.Parameters.Add("@draft_no",SqlDbType.Int).Value = int.Parse(TextBox12.Text);

cmd.Parameters.Add("@dd_date", SqlDbType.DateTime).Value = TextBox13.Text;

cmd.Parameters.Add("@date_of_application", SqlDbType.DateTime).Value = TextBox14.Text;

cmd.Parameters.Add("@bank_ac_number", SqlDbType.VarChar).Value = TextBox15.Text;

adp = new SqlDataAdapter(cmd);
adp.Fill(ds, "houseing_booking");

Label19.Text="Congratulation Booking Confirmed!!!!!!";
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3.Text = "";
TextBox4.Text = "";
TextBox5.Text = "";
TextBox6.Text = "";
TextBox7.Text = "";
TextBox8.Text = "";
TextBox9.Text = "";
TextBox10.Text = "";
TextBox11.Text = "";
TextBox12.Text="";
TextBox13.Text="";
TextBox14.Text="";
TextBox15.Text = "";

}
catch (SqlException se)
{
if (se.ErrorCode == 2627)
{
Label19.Text = " Application No. already exists in database";

}
else
{
Label19.Text="Exception occured !!!! Plz reload and try again later";
}
}

finally
{

}

Save data to dabase

2011-07-30T12:16:00.001-07:00

login without session's implementaiton

2011-07-08T20:16:00.000-07:00

protected void Button1_Click(object sender, EventArgs e)
{
int i=0;
SqlConnection con = new SqlConnection("server=YASH-BA85E3332D\\SQLEXPRESS;database=db_dda;trusted_connection=yes;");
SqlCommand cmd =new SqlCommand("select * from adm_login",con);
SqlDataAdapter da=new SqlDataAdapter(cmd);
DataSet ds=new DataSet();
da.Fill(ds);
string dlogin;
string dpassword;
int lrecord;
dlogin = ds.Tables[0].Rows[i]["adm_id"].ToString();
dpassword = ds.Tables[0].Rows[i]["adm_pwd"].ToString();
lrecord = ds.Tables[0].Rows.Count;

while (i < lrecord)
{
if (TextBox1.Text == dlogin && TextBox2.Text == dpassword)
{
Response.Redirect("admin_pages\\admin_main_page.aspx");

return;
}
else
{
i++;
if (i < lrecord)
{
dlogin = ds.Tables[0].Rows[i]["adm_id"].ToString();
dpassword = ds.Tables[0].Rows[i]["adm_pwd"].ToString();
}
else
{
Label3.Text="Invalid User id or Password";

}
}
}

}

Create DHTML menus in asp.net

2011-07-06T11:07:00.000-07:00

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<meta http-equiv="content-type" content="text/html;charset=utf-8" />
<title>My menus</title>
<link type="text/css" href="menu.css" rel="stylesheet" />
<script type="text/javascript" src="jquery.js"></script>
<script type="text/javascript" src="menu.js"></script>
<style type="text/css">
* { margin:0;
padding:0;
}

body { background:#000; }
div#menu {
top:40px;
left:0px;
width:100%;
}
</style>
</head>
<body style="background-color: white">

<div id="menu" style="left: 120px; top: 57px">
<ul class="menu">
<li><a href="#" class="parent"><span>Home</span></a>
<div><ul>
<li><a href="#" class="parent"><span>Sub Item 1</span></a>
<div><ul>
<li><a href="#" class="parent"><span>Sub Item 1.1</span></a>
<div><ul>
<li><a href="#"><span>Sub Item 1.1.1</span></a></li>
<li><a href="#"><span>Sub Item 1.1.2</span></a></li>
</ul></div>
</li>
<li><a href="#"><span>Sub Item 1.2</span></a></li>
<li><a href="#"><span>Sub Item 1.3</span></a></li>
<li><a href="#"><span>Sub Item 1.4</span></a></li>
<li><a href="#"><span>Sub Item 1.5</span></a></li>
<li><a href="#"><span>Sub Item 1.6</span></a></li>
<li><a href="#" class="parent"><span>Sub Item 1.7</span></a>
<div><ul>
<li><a href="#"><span>Sub Item 1.7.1</span></a></li>
<li><a href="#"><span>Sub Item 1.7.2</span></a></li>
</ul></div>
</li>
</ul></div>
</li>
<li><a href="#"><span>Sub Item 2</span></a></li>
<li><a href="#"><span>Sub Item 3</span></a></li>
</ul></div>
</li>
<li><a href="#" class="parent"><span>Product Info</span></a>
<div><ul>
<li><a href="#" class="parent"><span>Sub Item 1</span></a>
<div><ul>
<li><a href="#"><span>Sub Item 1.1</span></a></li>
<li><a href="#"><span>Sub Item 1.2</span></a></li>
</ul></div>
</li>
<li><a href="#" class="parent"><span>Sub Item 2</span></a>
<div><ul>
<li><a href="#"><span>Sub Item 2.1</span></a></li>
<li><a href="#"><span>Sub Item 2.2</span></a></li>
</ul></div>
</li>
<li><a href="#"><span>Sub Item 3</span></a></li>
<li><a href="#"><span>Sub Item 4</span></a></li>
<li><a href="#"><span>Sub Item 5</span></a></li>
<li><a href="#"><span>Sub Item 6</span></a></li>
<li><a href="#"><span>Sub Item 7</span></a></li>
</ul></div>
</li>
<li><a href="#"><span>Help</span></a></li>
<li class="last"><a href="#"><span>Contacts</span></a></li>
</ul>
</div>
<asp:Label ID="Label1" runat="server" BackColor="Red" Height="37px" Text="Label"
Width="257px"></asp:Label><br />
<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />
<br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br /><br />

</body>
</html>

How To search make work

2011-07-02T20:10:00.000-07:00

SqlCommand cmd = new SqlCommand("select * from emp_sal where emp_id="+TextBox17.Text, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

if (ds.Tables[0].Rows.Count == 0)
{
Label18.Text = "Record Not found";
}
else
{
TextBox1.Text = ds.Tables[0].Rows[0]["emp_id"].ToString();
TextBox2.Text = ds.Tables[0].Rows[0]["basic_salary"].ToString();
TextBox3.Text = ds.Tables[0].Rows[0]["hra"].ToString();
TextBox4.Text = ds.Tables[0].Rows[0]["da"].ToString();
TextBox5.Text = ds.Tables[0].Rows[0]["ta"].ToString();
TextBox6.Text = ds.Tables[0].Rows[0]["emp_grade"].ToString();
TextBox7.Text = ds.Tables[0].Rows[0]["pf_reduction"].ToString();
TextBox8.Text = ds.Tables[0].Rows[0]["pf_amount"].ToString();
TextBox9.Text = ds.Tables[0].Rows[0]["pension_benifits"].ToString();

TextBox11.Text = ds.Tables[0].Rows[0]["medical_benifits"].ToString();
TextBox12.Text = ds.Tables[0].Rows[0]["total_salary"].ToString();

}

How to Use File Upload Control in asp.net

2011-07-02T19:43:00.001-07:00

if (FileUpload1.HasFile)
{

FileUpload1.SaveAs("c:\\yash\\" + FileUpload1.PostedFile.FileName);
Response.Write("<script> alert('file Uploaded Sucessfully') </script>");

}

connectivity of oracle and c#

2011-06-21T22:47:00.000-07:00

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.Data.Odbc;
namespace WindowsApplication12
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}

private void Form1_Load(object sender, EventArgs e)
{
OdbcConnection con=new OdbcConnection ("driver={microsoft odbc for oracle};uid=scott;pwd=tiger;");
OdbcCommand cmd=new OdbcCommand ("select * from emp",con );
OdbcDataAdapter da=new OdbcDataAdapter (cmd);
DataSet ds=new DataSet();
da.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];

}
}
}

create E-mail service in asp.net

2011-01-24T06:36:00.001-08:00

private void btnSend_Click(object sender, System.EventArgs e) {
MailMessage msg = new MailMessage();
msg.To = this.txtTo.Text;
msg.Subject = this.txtSubject.Text;
msg.Body = this.txtBody.Text;
msg.From = "info@Builder.com";
SmtpMail.SmtpServer = "localhost";
SmtpMail.Send(msg);
}