(c) 2002 Visual Studio Magazine
Fawcette Technical Publications

Issue: January 2002
Section: ASP.NET
Author: Robert Lair

VB.NET	Call the ContactsListByLetter Stored Procedure
Listing 1	The List method accepts one argument: the letter indicating what contacts to return. The List method then calls the ContactsListByLetter stored procedure, and returns all contacts that begin with that letter in a DataSet.  The method then returns the DataSet to the calling procedure.

Public Shared Function List(ByVal _
	BeginsWith As String) As DataSet

	Dim myConnection As SqlConnection = New _
	SqlConnection("server=localhost;uid=sa;pwd=; _
		database=contactmanager")
	Dim myCommand As SqlCommand = New SqlCommand _
		("ContactsListByLetter", myConnection)

	'Mark the Command as a SPROC
	myCommand.CommandType = _
		CommandType.StoredProcedure

	'Add Parameter to the Command
	Dim paramLetter As SqlParameter = New _
		SqlParameter("@Letter", _
		SqlDbType.VarChar, 1)
	paramLetter.Value = BeginsWith
	myCommand.Parameters.Add(paramLetter)

	myConnection.Open()
	Dim myDataAdapter As SqlDataAdapter = New _
		SqlDataAdapter()
	myDataAdapter.SelectCommand = myCommand

	Dim myDataSet As DataSet = New DataSet()
	myDataAdapter.Fill(myDataSet, "DataSet")

	Return myDataSet

End Function

VB.NET	Display Contacts
Listing A	Use the BindContacts and Page_Load methods to bind the two DataLists to their data sources and invoke the DataBind() method, which performs the data binding.  This is what causes the data to be loaded into the DataLists.

Private Sub BindContacts(ByVal Letter As String)

Me.Contacts.DataSource = _
	ContactManager.Contacts.List(Letter)
Page.DataBind()

End Sub

Private Sub Page_Load(ByVal sender As _
	System.Object, ByVal e As _
	System.EventArgs) Handles MyBase.Load

	'Put user code to initialize the page here

	LetterList.DataSource = ContactManager. _
		Contacts.CreateAlphabetList()

	If Not IsPostBack() Then
		If Not Session("Letter") = "" Then
			BindContacts(Session("Letter"))
		Else
			'load contacts that start with "A"
			BindContacts("A") 
		End If
		Page.DataBind()
	End If

End Sub

VB.NET	Bind the DataList
Listing B	Use the Page_Load and Bind_Contact methods to bind the DataList to the proper data source.  The Page_Load binds the LetterList DataList and the Bind_Contact method binds to the Contacts DataList.  When the DataBind method is executed, the data will be loaded into the DataLists.

Private Sub Bind_Contact(ByVal ContactId As _
	Integer)

If ContactId > 0 Then
	Me.ContactDetails.DataSource = _
		ContactManager.Contacts.GetDetails _
		(ContactId)
Else
	Me.ContactDetails.DataSource = _
		ContactManager.Contacts. GetBlankContact()
	Me.ContactDetails.EditItemIndex = 0
End If
Me.ContactDetails.DataBind()

End Sub

Private Sub Page_Load(ByVal sender As _
	System.Object, ByVal e As _
	System.EventArgs) Handles MyBase.Load

	'Put user code to initialize the page here
	Dim ContactId As Integer
	If Not Request.Params("cid") = "" Then
		ContactId = Int32.Parse(Request.Params _
			("cid"))
	Else
		ContactId = 0
	End If

	Session("ContactId") = ContactId
	If Not IsPostBack() Then
	Bind_Contact(CInt(Session("ContactId")))
	End If

End Sub


VB.NET	Cancel the Command
Listing C	Clicking on the Edit Contact button and the Cancel button invokes the EditCommand and CancelCommand events.  This listing contains the implementation of these two events.  

Private Sub ContactDetails_EditCommand(ByVal _
	source As Object, ByVal e As System.Web.UI. _
	WebControls.DataListCommandEventArgs) _
	Handles ContactDetails.EditCommand

	Me.ContactDetails.EditItemIndex = 0
	Bind_Contact(CInt(Session("ContactId")))

End Sub

Private Sub ContactDetails_CancelCommand(ByVal _
	source As Object, ByVal e As System.Web.UI. _
	WebControls.DataListCommandEventArgs) _
	Handles ContactDetails.CancelCommand

	Me.ContactDetails.EditItemIndex = -1
	Bind_Contact(CInt(Session("ContactId")))

	If CInt(Session("ContactId")) = 0 Then
	Response.Redirect("ContactsByLetter.aspx")
	End If

End Sub

VB.NET	Execute the ContactDetails_UpdateCommand Method
Listing 2	The ContactDetails_UpdateCommand method handles the UpdateCommand event for the DataList.  This method collects the updated values for the contact and writes them back to the database.

Private Sub ContactDetails_UpdateCommand(ByVal _
	source As Object, ByVal e As System.Web.UI. _
	WebControls.DataListCommandEventArgs) _
	Handles ContactDetails.UpdateCommand

Dim TxtFName As TextBox = e.Item.FindControl _
	("FName")
Dim TxtLName As TextBox = e.Item.FindControl _
	("LName")
Dim TxtAddress As TextBox = e.Item.FindControl _
	("Address")
Dim TxtCity As TextBox = e.Item.FindControl _
	("City")
Dim TxtState As TextBox = e.Item.FindControl _
	("State")
Dim TxtZip As TextBox = e.Item.FindControl("Zip")
Dim TxtPhone As TextBox = e.Item.FindControl _
	("Phone")
Dim TxtEmail As TextBox = e.Item.FindControl _
	("Email")

	Dim FName = TxtFName.Text
	Dim LName = TxtLName.Text
	Dim Address = TxtAddress.Text
	Dim City = TxtCity.Text
	Dim State = TxtState.Text
	Dim Zip = TxtZip.Text
	Dim Phone = TxtPhone.Text
	Dim Email = TxtEmail.Text

	If CInt(Session("ContactId")) = 0 Then
		Session("ContactId") = Contacts.Add _
			(LName, FName, Address, City, State, _
			Zip, Phone, Email)
		Session("Letter") = Left(LName, 1)
	Else
	Contacts.Update(CInt(Session("ContactId")), _
		LName, FName, Address, City, State, Zip, _
		Phone, Email)
	End If

	Me.ContactDetails.EditItemIndex = -1
	Bind_Contact(CInt(Session("ContactId")))

End Sub
