Monthly Archives: November 2008

Referring to the ConnectionStrings section of Web.Config in code

Posted on by
Reply

In ASP.Net 1.1, we used to use the Web.Config file to store the connectionstrings in the AppSettings section, then we referred to each setting in code like this:
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(“MyDBConnection”))

Now, with ASP.net 2.0, there’s a new ConnectionStrings section. To refer to a setting there, now, it’s a little different:
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings(“YourConnStringName”).ConnectionString)

Retrieving the Full DB Schema – SQL Server

Posted on by
Reply

This code sample simply shows how to retrieve the full schema of a SQL Server table. For this sample, we’re using the Northwind database – the Employees table. Be sure to set the connection string on your machine, so that it is correct for the DB you are accessing.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<script language="VB" Runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=Northwind"
Dim MySQL as string = "Select * from Employees"
Dim MyConn as New SQLConnection(strConn)
Dim schemaTable As DataTable
Dim myField As DataRow
Dim myProperty As DataColumn
Dim Cmd as New SQLCommand(MySQL, MyConn)
MyConn.Open()
Dim dr As SQLDataReader
dr = cmd.ExecuteReader
schemaTable = dr.GetSchemaTable()
For Each myField In schemaTable.Rows
For Each myProperty In schemaTable.Columns
label1.text+="<b>" & MyProperty.ColumnName & " = </b>" & myField(myProperty).ToString() & "<br>"
Next
label1.text+="<p>"
Next
dr.Close()
MyConn.Close()
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.0">
<title>Getting Column Names from a SQL Server Table</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:Label ID="label1" Runat="server" />
</form>
</body>
</html>

DataFormatString does not work in Boundfield

Posted on by
Reply

The new Gridview control in v2.0 take over the formatting of items in the BoundFields. Therefore, using a DataFormatString by itself won’t work. To change the format to, let’s say, Currency, we’d need to, not only set the DataFormatString=”{0:c}”, but we’d need to make use of the HTMLEncode property, setting it to ‘False’

Retrieving the Full DB Schema – OleDB

Posted on by
Reply

This code sample simply shows how to retrieve the full schema of an MS Access table. For this sample, we’re using the Northwind database – the Employees table. Be sure to set the connection string path on your machine, so that it is correct for the DB you are accessing.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<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("\data\northwind.mdb") & ";"
		Dim MySQL as string = "Select * from Employees"
		Dim MyConn as New OleDBConnection(strConn)
		Dim schemaTable As DataTable
		Dim myField As DataRow
		Dim myProperty As DataColumn
		Dim Cmd as New OLEDBCommand(MySQL, MyConn)
		MyConn.Open()
		Dim dr As OleDbDataReader
		dr = cmd.ExecuteReader
		schemaTable = dr.GetSchemaTable()
		For Each myField In schemaTable.Rows
			For Each myProperty In schemaTable.Columns
					label1.text+="<b>" & myProperty.ColumnName & "</b> = " & myField(myProperty).ToString() & "<br>"
		     Next
		     label1.text+="<p>"
		Next
		dr.Close()
		MyConn.Close()
	End Sub
</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.0">
		<title>Getting Column Names from MS Access Table</title>
	</head>
	<body>
		<form id="form1" Runat="server">
			<asp:Label ID="label1" Runat="server" />
			<asp:DropDownList id="ddl" Runat="server">
			</asp:DropDownList>
		</form>
	</body>
</html>

Binding DDL With a DataSource – Then adding Item to Top of List

Posted on by
Reply

With the new declarative form of databinding in ASP.Net 2.0, we can use DataSource controls for DataAccess, and then bind the Data Control with the DataSource control, to display the data. But many times, we need to add an item to the top of the DropDownList like ‘Select Item’. You can still do this in v2.0.

Just set the ‘AppendDataBoundItems’ property of the DropDownList to ‘True’, then, create the code to add the item in your Page_Load event, surrounded by a Postback block.

Full Code Sample provided here:
http://aspnet101.com/aspnet101/aspnet/codesample.aspx?code=dbDDL20

Getting Column Names – OleDb/MS Access

Posted on by
Reply

Sometimes we find it necessary to retrieve the names of the columns of a particular table in an OleDb (MS Access, for this example).

This sample shows how to get those names and add them to a DropDownList. Be sure to change the connection string, so that it matches the path to your MS Access database

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<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("\data\northwind.mdb") & ";"
Dim MySQL as string = "Select * from Employees"
Dim MyConn as New OleDBConnection(strConn)
Dim schemaTable As DataTable
Dim myField As DataRow
Dim myProperty As DataColumn
Dim Cmd as New OLEDBCommand(MySQL, MyConn)
MyConn.Open()
Dim dr As OleDbDataReader
dr = cmd.ExecuteReader
schemaTable = dr.GetSchemaTable()
For Each myField In schemaTable.Rows
For Each myProperty In schemaTable.Columns
if myProperty.ColumnName="ColumnName" then
ddl.Items.add(myField(myProperty).ToString())
End If
Next
Next
dr.Close()
MyConn.Close()
End Sub
</script>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 4.0">
<title>Getting Column Names from MS Access Table</title>
</head>
<body>
<form id="form1" Runat="server">
<asp:Label ID="label1" Runat="server" />
<asp:DropDownList id="ddl" Runat="server">
</asp:DropDownList>
</form>
</body>
</html>

AccessDataSource – so easy

Posted on by
Reply

As you may or may not know, with ASP.Net v2.0, there are several DataSource controls, like SQLDataSource, and AccessDataSource. The AccessDataSource is even easier to use than the SQLDataSource.

There is no ‘ConnectionString’ property for the AccessDataSource, but there is a ‘DataFile’ property. All it requires is a path to the Access Database you want to use:

<asp:AccessDataSource ID="DS1"
	 Runat="Server"
	 SelectCommand = "SELECT  EmployeeID,  LastName,  FirstName,  Title From  NWEmployees"
	 DataFile="\data\northwind.mdb;">
</asp:AccessDataSource>

Working with Calender Day of Week

Posted on by
Reply

This sample shows how to get the Day of Week when clicking a particular calendar date, as well as manipulating the display of the calendar based on the day of the week

<script language="VB" Runat="server">
	Sub Page_Load(Source as Object, E as EventArgs)

	End Sub
Sub Calendar_DayRender(source As Object, e As DayRenderEventArgs)
             Dim d as CalendarDay
            d = e.Day
If e.Day.IsWeekend Then
	if d.Date.DayofWeek.ToString="Sunday" then
 		e.Cell.BackColor = System.Drawing.Color.Green
 	else
 		e.Cell.BackColor = System.Drawing.Color.Red
 	End If
e.Cell.ForeColor = System.Drawing.Color.White
End If
End Sub

Sub GetDate(Source as Object, E as EventArgs)
	label1.text=cal1.SelectedDate.DayofWeek.ToString
End Sub
</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.0">
		<title>Untitled</title>
	</head>
	<body>
		<form id="form1" Runat="server">
			<asp:Calendar id="cal1" Runat="server"
				 WeekendDayStyle-Font-Bold="True"
				ondayrender="Calendar_DayRender"
				 Font-Name="Arial"
			OnSelectionChanged="GetDate" Backcolor="White" Forecolor="Black"
				Borderwidth="2" ShowGridLines="true" />
			<asp:Label ID="label1" Runat="server" />
		</form>
	</body>
</html>

Holidays with the Calendar Control

Posted on by
Reply

Adding preset Holidays to the displayed ASP.Net Calendar Control is really very simple. First, we need to creat a DayRender Event, which we call ‘Cal1_DayRender’, for this example. Then, we need to point to that DayRender Event, in the Control Tag itself (OnDayRender=”Cal1_DayRender”).

Then, we need to create an array (globally) with the max number of months and the max number of days possible: 

Dim holidays(12, 31) As String

Then, inside the Page_Load Event, we just list the holidays we wish to have displayed automatically, using the month and the date. That’s all there is to it!

As an extra here, we can also see how to programmatically change the font and font size of a label, in the Cal1_DayRender Event.

<script language="VB" Runat="server">
Dim holidays(12, 31) As String
	Sub Page_Load(Source as Object, E as EventArgs)
	  	holidays(8, 5) = "Birthday"
	 	holidays(8, 14) = "Anniversary"
		holidays(1,1)   = "New Year''s Day"
		holidays(7,4)="The US Declares Independance"
		holidays(12,25)="Christmas"
		holidays(12,31)="New Year''s Eve"
	End Sub
Sub Cal1_DayRender(sender As Object, _
   e As DayRenderEventArgs)
   If e.Day.IsOtherMonth Then
      e.Cell.Controls.Clear()
   Else
      Dim aDate As Date = e.Day.Date
      Dim aHoliday As String = holidays(aDate.Month, aDate.Day)
      If (Not aHoliday Is Nothing) Then
         Dim aLabel As Label = New Label()
		aLabel.Font.Name = "verdana"
 		aLabel.Font.Size = FontUnit.Point(10)
		aLabel.Text = "<br>" & aHoliday
		e.Cell.Controls.Add(aLabel)
      End If
   End If
End Sub
</script>
<html>
	<head>
		<meta name="GENERATOR" Content="ASP Express 4.0">
		<title>Holidays in the ASP.Net Calendar</title>
	</head>
	<body><asp:Label ID="PutIDNameHere"   Runat="server" />
		<form id="form1" Runat="server">
			<asp:Calendar runat="server"
				id="cal1"
				Backcolor="White"
				Forecolor="Black"
				Borderwidth="2"
				Width="75%"
				ShowGridLines="true"
				OnDayRender="Cal1_DayRender"
				SelectedDayStyle-Backcolor="#DEEFFF"
				SelectedDayStyle-forecolor="Black"
				TitleStyle-BackColor="#DEEFFF"
				TitleStyle-Font-Bold="True"
				TitleStyle-Height="36px"
				OtherMonthDayStyle-ForeColor="gray"
				TodayDayStyle-Font-Bold="True"
				TodayDayStyle-Font-Italic="True"
				TodayDayStyle-Font-Size="12pt"
				DayHeaderStyle-Font-Bold="True"
				DayHeaderStyle-BackColor="LightGray" />
		</form>
	</body>
</html>