2. Text box[ txtrad ] - to enter value for radius
3. Button -[ Area & circum ] BTN_ARE_CIR
-----------------------------------------------
txtarea.Text = 3.14 * txtrad.Text * txtrad.Text
txtcir.Text = 2 * 3.14 * txtrad.Text
------------------------------------------------
whenever if we change the value of text ,
textchanged event is executed on its own.
--------------------------------------------------
Performing form validation with Validation Controls
---------------------------------------------------
By providing validation controls , we can
reduce the error made by the user. Errors are
reduced considerably , but not guaranteed that
we can elimiate 100 % errors.
Keeping more controls reduced the error levels
but at the same time , speed will be reduced.
1. server side validation[vb.net
2. client side validation[vbscript, javascript]
Server side validation
Demerits : network traffic , can not get thru,
Most of the time server busy in validation
Merits : More secured. No one can bypass the
validation
Client side:
------------
Demerits: Not secured. Smart programmers can by pass
the validation
merits : less network traffic
server side validation controls
-------------------------------
REQUIRED FIELD VALIDATOR
REGULAR EXPRESSION VALIDATOR
COMPARE VALIDATOR
RANGE VALIDATOR
VALIDATIONSUMMARY CONTROL VALIDATOR
CUSTOM VALIDATOR
REQUIRED FIELD VALIDATOR
------------------------
Used to check whether field is empty or not
Properties :
ControlToValidate
Text
Isvalid : Has the valid true when the validation
check succeeds and false otherwise
Compare Validator
-----------------
Performs comparisons between the data entered
into a form field and another value.
The other value can be fixed value , such
as particular number or value entered into another
control.
Properties :
ControlToValidate ,ControlToCompare,
IsValid , Operator , type , text
value to compare.
VALIDATIONS
1. Amount field shoul not be empty.
2. Entererd value must be more than 2300.
Include Neccessary validation controls to
achieve the two goas 1 and 2
hint: use Required field validator for
the validation 1.
Use compare validator for validation 2
ControlToValidate = txtamt
ValueToCompare = 2300
type = Money
Operator = Greater than
text = Must be greater than 2300
--------------------------------------------------------
REGULAR EXPRESSION - CONTROL
----------------------------
This is used to validate like phone number ,
email address, postal code , code number etc.
Property : controlToValidate , text
, ValidationExpression.
symbols used
-------------
[ ] - Range
[0-4] -Can be any numbers form 0 to 4.
{ } - Exact length
{2} - Exact length 2
+ - one or more characters
* - zero or more characters
--------------------------------------------------------
P[0-9]{4}- P followed by 4 digits lenth
[pP][0-9]{4} - Either lower case p or Upper case P
followed by 4 digits lenth
[^0][0-9]{4}
[^0] = not zero included
[^0^a-z^A-Z][0-9]{4}
----------------------------------------------------
RangeValidator:
Property : controlTovalidate , type , text ,
MinimumValue , MaximumValue
------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
con = New OleDbconnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.open()
cmd = New oledbcommand("Select au_lname ,STATE
from authors WHERE STATE='CA' " ,con)
dr = cmd.ExecuteReader()
while dr.read()
Response.write("
Response.write(dr("au_lname")+ " ")
Response.write(dr("STATE"))
End While
dr.close()
con.close()
---------------------------------------------------
List au_lname , STATE from authors table who stay
in state "CA"
SELECT AU_LNAME , STATE FROM AUTHORS WHERE STATE="CA"
--------------------------------------------------------
ExecuteScalar is used to if it fetches only one record,
Example: count , min , max , total
avg .. etc
select count(*) from authors - Returns total records
in the table.
select min(salary) from authors - Returns minimum salary
in the table.
------------------------------------------------------
question 3 : Display the number of records from
the authors table of pubs database.
Output
There are 23 records in the authors table
-------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = New OleDbCommand("select count(*) from authors", con)
LBLCOUNT.Text = cmd.ExecuteScalar()
con.Close()
--------------------------------------------------------
Display au_lname , state those who stay
in any one of the states (CA ,KS,IN)
-------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = New OleDbCommand("select * from titles ", con)
GV1.DataSource = cmd.ExecuteReader()
GV1.DataBind()
con.Close()
----------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = new oledbcommand("select phone from authors
where au_fname=@fname and au_lname =@lname" ,con)
cmd.parameters.add("@fname" , txtfname.text)
cmd.parameters.add("@lname" , txtlname.text)
txtphone.text = cmd.executescalar()
con.close()
-----------------------------------------------------------
fname = ann
lastname = dull
----------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = New OleDbCommand("select * from titles where type =@type", con)
cmd.Parameters.Add("@type", txttype.Text)
GV1.DataSource = cmd.ExecuteReader()
GV1.DataBind()
con.Close()
--------------------------------------------------
update statement
----------------
we want to update phone field of authors table with
new phone number for a given first name and last name
update table_name set field = value
where condition
UPDATE authors set phone = @newPhone
where au_fname=@fname and au_lname=@lname
----------------------------------------------
executeNonQuery is a method used to insert , update
and for delete statements
cmd.executeNonquery()
----------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim cnt As Integer = 0
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = New OleDbCommand("update authors set phone=@phone
where au_fname=@fname and au_lname=@lname", con)
cmd.Parameters.AddWithValue("@phone", txtphone.Text)
cmd.Parameters.AddWithValue("@fname", txtfname.Text)
cmd.Parameters.AddWithValue("@lname", txtlname.Text)
cnt = cmd.ExecuteNonQuery()
Response.Write(cnt)
Response.Write(" " + "record(s) updated")
con.close()
--------------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim cnt As Integer = 0
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
cmd = New OleDbCommand("SELECT PHONE FROM AUTHORS
where au_fname=@fname and au_lname=@lname", con)
cmd.Parameters.AddWithValue("@fname", txtfname.Text)
cmd.Parameters.AddWithValue("@lname", txtlname.Text)
txtphone.Text = cmd.ExecuteScalar()
con.close()
--------------------------------------------------------------
con.Open()
cmd = New OleDbCommand("select * from asp where
id = @id", con)
cmd.Parameters.AddWithValue("@id", txtID.Text)
dr = cmd.ExecuteReader
dr.Read()
txtLname.Text = dr("lastname")
txtState.Text = dr("stateorprovince")
txtCountry.Text = dr("country")
txtMobile.Text = dr("MobilePhone")
con.Close()
-----------------------------------------------
cmd = New OleDbCommand("insert into asp
(lastname , stateorprovince,country,mobilephone)
values(@lname ,@state,@country,@mobile)", con)
cmd.parameters.addwithvalue("@lastname" , txtlname.text)
cmd.parameters.addwithvalue("@state" , txtstate.text)
cmd.parameters.addwithvalue("@country" , txtcountry.text)
cmd.parameters.addwithvalue("@mobile , txtmobile.text)
cmd.executeNonquery
con.close()
--------------------------------------------------------
delete statement is used to delete record(s)
syntax:
delete from table_name where condition
delete from asp where id= @id
-------------------------------------------------
lblMessage.Text = ""
Dim cnt As Integer = 0
lblMessage.text =""
con.Open()
cmd = New OleDbCommand("DELETE FROM ASP WHERE ID =@ID", con)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
cnt = cmd.ExecuteNonQuery
If (cnt > 0) Then
lblMessage.Text = "Record has been Deleted successfully"
Else
lblMessage.Text = "No Record Has been found"
End If
con.Close()
--------------------------------------------------------------
lblMessage.Text = ""
Dim cnt As Integer = 0
con.Open()
cmd = New OleDbCommand("Update asp set mobilephone=@phone",
con)
cmd.Parameters.AddWithValue("@phone", txtMobile.Text)
cnt = cmd.ExecuteNonQuery
If (cnt > 0) Then
lblMessage.Text = "Phone No has been Deleted successfully"
Else
lblMessage.Text = "No Record Has been found"
End If
con.Close()
------------------------------------------------
Protected Sub BTN_UPDATE_Click(ByVal sender As Object,
ByVal e As System.EventArgs) Handles BTN_UPDATE.Click
lblMessage.Text = ""
Dim cnt As Integer = 0
con.Open()
cmd = New OleDbCommand("Update asp
set mobilephone=@phone where id=@id", con)
cmd.Parameters.AddWithValue("@phone", txtMobile.Text)
cmd.Parameters.AddWithValue("@id", txtID.Text)
cnt = cmd.ExecuteNonQuery
If (cnt > 0) Then
lblMessage.Text = "Phone No has been updatedsuccessfully"
Else
lblMessage.Text = "No Record Has been found"
End If
con.Close()
End Sub
----------------------------------------------------------
Protected Sub BTN_QUERY_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BTN_QUERY.Click
con.Open()
lblMessage.Text = " "
txtLname.Text = ""
txtState.Text = " "
txtCountry.Text = " "
txtMobile.Text = " "
cmd = New OleDbCommand("select * from asp where id = @id",
con)
cmd.Parameters.AddWithValue("@id", txtID.Text)
dr = cmd.ExecuteReader
If dr.HasRows Then
dr.Read()
txtLname.Text = dr("lastname")
txtState.Text = dr("stateorprovince")
txtCountry.Text = dr("country")
txtMobile.Text = dr("MobilePhone")
Else
lblMessage.Text = "No Record Found"
End If
con.Close()
End Sub
Dim l1, l2, l3, l4 As Integer
l1 = (txtLname.Text).Length
l2 = (txtState.Text).Length
l3 = (txtCountry.Text).Length
l4 = (txtMobile.Text).Length
If (l1 > 0) And (l2 > 0) And (l3 > 0) And (l4 > 0) Then
cmd = New OleDbCommand("insert into asp
(lastname , stateorprovince,country,mobilephone)
values(@lname ,@state,@country,@mobile)", con)
cmd.Parameters.AddWithValue("@lastname", txtLname.Text)
cmd.Parameters.AddWithValue("@state", txtState.Text)
cmd.Parameters.AddWithValue("@country", txtCountry.Text)
cmd.Parameters.AddWithValue("@phone", txtMobile.Text)
cmd.ExecuteNonQuery()
Else
lblMessage.Text = "Data is blank and CAN NOT INSERTED"
End If
con.Close()
-------------------------------------------
create table db in MOHR database.
table contains the following columns
1. ICNO
2. NAME
3. SEX
---------------------------------------------
1. WRITE AN INTERFACE TO INSERT A RECORD
con.Open()
lblMESSAGE.Text = ""
cmd = New OleDbCommand("INSERT INTO DB (ICNO ,NAME, SEX)
VALUES (@IC ,@NAME, @SEX)", con)
cmd.Parameters.AddWithValue("@ic", txtICNO.Text)
cmd.Parameters.AddWithValue("@name", txtNAME.Text)
cmd.Parameters.AddWithValue("@sex", txtSEX.Text)
rec=cmd.ExecuteNonQuery
if (rec > 0 ) then
lblMessage.text="Record has been inserted Successfully"
else
lblMessage.text ="Record is not inserted"
End if
----------------------------------------------------------
Dim REC As Integer = 0
lblMESSAGE.Text = ""
con.Open()
cmd = New OleDbCommand(" SELECT * FROM DB WHERE ICNO=@NO", con)
cmd.Parameters.AddWithValue("@NO", txtICNO.Text)
dr = cmd.ExecuteReader
If dr.HasRows Then
DR.READ()
txtNAME.Text = dr("NAME")
TxtSEX.Text = dr("SEX")
else
lblMESSAGE.Text = "NO RECORD FOUND"
txtname.text=" "
txtsex.text=" "
End If
con.Close()
Data Binding
------------
Loading of items to the controls at the
runtime is known as DATA BINDING.
Controls can be DropDownList , ListBox , Checkbox List
and RadioButton List
-------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
Cmd = New OleDbCommand("select au_lname from authors", con)
dr = cmd.ExecuteReader
DDL1.DataSource = dr
DDL1.DATATEXTFIELD ="au_lname"
DDL1.DataBind()
DR.CLOSE()
con.Close()
End Sub
---------------------------------------------------------
Control : DropDownList - DDL2
DATABASE : PUBS
TABLE : AUTHORS
FIELD : STATE
DDL2 is binded to state field
---------------------------------------------------------
Order by field_name asc :sorting by ascending order
order by field_name :sorting by ascending order
Order by field_name desc :sorting by descending order
-----------------------------------------------------------
Control List : =RadioButton List
database = Nwind
Table = CATEGORIES
fieldname = CATEGORYNAME
-
-----------------------------------------------------------
Binding to checkboxList
-----------------------
Control : checkboxlist
Database : pubs
table : Titles
Field : Title
------------------------------------------------------------
Binding to ListBox Control
--------------------------
Control : ListBoxList
Database : NWIND
table : ProductName
Field : Products
---------------------------------------------------------- Understanding DataSets
----------------------
Unlike a DataReader , a dataset represents a disconnected
set of records.
Elements of datasets
--------------------
DataAdapter
DataSet
DataTable
DataRelation
DataView
- ---------------------------------------
dataset contains one or more datatables
Difference : database table and dataTable
DataAdapter : Class represents the bridge between
dataset and data source it. We use DataAdapter to
populate a dataset from an existing dataabase table.
---------------------------------------------------
Dim con As OleDbConnection
Dim dad As OleDbDataAdapter
Dim dst As DataSet
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\NWIND.mdb")
dad = New OleDataAdapter("Select * from Products", con)
dst = New DataSet
dad.fill(dst ,"Products")
GV1.DataSource=dst
GV1.DataBind()
---------------------------------------------------------------
Server Authentication
---------------------
con = new sqlconnection("server=localhost; uid=sa ;pwd=sa;
database=pubs"
con.open()
--------------------------------------------------------------
windows authentication
----------------------
con = new SqlConnection("server=localhost ;
Integrated Security=SSPI;
Initial Catalog=Northwind" )
---------------------------------------------------------
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader
con = new SqlConnection("server=localhost ;
Integrated Security=SSPI;
Initial Catalog=DBX")
con.Open()
cmd = New SqlCommand("select NAME from dbo.x", con)
dr = cmd.ExecuteReader()
While dr.Read()
Response.Write("
Response.Write(dr("NAME"))
End While
dr.Close()
con.Close()
End Sub
--------------------------------------------------------
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader
con = New SqlConnection("Data Source=localhost;
Initial Catalog=Dbx;Integrated Security=SSPI")
con.Open()
cmd = New SqlCommand("select NAME from dbo.x", con)
dr = cmd.ExecuteReader()
While dr.Read()
Response.Write("
Response.Write(dr("NAME"))
End While
dr.Close()
con.Close()
End Sub
---------windows Authentication----------------------------
con = New SqlConnection("Data Source=localhost;
Initial Catalog=adventureworks;
Integrated Security=SSPI")
---------------server Authentication-------------------------
con = New SqlConnection("server=localhost;uid=vb;pwd=dbx;
database=adventureworks")
------------------------------------------------------------
Dim con As SqlConnection
Dim cmd As SqlCommand
Dim dr As SqlDataReader
con = New SqlConnection("Data Source=localhost;
Initial Catalog=adventureworks;
Integrated Security=SSPI")
' con = New SqlConnection("server=localhost;
' uid=mohr; pwd=dbx; database=adventureworks")
con.Open()
cmd = New SqlCommand("select FirstName from
Person.Contact", con)
dr = cmd.ExecuteReader()
While dr.Read()
Response.Write("
Response.Write(dr("FirstName"))
End While
dr.Close()
con.Close()
-------------------------------------------------------
question :
List EmployeeID , Title, MaritalStatus and sex
from HumanResources.Employee of Adventureworks database
having male sex and married
select EmployeeID , Title , MaritalStatus , Gender
From HumanResources.Employee
where Gender='F' and MaritalStatus ='M'
GRIDVIEW1.DATASOURCE = DR.EXECUTEREADER()
GRIDVIEW1.DATABIND()
------------------------------------------------------
MS-ACCES DATABASE : NWIND
TABLE Products ( ProductID, ProductName,categoryName)
TABLE CATEGORIES (categoryId , CategoryName)
output
----------------------------------------------------------
ProductID ProductName CategoryId CategoryName
-----------------------------------------------------------
when we want to populate data from two or more tables
find a common column that appears in two or more tables.
cmd = new oledbcommand("select Productid , productname,
products.categoryID , categoryName
from products , categories
where products.categoryid= categories.categoryid" ,con)
----------------------------------------------------------
Dim con As OleDbConnection
Dim cmd As OleDbCommand
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\nwind.mdb")
con.Open()
cmd = New OleDbCommand("select Productid,productname,
products.categoryID,categoryName
from products, categories
where products.categoryid= categories.categoryid", con)
GridView1.DataSource = cmd.ExecuteReader()
GridView1.DataBind()
con.Close()
-----------------------------------------------------------
Use Nwind ms-access database of Employees
table to display EmployeeID , LastName
FirstName and Title whose city is London.
Display using Gridformat
Select EmployeeID , LastName , FirstName ,Title
From Employees
where city='London'
WORKING WITH XML
----------------
1. CREATE XML FILE
2. READING XML DATA USING ASP.NET
3. TRANSFORMING SQL SERVER DATA INTO XML FORMAT
4. CALLING SQL SERVER PROCEDURE IN ASP.NET
---------------------------------------------------
XML : Extensible Markup Language
Similar to HTML(Hypher Text Markup Language)
HTML is not Case sensitive.
XML is sensitive
Html has pre-defined tags
Xml has user defined tags
Html describes how to present data
XMl desribles the data.
XMl data is pure text file.
Any system can read pure text file
-----------------------------------------------------
Dim dst As DataSet
dst = new dataset
dst.ReadXml(MapPath("MENU.XML"))
gridview1.datasource =dst
gridview1.databind()
-------------------------------------------------------
Dim con As OleDbConnection
Dim dad As OleDbDataAdapter
Dim dst As DataSet
Dim strxml As String
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\pubs.mdb")
con.Open()
dad = New OleDbDataAdapter("select * from authors", con)
dst = New DataSet()
dad.Fill(dst, "authors")
strxml = dst.GetXml()
response.write("
" & server.htmlEncode(strxml) & "")
------------------------------------------------------
write asp.net program to convert into xml file
of products table from NWIND database , of those
category of type either "SeaFood" or "Meat/Poultry".
Store the xml file in c: drive
-------------------------------------------------------
"select ProductId, ProductName , Products.categoryid
, categoryName
from products, categories
where products.categoryid= categories.categoryid
and categoryname
in ('SeaFood' , 'Meat/Poultry')" ,con)
dad.fill(dst, "products")
dst.writexml("c:\products.xml")
response.write("Products file is saved")
-----------------------------------------------
TASK 1: draw dropdownlist control and add items
categoryname from categories table
of NWIND database.
----------------------------------------------------------
IsPostBack Property is false when the page
is loaded first time,
when the form is sent to back to server for processing
and sends back to client. once sent to client
again ispostback property is true.
----------------------------------------------------------
If Not IsPostBack Then
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\NWIND.mdb")
con.Open()
cmd = New OleDbCommand("Select categoryid , categoryname
from categories", con)
dr = cmd.ExecuteReader()
DDL1.DataSource = dr
DDL1.DataTextField = "Categoryname"
DDL1.DataValueField = "CATEGORYID"
DDL1.DataBind()
DDL1.ITEMS.INSERT(0, New ListItem("None Selected" , -1))
dr.Close()
con.Close()
End If
----------------------------------------------------------
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDL1.SelectedIndexChanged
Dim catid As Integer
Dim con as Oledbconnection
DIm cmd as OledbCOmmand
Dim dr as OlddbReader
catid = DDL1.SelectedItem.Value
If catid <> -1 Then
con = New OleDbConnection("Provider=Microsoft.jet.OLEDB.4.0;
Data Source=c:\NWIND.mdb")
con.Open()
cmd = New OleDbCommand("Select Productid ,ProductName
, UnitPrice from products where Categoryid=@id ", con)
cmd.Parameters.AddWithValue("@id", catid)
dr = cmd.ExecuteReader()
GridView1.DataSource = dr
GridView1.DataBind()
ELSE
GRIDVIEW1.DATASOURCE=""
GRIDVIEW1.DATABIND()
End If
End Sub
1 comment:
the corruption of various data formats may affect other files, but it can be fixed by the sql server 2000 data recovery utility
Post a Comment