Adding a MailTo Link inside a DataGrid

Suppose you have a database table which contains an email field, and you’d like to display that field, as well as others in a DataGrid. Let’s go one step further – - let’s say you’d like to display that email field as a clickable MailTo link. One way that this can be easily be done using TemplateColumns. First, you create your DataGrid start and end tags. Then, inside these tags, you include ‘Columns’ start and end tags. That’s where you put your TemplateColumn. Here’s how to do it:

<Columns> <i>.....Put any other boundcolumns you want inside here also</i> <asp:TemplateColumn HeaderText="Email Address" HeaderStyle-Font-Bold="True"> <ItemTemplate> <A HREF="Mailto:<%# Container.DataItem("email") %>"> <%# Container.DataItem("email") %> </a> </ItemTemplate> </asp:TemplateColumn> </Columns>

Getting CheckBoxList Selected Items

This sample uses the Northwind Database (OleDb Managed Provider) to populate a CheckBoxList. Then, the code goes on to show how to get the list of selected items.

Also, since we’re displaying the items, separated by a comma, we use a couple of string functions to display it. When we say ‘Stuff = Stuff & Item.Text & “, “‘, naturally, when we get to the last item selected, there is also a comma at the end of it. To ‘chop’ it down, we use the length function to help us do this.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.5">
<script language="VB" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)

if not Page.IsPostBack then
	Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("\northwind.mdb") & ";"
	Dim MySQL as string = "Select CategoryID, CategoryName from Categories"
	Dim conn as New OleDBConnection(strConn)
	Dim objDR as OleDBDataReader
	Dim Cmd as New OLEDBCommand(MySQL, conn)
	conn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	cbl1.DataSource = objDR
	cbl1.DataTextField="CategoryName"
	cbl1.DataValueField="CategoryID"
	cbl1.DataBind()
end if
End Sub

Sub doit(Source as Object, E as EventArgs)
Dim Stuff as String
   Dim Item As ListItem
        For Each Item In cbl1.Items
            If Item.Selected Then
                Stuff = Stuff & Item.Text & ", "
            End If
        Next
Stuff=Stuff.Substring(0, (stuff.length-2))
label1.text=" <b>Selected Items Were: </b><br> " & stuff
End Sub
</script>
	<title>Getting CheckBoxList Selected Items</title>
</head>
<body>
<Form id="Form1" runat="server">
<asp:CheckBoxList id="cbl1" runat="server" /><asp:Button id="button1" Text="Get Items" onclick="doit" runat="server" />
<br>
<asp:Label ID="label1"  runat="server" />
</Form>
</body>

MS Access Update Problems with Parameters

A very common problem, using parameters with MS Access, in ASP.Net is that it does not update the database, but also, it doesn’t return an error. The problem, most likely, is that the parameters are not in the same order as the SQL statement.

Remember to put the list of parameters in the same order as they hold sql statement, including any parameters in the Clause(s) at the end of the statement.

Confirmation Before Form Submission

Sometimes, we need to add a Javascript confirmation attribute to a button’s click event. When the button is clicked, this will give them an extra pop-up box that says something like, “Are you SURE you want to submit this form?”.

This sample shows how to do this, inside the Page_Load event, showing how to add the ‘onclick’ event as an attribute, to the button which submits the form.

<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Confirmation Before Form Submission</title>
<script language="VB" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Button1.Attributes("OnClick") = "return confirm(''Would you like to submit this form?'');"
End Sub

Sub doit(Source as Object, E as EventArgs)
	label1.text="The Name you entered is " & txtFirst.text & " " & txtLast.text
End Sub
</script>
</head>
<body>
<Form id="form1" runat="server">
First Name: <asp:TextBox id="txtFirst" runat="server" /><br>
Last Name: <asp:TextBox id="txtLast" runat="server" />
<asp:Button id="Button1" Text="Submit" onclick="doit" runat="server" />
</Form>
<asp:Label ID="label1"  runat="server" />
</body>
</html>

Show DataGrid Page Numbers only if Pages >1

When using DataGrid paging, it seems rather ludicrous to show the ’1′ in the Pager Section, if there is only one page. However, you can get around this. First – do not set ‘Paging=True’ in the DataGrid tag itself.

Second – check the number of rows in the returned DataSet. If the number of rows is smaller than, or equal to the PageSize number in the DataGrid tag, set the ‘AllowPaging‘ property of the DataGrid to ‘False‘. Otherwise set it to ‘True.


If DS.Tables(0).Rows.Count <= MyDataGrid.PageSize Then      MyDataGrid.AllowPaging = False Else      MyDataGrid.AllowPaging = True End If

Beginners Guide to Functions

In this tutorial we are going to discuss the differences between subroutines and functions in both VB.Net and C#.Net. These are both considered ‘methods’ in the .Net world.

The basic difference between the two, is that a function returns some sort of data, but a subroutine does not. In other words, when we say ‘returns data’, the data could be a string, an integer, a DataSet, a DataReader or many other objects in the .Net world. The SubRoutine (or Sub) does not.

These two items (Subroutines and Functions) are some of the basic blocks of reuse when it comes to programming in .Net.

First, let’s look at Functions. and we’ll use a DataSet as an example. Inside your application, you might have many places in which you need a Dataset. There’s no reason to write the code over and over, so you’d create a method (in this case a Function) to return a Dataset.

In VB.Net, the signature would be something like this:

			Private Function CreateDataSet() As DataSet
			' your programming goes here
			End Function

In C#, the signature would be something like this:

			private DataSet CreateDataSet()
			{
			// your programming goes here
			}

In each case, ‘CreateDataSet’ is the name of the function. In VB.Net, adding ‘As DataSet’ at the end shows that you are wanting to create a DataSet and return it to the program. In C#, ‘DataSet’ is included in the initial signature, showing that you are, indeed, wanting to return a DataSet to the program.

You probably noticed the parentheses, just after ‘ CreateDataSet’. This is where you will put any arguments/parameters affecting the Function. Your particular needs will dictate which arguments or how many arguments you’ll need to fulfill the needs of your Function. Think of the arguments as being the items that might change from usage to usage throughout your program. They basically are variables, but instead of your normal declaration, they are included in the arguments of the Function.

For instance, if every place in your code used the same database table, and the only thing that needs to be different is your sql statement, then, you can include one argument (a string) which will be your sql statement.

In VB.Net, the signature would be something like this:
Continues…

Handling Database Nulls with a DataReader

In this code sample, I use the OleDb Managed Provider (with the MS Access Biblio Database) to show how to handle Database Nulls with a Datareader. This uses a ‘helper function’ in order to handle null values at display time.

As it turns out, there weren’t many actual ‘Year Born’ dates entered into the database, so I had to add in a few just to show a varied display.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Handling Database Nulls with a DataReader</title>
	<script language="VB" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
	Dim strConn as string = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
	& server.mappath(".\BIBLIO.MDB") & ";"
	Dim MySQL as string = "Select author, [Year Born] from Authors"
	Dim MyConn as New OleDBConnection(strConn)
	Dim objDR as OleDBDataReader
	Dim Cmd as New OLEDBCommand(MySQL, MyConn)
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	MyDataList.DataSource = objDR
	MyDataList.DataBind()
End Sub

Function fixBirthDate(sItem) as String
if sItem is System.DBNull.Value Then
	fixBirthDate="<i><font Color=''#FF0000''>No Date Specified</font></i>"
else
	fixBirthDate="<b>" & sItem & "</b>"
End If
End function

</script>
</head>
<body>

<asp:DataList runat="server"
	Id="MyDataList"
	GridLines="Both"
	cellpadding="2"
	cellspacing="2"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Size="8"
	Font-Name="Arial"
	Font-Bold="false"
	RepeatDirection="Horizontal"
	RepeatColumns="4"
	Font-Size="8">
	<ItemTemplate>
		<%# Container.DataItem("Author")%> -
		<%# fixBirthDate(Container.DataItem("YearBorn"))%>
	</ItemTemplate>
</ASP:DataList>

</body>
</html>

Creating a Method to Return a DataSet in C#

In this tutorial, we’ll show how to create a method, within your ASP.Net code, which returns a Dataset, using C#. To demonstrate this, we’ll be using the Northwind database, which is easily accessible, if you don’t already have it available in your SQL Server. We created a Gridview on our ASP.Net page and used the ‘Rainy Day’ formatting option:


 <asp:GridView ID="GridView1" runat="server" BackColor="White"
BorderColor="#999999" BorderStyle="None" BorderWidth="1px" CellPadding="3"
GridLines="Vertical">
<RowStyle BackColor="#EEEEEE" ForeColor="Black" />
<FooterStyle BackColor="#CCCCCC" ForeColor="Black" />
<PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />
<SelectedRowStyle BackColor="#008A8C" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="#DCDCDC" />
</asp:GridView>

Then, in our code, we created the method, which returns a dataset:


private DataSet getData()
        {
// get the connection string
String conn = ConfigurationManager.ConnectionStrings["NW"].ConnectionString;
// build our SQL Select statement (should preferably be a stored procedure)
String mySQL = "Select ProductName, QuantityPerUnit, UnitPrice from Products where CategoryID=1";
DataSet ds;
using (SqlDataAdapter da = new SqlDataAdapter(mySQL, conn))
{
ds = new DataSet();
 da.Fill(ds, "ProductInfo");
}
return ds; // 'returns' the dataset
}

All that is left is to consume, or use the getData() method, to add the data to the Gridview, in some event on the page:
Continues…

Checking User ID in Database

This sample shows how to check user input against data already in a database. In many cases, when a user is ‘signing up’ for a web site, he/she is asked to choose a username and password. Here, you will see how to use the SQL Managed Provider to query the database BEFORE inserting the user id into the database.

To see a code sample of how to insert data from a form, into a database, check out this sample:
Inserting Record into Database.

Here, we just use the Employees Table of the Northwind Database, but we add a UID (and password, if you want to complete the functionality) column/field to the table.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Checking User ID in Database</title>
<script language="VB" runat="server">
Dim intCount as Integer

Sub doCheck(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=YourDB"
Dim MySQL as string = "Select Count(uid) from Employees where uid=@uid"
Dim MyConn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(MySQL, MyConn)
cmd.Parameters.Add(New SQLParameter("@uid", txtuid.text))
MyConn.Open()
intCount=Cmd.ExecuteScalar
MyConn.Close
	if intCount>0 then
		label1.text="Sorry, that user id is already taken. Try another"
	else
		label1.text ="The user id you chose is not being used "
		''here, you would insert the user information into the database
	End If
End Sub

</script>
</head>
<body>
<Form id="form1" runat="server">
Enter User Name and Password you would like to use:<br>
<table>	<tr>
		<td align="Right" valign="Top">User ID: </td>
		<td align="Left" valign="Top"><asp:TextBox id="txtUID" runat="server" /></td></tr>
	<tr>
		<td align="Right" valign="Top">PWD: </td>
		<td align="Left" valign="Top"><asp:TextBox id="txtPWD" runat="server" /></td>
	</tr>
		<tr>
		<td align="right" valign="Top" Colspan="2"><asp:Button id="button1" Text="Submit" onclick="doCheck" runat="server" /></td>
	</tr>
</table>
<asp:Label ID="label1"  runat="server" />
</Form>
</body>
</html>

Check for Null before inserting into Database

Here’s a function to ensure a Null value gets inserted into the database.

Function CheckForNULL(strStringToCheck) if len(trim(strStringToCheck)) = 0 then CheckForNULL =dbNull.Value else CheckForNULL = strStringToCheck end if end function

If you have a DateTime field, without a function like this, a default date will be entered (ie 1/1/1900 12:00:00 AM), instead of null, when your insert is executed.