Monthly Archives: November 2007

Data from multiple columns in a dropdownlist

Posted on by
Reply

Code shows how to display multiple fields in a dropdownlist

Dim myconnection As SqlConnection
Dim myda As SqlDataAdapter
Dim ds As DataSet
Dim strConn As String
strConn = "Server=localhost;uid=sa;password=;database=northwind;"
If Not Page.IsPostBack Then
    myconnection = New SqlConnection(strConn)
    myda = New SqlDataAdapter("Select * from Categories", myconnection)
    ds = New DataSet()
    myda.Fill(ds, "Table")
    Dim dr As DataRow
    For Each dr In ds.Tables(0).Rows
        DropDownList1.Items.Add(dr("CategoryID") & dr("CategoryName"))
    Next
End If

Two Page Data Retrieval in ASP.NET

Posted on by
Reply

Here’s the scenario, using ASP.Net 2.0 (and above):
We have two web pages called GV1.aspx and NewPage.aspx. In GV1.aspx, we have a GridView, listing only a few fields from a database. From that page, we will designate one field as a HyperLinkField, so that, by clicking on it, we will be transferred to the second page (Newpage.aspx), using a QueryString. In that second page, we will retrieve all fields in the table based on the ID used in the QueryString.

This actually very easy to accomplish using ASP.Net 2.0, and most of the technology has already been included here, atASPNet101.com: Code Sample – Master Detail Solution
Really, the only 2 new things are the use of the HyperLinkField and the two-page scenario.

For those of you who are familiar with ASP.Net 1.x, the HyperLinkField looks pretty much like the HyperLinkColumn, used with a DataGrid. The format is:

<asp:HyperLinkField DataTextField="au_lname"
	DataNavigateUrlFields="au_ID"
	DataNavigateUrlFormatString="NewPage.aspx?id={0}"
	HeaderText="Click For Details" />

Like the BoundField (or BoundColumn, with the DataGrid), it is not necessary to use a TemplateColumn.

In our scenario, the first page will consist of a GridView and SQLDataSource control only, using the Pubs database for the example. The last name field will be used for the Hyperlink:


<asp:GridView Runat="server"
	Id="gvAuthors"
	GridLines="Both"
	DataSourceID="SQLDS2"
	DataKeyNames="au_id"
	SelectedItemStyle-Backcolor="Pink"
	AllowPaging="True"
	AutoGenerateColumns="False">
            <Columns>
		<asp:BoundField DataField="au_fname" HeaderText="au_fname"  />
		<asp:HyperLinkField DataTextField="au_lname"
			DataNavigateUrlFields="au_ID"
			DataNavigateUrlFormatString="NewPage.aspx?id={0}"
			HeaderText="Click For Details" />
              <asp:BoundField DataField="state" HeaderText="state" />
            </Columns>
	</asp:GridView>

The SQLDataSource control looks like this:

<asp:SqlDataSource ID="SQLDS2" runat="server"
	SelectCommand="SELECT [au_id], [au_lname], [au_fname], [state] FROM [Authors] Order by [state]"
ConnectionString="<%$ ConnectionStrings:Pubs %>">
</asp:SqlDataSource>

The ConnectionString here, is using the ConnectionString section of the Web.Config file, with an entry titled ‘Pubs’.

What happens on this page, is that, all the records are returned from the Authors table and, with the use of the HyperLinkField, the end-user is then, taken to the second page, based on the item selected in the GridView.

Now, on the second page (NewPage.aspx), we again have only two controls – a DetailsView and a SQLDataSource control. The DetailsView is fairly straightforward, using all BoundFields for display. Naturally, if there were other needs or possibilities here, we could use TemplateColumns, or add an Edit Button, etc:
Continues…

Pages: 1 2

DataBase Record Navigation (Next/Prev)

Posted on by
Reply

There have been many requests for this particular type of example in the newsgroups and forums. What we’re doing here is doing a representation of the classic Windows app which accesses a database, moving to the first record of a table when it runs. Then, the user can navigate from one record to the next (or also to the first or last record, in this case) with the mere click of a button.

We’re using the SQL Server managed provider, accessing the Northwind Database Employee Table (though here, it is altered/edited by other processes in the code samples, of course).

When the app first starts, it accesses the Minimum and the Maximum ID from the table and uses labels to store the information during the life of the app.

Just copy this to your new file, change the provider/and/or connection info and you’re off and running.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<script language="VB" runat="server">
Dim intMinID, MinRecNo, MaxRecNo as Integer
Dim sLast, sFirst, sTitle, sHireDate, sAddress, sCity, sExtension, sReportsTo as String

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 Min(EmployeeID) as MinID, Max(EmployeeID) as MaxID 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)
	While objDR.Read()
		intMinID=objDR("MinID")
		MinRecNo=intMinID
		MaxRecNo=objDR("MaxID")
	End While
	objDR.Close
	doPopulation
	lblID.text=intMinID
	lblMin.text=MinRecNo
	lblMax.text=MaxRecNo

end if
response.Write ("MaxRecNo = " & lblMax.text & "<br>MinRecNo = " & lblMin.text)
End Sub

Sub doPopulation()
	Dim strConn as string = "server=server=YourServer;uid=YourUID;pwd=YourPWD;database=NorthWind"
	Dim MySQL as string = "Select EmployeeID, LastName, FirstName, Title, HireDate, Address, City, " & _
        "Extension from Employees Where EmployeeID =" &  intMinID
	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)
	While objDR.Read()
		sLast=objDR("LastName")
		sFirst=objDR("FirstName")
		sTitle=objDR("Title")
		sHireDate=objDR("HireDate")
		sAddress=objDR("Address")
		sCity=objDR("City")
		sExtension=objDR("Extension")
	End While
	frmLastName.text=sLast
	FrmFirstName.text=sFirst
	frmTitle.text=sTitle
	frmHireDate.text=sTitle
	frmAddress.text=sAddress
	frmCity.text=sCity
	frmExtension.text=sExtension
End Sub

Sub doNext(Source as Object, E as EventArgs)
	if cInt(lblID.text)< cInt(lblMax.text) then
		intMinID=Cint(lblID.text) + 1
		lblWarning.text=""
	else
		intMinID=lblID.text
		lblWarning.text= ("<font Color=""#FF0000""><i>You''ve Reached the End of the Records</i></font>")
	End If
	DoPopulation
	lblID.text=intMinID

End Sub

Sub doPrev(Source as Object, E as EventArgs)
	if cInt(lblID.text)>cInt(lblMin.text)then
		intMinID=Cint(lblID.text) - 1
		lblWarning.text=""
	else
		intMinID=lblID.text
		lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the Beginning of the Records</i></font>")
	End If
	DoPopulation
	lblID.text=intMinID
End Sub

Sub doFirst(Source as Object, E as EventArgs)
	intMinID=cInt(lblMin.text)
	DoPopulation
	lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the Beginning of the Records</i></font>")
	lblID.text=intMinID
End Sub

Sub doLast(Source as Object, E as EventArgs)
	intMinID=cInt(lblMax.text)
	DoPopulation
	lblWarning.Text= ("<font Color=""#FF0000""><i>You''ve Reached the End of the Records</i></font>")
	lblID.text=intMinID
End Sub
</script>
	<meta name="GENERATOR" Content="ASP Express 2.2">
	<title>Next - Previous Records in a DataBase</title>

</head>
<body>
<form runat="server">
<table>
	<tr>
		<td align="right">Last Name</td>
		<td> <asp:textbox id="frmLastName" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">First Name</td>
		<td> <asp:textbox id="frmFirstName" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">Title</td>
		<td> <asp:textbox id="frmTitle" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">Hire Date</td>
		<td> <asp:textbox id="frmHireDate" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">Address</td>
		<td> <asp:textbox id="frmAddress" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">City</td>
		<td> <asp:textbox id="frmCity" runat="server" /></td>
	</tr>
	<tr>
		<td align="right">Extension</td>
		<td> <asp:textbox id="frmExtension" runat="server" /></td>
	</tr>
	<tr>
		<td align="right"><b>Current Record #: </b>
		<asp:Label ID="lblID" runat="server" />
		</td>
		<td>
		<asp:Button id="btnPrev" Text="<< Previous" onclick="doPrev" runat="server" />
		<asp:Button id="btnNext" Text="Next >>" onclick="doNext" runat="server" />
		</td>
	</tr>
	<tr>
		<td align="Left" valign="Top">
			<asp:Label ID="lblMin"  visible="false" runat="server" />
			<asp:Label ID="lblMax"  visible="false" runat="server" />
		</td>
		<td align="Left" valign="Top">
			<asp:Button id="lblFirst" Text="First" onclick="doFirst" runat="server" />
			<asp:Button id="lblLast" Text="Last" onclick="doLast" runat="server" /><br>
			<asp:Label ID="lblWarning"  runat="server" /><br>
		</td>
	</tr>
</table>
</form>
</body>
</html>

Using the QueryStringParameter in ASP.NET

Posted on by
Reply

If you have already read the ASPNet101.com tutorial on DataSource controls, you have seen the ControlParameter used. Here, we will look into another very handy class – the QueryStringParameter class.

Most likely, one of the first things you learned in either Classic ASP, or ASP.Net was how to send form results in your web pages. As you might recall, you probably learned about the Request Object, using Request.Form (with a POST, in Classic ASP), or Request.QueryString. When using a QueryString, to be able to capture the information in your page, you ‘added on’ to the actual url, with a question mark, followed by a key/value scenario:

MyPage.asp?name=Stewart

Then, on the ‘receiving’ page, whether it was the same or a separate page, you would then capture the data in the key/value and parse it somehow – something like: Dim strName as String (or without the ‘as’ clause, in Classic ASP)

strName=Request.QueryString("name")

Then, in that page, you could use the variable ‘strName’ wherever and however you liked. The most common use was/is to query a database, based on that variable. So, to use the variable in a SQL Select statement:

"Select * from [TableName] Where LName='" & strName & "'"

Of course, the code to connect and use that SQL Select Statement would then follow in the web page. Naturally, this is a very simplistic approach, but it’s most likely at least globally understandable at this point. This concept was easily do-able in Classic ASP and ASP.Net 1.x

Now – along comes v2.0 of ASP.Net. We find out that one of the key goals of the ASP.Net team was to reduce coding by as much as 70%. So, in doing so, new controls were created, like the different DataSource controls (SQLDataSource, AccessDataSource, etc). By having these controls do all the connecting and querying of the database, you might be wondering how to take care of a querystring scenario such as this. Don’t worry – it’s well handled.

Not only does the solution v2.0 has built in take care of it, but it handles the situation with Parameters, which is much safer than the concatenation shown above, in the SQL statement. Now, in your DataSource control, your SQL statement would look something like this:
Continues…

Pages: 1 2

Nested DataLists (C# Version)

Posted on by
Reply

Sometimes, we need to nest data – say for instance – List the Categories, and under each Category, list the Products for that particular Category.

This sample uses the SQL Managed Provider to show exactly that, using the Categories and Products tables from the NorthWind Database.

As usual, just copy this code directly into a new page, change the database/connection information to match your system, and run it from your own computer.

You can read a Tutorial about this code here.

Thanks to Mike Houston for this code sample
http://www.nexus6studio.com

See this code in VB.Net

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<HTML>
  <HEAD>
    <title>Nested Datalists Using C#</title>
  <script language=C# runat=server>
  void Page_Load(object sender, System.EventArgs e)
  {
	string strConn = "Server=YourServer;uid=yourUID;pwd=YourPWD;database=Northwind";
	string MySQL = "Select CategoryID, CategoryName from Categories";
	SqlConnection MyConn = new SqlConnection(strConn);
	DataSet ds = new DataSet();

	SqlDataAdapter Cmd = new SqlDataAdapter(MySQL,MyConn);
	Cmd.Fill(ds,"Categories");

	SqlDataAdapter Cmd2 = new SqlDataAdapter("select * from Products",MyConn);
	Cmd2.Fill(ds,"Products");

	ds.Relations.Add("myrelation", ds.Tables["Categories"].Columns["CategoryID"], ds.Tables["Products"].Columns["CategoryID"]);

	dlCategories.DataSource = ds.Tables["Categories"].DefaultView;
	DataBind();
  }
  </script>
  </HEAD>
  <body>
    <form id="Form1" method="post" runat="server">
<asp:DataList runat="server"
	Id="dlCategories"
	GridLines="Both"
	Bordercolor="black"
	cellpadding="3"
	cellspacing="0"
	Headerstyle-BackColor="#DDDDDD"
	Headerstyle-Forecolor="#777777"
	Headerstyle-Font-Name="Arial"
	Headerstyle-Font-Size="14"
	Headerstyle-Font-Bold="true"
	Font-Name="Arial"
	Font-Bold="true"
	Font-Italic="true"
	Font-Size="11"
	ForeColor="Red"
	RepeatColumns="1">
	<HeaderTemplate>
		Categories & Products
	</HeaderTemplate>
	<ItemTemplate><%# DataBinder.Eval(Container, "DataItem.CategoryName") %>
		 <br>
 <asp:DataList runat="server"
 Id="ChildDataList"
 GridLines="None"
 Bordercolor="black"
 cellpadding="3"
 cellspacing="0"
 Headerstyle-BackColor="#8080C0"
 Headerstyle-Font-Name="Arial"
 Headerstyle-Font-Size="8"
 Font-Name="Arial"
 Font-Size="8"
 datasource='<%# DataBinder.Eval(Container, "DataItem.myrelation") %>'
 RepeatColumns="3">
				<ItemTemplate>
					&nbsp; &nbsp; <%# DataBinder.Eval(Container, "DataItem.ProductName") %>
				</ItemTemplate>
			</ASP:DataList>
	</ItemTemplate>
</asp:DataList>
    </form>

  </body>
</HTML>

Beginner's Guide to Master Pages in ASP.NET

Posted on by
Reply

All of us have struggled over the years to get a consistent look and feel for our web pages. There have been many ways, but now, with ASP.Net 2.0, Master Pages have created a very simple way for this to happen.

To begin with, there’s a new page extension (.Master). So, the first thing to do is to create a new Master page, with the .Master extension, and lay it out any way you’d like. At the top of that page, there’s a Master Directive, much like the Page Directive (and others) you’re already familiar with:

<%@ Master Language="VB" %>

The basic concept is, that you will then create Content pages, which in the Page Directive, will refer to the master page:

<%@ Page Language="VB" MasterPageFile="Main.master" Title="My Content Page" %>

Within each master page, you can have one or more ContentPlaceHolders (a new ASP.Net control):

<asp:contentplaceholder id="cph1" runat="server">
</asp:contentplaceholder>

Then, in the Content Pages, there are new Content controls, which refer back to the ContentPlaceHolder’s ID in the Master Page:

<asp:Content ID="first" ContentPlaceHolderID="cph1" Runat="Server">
	Here's where you put the actual content
</asp:Content>

The Content Pages use the normal ‘.aspx‘ extension.

One of the really cool features, is that, you can, from the Content pages, refer to any control on/in the Master Page, and change the properties of it. Let’s say you have an ASP.Net Image Control in the top left corner of the Master Page, and you want to change it in your Content page. Just create your image control, using whatever properties you’d like to assign:

<asp:Image id="imMain" Runat="server" />

Then, inside the Page_Load event of your Content page, your code would look something like this:

Dim Img As Image = Master.FindControl("ImMain")
Img.ImageUrl="page2.gif"

You can do this with any DotNet control you put on the Master Page. Let’s say you also have a label control at the bottom of your Master page, for the footer, called ‘lblFooter’. You want it to always say the same on every page, at least. But then, on one or more of your Content pages, you need to add something. So – in the Content Page’s Page_Load event:

Dim lbl as Label=Master.findControl("lblFooter")
lbl.Text=lbl.Text & "
Special Content -  Copyright by the Author - Harold Magnolia"

This will keep the same footer that you have on every other page, and also add the extra text (on a second line) above.

To see these basic concepts in action Click Here.

Code for the Master Page:
Continues…

Pages: 1 2

RadioButtonList with AutoPostback

Posted on by
Reply

This sample uses the SQL Managed Provider and the Northwind Database to, based on the selection from the RadioButtonList (Manually populated, in this case), populate a DropDownList with all the Product Names that fall in the selected Category. The AutoPostback Property, being set to ‘True’, takes the place of the button. When you click on the RadioButtonList item, this tells it to automatically postback and run the sub or function designated in the onselectedindexchanged method.

Just copy the code into new page on your site and change the database connection information. 

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
	<meta name="GENERATOR" Content="ASP Express 2.1">
	<title>Populating a DropDownList from a RadioButtonList</title>
<script language="VB" runat="server">

Sub popddl(Source as Object, E as EventArgs)
	Dim strConn as string = "server=YourServer;uid=YourUID;pwd=YourPWD;database=Northwind"
	Dim MySQL as string = "Select ProductName from NWProducts Where CategoryID = @CatID"
	Dim MyConn as New SQLConnection(strConn)
	Dim objDR as SQLDataReader
	Dim Cmd as New SQLCommand(MySQL, MyConn)
	cmd.parameters.add(New SQLParameter("@CatID", rb1.selecteditem.value))
	MyConn.Open()
	objDR=Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
	ddl.DataSource = objDR
	ddl.DataBind()
	ddl.visible="true"
	lblProduct.text="Products in " & rb1.selecteditem.text & " Category"
	label1.text= ("Selected Value = " & rb1.selecteditem.value )
End Sub
</script>

</head>
<body>
<Form id="form1" runat="server">

<table border="0">	<tr>
		<td align="Left" valign="Top"><b><i>Categories:</i></b><br>
		<asp:RadioButtonList id="rb1" AutoPostback="true" OnSelectedIndexChanged ="popddl" runat="server">
		<ASP:LISTITEM value="1">Beverages</ASP:LISTITEM>
		<ASP:LISTITEM value="2">Condiments</ASP:LISTITEM>
		<ASP:LISTITEM value="3">Confections</ASP:LISTITEM>
		<ASP:LISTITEM value="4">Dairy Products</ASP:LISTITEM>
		<ASP:LISTITEM value="5">Grains/Cereals</ASP:LISTITEM>
		<ASP:LISTITEM value="6">Meat/Poultry</ASP:LISTITEM>
		<ASP:LISTITEM value="7">Produce</ASP:LISTITEM>
		<ASP:LISTITEM value="8">Seafood</ASP:LISTITEM>
		</asp:RadioButtonList>
		</td>
		<td align="Left" valign="Top">
		<b><i><asp:Label ID="lblProduct" runat="server" /></i></b><br>
		<asp:DropDownList id="ddl" datatextfield="Productname" visible="false" runat="server" />
		</td>
	</tr>
</table>
</Form>
<asp:Label ID="label1"  runat="server" />
</body>
</html>

Check a DataSet for Empty

Posted on by
Reply

Let’s say you fill a dataset:

Dim ds as DataSet=New DataSet()
Dim Cmd as New OleDBDataAdapter(sql,MyConn)
Cmd.Fill(ds,”Employees”)
dg.Datasource=ds.Tables(“Employees”).DefaultView

Before you Bind it to a server control, you’d like to see if it actually has any data in it……so – you can do something like this:

If ds.Tables(0).Rows.Count >0 then
   ‘do your databinding here
Else
   ‘ enter your message here, like, ‘Sorry but no data exists with this criteria’
End if