access database

Printable View

access database

I'm having trouble accessing data from an access database. I'm used to database interactions using asp 3.0. I'm using some code that was provided by "pvb" in this thread:
http://vbforums.com/showthread.php?t...ccess+database

But, none of the data is being displayed on the page. Here is the code as I have implemented it.

I don't get any errors or anything, I just see a blank page when I load it. Shouldn't the data from my database appear in the datagrid when the page is loaded?

Thanks for any help!
VB Code:
Imports System.Data
Imports System.Data.OleDb
 
Public Class WebForm1
    Inherits System.Web.UI.Page
    Protected WithEvents gridAuthors As System.Web.UI.WebControls.DataGrid
#Region " Web Form Designer Generated Code "
 
    'This call is required by the Web Form Designer.
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
 
    End Sub
 
    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
        'CODEGEN: This method call is required by the Web Form Designer
        'Do not modify it using the code editor.
        InitializeComponent()
    End Sub
 
#End Region
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
        If Not Page.IsPostBack Then
            bindAuthors()
        End If
    End Sub
    Protected Sub bindAuthors()
        Dim mdbPath As String = "G:\WebServer\cpthandler\database\cptDataInfo.mdb"
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbPath
        Dim cn As New OleDbConnection(connString)
        Dim cmdText As String = "SELECT CONTACT_NAME FROM cptDataInfo"
        Dim cmd As New OleDbCommand(cmdText, cn)
        cmd.Connection.Open()
 
        gridAuthors.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        gridAuthors.DataBind()
 
    End Sub
 
 
End Class
Here's the html source:

Code:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="createCPTxml.aspx.vb" Inherits="createCPTxml.WebForm1"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML> <HEAD> <title>WebForm1</title> <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0"> <meta name="CODE_LANGUAGE" content="Visual Basic 7.0"> <meta name="vs_defaultClientScript" content="JavaScript"> <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5"> </HEAD> <body MS_POSITIONING="GridLayout"> <form runat="server"> <asp:DataGrid Runat="server" AutoGenerateColumns="True" ID="gridAuthors" /> </form> </body> </HTML>

I don't get any errors or anything, I just see a blank page when I load it. Shouldn't the data from my database appear in the datagrid when the page is loaded?

This only works when you're not using codebehind:
VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
    If Not Page.IsPostBack Then
        bindAuthors()
    End If
End Sub
You have to add Handles MyBase.Load if you use codebehind otherwise that event never fires(which results in a blank page with no errors):
VB Code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not Page.IsPostBack Then
        bindAuthors()
    End If
End Sub

Aug 14th, 2003, 12:52 PM
pgrimes

Thanks for the reply pvb.

It still does the same thing though. Nothing shows up. I don't have to iterate through the records or anything to make them appear? The database is populated.

Thanks,

Paul

Hi, I got the code listed below to do what I'm shooting for. But, I'm having trouble getting it to work using "codebehind". Could anyone point me in the right direction? How would I modify this to work using codebehind. The things I've tried are unsuccessful.

Thanks... Paul

VB Code:

<%@ Import Namespace="System.Data.OleDb" %><script runat="server">
sub Page_Load
dim dbconn,sql,dbcomm,dbread
dbconn=New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;data source=G:\WebServer\cpthandler\database\cptDataInfo.mdb")
dbconn.Open()
sql="SELECT * FROM cptDataInfo"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
cptData.DataSource=dbread
cptData.DataBind()
dbread.Close()
dbconn.Close()
end sub
</script><html>
<body><form runat="server">
<asp:Repeater id="cptData" runat="server"><HeaderTemplate>
<table border="1" width="100%">
<tr>
<th>ID</th>
<th>Cone Used</th>
<th>Latitude</th>
<th>Longitude</th>
</tr>
</HeaderTemplate><ItemTemplate>
<tr>
<td><%#Container.DataItem("ID")%></td>
<td><%#Container.DataItem("CONE_USED")%></td>
<td><%#Container.DataItem("LATITUDE")%></td>
<td><%#Container.DataItem("LONGITUDE")%></td>
</tr>
</ItemTemplate><FooterTemplate>
</table>
</FooterTemplate></asp:Repeater>
</form></body>
</html>

Ok, in this example(almost the same as the other example) I'm using the biblio access database that ships with VS6.0. Here's the aspx....

Code:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataBoundGrid.aspx.vb" Inherits="localhost.DataBoundGrid"%>

<html>

        <body>

                <form runat="server">

                        <asp:DataGrid ID="gridPublishers" Runat="server" AutoGenerateColumns="True"/>

                </form>

        </body>

</html>

and here's the codebehind:

VB Code:

Imports System
Imports System.Data
Imports System.Data.OleDb
 
Public Class DataBoundGrid : Inherits System.Web.UI.Page
    Protected gridPublishers As DataGrid
    Private Sub bindPublishers()
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "data source=F:\msaccess_data\biblio2002.mdb"
        Dim cn As New OleDbConnection(connString)
        Dim cmdText As String = "Select * From Publishers"
        Dim cmd As New OleDbCommand(cmdText, cn)
        cmd.Connection.Open()
        gridPublishers.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection)
        gridPublishers.DataBind()
    End Sub
    Protected Overrides Sub OnInit(ByVal e As EventArgs)
        If Not Page.IsPostBack Then
            bindPublishers()
        End If
        MyBase.OnInit(e)
    End Sub
End Class

this is cut and pasted from a working example so i know it works. If you still get a blank screen, amongst other things, you can change bindPublishers to the following just for testing:

VB Code:

Private Sub bindPublishers()
    Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                "data source=F:\msaccess_data\biblio2002.mdb"
    Dim cn As New OleDbConnection(connString)
    Dim cmdText As String = "Select * From Publishers"
    Dim cmd As New OleDbCommand(cmdText, cn)
    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataSet
    da.Fill(ds)
    If (ds.Tables.Count = 0) Then
        Throw New Exception("No tables")
    Else
        If (ds.Tables(0).Rows.Count = 0) Then
            Throw New Exception("No rows")
        End If
    End If
    gridPublishers.DataSource = ds
    gridPublishers.DataBind()
End Sub

Aug 15th, 2003, 12:34 PM
pgrimes

Sweet! That worked fine. I should be able to manage what I need to do from here. Thanks a lot for the help!

Paul
Aug 19th, 2003, 06:58 PM
pgrimes
Ok, so I have another question. How can I access each field from the database (one at a time). It seemed so much easier to me in ASP 3. All I had to do was get whatever field values I wanted while I looped through my recordset without reaching EOF. How can I do the same thing with asp.net?

Example:
VB Code:
```
<%
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL
 
While Not objRS.EOF
%>
    <td><%=objRS("lName")%></td>
    <td><%=objRS("username")%></td>
    <td><%=objRS("password")%></td>
<%
objRS.MoveNext
 
Wend
%>
```
Thanks,

Paul

Here's one way you can do it with a repeater control and databinding. Using that biblio mdb again, here's the aspx page:

Code:

<%@ Page Language="vb" AutoEventWireup="false" Codebehind="DataBinding.aspx.vb" Inherits="localhost.DataBinding"%>

<html>

        <body>

                <form runat="server">

                        <table>

                                <tr>

                                        <td>PubID</td>

                                        <td>Name</td>

                                        <td>Company</td>

                                </tr>

                                <asp:Repeater ID="rptrPublishers" Runat="server">                

                                        <ItemTemplate>

                                                <tr>

                                                        <td><%# DataBinder.Eval ( Container.DataItem , "PubID" ) %></td>

                                                        <td><%# DataBinder.Eval ( Container.DataItem , "Name" ) %></td>

                                                        <td><%# DataBinder.Eval ( Container.DataItem , "CompanyName" ) %></td>

                                                </tr>

                                        </ItemTemplate>                                

                                </asp:Repeater>

                        </table>

                </form>

        </body>

</html>

and here's the code behind:

VB Code:

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class DataBinding : Inherits System.Web.UI.Page
 
    Protected rptrPublishers As Repeater
 
    Protected Overrides Sub OnInit(ByVal e As EventArgs)
        If Not Page.IsPostBack Then
            bindPublishers()
        End If
        MyBase.OnInit(e)
    End Sub
 
    Private Sub bindPublishers()
        rptrPublishers.DataSource = getData()
        rptrPublishers.DataBind()
    End Sub
 
    Private Function getData() As DataSet
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                            "data source=F:\msaccess_data\biblio2002.mdb"
        Dim cn As New OleDbConnection(connString)
        Dim cmdText As String = "Select * From Publishers"
        Dim cmd As New OleDbCommand(cmdText, cn)
        Dim da As New OleDbDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds)
        Return ds
    End Function
 
End Class

Thanks again for the help pvb. One more question.

How do I access each cell in my code segment? Like the part in comments below....

example:

VB Code:

Dim cn As New OleDbConnection(connString)
Dim cmdText As String = "Select * From Publishers"
Dim cmd As New OleDbCommand(cmdText, cn)
Dim da As New OleDbDataAdapter(cmd)
Dim ds As New DataSet
 
Dim someVar As String
 
da.Fill(ds)
 
'While Not da.EOF
'If da('aFieldName') = "desiredString" Then
'  someVar = da('aFieldName')
'End If
 
'da.MoveNext
'Wend

Well, at that point you could do the following(i'm not really sure what you're tryin to do but here's how you'd iterate through the dataset):

VB Code:

For Each row As DataRow In ds.Tables(0).Rows
    If row("Name").ToString() = "MyName" Then
        'do something
    End If
Next

You can also intercept the item when it's being created and modify it before it's output is rendered to the client(the following just makes the values in the Name column bold):

VB Code:

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class DataBinding : Inherits System.Web.UI.Page
 
    Protected WithEvents rptrPublishers As Repeater
 
    Protected Overrides Sub OnInit(ByVal e As EventArgs)
        If Not Page.IsPostBack Then
            bindPublishers()
        End If
        MyBase.OnInit(e)
    End Sub
 
    Private Sub bindPublishers()
        rptrPublishers.DataSource = getData()
        rptrPublishers.DataBind()
    End Sub
 
    Private Function getData() As DataSet
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                            "data source=F:\msaccess_data\biblio2002.mdb"
        Dim cn As New OleDbConnection(connString)
        Dim cmdText As String = "Select * From Publishers"
        Dim cmd As New OleDbCommand(cmdText, cn)
        Dim da As New OleDbDataAdapter(cmd)
        Dim ds As New DataSet
        da.Fill(ds)
        Return ds
    End Function
 
    Private Sub rptrPublishers_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles rptrPublishers.ItemCreated
        If e.Item.ItemType = ListItemType.AlternatingItem Or e.Item.ItemType = ListItemType.Item Then
            Dim dr As DataRowView = CType(e.Item.DataItem, DataRowView)
            dr.Item("Name") = "<b>" & dr.Item("Name") & "</b>"
        End If
    End Sub
End Class

Aug 20th, 2003, 10:51 AM
pgrimes

Thanks a lot! I'll give that a whirl.

FYI, I'm going to be using the data from the database to show to the client (in the datagrid) and to write to dynamically generated xml files at the same time. When this page loads, it will take the desired data, display it and create an xml file containing it.

Thanks again.