In this example we are learning the use of SQLDatasource and GridView controls. (Also see Link )
  • SqlDataSource
  • Access Database
  • DataSource Edit mode support these controls in grid-view control
    • <asp:CommandField ShowSelectButton="True" />
    • <asp:CommandField ShowEditButton="True" />

Adding an Existing database

Add a database

Feed password ManasnewPx237,

Drag SQlDatasource

Use smart tag to configure data source

 

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\aspnet.35\mm.Access1\App_Data\NewPro1.mdb;Persist Security Info=True;Jet OLEDB:Database Password=ManasnewPx237

Selecting above creates a string in Web.config

<connectionStrings>
<add name="winepro1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\inetpub\wwwroot\aspnet.35\mm.Access1\App_Data\NewPro1.mdb;Persist Security Info=True; Jet OLEDB:Database Password=ManasnewPx237"
providerName="System.Data.OleDb" />
</connectionStrings>


Property window of SqlDataSource, provides all the help you may need to connect and query the database. Unlike SQL , Access database uses hard coded path. The database can remain the same or anywhere in the network.

Add a Grid Box

Adjust view and your selections as needed

 

During it's use Access database creates lbd files the App_Data, Cllick on list box., to retrieve the value of an listed item.

Code Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>mm.Access Database </title>
<style type="text/css">
.style1 {  width: 273px; }
.style4 { width: 468px; }
.style5 { width: 273px; height: 30px; }
.style6 { width: 468px; height: 30px; }
.style7 { height: 47px; }
.style8 { width: 468px; height: 47px; }
</style>
</head>
<body bgcolor="#cccccc">
<form id="form1" runat="server">
<div>
   <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False"       DataSourceID="SqlDataSource1" Width="702px">
     <Columns>
           <asp:CommandField ShowSelectButton="True" />
          <asp:CommandField ShowEditButton="True" />
           <asp:BoundField DataField="BottleSize" HeaderText="BottleSize" SortExpression="BottleSize" />
           <asp:BoundField DataField="Producer" HeaderText="Producer" SortExpression="Producer" />
          <asp:BoundField DataField="Country" HeaderText="Country" SortExpression="Country" />
          <asp:BoundField DataField="Vintage" HeaderText="Vintage" SortExpression="Vintage" />
</Columns>
<EmptyDataTemplate>
&nbsp;
</EmptyDataTemplate>
</asp:GridView>

</div>
<!-- this is SqlDataSource1 for Grid view gets all the column and Gridview uses filter to view four columns -->
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="
<%$ ConnectionStrings:winepro1 %>"ProviderName="<%$ ConnectionStrings:winepro1.ProviderName %>"  
SelectCommand="SELECT DISTINCT [LabelName], [Varietal], [Appellation], [Reserve],
[Supplier], [Specialty], [Type],[Country], [Vintage], [List], [NetCase], [Netbottle], [OzCost], [BottleSize],
[Producer] FROM [WineProposal] ORDER BY [BottleSize] DESC">
</asp:SqlDataSource><br />
<table style="width:773px; height: 140px;">
<tr> <td class="style5">&nbsp;Field Name</td>
<td class="style6">&nbsp;</td>
</tr>
<tr>
<td class="style1">
<!-- this is SqlDataSource1 is used with ListBox-->
<asp:ListBox ID="LB1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource1" DataTextField="BottleSize"
DataValueField="BottleSize" onselectedindexchanged="ListBox1_SelectedIndexChanged" Width="307px">
</asp:ListBox>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:winepro1 %>"
ProviderName="<%$ ConnectionStrings:winepro1.ProviderName %>" SelectCommand="SELECT DISTINCT [BottleSize] FROM [CompactWine]">
</asp:SqlDataSource>
</td>
<td class="style4" nowrap="nowrap">
</td></tr><tr>
<td class="style7" colspan="2"><asp:Label ID="L1" runat="server" Text="Label"></asp:Label>
</td></tr></table>
</form>
</body>
</html>
Code Default.aspx.cs

using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
// this handles SqlConnection
using System.Data.SqlClient;
// this handles configuration manager
using System.Web.Configuration;

public partial class _Default : System.Web.UI.Page
{
private string str = "<br/>"; private string str1 = "";
private string cnn_str = WebConfigurationManager.ConnectionStrings["winepro1"].ConnectionString;
//C:\inetpub\wwwroot\aspnet.35\mm.Access1\App_Data\NewPro1.mdb;Persist Security Info=True;Jet OLEDB:Database Password=ManasnewPx237
protected void Page_Load(object sender, EventArgs e)
{

}

protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//L1.Text = e.ToString() + str;
// L1.Text = sender.ToString() + str;
L1.Text += "<hr/>You selected " + LB1.SelectedValue;
//SqlConnection cnn = new SqlConnection(cnn_str);
L1.Text += "<br/>" +cnn_str;


}
}