unable to connect to MS Access database with ASP.NET code. PLEASE PLEASE HELP??

Printable View

Feb 1st, 2004, 01:22 AM
Andrew2004

unable to connect to MS Access database with ASP.NET code. PLEASE PLEASE HELP??

Hi

I am new to ASP.NET. I have spent a whole day tried to figure out why the below asp.net code CAN'T open/display the content of the database "Northwind.mdb", which is MS Access database.
Could someone please help me why the below code doesn't work? Thank you so much

I found the below code at this website: http://www.w3schools.com/aspnet/aspnet_dbconnection.asp

----- my_access.aspx --------------------------
<%@ 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=" & server.mappath("northwind.mdb"))
dbconn.Open()
sql="SELECT * FROM customers"
dbcomm=New OleDbCommand(sql,dbconn)
dbread=dbcomm.ExecuteReader()
customers.DataSource=dbread
customers.DataBind()
dbread.Close()
dbconn.Close()
end sub
</script>

<html>
<body><form runat="server">
<asp:Repeater id="customers" runat="server"><HeaderTemplate>
<table border="1" width="100%">
<tr>
<th>Companyname</th>
<th>Contactname</th>
<th>Address</th>
<th>City</th>
</tr>
</HeaderTemplate><ItemTemplate>
<tr>
<td><%#Container.DataItem("companyname")%></td>
<td><%#Container.DataItem("contactname")%></td>
<td><%#Container.DataItem("address")%></td>
<td><%#Container.DataItem("city")%></td>
</tr>
</ItemTemplate><FooterTemplate>
</table>
</FooterTemplate></asp:Repeater>
</form></body>
</html>

The following works assuming the Northwind.mdb file is located in the same folder as the .aspx page.

VB Code:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" Debug="True"%>
<%@ Import Namespace="System.Data.OleDb" %>
 
<script runat="server">
Sub Page_Load
    Dim Conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.mappath("Northwind.mdb"))
    Dim Comm As New OleDbCommand("SELECT * FROM Customers",Conn)
    Dim reader As OleDbDataReader
    Try
        Conn.Open()
        reader = Comm.ExecuteReader()
        customers.DataSource = reader
        customers.DataBind()
        reader.Close()
        Conn.Close()
    Catch ex As Exception
        Response.write("Error Occured: " & ex.Message() & "<br>" & ex.StackTrace)
    End Try
End Sub
</script>
 
<html>
<body><form runat="server">
<asp:Repeater id="customers" runat="server">
 <HeaderTemplate>
  <table border="1" width="100%">
    <tr>
      <th>Companyname</th>
      <th>Contactname</th>
      <th>Address</th>
      <th>City</th>
    </tr>
  </HeaderTemplate>
  <ItemTemplate>
    <tr>
      <td><%#Container.DataItem("companyname")%></td>
      <td><%#Container.DataItem("contactname")%></td>
      <td><%#Container.DataItem("address")%></td>
      <td><%#Container.DataItem("city")%></td>
    </tr>
  </ItemTemplate>
  <FooterTemplate>
    </table>
  </FooterTemplate>
</asp:Repeater>
</form>
</body>
</html>

You must add the ASPNET user account and give "write" permissions to the folder containing the database and the database itself

Feb 1st, 2004, 08:14 PM
Andrew2004

Thank you so much for your help, Memnoch1207. But I don't know what you mean "add the ASPNET user account". How can I do that? Do I need to have Visual Studio.NET program in order to try your code?
Feb 1st, 2004, 11:18 PM
Memnoch1207

on the folder containing the database, as well as, the database itself.
1) Right click it, Select properties
2) Click the Security tab
3) Click the Add button
4) Locate the ASPNET account
5) Click Add, Click Ok
6) Give Write permissions to the account

No, you don't need VS.NET to run the code I posted above, but you do need IIS or PWS running on the machine.
Feb 2nd, 2004, 09:53 AM
Andrew2004

Thank you for your reply Memnoch1207. But after I selected the Properties, I don't see the "Security" tab. It only has General, Sharing, Web Sharing, and Customize tabs. Since I am using Windows XP Pro and I have installed and configure IIS on my computer. I have also setup the virtual directory for the folder that contains the database. Thank you for any help.
Feb 2nd, 2004, 02:01 PM
Memnoch1207

How to display the Security Tab.

1) Click Start, and then click My Computer or Windows Explorer.
2) On the Tools menu, click Folder Options, and then click the View tab.
3) In the Advanced Settings section, clear the Use simple file sharing (Recommended) check box.
4) Click OK.
Feb 2nd, 2004, 04:23 PM
Andrew2004

Hi Memnoch1207. After I did what you told me how to display the Security tab, but I still could not get the Security tab after clicking on the Property of the folder. Everything seems to be the same thing as before. So what can I do?
Feb 2nd, 2004, 04:48 PM
Memnoch1207

That should have done it....It works just fine on my machine.
Feb 2nd, 2004, 05:25 PM
Andrew2004

Thank you. But so far the code that you gave me still could not open and display the Northwind.mdb database.
Feb 2nd, 2004, 06:04 PM
Memnoch1207

are you getting an error? If so, what is it?
Feb 2nd, 2004, 09:34 PM
Andrew2004

1 Attachment(s)

I did not get any error. It only displays one row with attributes such as Companyname, Contactname, Address, City. Below attached file is the result after I ran the script by entering the address, "http://localhost/ASPNETdb/ch03/my_access_connection_1.aspx" into the IE 6 browser. Thank you so much for your time to help me.
Feb 3rd, 2004, 10:55 AM
Memnoch1207

Does your code look like the code I posted above with a Try - Catch block???

I am assuming you have checked the database and made sure there are records in it?
Feb 3rd, 2004, 12:06 PM
Andrew2004

Thank you Memnoch1207. Yes, my code looks exactly at your code. I have also checked the Customers table of the Northwind.mdb database and everything looks all right, but I don't know why it did not work.

Before I have read a book that teaches how to write code in ASP.NET with database. I followed exactly what the book told me to do and I actually understood well what it is talking about, but I also did not get the Northwind.mdb database to display on the browser. So I have spent a lot of time to search code on Internet to see what I did wrong. But so far, I don't know what is wrong.
Below is the code from the textbook that I have read. It couldn't display the Northwind database.

---- Access_connection.aspx --------------

<%@ Import namespace="System.Data" %>
<%@ Import namespace="System.Data.OleDb" %>
<%@ Page debug="true" %>

<html>
<head>
<title>Connecting to an Access Database</title>
</head>

<body>
<H3>Connecting to an Access Database</H3>
<asp: DataGrid id="dgSuppliers" runat="server" />
</body>
</html>

<script language="VB" runat="server">

Sub Page_Load(Source As Object, E As EventArgs)
Dim strConnection As String = "Provider=Microsoft.Jet.OleDb.4.0;" & _
"data source=D:\ASPNETdb\datastores\Northwind.mdb;"
Dim objConnection As New OleDbConnection(strConnection)

Dim strSQL As String = "SELECT SupplierID, CompanyName " & _
"FROM Suppliers;"
Dim objCommand As New OleDbCommand(strSQL, objConnection)

objConnection.Open()
dgSuppliers.DataSource = objCommand.ExecuteReader()
dgSuppliers.DataBind()
objConnection.Close()
End Sub
</script>
Feb 14th, 2004, 03:41 AM
Andrew2004

Yes, finally I have solved my problem. The problem is not about coding, but something wrong in my computer that it couldn't recognize the access database. However, I am very appreciative for your help Memnoch1207.