Where To Store Database Connection

Posted on March 31, 2007 by admin

Let’s say you have a database connection (or several) that you will be using over and over. Yes, you can manually copy/type it in on every ASP.Net page – BUT – an easier way is to store it in the Web.Config file (formerly config.web) and then refer to it in the code.

In Web.Config, you would add a key to the AppSettings Section:


<appSettings>
<add key="MyDBConnection" value="server=YourServer;uid=Username;pwd=Password;database=DBName" />
</appSettings>

for OleDb - use Absolute Path - Not Server.MapPath:
<add key="NWOleDB" value= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Inetpub\wwwroot\data\northwind.mdb;" />

Then, in your ASP.Net application – just refer to it like this:
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(“MyDBConnection”))

That’s all there is to it

Examining List Controls in ASP.NET

Posted on March 31, 2007 by admin

There are many ways to add items to, or populate a List-type ASP.Net Server Control. I’m only addressing the DropDownList here, though the same techniques, for the MOST part, can be used interchangeably with the ListBox, RadioButtonList, and CheckBoxList controls.

The ways we’ll be looking at here are:
In the Web Page itself, Manually (in Page_Load), binding to a Table (field) in a Database, binding to an ArrayList, and adding items after Databinding.

In the Web Page itself
Here’s the way they show you in the Quickstart, so you’re most likely to be familiar with this one:
First Item
Second Item
Third Item

In the Page_Load Event
You can manually populate your DropDownList a couple of different ways (at least that’s all we’re looking at in this tutorial). The first way involves manually adding the items, similar to the above example:
DropDownList.Items.Add(“First Item”)
DropDownList.Items.Add(“Second Item”)
To add items to the DropDownList, along with a Value Field, you can do something like this (the second item is the one that populates the Value:
ddl2.Items.Add(New ListItem(“Item 1″, “1″))
ddl2.Items.Add(New ListItem(“Item 2″, “2″))
Then, of course, there’s dynamic population, with a loop :
Dim x as integer
for x=1 to 10
ddl.items.add(x)
next x
Binding to an ArrayList:
Of course, the other way the Quickstart shows is to add items to an ArrayList, and then binding the DropDownlist to the ArrayList. This particular method provides extra functionality due to the fact that the DropDownlist (or ListBox) does not have a built in ‘Sort’ property. You can sort the ArrayList, and then bind it to the DropDownList. Instead of providing extra code here, we’ll point you to an online example of this:
Arraylist – Adding Items/Sorting/Binding
The example uses a ListBox, but as we said earlier – we’re covering multiple scenarios with this tutorial.
Binding to a Field in a DataBase table:

Continues…

Pages: 1 2

Paging AND Sorting with a DataGrid

Posted on March 31, 2007 by admin

Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the Northwind database with SQL Server and save it as a ‘.aspx’ page.

To make it “Next-Previous” instead of numbering – change the PagerStyle-Mode to:

PagerStyle-Mode=’NextPrev’
PagerStyle-NextPageText=’Next ->’
PagerStyle-PrevPageText=”<- Previous”

<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SQLClient” %>

Sub Page_Load(Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
SQLStatement.Text = SQLStmt
BindData()
End If
End Sub

Sub BindData()
Dim myDataSet As New DataSet
Dim myDataSetCommand As SQLDataAdapter
Dim ConString As String

ConString =”server=localhost;database=Northwind;uid=UserName;pwd=Userpassword;”
myDataSetCommand = New SQLDataAdapter(SQLStatement.Text, ConString)
myDataSetCommand.fill(myDataSet, “Customers”)

myDataGrid.DataSource = myDataSet.Tables(“Customers”).DefaultView
myDataGrid.DataBind()
End Sub

Sub PageIndexChanged_OnClick(Source As Object, E As DataGridPageChangedEventArgs)
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindData()
End Sub

Sub SortCommand_OnClick(Source As Object, E As DataGridSortCommandEventArgs)
SQLStatement.Text = SQLStmt & ” ORDER BY ” & E.SortExpression
BindData()
End Sub
</script>
<style>
.DataGrid {font:x-small Verdana, Arial, sans-serif}
</style>

<title>ASPExpress.com – Paging AND Sorting in the DataGrid</title>
</head>
<body>
<div align=”center”>Paging And Sorting Together with ASP.Net </div>
<form runat=”server” method=”post”>

<asp:DataGrid runat=”server” id=”myDataGrid”
borderwidth=”5″
bordercolor=”black”
Borderstyle=”double”
PagerStyle-VerticalAlign=”top”
Cellpadding=”4″
Cellspacing=”0″
ShowHeader=”True”
CssClass=”DataGrid”
HeaderStyle-ForeColor=”Black”
HeaderStyle-Font-Bold=”True”
AllowSorting=”True”
OnSortCommand=”SortCommand_OnClick”
AllowPaging=”True”
OnPageIndexChanged=”PageIndexChanged_OnClick”
PageSize=”10″
width=”100%”
HeaderStyle-BackColor=”#aaaadd”
AlternatingItemStyle-BackColor=”#eeeeee”
PagerStyle-Backcolor=”#aaaadd”
PagerStyle-Forecolor=”Black”
PagerStyle-HorizontalAlign=”center”
PagerStyle-Mode=”NumericPages”
PagerStyle-BorderStyle=”Inset”

/>
</form>
<asp:Label id=”SQLStatement” runat=”server” Visible=”false” />
</body>
</html>

Paging with a DataGrid

Posted on March 26, 2007 by admin

Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the Northwind database with SQL Server and save it as a ‘.aspx’ page.

To make it “Next-Previous” instead of numbering – change the PagerStyle-Mode to:
PagerStyle-Mode=’NextPrev’
PagerStyle-NextPageText=’Next ->’
PagerStyle-PrevPageText=’<- Previous'
Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the Northwind database with SQL Server and save it as a ‘.aspx’ page.

To make it “”Next-Previous”" instead of numbering – change the PagerStyle-Mode to:

PagerStyle-Mode=’NextPrev’
PagerStyle-NextPageText=’Next ->’
PagerStyle-PrevPageText=’<- Previous’

<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SqlClient” %>
<script language=”VB” runat=”server”>

Sub Page_Load(sender As Object, e As EventArgs)
BindGrid
End Sub

Sub Page_Change(sender As Object, e As DataGridPageChangedEventArgs)
dim start as Integer
start = MyDataGrid.CurrentPageIndex * MyDataGrid.PageSize
MyDataGrid.CurrentPageIndex = e.NewPageIndex
BindGrid
End Sub

Sub BindGrid()
‘You can store the connection as a string in Web.Config, if you want:
‘Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings(“Whatever”))

Dim myConnection As SqlConnection = New SqlConnection(“server=YourServer;uid=UserName;pwd=Password;database=northwind”)

dim ds as DataSet = new DataSet()
dim adapter as SqlDataAdapter = new SqlDataAdapter(“Select * from customers”, myConnection)
adapter.Fill(ds,”customers”)

MyDataGrid.DataSource=ds.Tables(“customers”).DefaultView
MyDataGrid.DataBind()
End Sub

Sub PagerButtonClick(sender As Object, e As EventArgs)
BindGrid
End Sub

</script>
<html>
<head>
</head>
<body>
<div align=”center”>Paging with ASP.Net</div>

<ASP:DataGrid id=”MyDataGrid” runat=”server”
AllowPaging=”True”
PageSize=”5″
PageCount=”1″
PagerStyle-Mode=”NumericPages”
PagerStyle-HorizontalAlign=”Center”
OnPageIndexChanged=”Page_Change”
BorderColor=”black”
BorderWidth=”1″
GridLines=”Both”
CellPadding=”3″
CellSpacing=”0″
Font-Name=”Verdana”
Font-Size=”8pt”
HeaderStyle-BackColor=”#aaaadd”
AlternatingItemStyle-BackColor=”#eeeeee”
width=”100%”
/>
<p>
</form>

</body>
</html>

CDO Form to Email Tutorial

Posted on March 26, 2007 by admin

Click here for Tutorial

If Not Page.IsPostBack in ASP.NET

Posted on March 26, 2007 by admin

Many of you have seen this mysterious if/then statement in a lot of the online and book code samples, and you probably have, at one time or another, wondered just what exactly this was all about. This tutorial plans to answer all your questions. There is one basic question that gets asked on ASP.Net Forums and ASPFriends.com ListServes, over and over, in a several different ways. It all boils down to one answer concerning the IsPostBack Property of the page.
“Why doesn’t my DropDownList keep it’s Selection?”
“Why is the selectedindex for my (ASP.Net control) always turning up a -1?”

Fortunately, today, you’ve come to the right place. You questions will be answered.

Scenario:
You put a DropDownList or a ListBox (or just about any ASP.Net control which has multiple items assigned to it) on your web page (inside a form, naturally). Then, at some point, you either populate the list items manually, or bind it to a database table. However, you do it, you get a list of items from which, at some point, the end user can make a choice. Based on that choice, the end user gets more data in return. Most of the basic item population of these controls is done during the initial loading of the page (Page_Load event). That way, the list items are available for choosing once the page is finished loading.

Let’s say, then, you also put a button on the page. That way, the end user can choose an item in the list, click the button and get the extended data, based on the selection made. The button’s click event would then take the item which was selected and use it in a click event that could then, possibly connect to a database and use the selected item’s data to filter a query against a database.

Continues…

Pages: 1 2

Sorting with a DataGrid

Posted on March 21, 2007 by admin

Just copy and paste this code into a new blank page. Change the Database name, UID and password. It uses the Northwind database with SQL Server and save it as a ‘.aspx’ page.

<%@ Import Namespace=”System.Data” %>
<%@ Import Namespace=”System.Data.SQLClient” %>
<html>
<head>
<title>ASPExpress.com – Column Sorting in the DataGrid</title>
<script runat=”server” language=”VB”>
Protected SQLStmt As String = “SELECT CompanyName, ContactName, ContactTitle, Phone, Fax FROM Customers”

Sub Page_Load(Source As Object, E As EventArgs)
If Not Page.IsPostBack Then
BindData()
End If
End Sub

Sub BindData()
Dim myDataSet As New DataSet
Dim myDataSetCommand As SQLDataAdapter
Dim ConString As String
ConString = “server=YourServerName;database=Northwind;uid=internet;pwd=;”
myDataSetCommand = New SQLDataAdapter(SQLStmt, ConString)
myDataSetCommand.Fill(myDataSet, “Customers”)
myDataGrid.DataSource = myDataSet.Tables(“Customers”).DefaultView
myDataGrid.DataBind()
End Sub
</script>

</BODY>
</HTML>

DataReader vs DataSet

Posted on March 21, 2007 by admin

Whenever you have a one-time hit of data from a database – use a DataReader – it’s much faster than a DataSet – AND — a Dataset uses a DataReader to get its data…..
If you are requesting data that is going to be used/accessed multiple times, use the DataSet – It grabs the data from the table, puts it in memory and then closes the connection

Single & Double Quotes in SQL

Posted on March 21, 2007 by admin

First of all:
Double Quote Marks (“) have to be around an entire SQL Statement

If there is Limiter (non-numeric), like ‘where Fieldname=Peter’, where the limiter is not numeric, then you must put single quotes around it:
“Select * from Tablename where (Firstname)=’Peter’
This is an example of an EXACT SQL Statement. We are asking to return all instances of the name ‘Peter’.

Here’s where it gets tricky with variables. In our form, we are requesting a name, but whoever fills out the form might want to search for someone with a different name. So, since it can be different things to different people, we assign that section of the SQL statement a variable.

Let’s say you have designated a variable called FirstNameVar. Above where Peter starts is where the variable would go, but Peter is an exact search and a variable is not, so your SQL ending double quote goes directly after the single quote. This is where the EXACT SQL statement ends. At this point, you have:
“Select * from Tablename where (Firstname)=’”

Continues…

Pages: 1 2

DropDownList Selection

Posted on March 16, 2007 by admin

Just copy and paste this code into a new blank page. This is example uses MS Access. Just change the location of the database on your computer to use this code. If you want to use the pubs database with SQL Server, change the whole Provider stringconnection, command and the imported namespace.

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.0">
<title>Dropdown List</TITLE>
<Link REL=STYLESHEET HREF="/basicArial.css" TYPE="text/css">

Sub Page_Load(Source as Object, E as EventArgs)

If not IsPostBack then
BindData()
end if
End Sub
Sub button1_click(Source as Object, E as EventArgs)
label1.text=" You selected " & MyList.selecteditem.text
End Sub

Sub Reset_Click(Source as Object, E as EventArgs)
label1.text=""
BindData()
End Sub

Sub BindData()
Dim strSQL as string
Dim strConn as string
strSQL="Select distinct city from publishers where city <>”"
strConn ="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/biblio.mdb") & ";"
Dim Conn as New OLEDBConnection(strConn)
Dim Cmd as New OLEDBCommand(strSQL,Conn)
Conn.Open()
MyList.DataSource = Cmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)
MyList.DataBind()
Conn.Close()
End Sub
</script>
</head>

</body>
</html>

ASP.NET 101

ASP.NET and MVC Tutorials

Monthly Archives: March 2007

Where To Store Database Connection

Examining List Controls in ASP.NET

Paging AND Sorting with a DataGrid

Paging with a DataGrid

CDO Form to Email Tutorial

If Not Page.IsPostBack in ASP.NET

Sorting with a DataGrid

DataReader vs DataSet

Single & Double Quotes in SQL

DropDownList Selection