Database connection problem

[XTREEMMAK]

Hey guys,
I'm just getting started in learning ASP.NET and in my book I'm learning a chapter on how to connect to a database through Dreamweaver. I have everything set up (I think) on my comp to where I can make a testing server viewing ASP and ASPX pages on it through a local connection. With that out the way I started a new ASPX page and started creating a new database connection. In the book it instructed me define it as OLE DB Connection and then find the provided Access 2000 database file that I should have (and have) draged into the webroot folder indicated as C:\inetpub\wwwroot\cuisine\cuisine.mdb (this is where the testing server is pointed to). Did that and tested it to see if it found the file in the Data Link properties and it said it did. So I hit ok. Now the book told me to get rid of the "Persist Security Info=False" string. I did and they told me to test the connection. I get an error that says "Unspecified Error". WTF?! Just to check I also moved the database file to a different folder that I defined as a Local Remote server on the same system. Still got the same error. The CD also provided MDAC v2.8 and I installed it. (Running Windows Vista BTW) still getting the same error. Anyone know what's going on?

[buntine]

Moved to .NET

[lmf232s]

Can you post your connection string?

[XTREEMMAK]

"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\inetpub\wwwroot\cuisine\database\cuisine.mdb"

[lmf232s]

Well that looks fine.

Are you getting the error in code or are you getting the error in Dreamweaver when trying to create the connection string?

[XTREEMMAK]

When I try to create the connection string. When I get to the OLE DB Connection screen I hit test and it comes up with an "Unspecified Error". No code, no nothing....

Would it also have anything to do with me having my remote server defined on my local computer?

[lmf232s]

xtreemMak,

You might want to check the permissions on your folder that contains the DB.

I dont think having the remove server defined is causing the problem.

Also lets create a test page and see if we cant get it to work and maybe have it display a better error. Create a new page and place this in the page_load sub. Make sure to change your connection string. Also change your select statement and make sure to change the name of the field being written to the page.

See if this helps display a better error:

Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\Testing\WorkingExamples\AccessExample\") & "db2.mdb"

Dim mySelectQuery As String = "SELECT * From Table1"
Dim myConnection As New OleDb.OleDbConnection(myConnString)
Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDb.OleDbDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()
Response.Write(myReader.Item("Field1") & "<BR>")
End While

myReader.Close()
myConnection.Close()

[XTREEMMAK]

Srry if I'm doing this wrong, as I said I'm very new to this. This is what it gave me.
Server Error in '/' Application.
Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30002: Type 'OleDb.OleDbConnection' is not defined.

Source Error:

Line 11:
Line 12: Dim mySelectQuery As String = "SELECT * From Table1"
Line 13: Dim myConnection As New OleDb.OleDbConnection(myConnString)
Line 14: Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
Line 15: myConnection.Open()


Source File: C:\inetpub\wwwroot\cuisine\Test2.aspx Line: 13


Show Detailed Compiler Output:

c:\windows\system32\inetsrv> "C:\Windows\Microsoft.NET\Framework\v2.0.50727\vbc.exe" /t:library /utf8output /R:"C:\Windows\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Runtime.Serialization\3.0.0.0__b77a5c561934e089\System.Runtime.S erialization.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Web.Mobile\2.0.0.0__b03f5f7f11d50a3a\System.Web.Mobile.dll" /R:"C:\Windows\assembly\GAC_32\System.Web\2.0.0.0__b03f5f7f11d50a3a\System.Web.dll" /R:"C:\Windows\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.IdentityModel\3.0.0.0__b77a5c561934e089\System.IdentityModel.dll" /R:"C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\26676eb7\92c7e946\assembly\dl3\38fa6148\004ab74a_206bc701\DreamweaverCtrls.DLL" /R:"C:\Windows\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll" /R:"C:\Windows\assembly\GAC_32\System.EnterpriseServices\2.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServ ices.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.ServiceModel\3.0.0.0__b77a5c561934e089\System.ServiceModel.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\System.Web.Services.dll" /R:"C:\Windows\assembly\GAC_MSIL\System.Drawing\2.0.0.0__b03f5f7f11d50a3a\System.Drawing.dll" /out:"C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\26676eb7\92c7e946\App_Web__3y4taaz.dll" /debug- /win32resource:"C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\26676eb7\92c7e946\_3y4taaz.res" /define:_MYTYPE=\"Web\" /imports:Microsoft.VisualBasic,System,System.Collections,System.Collections.Specialized,System.Config uration,System.Text,System.Text.RegularExpressions,System.Web,System.Web.Caching,System.Web.SessionS tate,System.Web.Security,System.Web.Profile,System.Web.UI,System.Web.UI.WebControls,System.Web.UI.We bControls.WebParts,System.Web.UI.HtmlControls "C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\26676eb7\92c7e946\App_Web__3y4taaz.0.vb" "C:\Windows\Microsoft.NET\Framework\v2.0.50727\Temporary ASP.NET Files\root\26676eb7\92c7e946\App_Web__3y4taaz.1.vb"


Microsoft (R) Visual Basic Compiler version 8.0.50727.312
for Microsoft (R) .NET Framework version 2.0.50727.312
Copyright (c) Microsoft Corporation. All rights reserved.

C:\inetpub\wwwroot\cuisine\Test2.aspx(10) : warning BC42020: Variable declaration without an 'As' clause; type of Object assumed.

Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("C:\inetpub\wwwroot") & "cuisine.mdb"
~~~~~~~~~~~~
C:\inetpub\wwwroot\cuisine\Test2.aspx(13) : error BC30002: Type 'OleDb.OleDbConnection' is not defined.

Dim myConnection As New OleDb.OleDbConnection(myConnString)
~~~~~~~~~~~~~~~~~~~~~
C:\inetpub\wwwroot\cuisine\Test2.aspx(14) : error BC30002: Type 'OleDb.OleDbCommand' is not defined.

Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
~~~~~~~~~~~~~~~~~~
C:\inetpub\wwwroot\cuisine\Test2.aspx(16) : error BC30002: Type 'OleDb.OleDbDataReader' is not defined.

Dim myReader As OleDb.OleDbDataReader
~~~~~~~~~~~~~~~~~~~~~



Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.833

This is what I put:
<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="utf-8" %>
<!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>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Untitled Document</title>
</head>
<body>
<%
Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("C:\inetpub\wwwroot") & "cuisine.mdb"

Dim mySelectQuery As String = "SELECT * From Table1"
Dim myConnection As New OleDb.OleDbConnection(myConnString)
Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDb.OleDbDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()
Response.Write(myReader.Item("Field1") & "<BR>")
End While

myReader.Close()
myConnection.Close()
%>
</body>
</html>


Data Base Info:
"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\inetpub\wwwroot\cuisine.mdb"

Still getting a Unspecified Error there as well.

[donwyo]

Your page declarations are missing the following:

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

Don

[XTREEMMAK]

Here's basically what's going on:
http://img.photobucket.com/albums/v622/XTREEMMAK/1.jpg
http://img.photobucket.com/albums/v622/XTREEMMAK/2.jpg
http://img.photobucket.com/albums/v622/XTREEMMAK/3.jpg
http://img.photobucket.com/albums/v622/XTREEMMAK/4.jpg

[lmf232s]

It seems like its an error being displayed by Adobe Dreamweaver and since I never create my connection strings through a wizard per-say I cant really help you.

On the other hand here is a fully working page. Just copy this to your web site, run it, and see if it displays a better error message. Something that we can deal with besides Unspecified error.

The only thing you will most likely need to change is the select statement and the value of the field that is being displayed to the page.

The goal is to produce an error we can work with instead of Unspecified error.


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

<script language="vbscript" runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\") & "cuisine.mdb"

Dim mySelectQuery As String = "SELECT * From Table1"
Dim myConnection As New OleDb.OleDbConnection(myConnString)
Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDb.OleDbDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()
Response.Write(myReader.Item("Field1") & "<BR>")
End While

myReader.Close()
myConnection.Close()
Catch ex As Exception
Response.Write(Err.Number & ": " & ex.Message & "<BR>")
End Try
End Sub
</script>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
</form>
</body>
</html>

[XTREEMMAK]

Ok I entered the script with and just modified the select statement (I think) like this:
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script language="vbscript" runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\") & "cuisine.mdb"

Dim mySelectQuery As String = "SELECT * From login"
Dim myConnection As New OleDb.OleDbConnection(myConnString)
Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDb.OleDbDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()
Response.Write(myReader.Item("Field1") & "<BR>")
End While

myReader.Close()
myConnection.Close()
Catch ex As Exception
Response.Write(Err.Number & ": " & ex.Message & "<BR>")
End Try
End Sub
</script>
<!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>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>

</div>
</form>
</body>
</html>

This is what the browser returned:
9: Field1

Here's the sample database file they provided on the CD to see if you want to play around with it or what not:
http://www.geocities.com/hufo_2000/cuisine.mdb

If this way is not really a good way of connecting a website with a database, what's better and what should I do? I have been hearing that Visual Studio is better for web app creation anyway. My concern is having the site communicate /connect with all the dynamic content A.K.A. database and all. I do have Visual Studio 2005 but no books on this yet. This is the only reason why I'm trying to get Dreamweaver working with the database connection....BUUUUUUUUUUUUT lol....

[donwyo]

Try this:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" Debug="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Try
Dim myConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("\") & "cuisine.mdb"

Dim mySelectQuery As String = "SELECT * From login"
Dim myConnection As New OleDb.OleDbConnection(myConnString)
Dim myCommand As New OleDb.OleDbCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As OleDb.OleDbDataReader
myReader = myCommand.ExecuteReader()

While myReader.Read()
Response.Write(myReader.Item("Field1") & "<BR>")
End While

myReader.Close()
myConnection.Close()
Catch ex As Exception
Response.Write(Err.Number & ": " & ex.Message & "<BR>")
End Try
End Sub
</script>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<form runat="server">
</form>
</body>
</html>

[XTREEMMAK]

Same output:
9: Field1

[lmf232s]

this line needs to be changed

Response.Write(myReader.Item("Field1") & "<BR>")

Change Field1 to a valid column name that your returning

[donwyo]

Sorry man...there were several other "issues" - I should have downloaded the db and run up a test page before I answered ya.

Here's the code you need for the page itself:

<%@ Page Language="VB" ContentType="text/html" ResponseEncoding="iso-8859-1" Debug="True"%>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>

<script runat="server">

DIM con As OleDbConnection
DIM strConnString As String = ConfigurationSettings.AppSettings( "ConnString4" )

SUB Page_Load

IF NOT Page.IsPostBack THEN
GetRecords()
END IF

END SUB

SUB GetRecords()

DIM strSELECT As String
DIM cmdSELECT As OleDbCommand
DIM rdr As OleDbDataReader

con = NEW OleDbConnection(strConnString)
strSELECT = "SELECT * From login"
cmdSELECT = NEW OleDbCommand(strSELECT, con)

TRY
con.Open()
rdr = cmdSELECT.ExecuteReader()
WHILE rdr.Read()
Response.Write( rdr("FNAME") & "<BR>" )
Response.Write( rdr("LNAME") & "<BR>" )
Response.Write( rdr("ADDRESS1") & "<BR>" )
Response.Write( rdr("ADDRESS2") & "<BR>" )
Response.Write( rdr("CITY") & "<BR>" )
END WHILE
CATCH ex As Exception
Response.Write(Err.Number & ": " & ex.Message & "<BR>")
FINALLY
con.Close()
END TRY

END SUB

</script>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>Untitled Document</title>
</head>
<body>
<form runat="server">
</form>
</body>
</html>

Then, make sure that in your web.config file you have it similar to this:

<?xml version="1.0" encoding="iso-8859-1"?>
<configuration>
<system.web>
<customErrors mode="Off" />
</system.web>
<appSettings>
<add key="ConnString4" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\inetpub\webs\production.com\db\cuisine.mdb;" />
</appSettings>
</configuration>

The test page is working for me with this code.

[donwyo]

Incidentally, and as you can probably tell, I don't let Dreamweaver do my code. I use Dreamweaver every day, but I go into code view and hand code this stuff myself. I never have trusted it to do it right, and evidently this kinda proves that point!

:eek:

[XTREEMMAK]

OMG I THINK IT WORKED!

Output:

Phil
Cowcill
100 College Drive

North Bay


So it looks like I cant use this book now considering that I cant connect this way. Lucky for me it was free lol. So what do you guys purpose I do? I guess I should pick up a book on ASP.NET programming with Visual Studio considering from what I hear is what is commonly used for something like this? (I mean I have VS2005 but no books on ASP.NET for it). The other thing is where will I be able to find information on how to connect a site to a data base like this? In the same type of books?

[lmf232s]

Hit the web and youll find everything you need and more. I usually dont buy books anymore. I did buy a book a couple of years ago when I first started with .net to get a bit of a background on it but then it was my gold old friend GOOGLE :)

IMO I dont care for the technique that the book listed. Ya it works but I feel like you dont get a good understanding of whats happening under the hood.

Anyway here are some links that you may find usefull.
Also Look at the MSDN as it will have helpful information although it does not seem to be loading at the moment :confused: .
http://msdn2.microsoft.com/en-us/default.aspx
http://quickstart.developerfusion.co.uk/QuickStart/howto/doc/adoplus/adodtreader.aspx
http://www.w3schools.com/aspnet/aspnet_dbconnection.asp
http://aspnet101.com/aspnet101/tutorials.aspx?id=17
http://www.java2s.com/Code/ASP/ADO.net-Database/LoopthroughDataSet.htm