• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1753
  • Last Modified:

How do I create an address book using asp and MS Access database?

I want to create a personal address book with an MS Access database, ASP. I will host on a Web server. This will give me the ability to access, update information from anywhere I have Internet access.

I searched the EE database and didn't find what I am looking for. Suggestions on where to start, etc. Thanks.

0
kfranck
Asked:
kfranck
  • 14
  • 13
1 Solution
 
Wayne BarronCommented:
All you need to do is create a page that have you Form information on it.
With a Submit Button that sends the information to the ASP Code to
Generate the Record in the Database for you.
Then have another page to Update each entry in the database.

If this is what you want, let me know and I can make you up a demo right quick.

Carrzkiss
0
 
kfranckAuthor Commented:
Thanks Carrzkiss. If you could give me a quick demo that would be great.

Several years back, I created a personnel address book in MS Access. I could click on the Access database icon on the desktop of one of my computers and look up people by Last name,  add people, etc.

Now I want to develop a similar address book in ASP and host on a Web server. It will grab the data from the MS Access database that I already use.

Any suggestions would be helpful. Thanks.
Kfranck
0
 
Wayne BarronCommented:
Is this going to be something that ONLY YOU are going to be using?
(or) Will other people beable to create Entries in it as well?

(Almost done with the demo)

Carrzkiss
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
kfranckAuthor Commented:
Only family members. Not for public use.

I don't have the fields with me at work, but I can adapt from your demo. I just want to be able to search for names, etc., and add/update names, addresses. Thanks.

0
 
Wayne BarronCommented:
Here you go
http://ee.cffcs.com/Q_24342269/New.asp
code
http://ee.cffcs.com/Q_24342269/Q_24342269.zip

Files include:
ACN.asp = Connection to Database
Edit.asp = Edit Existing Entry
List.asp = List all Entries  (If you choose to use this, put it within a paging system, you have the code for that)
New.asp = Create New Entry
Q_24342269.mdb = Demo Database with 2 Entries, for Demo Purpose only
search.asp = The search page. (Search for either First (OR) Last Name, not both)
SQL.asp = This is the Insert & Update Statements Holder Page.

If you have any question, feel free to ask.

Carrzkiss
0
 
kfranckAuthor Commented:
Perfect! More than I ever dreamed. I will play around with it. I will add the data from my database and test it out. Thanks for the help and the quick turnaround. You're terrific.
kfranck
0
 
Wayne BarronCommented:
You are welcome.
It was a fun one.

have a good one.
Carrzkiss

Author Comments:
Perfect! More than I ever dreamed. I will play around with it. I will add the data from my database and test it out. Thanks for the help and the quick turnaround. You're terrific.
kfranck
0
 
kfranckAuthor Commented:
Carrzkiss:

I made a change in the ACN.asp and the database. I called the database franck.mdb and changed in the ACN.asp.

I am getting this error when I try to add my data as a test into the database:

Microsoft JET Database Engine error '80004005'
Operation must use an updateable query.
/address/SQL.asp, line 20
0
 
Wayne BarronCommented:
This is something that happens when your directory does not have the proper rights.

This is from my Future KB that I am working on.
I have run into this error so many times that when I reinstall my system, I always set my
Right before I even open a site locally.
===================
Right click on your Database (Access in this case)
Go to: [Properties]
Then choose the [Security] tab.

Make sure that you have the [Internet Guest Account]
Listed in the [Group or user names:] Area.
If it is, then give it all permissions.
If it is not, then add it to the list.
Then give it all permissions.

(If Security Tab is not available)
In Windows Explorer, go to
Tools/Folder Options/View and click to turn off 'Use simple file sharing' at
the bottom of the list.
===================

Carrzkiss
0
 
kfranckAuthor Commented:
Carrzkiss:

I put on my office intranet and my yoursourceblade.com server and I was able to add an address. However, when I try to update one of the entries or I run list.asp,  I get a connection error:

 (This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying.

It must be a problem with the sql.asp page. Suggestions?

You can access at yoursourceblade.com/AB/new.asp

0
 
Wayne BarronCommented:
Updated the page.
edit.asp
You can either download the code and get the updated code.
(or)
In edit.asp
Replace
<form class="Address" name="AddressForm" method="post" action="SQL.asp?Address=Update">

With this

<form class="Address" name="AddressForm" method="post" action="SQL.asp?AddID=<%=request.QueryString("AddID")%>&amp;Address=Update">

===============
List.asp
Is NOT Self Contained.
If you are wanting to run it on it's own outside of the other pages.
Then you will need to add this line to the top of the page.

<!--#include file="ACN.asp"-->

You will also need to make it into a Real XHTML page.
As in.
<html>
<head>
And the rest of the goodies, as that page only consist of a Table,
As it was originally designed to be called only as an INCLUDE FILE.
Included in:  new.asp & edit.asp

Have a good one.
Carrzkiss
0
 
kfranckAuthor Commented:
Thanks. I fixed the edit.asp page. Just changed the code you suggested. Works great.

I will have to think over the list.asp page. Too tired now. I will look it over tomorrow.

Thanks again.
0
 
Wayne BarronCommented:
Updated Files (Includes List_Self.asp)

ACN.asp = Connection to Database
Edit.asp = Edit Existing Entry
List.asp = List all Entries (If you choose to use this, put it within a paging system, you have the code for that)
List_Self.asp = List the contacts and Search Box in a Self Contained page.
New.asp = Create New Entry
Q_24342269.mdb = Demo Database with 2 Entries, for Demo Purpose only
search.asp = The search page. (Search for either First (OR) Last Name, not both)
SQL.asp = This is the Insert & Update Statements Holder Page.


This should do what you want it to now.
Just make the pages more your own, customize them and so forth.

Good Luck
Carrzkiss
0
 
kfranckAuthor Commented:
Thanks.

I have three other questions about deleting, adding a checkbox (such as XMAS card sent) and print label ability.

Want me submit another post?
0
 
Wayne BarronCommented:
yep.
That would require another question.

Just post the link in here and I will try to see what I can do for you.
If someone else does not assist you first.

Carrzkiss
0
 
kfranckAuthor Commented:
Carrzkiss:

Thanks. I will post a new question shortly.

Before I do that, I have one thing with the previous post: I successfully imported my data into the database and added "Cell" as a new field. I made the code changes on all pages, adding "Cell" to SQL lines, etc.

Everything works, except the search page. I get this error:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

/AB/search.asp, line 120

I believe the problem is on this page and not any other page. I am including the search.asp page in the code snippet. Thanks.


<!--#include file="ACN.asp"--> 
<%
 
'then you would create your recordset object
set mRS = server.createobject("ADODB.Recordset")
 
if Request.form("FName") = "" then
strFName = "%"
else
strFName = Request.form("FName")
 
 
strFName = replace(strFName, " ", "%' and FName like '%", 1)
strFName = "%" & strFName & "%"
 
mSQL = "SELECT AddID, FName, LName, Email, HPhone, Cell, BPhone, HAddress, BAddress, City, State, ZIP, PerMemo, DateAdded FROM Addresses WHERE (((FName) Like '%" &replace(strFName,"'","''")&"%'))ORDER BY AddID ASC"
 mRs.Open mSQL, OneCon
end if 
 
 
 if Request.form("LName") = "" then
strLName = "%"
else
strLName = Request.form("LName")
 
 
strLName = replace(strLName, " ", "%' and LName like '%", 1)
strLName = "%" & strLName & "%"
 
mSQL = "SELECT AddID, FName, LName, Email, HPhone, Cell, BPhone, HAddress, BAddress, City, State, ZIP, PerMemo, DateAdded FROM Addresses WHERE (((LName) Like '%" &replace(strLName,"'","''")&"%')) ORDER BY AddID ASC"
 mRs.Open mSQL, OneCon
 
end if
'=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
%>
<html>
 
 
<head>
<title>Search Addresses</title>
 
<STYLE TYPE="text/css">
<!-- 
A:link {
text-decoration: none
}
A:visited{
text-decoration:none
}
A:active{
text-decoration:none
}
a:hover{
color: #CA0002;
text-decoration:underline
}
.oddRow{
background-color:#FFFFFF;
}
.evenRow{
background-color:#C0C0C0;
}
#MainTR{
color:#FFFFFF;
font-family:Arial, Helvetica, sans-serif; 
font-size:11pt;
background-color:#9d0014;
border: 1px solid #C0C0C0;
text-align:left;
}
#BannerTD{
width:359px;
}
#BannerTD .BanTD{
border-top:2px solid #002E89;
text-align:center;
font-size:2px;
font-family:arial;
}
#MainTD{
width:300px;
}
#MainTD .thTD{
background-color: #002E89;
font-size:12px;
}
-->
</style>
</head>
<body> 
<div align="center">
	&nbsp;<table id="BannerTD">
		<tr>
		  <td class="BanTD">
			<p align="left">
			<br>
			<img border="0" src="images2/UTtitleSearch.gif" width="354" height="62"><br>
			&nbsp;</td>
		</tr>
		<tr>
		  <td class="BanTD">
			<p align="right"><b>
		</tr>
	</table>
</div>
 
 
<div align="center">
&nbsp;<table  id="MainTD" width="300">
<tr id="MainTR">
      <th width="173" class="thTD">LAST Name</th>
    <th width="150" class="thTD">First Name</th>
 
  </tr>
      <%
'cycle through result set
Dim counter,theClass
counter=0
do while not mRS.EOF
	counter = counter + 1
	theClass="oddRow"
	If (counter Mod 2 ) = 0 Then
		theClass="evenRow"
	End If
   %>
      <tr class="<%=theClass%>">
      <td style="width:15px; font-family:Arial; font-size:11pt"><b><a href="Edit.asp?AddID=<%=mRS("AddID")%>"><%=mRS("LName")%></a></b></td>
      <td style="width:150px; font-family:Arial; font-size:9pt"><%=mRS("FName")%></td>
    </tr>
      <%                                                         
    mRS.Movenext
loop
      %>
</table>
</div>
</body>
</html> 

Open in new window

0
 
Wayne BarronCommented:
Are you searching BOTH fields at the same time?
If so, then that is the reason why you are getting the error.
The code will only allow for you searching a single field at a time.
Either by: First (or) Last Name Only, not both of them together.

Carrzkiss
0
 
kfranckAuthor Commented:
No. Just searching Last Name and I get the error.

Am I correct that the problem would be on the search.asp page and not one of the other pages? I screwed something up.
0
 
Wayne BarronCommented:
.zip up the scripts and send them over to me.
With your midifed database and evreything.
You can send them via my email.
I will have a look at it. And post my results back in here.

Carrzkiss
0
 
kfranckAuthor Commented:
Done.
0
 
Wayne BarronCommented:
Humm.
Should I make you guess as to why the search got broken?
..thinking here...... should I make you think...?

OK.
Lets look at this together shall we.

A Form consist of Fields, and them Fields have Names.
FName and LName
OK.
The Search Query that is made on the Search page, is looking for
FName & LName
So, when you type in your Search Query into the FName and LName fields
The Query is passed along to the Search Page and then Query is made against the database
And that information is then sent back to you giving you the end-results.
BUT
In the chance that you change something... oh lets see here.
The Form Field Names perhaps.
FName0 and LName0
When you type in information into either of these 2 Fields, you are not going to get nothing
Returned from the database. WHY?
Simple.
strLName = Request.Form("LName")
strFName = Request.Form("FName")

See, the above lines of code is looking for the Form Fields named respectfully named:
FName and LName.
NOT
FName0 and LName0

So, there you go.

In your List.asp page (Which really needed to be redeisgned
The </form> element was not in the proper place and it was blown out of whack.)
You need to change the Search Form Fields back to what I had them in the original code.

Take my code below and replace yours with it.
Better on the eyes. And also better for people going onto the page.
This is a way better design.

Also
Please use the CSS Code to work with your Table and Field Elements.
Using the   <Table Width="100px"> is not Valid any more.
So please use the CSS Version of it. (As shown in the code below)

Have a good one.
Carrzkiss
<%
Set objRs1=Server.CreateObject("ADODB.RecordSet")
SQLAddress1 = "SELECT AddID, FName, LName, Email, HPhone, Cell, BPhone, HAddress, BAddress, City, State, ZIP, PerMemo, DateAdded FROM Addresses ORDER BY LName ASC"
objRs1.open SQLAddress1, OneCon
%>
<STYLE TYPE="text/css">
<!-- 
A:link {text-decoration: none}
A:visited{text-decoration:none}
A:active{text-decoration:none}
a:hover{color: #CA0002;text-decoration:underline}
.Holder {
height: 500px; 
width: 450px; 
font-size: 12px; 
overflow: auto;
background-color:skyblue;
}
#HeaderTable{
width: 450px; 
font-size: 12px;
background-color:lightblue;
}
-->
</STYLE>
 
<form action="search.asp" method="post">
<table>
<caption>
<b><font color="#003366" face="Arial" size="5">Contacts</font></b>
<tr>
<td>
<table>
  <tr>
    <td class="Address"><font style="font-size: 11pt; font-weight: 700" color="#003366"><font face="Arial">First Name</font></font></td>
    <td>&nbsp;</td>
    <td class="Address"><font style="font-size: 11pt; font-weight: 700" color="#003366"><font face="Arial">Last Name</font></font></td>
  </tr>
  <tr>
    <td class="Address"><span style="font-size: 11pt"><font color="#003366">
      <input name="FName" style="font-weight: 700" />
    </font></span></td>
    <td class="Address"><div align="center"><font style="font-size: 11pt; font-weight: 700" color="#003366">OR </font></div></td>
    <td class="Address"><input name="LName" type="text" /></td>
  </tr>
  <tr>
    <td class="Address">&nbsp;</td>
    <td><span class="Address">
      <input name="Submit" type="submit" value="Submit" />
    </span></td>
    <td>&nbsp;</td>
  </tr>
</table>
</form>
 
<table id="HeaderTable">
<tr>
<td class="Address">
<b><font color="#003366" face="Arial" size="4">Name</font></b></td>
<td class="Address">
	<div align="right"><b><font color="#003366" face="Arial" style="font-size: 13pt">Date Added</font></b></div></td>
</tr>
</table>
 
<div class="Holder">
<table>
  <tr>
    </td>
  </tr>
   <%While Not objRs1.EOF
   if n mod 1 = 0 then%>
  <tr>
   
 <td class="Address"><a href="Edit.asp?AddID=<%=objRS1("AddID")%>"><%=objRS1("FName")&"&nbsp;"& objRS1("LName")%></a></td>
    <td class="Address" style="font-size: 8pt" align="right"><%=objRS1("DateAdded")%></td>
    
  </tr>
  
  <%
   end if
    n = n + 1
    objRs1.MoveNext
	Wend
	objRs1.Close
	Set objRs1=Nothing
%>
</table>
</div>

Open in new window

0
 
kfranckAuthor Commented:
Carrzkiss:

Thanks for all your help. You are good teacher. I'm just a bad student, at times. Search works great.

Question: I tried to add another contact and I got a connection error:

"This error (HTTP 500 Internal Server Error) means that the website you are visiting had a server problem which prevented the webpage from displaying.
For more information about HTTP errors, see Help."

I didn't change anything on that page.
0
 
Wayne BarronCommented:
I just test your script that you sent to me.
And I had no problems adding OR editing any entries.

There is a problem on the server side that is causing this issue.

Carrzkiss
0
 
kfranckAuthor Commented:
Our server have been down. It works fine now. Thanks. Still can't get the date entered data to show. Wonder why?


Later tonight, I will open a new post with additional questions.

Thanks again.
0
 
Wayne BarronCommented:
what do you mean:
>>Still can't get the date entered data to show

Please explain more detailed what you mean by this one?
0
 
kfranckAuthor Commented:
I figured the "date entered problem". It was missing Now() in the database.
kfranck
0
 
Wayne BarronCommented:
yep.
That would do it.

Let me know about the other question, when you post it.
I get in over 30 EE emails an hour and sometimes it is hard to keep track of all of them.
As well as working on my own stuff as well.

Have a good one.
Carrzkiss
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now