Monthly Archives: October 2007

A Beginner's Guide to the DataSource Control

Posted on by
Reply

The DataSource control (new in ASP.net) is a real timesaver for programmers. Now, instead of writing all the code to populate a grid, it’s possible to use a DataSource control, a new ‘genre’ (if you will) of controls, which provide ‘declarative’ access to data. Though I won’t go into all of them (there are several – SQL, Access, XML, SiteMapDataSource and ObjectDataSource controls), I will cover the Database type DataControl (SQLDataSource and AccessDataSource). The properties are similar for both.

In general, at the basic level, what happens is that each DataSource control has many properties which the programmer populates, like the ConnectionString property and the SQL statement properties (SelectCommand, UpdateCommand, etc).

Once these properties have been provided, a presentation control (like a GridView, FormView or DetailsView control, all new in v2.0 of the Dotnet Framework) can point to the DataSource control, and without any coding at all – voila – a web page has been created that displays data from a data source. Naturally, it doesn’t do much except presentation, at this point, but like was said earlier, this is just the basic leve..

For the SelectCommand, one can choose a pure SQL Select statement (Select [field name list] from [Table name]), or a Stored Procedure. If a Stored Procedure is used, the ‘SelectCommandType’ property needs to be set thusly:

SelectCommandType="StoredProcedure"

Here, we might add that a new section has been added to the Web.Config file, specifically for Connection Strings. And, as you might figure, the section is called ‘ConnectionStrings’, and is placed inside the Configuration section of the Web.Config file, much like the AppSettings section was used earlier, for the connection strings. The main difference here, is, instead of ‘add key’, we use ‘add name’:

Continues…

Pages: 1 2

Updating/Editing a Chosen DataBase Record

Posted on by
Reply

Anyone who has had any dealings with databases knows there are several things which are needed:
Selecting Records
Inserting New Records
Deleting Records
Updating/Editing Recordsh

This code sample shows two things – Selecting Records, and then, choosing one record to then Update/Edit. It’s based on an earlier code sample (Populating a Form’s TextBoxes from a DB Query)

What you will see added here, is a very simple subroutine called ‘doUpdate’, which makes a connection to the database and performs a very simple Update SQL statement. There are only three fields in this simple table (ID, fname and lname). The ID field is an autonumbering/Identity field in SQL Server

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.1">
	<title>Choosing & Updating a Record</title>
<script language="VB" runat="server">
Dim intWdth, intID as integer
Dim strFirst, strLast, strTitle as string
Sub Page_Load(Source as Object, E as EventArgs)
	if not Page.IsPostBack then
		BindData
	End If
End Sub

Sub BindData()
	Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=YourDataBase"
	Dim sql as string = "Select id, fname, lname from TestInsert"
	Dim conn as New SQLConnection(strConn)
	Dim Cmd as New SQLCommand(sql, conn)
	Dim objDR as SQLDataReader
	conn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	list1.datasource=objDR
	list1.DataValueField="ID"
	list1.datatextfield="Lname"
	list1.databind
	list1.selectedindex=0
	conn.Close()

End Sub

Sub EditRecord(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=YourDataBase"
	Dim sql as string = "Select ID, Fname, Lname from TestInsert Where ID = "  & list1.selecteditem.value
	Dim conn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(sql, conn)
	conn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	While objDR.Read()
	   intID=objDR("ID")
	   strFirst=objDR("Fname")
	   strLast=objDR("Lname")
	End While
	page.databind()
	label1.text=""
	label2.text=""

End Sub

Sub doUpdate(Source as Object, E as EventArgs)
	Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=Your_Db"
	Dim MySQL as string = "Update TestInsert Set Fname=@Fname, Lname=@Lname Where id=@ID"
	Dim Conn as New SQLConnection(strConn)
	Dim Cmd as New SQLCommand(MySQL, Conn)
	  cmd.Parameters.Add(New SQLParameter("@ID", lblID.text))
	  cmd.Parameters.Add(New SQLParameter("@Fname", frmFirstName.text))
	  cmd.Parameters.Add(New SQLParameter("@Lname", frmLastName.text))

	Conn.Open()
	cmd.ExecuteNonQuery
	label1.text = "It''s Done!"

	label1.text="Successfully updated -- - "
	BindData
	list1.SelectedIndex=List1.Items.IndexOf(List1.Items.FindByValue(lblID.text))

End Sub
</script>
</head>
<body>
<form Name="form1" runat="server">
<table border="1" width="100%">
	<tr>
		<td align="center"width="145" valign="Top"><b><i>Choose from List:</i></b><br>
<asp:ListBox id="list1" Width="125px" height="150px" runat="server" /><br>
<asp:button id="button1" Text="Edit Record" onclick="EditRecord" runat="server" />
		</td>

		<td align="Left" valign="Top">
<b>ID :</b> <asp:Label id="lblID" Text="<%# intID %>" runat="server" /><br>
<b>FirstName : </b> <asp:textbox id="frmFirstName" Text="<%# strFirst %>" runat="server" /><br>
<b>LastName : </b><asp:textbox id="frmLastName" runat="server" Text="<%# strLast %>" /><br>
<br>
<br>
<asp:button id="button2" Text="Update" onclick="doupdate" runat="server" />
<i><asp:Label ID="label1" forecolor="red" runat="server" /></i><br>
<i><asp:Label ID="label2" runat="server" /></i>
		</td>
	</tr>
</table>
</form>
</body>
</html>

Coolest New ASP.NET 2.0 Additions

Posted on by
Reply

Here are the coolest features, as I see it, in ASP.Net 2.0:

  1. Cross Page Posting
  2. Focus API
  3. Validation Groups
  4. New Client-Script Features

Yes, ASP.Net was a huge advance in the programming world. BUT – there were several (maybe even quite a few) annoyances that really bugged most of us. This list of Top 4 Coolest Features in 2.0 address some of those items directly.

Ok, let’s start with the biggest one of the four - . For all of us that came from Classic ASP world, this is a biggie! Though I really like the new paradigm of posting back to the same page, and use that most of the time, we’ve all found instances that really needed this feature.

if you go to the quickstarts, you’ll find an example of how to handle this. then, you’ll say to yourself, “Here we go again, with the off the wall, klunky code concepts”. However, don’t worry about it - I have good news.

First off, the posting page is NOT listed in the FORM tag. That’s not really a big problem, because it’s a property in the button control – the PostBackUrl property. For example:





<asp:Button
	id="button1"
	Text="Submit"
	onclick="whatever"
	PostBackURL="NextPage.aspx"
	Runat="server" />

Then, (here’s where the new coding concept comes in) according to the QuickStarts example, we should do this, in the Postback page’s Page_Load event:
Continues…

Pages: 1 2

DropDownList – DataField, ValueField (from DB)

Posted on by
Reply

This code sample uses the SQL Managed Provider, along with the Northwind Database (Employees table), to show how to populate a DropDownList with text as well as a value, and how to refer to them in order to retrieve the actual data.

As usual, just copy the code to a new file and change the connection info to match that of your system.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.1">
	<title>Untitled</title>
<script language="VB" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
if not Page.IsPostBack then
	Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=NorthWind"
	Dim MySQL as string = "Select EmployeeID, LastName from Employees"
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	ddl.DataSource = objDR
	ddl.DataBind()
end if
End Sub

Sub doit(Source as Object, E as EventArgs)
label1.text="<b><i>Selected Data:</i></b><br>Last Name = " & _
ddl.selecteditem.text & " (DataTextField)<br>" & _
"Employee ID = " & ddl.selectedItem.value & " (DataValuefield)"
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
<asp:DropDownList id="ddl" DataValueField="EmployeeID" DataTextField="LastName" runat="server" />
<asp:Button id="button1" Text="Get Text & Value" onclick="doit" runat="server" />
</Form>
<asp:Label ID="label1"  runat="server" />
</body>
</html>

Display CSV File with DataGrid

Posted on by
Reply

This code sample shows how to read a .CSV file using the OleDb Managed Provider, and then display it, using a DataGrid. For this particular sample, after downloading the Lotto Texas Results in a CSV file, I just trimmed it a little for speed and ease of viewing. If no other .CSV file is available, you can download the full file here:
Download Lottery file
To make this work on your system, just adjust the path to match your needs on your system.

Also, this sample shows how to add Header Text when the .CSV file doesn’t have any headers. — Extra Thanks to DataGrid Girl for the extra help on the header definitions, since the CSV file comes without headers. To Do this, just create a DataBound Subroutine using DatagridItemEventArgs as an argument, then refer to it in the DataGrid properties:
OnItemDataBound=”DataBound”

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<script language="VB" runat="server">
	dim x as integer
Sub Page_Load(Source as Object, E as EventArgs)
	GetCSV()
End Sub

Sub GetCSV()
	Dim strConn As String
	strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
	"Data Source=c:\inetpub\wwwroot;Extended Properties=Text;"
	Dim Conn As New System.Data.OleDb.OleDbConnection(strConn)
	Conn.Open()

	Dim da As New System.Data.OleDb.OleDbDataAdapter("Select * from lotto.csv", Conn)
	Dim ds As New DataSet("lotto.csv")

	da.Fill(ds, "lotto.csv")
	Dim dt As DataTable
	dt = ds.Tables("lotto.csv")
	MyDataGrid.Datasource=ds.Tables("lotto.csv").DefaultView
	MyDataGrid.DataBind
End Sub

Sub DataBound (s as Object, e as DatagridItemEventArgs)
Select Case e.Item.ItemType
Case ListItemType.Header
	e.Item.Cells(0).Text="<b>Month</b>"
	e.Item.Cells(1).Text="<b>Day</b"
	e.Item.Cells(2).Text="<b>Year</b>"
	e.Item.Cells(3).Text="<b>First</b>"
	e.Item.Cells(4).Text="<b>Second</b>"
	e.Item.Cells(5).Text="<b>Third</b>"
	e.Item.Cells(6).Text="<b>Fourth</b>"
	e.Item.Cells(7).Text="<b>Fifth</b>"
	e.Item.Cells(8).Text="<b>Sixth</b>"
	For x=0 to 8
	e.item.cells(x).Font.Size= FontUnit.Point (14)
	next

Case ListItemType.Item, ListItemType.AlternatingItem

	For x=0 to 8
	e.item.Cells(x).HorizontalAlign=HorizontalAlign.Center
	e.item.cells(x).Font.Size= FontUnit.Point (11)
	next
End Select
End Sub
</script>

<div align="center">
<form runat="server" method="post">
<b><font Size="5" Color="#0000FF" Face="Arial">Lotto Texas Results</font></b>
<asp:Datagrid runat="server"
	Id="MyDataGrid"
	GridLines="Both"
	cellpadding="2"
	cellspacing="0"
	Headerstyle-BackColor="#8080C0"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Size="14"
	BackColor="#8080FF"
	Font-Name="Arial"
	Font-Size="12"
	AlternatingItemStyle-BackColor="#C0C0C0"
	AlternatingItemStyle-Font-Name="Arial"
	AlternatingItemStyle-Font-Size="11"
	BorderColor="Black"
	OnItemDataBound="DataBound">
</asp:DataGrid>
</form>
</div>