Developer : Tariq Chaudhary ; Date: March 01,2004
Tools:ASP.Net Web Matrix, .Net Framework,SQL Server 2000/Access,Windows 2000 and IIS 5.0
Inserting Database Records using ASP.Net,Windows 2000,Access 2000
and IIS 5.0
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data.OleDb" %>'to access the Access database 2000
<script runat="server">
Dim DBConn As OleDbConnection
Dim DBCommand As OlwDbCommand
Dim SQLString As String
Dim DBReader As SQLDbDataReader
Sub AddRecord (Src As Object, Args As EventArgs)
Dim sqlstring as string
Dim DBCommand As OleDbCommand
DBConn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\WWWroot\aspdotnet\testdot.mdb")
DBConn.Open()
SQLString = "INSERT INTO tbldata(fname, Lname, city) " & _
"VALUES ('"& fname.Text & "', '" & Lname.Text & "' ,'" & city.Text & "')"
DBCommand = New OleDbCommand(SQLString, DBConn)
'-- Create a recordset of selected records from the database
DBReader = DBCommand.ExecuteReader()
DBConn.Close()
End Sub
'to clear the contents of the form
Sub ClearForm (Src As Object, Args As EventArgs)
fname.Text = ""
lname.Text = ""
city.Text = ""
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
First name:<asp:TextBox id="fname" runat="server"></asp:TextBox>
<br />
Last Name:<asp:TextBox id="lname" runat="server"></asp:TextBox>
<br />
City:<asp:TextBox id="city" runat="server"></asp:TextBox>
<br />
<asp:button id="button1" onclick="AddRecord" runat="server" Text="Add"></asp:button>
<asp:Button id="Button2" onclick="ClearForm" runat="server" Text="Clear"></asp:Button>
<br />
<br />
<asp:RequiredfieldValidator id="RequiredFieldValidator1" runat="server" ControlToValidate="fname" text="first name is requied"></asp:RequiredfieldValidator>
<br />
<asp:RequiredfieldValidator id="RequiredFieldValidator2" runat="server" ControlToValidate="lname" text="Last name is requied"></asp:RequiredfieldValidator>
<br />
<asp:RequiredfieldValidator id="RequiredFieldValidator3" runat="server" ControlToValidate="city" text="city name is requied"></asp:RequiredfieldValidator>
</form>
<a href="dispalyrecords.aspx">Display records</a>
</body>
</html
Top
Displaying Database Records using ASP.Net,Windows 2000 and SQL
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data.OleDb" %>
<script runat="server">
sub Page_Load
Dim DBConn As OleDbConnection
Dim DBCommand As OleDbCommand
Dim SQLString As String
Dim DBReader As OleDbDataReader
DBConn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Inetpub\WWWroot\aspdotnet\testdot.mdb")
DBConn.Open()
sqlstring="SELECT * FROM tbldata"
dbcommand =New OleDbCommand(sqlstring,dbconn)
DBCommand = New OleDbCommand(SQLString, DBConn)
'-- Create a recordset of selected records from the database
DBReader = DBCommand.ExecuteReader()
tbldata.DataSource=DBReader
tbldata.DataBind()
dbReader.close()
DBConn.Close()
end sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<asp:Repeater id="tbldata" runat="server">
<HeaderTemplate>
<center>
<table border="1" width="50%">
<tr bgcolor="#b0c4de">
<th>
First name</th>
<th>
Last name</th>
<th>
City</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr bgcolor="#f0f0f0">
<td>
<%#Container.dataitem("fname")%>
</td>
<td>
<%#Container.dataitem("lname")%>
</td>
<td>
<%#Container.dataitem("city")%>
</td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</center>
</FooterTemplate>
</asp:Repeater>
</form>
<center><a href="firstteat.aspx">Back</a>
</center>
</body>
</html>
Top
Displaying Database Records from Dropdown Listbox using Repeater Control
in ASP.Net,SQL Server 2000 and Windows 2000 Enviornment
<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace="System.Data" %> 'to access the SQL server database
<%@ Import Namespace="System.Data.SqlClient" %> 'to access the
SQL Server database
<script runat="server">
Dim DBCommand As SqlCommand
Dim DBReader As SqlDataReader
Dim SQLString As String
Sub Page_Load
If Not Page.IsPostBack Then
Dim DbConnection As SqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;uid=uid;pwd=pwd;database=nameofthedatabase")
DBConnection.Open()
SQLString = "SELECT empid FROM tblsample ORDER BY empid"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
empid.DataSource = DBReader 'empid is id of repeater control
empid.DataTextField = "empid"
empid.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Sub Display_Data (Src As Object, Args As EventArgs)
Dim DbConnection As SqlConnection = New
SqlConnection("server=localhost;Trusted_Connection=yes;uid=uid;pwd=pwd; database=nameofthedatabase")
DBConnection.Open()
SQLString = "SELECT * FROM tblsample WHERE empid = '" &empid.SelectedItem.Text & "'"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataDisplay.DataSource = DBReader
DataDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
End Sub
</script>
<html>
<head>
</head>
<body>
<form runat="server">
<center><table>
<asp:DropDownList id="empid" runat="server"/>
<asp:Button Text="Select" onclick="Display_Data" runat="server"/>
<asp:Repeater id="DataDisplay" runat="server">
<HeaderTemplate>
<table border="1" cellpadding="3"
style="border-collapse:collapse">
<tr style="background-color:#F0F0F0" font="verdana">
<th>Emp Id</th>
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center"><%# Container.DataItem("empid")%></td>
<td><%# Container.DataItem("f_name") %></td>
<td><%# Container.DataItem("l_name") %></td>
<td colspan="6"><%# Container.DataItem("address")%></td>
</tr>
</ItemTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
</asp:Repeater>
</table>
</form>
</body>
</html>
Output
Top
Deleting Database Records using DataGrid Control,Dataset,
Windows 2000,SQL 2000 and IIS 5.0
<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim MyConnection As SqlConnection
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;uid=*********;pwd=****;database=database")
If Not (IsPostBack)
BindGrid()
End If
End Sub
Sub DelDataGrid_DeleteRecord(Sender As Object, E As DataGridCommandEventArgs)
Dim MyCommand As SqlCommand
Dim DeleteCmd As String = "DELETE from tblsample where empid = @empid"
MyCommand = New SqlCommand(DeleteCmd, MyConnection)
MyCommand.Parameters.Add(New SqlParameter("@empId", SqlDbType.NVarChar, 11))
MyCommand.Parameters("@empId").Value = DelDataGrid.DataKeys(CInt(E.Item.ItemIndex))
MyCommand.Connection.Open()
'Try
MyCommand.ExecuteNonQuery()
Message.InnerHtml = "<b>Record Deleted</font></b><br>"
Message.Style("color") = "red"
'Catch Exc As SQLException
'Message.InnerHtml = "ERROR: Could not delete record"
' Message.Style("color") = "red"
'End Try
MyCommand.Connection.Close()
BindGrid()
End Sub
Sub BindGrid()
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter("select * from tblsample", MyConnection)
DS = new DataSet()
MyCommand.Fill(DS, "tblsample")
DelDataGrid.DataSource=DS.Tables("tblsample").DefaultView
DelDataGrid.DataBind()
End Sub
</script>
<html>
<head>
</head>
<body style="FONT: 10pt verdana">
<center>
<form runat="server">
<h3><font face="Verdana">Deleting a Record from the Database</font>
</h3>
<span id="Message" runat="server" enableviewstate="false">
<p>
<ASP:DataGrid id="DelDataGrid" runat="server" OnDeleteCommand="DelDataGrid_DeleteRecord" DataKeyField="empid" HeaderStyle-BackColor="#aaaadd" Font-Size="8pt" Font-Name="Verdana" CellSpacing="0" CellPadding="3" ShowFooter="false" BorderColor="maroon" BackColor="#cccfff" Width="400">
<Columns>
<asp:ButtonColumn Text="Delete Record>>" CommandName="Delete" />
</Columns>
</ASP:DataGrid>
</p>
</span>
</form>
<a href="InsertSqls.aspx">Insert A New Record</a>
</center>
</body>
</html>
Output
Top
Dispalying Database Records using Radiobuttonlist
and Repeater control,SQL 2000 and IIS 5.0
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">
Dim DBCommand As SqlCommand
Dim DBReader As SqlDataReader
Dim SQLString As String
Sub Page_Load
If Not Page.IsPostBack Then
Dim DbConnection As SqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;uid=******;pwd=****;database=mydatabase")
DBConnection.Open()
SQLString = "SELECT distinct f_name FROM tblsample ORDER BY f_name"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
ButtonList.DataSource = DBReader 'empid is id of repeater control
ButtonList.DataTextField = "f_name"
ButtonList.DataBind()
DBReader.Close()
DBConnection.Close()
End If
End Sub
Sub Display_Data (Src As Object, Args As EventArgs)
If ButtonList.SelectedIndex <> -1 Then
Dim DbConnection As SqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;uid=***;pwd=****;database=****")
DBConnection.Open()
SQLString = "SELECT * FROM tblsample WHERE f_name = '" &ButtonList.SelectedItem.Text & "'"
DBCommand = New SqlCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
DataDisplay.DataSource = DBReader
DataDisplay.DataBind()
DBReader.Close()
DBConnection.Close()
End if
End Sub
</script>
<html>
<head>
</head>
<body bgcolor="lightyellow">
<form runat="server">
<center>
<table border="2">
<tr>
<td>
<asp:RadioButtonList id="ButtonList" runat="server" AutoPostBack="true"></asp:RadioButtonList>
</td>
</tr>
</table>
<br />
<br />
<asp:Button id="button1" onclick="Display_Data" runat="server" Text="Display"></asp:Button>
<br />
<br />
<asp:Repeater id="DataDisplay" runat="server">
<HeaderTemplate>
<table border="3" cellpadding="3" style="border-collapse:collapse">
<tbody>
<tr style="background-color:lightblue" font="verdana">
<th>
Emp Id</th>
<th>
First Name</th>
<th>
Last Name</th>
<th>
Address</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td align="center">
<%# Container.DataItem("empid") %></td>
<td>
<%# Container.DataItem("f_name") %></td>
<td>
<%# Container.DataItem("l_name") %></td>
<td colspan="6">
<%# Container.DataItem("address") %></td>
</tr>
</ItemTemplate>
<FooterTemplate></FooterTemplate>
</asp:Repeater>
</tbody>
</table>
</center>
</form>
</body>
</html>
Output
Updating Database Records using Data Grid control,SQL 2000 and IIS 5.0
<%@ Page Language="VB" Debug="true" %>
<%@ import Namespace="system.data" %> 'this is for accessing SQL Server
<%@ import Namespace="system.data.SqlClient" %> 'this is for accessing SQL Server
<script runat="server">
Dim SQLString As String
Dim DBConnection As SQLConnection
Dim DBCommand As SQLCommand
Dim DBReader As SQLDataReader
Sub Page_Load
If Not Page.IsPostBack
DBConnection = New SQLConnection( _
"server=localhost;Trusted_Connection=yes;uid=****;pwd=****;database=mydatabase")
DBConnection.Open()
SQLString = "SELECT * FROM tblsample Order by empid"
DBCommand = New SQLCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
UpdateDataGrid.DataSource = DBReader
UpdateDataGrid.DataBind()
DBReader.Close()
DBConnection.Close()
End if
End Sub
Sub BindDataGrid
DBConnection = New SQLConnection( _
"server=localhost;Trusted_Connection=yes;uid=****;pwd=****;database=mydatabase")
DBConnection.Open()
SQLString = "SELECT * FROM tblsample ORDER BY empid"
DBCommand = New SQLCommand(SQLString, DBConnection)
DBReader = DBCommand.ExecuteReader()
UpdateDataGrid.DataSource = DBReader
UpdateDataGrid.DataBind()
DBReader.Close()
DBConnection.Close()
End Sub
'this is for selecting the record
Sub EditRecord (Src As Object, Args As DataGridCommandEventArgs)
UpdateDataGrid.EditItemIndex = Args.Item.ItemIndex
BindDataGrid
End Sub
Sub UpdateRecord (Src As Object, Args As DataGridCommandEventArgs)
'this will open the selected record in text box to perform update
Dim empid = CType(Args.Item.Cells(0).Controls(0), TextBox).Text '? what is this
Dim f_name = CType(Args.Item.Cells(1).Controls(0), TextBox).Text
Dim l_name = CType(Args.Item.Cells(2).Controls(0), TextBox).Text
Dim address = CType(Args.Item.Cells(3).Controls(0), TextBox).Text
SQLString = "UPDATE tblsample SET " & _
"f_name = '" & f_name & "', " & _
"l_name = '" & l_name & "', " & _
"address = '" & address & "' " & _
"WHERE empid = '" & empid & "'"
DBConnection = New SQLConnection( _
"server=localhost;Trusted_Connection=yes;uid=uid;pwd=pwd;database=tariqch")
DBConnection.Open()
DBCommand = New SqlCommand(SQLString, DBConnection)
DBCommand.ExecuteNonQuery() 'ExecuteNonQuery() is a method
DBConnection.Close()
UpdateDataGrid.EditItemIndex = -1
BindDataGrid
End Sub
'to go back to initial screen without doing any update
Sub CancelRecord (Src As Object, Args As DataGridCommandEventArgs)
UpdateDataGrid.EditItemIndex = -1 'this is for when you will hit the cancel
BindDataGrid
End Sub
</script>
<!--performing update in asp.net using datagrid web server control-->
<html>
<head>
</head>
<body>
<center>
<form runat="server">
<asp:DataGrid id="updateDataGrid" runat="server" OnEditCommand="EditRecord" OnUpdateCommand="UpdateRecord" OnCancelCommand="CancelRecord" AutoGenerateColumns="False" HeaderStyle-BackColor="white" HeaderStyle-Font-Bold="True" BackColor="orange" cellpadding="2" BorderWidth="1" cellspacing="2">
<Columns>
<asp:BoundColumn HeaderText="ID" DataField="empid" />
<asp:BoundColumn HeaderText="First Name" DataField="f_name" />
<asp:BoundColumn HeaderText="Last Name" DataField="l_name" />
<asp:BoundColumn HeaderText=" Address" DataField="address" />
<asp:EditCommandColumn HeaderText="Editing" EditText="Edit" UpdateText="Update" CancelText="Cancel" />
</Columns>
</asp:DataGrid>
</form>
</center>
</body>
</html>
Output
Top
How to call a Stored Procedure in ASP. Net using Data Grid control,SQL 2000 and IIS 5.0.
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<html>
<script language="VB" runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim DS As DataSet
Dim DbConnection As SqlConnection
Dim DbCommand As SqlDataAdapter
DbConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;uid=****;pwd=***;database=mydatabase")
DbCommand = New SqlDataAdapter("sp_displayAddress", DbConnection)
DbCommand.SelectCommand.CommandType = CommandType.storedprocedure
DS = new DataSet()
DbCommand.Fill(DS, "tbladdress")
AddressDataGrid.DataSource=DS.Tables("tbladdress").DefaultView
AddressDataGrid.DataBind()
End Sub
<body>
<center>
<h1><font face="Verdana">Simple Stored Procedure to Display Addresses using DataGrid Control</font></h1>
<ASP:DataGrid id="AddressDataGrid" runat="server"
Width="560"
BackColor="#ccccff"
BorderColor="maroon"
ShowFooter="false"
CellPadding=3
CellSpacing="1"
Font-Name="Verdana"
Font-Size="8pt"
HeaderStyle-BackColor = "white"
/>
</center>
</body>
</html>
Code of the stored procedure
CREATE procedure sp_DisplayAddress As
Select * from tbladdress
where id <16
GO
Output
Top
Inserting database records using ASP.Net and Access 2000