Displaying DB results

[LaPluma]

Hello

I have a very basic form input box (it's basic because I am only interested, at this stage, in understanding the principle behind what happens) which requires the visitor to enter an e.mail address.

Once submitted, the e.mail address is inserted into a MS Access DB.

This basic form input box, written in normal HTML, calls an ASP page: <form action=&quot;mail_ac.asp&quot; method=&quot;post&quot;>

The code behind the ASP page follows and it works, but now I am interested in being able to see all the database entries on a normal Web HTML page (it should probably be different to the HTML page on which the basic form appears).

The databse simply contains e.mail addresses, but how can I display them on a Web page (I would not particularly want anybody else to be able to see them)?

Any help appreciated

Best wishes

 

[onpnt]

this creates a table with all the fields from the DB and writes them to a page
just fill in your DSN and DB info

<%
'were going to connect to the DB here
Set ConnectionToDatabase=Server.CreateObject(&quot;ADODB.Connection&quot
connectionToDatabase.ConnectionTimeout=60
connectionToDatabase.Open &quot;DSN=DB&quot;
'get the data
Set recordSet=Server.CreateObject(&quot;ADODB.Recordset&quot
recordset.Open &quot;SELECT * FROM table&quot;, connectionToDatabase
'that opened the table using the execute function
%>
<%
Response.Write(&quot;<CENTER>&quot
Response.Write(&quot;<TABLE BORDER=3 bgcolor=lightblue width=650>&quot
Do while not Recordset.EOF
Response.Write(&quot;<TR>&quot
for x=0 to Recordset.Fields.count-1
Response.Write(&quot;<TD bgcolor=ivory>&quot
Response.Write(Recordset(x))
Response.Write(&quot;</TD>&quot

next 'so we populate all the records
Response.Write(&quot;</TR>&quot
Recordset.MoveNext

Loop
Response.Write(&quot;</TABLE>&quot
%>
<%
'close the database and set connection to nothing
connectionToDatabase.Close
Set connectionToDatabase=Nothing
%>
</BODY>
</HTML> I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[LaPluma]

Hello Onpnt

Very many thanks for your e.mail and assistance.

I'll try it out, but can I comment out the

connectionToDatabase.Open &quot;DSN=DB&quot;?

I ask that because the pages on hosted on Brinkster which cannot accept DSN connections.

The code I have in the mail_ac.asp page is:

<%

Dim email, con, data_source, sql_insert, sql_check, rs

email = Request.Form(&quot;email&quot
If Len(email) Then


data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
Server.MapPath(&quot;mail.mdb&quot

sql_check = &quot;select email from users where email = '&quot; & email & &quot;'&quot;
sql_insert = &quot;insert into users( email ) values ('&quot; & email & &quot;')&quot;

Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.Open data_source

Set rs = con.Execute (sql_check, , 1)
database
If rs.EOF Then

con.Execute sql_insert

Response.Write &quot;Thank you. Your email address <b>&quot; & email & _
&quot;</b> was successfully entered into the database&quot;

Else
Response.Write &quot;Your email address &quot; & email & _
&quot; is already present in the database. Thank you.&quot;
End If

Nothing
con.Close
Set con = Nothing

Else
Response.Redirect &quot;mail.htm&quot;
End If
%>


Many thanks again

 

[onpnt]

try this.
<%

Dim email, con, data_source, sql_read, rs

data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
Server.MapPath(&quot;mail.mdb&quot

sql_read = &quot;SELECT email FROM users&quot;


Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.Open data_source

Set rs = con.Execute (sql_check)
Do while not Recordset.EOF
Response.Write(&quot;<TR>&quot
for x=0 to rs.Fields.count-1
Response.Write(&quot;<TD bgcolor=ivory>&quot
Response.Write(rs(x))
Response.Write(&quot;</TD>&quot

next 'so we populate all the records
Response.Write(&quot;</TR>&quot
rs.MoveNext

Loop
Response.Write(&quot;</TABLE>&quot
%> I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[LaPluma]

Hello onpnt

Thanks again for your reply.

Sorry, mate but I can't get it to work.

The normal HTML page I have at:

http://www11.brinkster.com/stevehigham/db/mail.htm

contains the following code:

<HTML>
<HEAD>

<TITLE>Database test site</TITLE></HEAD>
<BODY>

<center><h3>Database test<font color=&quot;#000099&quot;>Records</font></h3></center>

<form action=&quot;mail_ac.asp&quot; method=&quot;post&quot;>



<center><b><font face=&quot;Arial,Helvetica&quot;><font color=&quot;#000099&quot;><font size=-1>Email</font></font></font></b>


<input type=&quot;text&quot; name=&quot;Email&quot; cols=&quot;60&quot;>

<p><input type=&quot;submit&quot; value=&quot;Submit&quot;></p></center>

</form>
</BODY>
</HTML>

As you suggested I have substituted the original code behind the mail_ac.asp file and this now looks as follows:
(This is your code)

<%

Dim email, con, data_source, sql_read, rs

data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
Server.MapPath(&quot;mail.mdb&quot

sql_read = &quot;SELECT email FROM users&quot;


Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.Open data_source

Set rs = con.Execute (sql_check)
Do while not Recordset.EOF
Response.Write(&quot;<TR>&quot
for x=0 to rs.Fields.count-1
Response.Write(&quot;<TD bgcolor=ivory>&quot
Response.Write(rs(x))
Response.Write(&quot;</TD>&quot

next 'so we populate all the records
Response.Write(&quot;</TR>&quot
rs.MoveNext

Loop
Response.Write(&quot;</TABLE>&quot
%>

Unfortunately, when I 'View' the mail_ac.asp page (that is, the page above), I get the following message:

Microsoft JET Database Engine error '80040e0c'

Command text was not set for the command object.

/stevehigham/db/mail_ac.asp, line 14

I would be grateful if you could spare the time to advise me where I might be going wrong.

Thank you.

Steve

 

[onpnt]

this shouldn't have replaced you're insert asp if that's what you did. This was only for viewing purposes. After the insert has been completed then you can view this page. Or actually at any point view it by just going to the .asp with this code in it. I'm sorry if I misunderstood what you were trying to do.

try changing this line
sql_read = &quot;SELECT email FROM users&quot;
to
sql_read = &quot;SELECT * FROM users&quot;

rememebr this is only to view records. It will not insert the new email intot he DB.
I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.

 

[onpnt]

I tested this script out with a DB of mine and it works fine. It should be perfect for you to display the records of the emails
<%

Dim email, con, data_source, sql_read, rs

data_source = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & _
Server.MapPath(&quot;mail.mdb&quot

sql_read = &quot;SELECT * FROM users&quot;


Set con = Server.CreateObject(&quot;ADODB.Connection&quot
con.Open data_source

set rs = con.Execute(sql_read)
Do while not rs.EOF
Response.Write(&quot;<TR>&quot
for x=0 to rs.Fields.count-1
Response.Write(&quot;<TD bgcolor=ivory>&quot
Response.Write(rs(x))
Response.Write(&quot;</TD>&quot

next 'so we populate all the records
Response.Write(&quot;</TR>&quot
rs.MoveNext

Loop
Response.Write(&quot;</TABLE>&quot
%> I may not get it the 1st or 2nd time,
but how sweet that 15th time can be.