Some help on ASP pages with MS Access DB backend pls 1

[slakker]

Hi.. I really need your help on this one guys..

I need to create an ASP page with a MS Access ( don't ask =\ ) backend database..
I basically need to fill the database with various info and then display it out in tables on an HTML page
It has to be a DNS-less connection to the database (db will reside local to the ASP pages)

A table will 7 colums with Date create, user posted, rank#, etc... at the top, then a Subject row.. and then a Description below that.

I also need to build an Admin web interfact with several admin ids/passwords that will be able to Add/Edit/Remove new data..
This whole site can be very simple as long as it works

Now a little about my experience.. I'm not a developer or programmer.. i'm a designer..
But, i've been using PHP/Perl scripts for a long time with MySQL backend..
I can install them, modify to my needs, and run on windows/*nix servers.. run mysql queries, etc..

But i've never used asp or even created an ms access db before..

Tried searching on the web for tutorials and reading a big fat book on creating websites with db's, but it wasn't that great.. no examples, just overviews

So, i'm asking for any help I can get.. even if it's just a link on some peace of info that'll help me

Thank you in Advance

 

[shaddow]

could give a try to ASP,VBScript section on

http://www.devguru.com

________
George, M

 

[TPetersonFlorida]

check out the following websites to learn ASP, they are an excellent resource!!!

http://www.4guysfromrolla.com

http://www.asp101.com

http://www.w3schools.com

http://www.aspronline.com

that should get you started.

 

[snowboardr]

Well since you know MySQL I would stick with it in ASP, because its many many many times better then Access.

I have a tutorial you can check out on my website that will help you do everything you need to do to get mysql going, important your exsisting Access database, and also connecting with DSN or DSN-Less Connection.

MySQL Installing, connection, etc

http://www.vzio.com/tutorials/mysql_database.asp

'# Getting data from a database (These use access database, but can easily be changed to MySQL if you read the above tutorial)

http://www.vzio.com/tutorials/sql_select_statement.asp

Look here for a list of inserting, deleting etc... you can learn here how to do everything you need to do, Also there is a tutorial on MySQL front, which really speeds up development in MySQL.

http://www.vzio.com/tutorials/

Good Luck! If you need further help let me know.

- Jason



www.vzio.com
ASP WEB DEVELOPMENT

 

[slakker]

Thank you all very much.. i'm going to try some samples off those sites...

And snowboardr, your tutorials look great.. and I would love to use MySQL, but i can't for this project =
I hate MS Access.. grrr

 

[DaveGerard]

How many columns are in the subject row and the description row?

 

[slakker]

this is what it looks like..

http://www.slakker.net/database2.asp

now i just need to add the description row below those rows in each tables, hide the ID column all together from showing on the page, add a FILTER option instead of the sort.. so it'll only show all the records with the APPLICATION name of "whatever here".. and somehow make sure that my rows do not resize no matter how long the data is

 

[DaveGerard]

I think I responded to your posting on WebmasterWorld, but I can be a little more detailed. I have a few questions though.

1) 7 column headings is that right? Are those just headings and not detail for those headings?

2) Subject row and Description row. Are these rows the same width as all 7 columns above them?

3) Are there multiple descriptions for each subject?

4) Is there a detail section of 7 columns with multiple records for each subject or description? In other words do you want to show the subject only once and the have multiple detail rows beneath for the subject?

Sorry, but we gotta be specific here. I know this is a piece of cake to put together. Just describe exactly what you would like the layout to be and include all of the database field names that we're going to be querying, including the subject and description fields. I can have it for you tonight if you need it.

Dave

 

[slakker]

Thanks Dave.. I'll post a screenshot of exactly how I already have this page layed out in HTML..

http://www.slakker.net/screenshot.gif

 

[DaveGerard]

Two last questions.

1)Are you showing multiple records on the same page?

2)If so do you want the column headings displayed only once at the top of the page?

 

[DaveGerard]

Here is a screen shot of what I'm asking.

http://www.webilities.com/dave/samples/MultiRecord/MultiRecords.jpg

 

[slakker]

Currently I have the heading included in every table, but the way you have it in the screenshot is better. So displaying the column headings once would be great..

I'm using MS Access db, and a DSN-less connection..

Thanks again

 

[DaveGerard]

Check out this link and let me know what you think of the functionality. Try the dropdown at the top left.

http://www.webilities.com/dave/Misc/MultiRecords.asp

 

[slakker]

that's perfect Dave! can images be used inplace of *NEW, and in the Status and Severity columns?

 

[DaveGerard]

Yep. Gotta run to the store. I'll post it when I get back.

Dave

 

[DaveGerard]

As promised, here it is. You can use images instead of new, status, and severity columns. I'm assuming that those columns are Yes/No columns. I usually set the format for those to True/False as it is more similar to MsSql Server's &quot;bit&quot; data type, which is 1/0 (true/false). I believe that Access uses -1/0 for true/false. -1 being true. With that in mind I don't often use criteria such as &quot;where field = 1&quot;, but rather &quot;where field <> 0&quot; as that would work for both MsSql and Access. Sorry to be so confusing.

Another thing that you'll notice at the top of the page are three lines that say the following...

<%
db = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & server.mappath(&quot;whatever.mdb&quot
MyApplication = request(&quot;MyApplication&quot
if MyApplication = &quot;$&quot; or MyApplication = &quot;All&quot; then MyApplication = &quot;&quot;
%>

The first is the connection string. I set it up at the top of the page so it is evident that it's not exclusive to one recordset.

Next two lines. The dropdown posts the selected value back to the same page. These two lines are just me requesting that value and if the value does not equal one of your Application Types, the code will set MyApplication back to &quot;&quot;, which will bypass that part of the select statement in the report. You'll see what I mean below.

Here is the whole page, HTML and all. Just copy it into a blank page and give it a shot. Check my field names against yours though first. Let me know how it works or if you aren't clear on something.


'=====================================================

<%
db = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & server.mappath(&quot;whatever.mdb&quot
MyApplication = request(&quot;MyApplication&quot
if MyApplication = &quot;$&quot; or MyApplication = &quot;All&quot; then MyApplication = &quot;&quot;
%>
<html>

<head>
<title>Application</title>
</head>

<body>

<table border=&quot;0&quot; width=&quot;100%&quot; cellspacing=&quot;1&quot; cellpadding=&quot;0&quot;>
<%
set rsApp = server.createobject(&quot;adodb.recordset&quot
sqlApp = &quot;select distinct Application from Tasks&quot;
sqlApp = sqlApp & &quot; order by Application&quot;
rsApp.open sqlApp, db
if not rsApp.eof then
%>
<tr>
<td width=&quot;100%&quot; colspan=&quot;8&quot;>
<form>
<select size=&quot;1&quot; name=&quot;MyApplication&quot; onChange=&quot;if(this.options[this.selectedIndex].value!='$') {window.open(this.options[this.selectedIndex].value,'_self')}&quot;>
<option selected value=&quot;$&quot;>Select Application</option>
<option value=&quot;MultiRecords.asp?MyApplication=All&quot;>All Applications</option>
<%
do while not rsApp.eof
%>
<option value=&quot;MultiRecords.asp?MyApplication=<%=rsApp(&quot;Application&quot%>&quot;><%=rsApp(&quot;Application&quot%></option>
<%
rsApp.movenext
loop
%>
</select></p>
</form>
</td>
</tr>
<%
end if
rsApp.close
set rsApp = nothing
%>
<tr>
<td width=&quot;100%&quot; colspan=&quot;8&quot;></td>
</tr>

<%
set rs = server.createobject(&quot;adodb.recordset&quot
sql = &quot;select * from Tasks&quot;
sql = sql & &quot; where Resolved <> 1&quot;
if MyApplication <> &quot;&quot; then
sql = sql & &quot; and Application = '&quot; & MyApplication & &quot;'&quot;
end if
sql = sql & &quot; order by Application, TimeCreated&quot;
rs.open sql, db
if not rs.eof then
do while not rs.eof
count = count + 1
if rs(&quot;Resolved&quot = true then
Resolved = &quot;Yes&quot;
else
Resolved = &quot;No&quot;
end if

if count = 1 then
%>
<tr>
<td width=&quot;9%&quot; bgcolor=&quot;#D9D9FF&quot;></td>
<td width=&quot;15%&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Application</b></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Last Updated</b></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Status</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Severity</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Case #</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Time Created</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Resolved</b></font></td>
</tr>
<tr>
<td width=&quot;100%&quot; colspan=&quot;8&quot;>
<hr noshade size=&quot;1&quot; color=&quot;#000080&quot;>
</td>
</tr>
<%
end if
%>
<tr>
<td width=&quot;9%&quot; bgcolor=&quot;#ECECFF&quot;>
<p align=&quot;center&quot;><font face=&quot;Verdana&quot; size=&quot;2&quot; color=&quot;#000080&quot;><%if rs(&quot;New&quot <> 0 then%><b>*NEW</b><%end if%></font></td>
<td width=&quot;15%&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;Application&quot%></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;LastUpdated&quot%></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;Status&quot%></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;Severity&quot%></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;CaseNum&quot%></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;TimeCreated&quot%></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=Resolved%></font></td>
</tr>
<tr>
<td width=&quot;100%&quot; colspan=&quot;8&quot;></td>
</tr>
<tr>
<td width=&quot;9%&quot; bgcolor=&quot;#ECECFF&quot; valign=&quot;top&quot; align=&quot;center&quot;>
<p align=&quot;center&quot;><font face=&quot;Verdana&quot; size=&quot;2&quot; color=&quot;#000080&quot;><b>--></b></font></td>
<td width=&quot;91%&quot; colspan=&quot;7&quot; bgcolor=&quot;#ECECFF&quot; valign=&quot;top&quot;>
<font face=&quot;Arial&quot; size=&quot;2&quot;><b><%=rs(&quot;Heading&quot%></b></font><br><br>
<p>
<font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;Description&quot%></font><br><br>
</td>
</tr>
<tr>
<td width=&quot;9%&quot; height=&quot;10&quot;></td>
<td width=&quot;91%&quot; colspan=&quot;7&quot; height=&quot;10&quot;></td>
</tr>
<%
rs.movenext
loop
end if
rs.close
set rs = nothing
%>
</table>

</body>

</html>

 

[DaveGerard]

Almost forgot. I have the column headings (HTML) within the recordset loop as I do the data columns, however it is only displayed once as I am counting records as I loop. I put the row with those headings inside the loop rather than outside the loop because as data gets longer and stretches table cells (We've all experienced it), it will stretch those column heading cells with it and they will always line up correctly.

Check out the part of the code that says.

<%if count = 1 then%>
<tr>
<td width=&quot;9%&quot; bgcolor=&quot;#D9D9FF&quot;></td>
<td width=&quot;15%&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Application</b></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Last Updated</b></font></td>
<td width=&quot;12%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Status</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Severity</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Case #</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Time Created</b></font></td>
<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#D9D9FF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><b>Resolved</b></font></td>
</tr>
<tr>
<td width=&quot;100%&quot; colspan=&quot;8&quot;>
<hr noshade size=&quot;1&quot; color=&quot;#000080&quot;>
</td>
</tr>
<%end if%>

It displays that whole section ONLY if count = 1.

Later...Dave

 

[slakker]

Thank you Dave, this is great.
I have it up and running at

http://www.slakker.net/test.asp

Just one thing i'm not sure of.. am i supposed to create a nother page called MutiRecords.asp for the filtering to work? because it's trying to create: MultiRecords.asp?MyApplication=DSS

And i'm not exactly sure what &quot;Data Type&quot; to use for the fields that are going to be images..

 

[DaveGerard]

Looks great. MultiReacords was just the name of the file that I used. Change your options as follows:

FROM:
<select size=&quot;1&quot; name=&quot;MyApplication&quot; onChange=&quot;if(this.options[this.selectedIndex].value!='$') {window.open(this.options[this.selectedIndex].value,'_self')}&quot;>
<option selected value=&quot;$&quot;>Select Application</option>
<option value=&quot;MultiRecords.asp?MyApplication=All&quot;>All Applications</option>
<%
do while not rsApp.eof
%>
<option value=&quot;MultiRecords.asp?MyApplication=<%=rsApp(&quot;Application&quot%>&quot;><%=rsApp(&quot;Application&quot%></option>
<%
rsApp.movenext
loop
%>
</select>

TO:
<select size=&quot;1&quot; name=&quot;MyApplication&quot; onChange=&quot;if(this.options[this.selectedIndex].value!='$') {window.open(this.options[this.selectedIndex].value,'_self')}&quot;>
<option selected value=&quot;$&quot;>Select Application</option>
<option value=&quot;test.asp?MyApplication=All&quot;>All Applications</option>
<%
do while not rsApp.eof
%>
<option value=&quot;test.asp?MyApplication=<%=rsApp(&quot;Application&quot%>&quot;><%=rsApp(&quot;Application&quot%></option>
<%
rsApp.movenext
loop
%>
</select>


Basically change the option values from &quot;MultiRecords.asp&quot; to &quot;test.asp&quot;. There should be two of them.

 

[DaveGerard]

Oh yeah. DataType for images should probably be Yes/No (formatting set to true/false).


CHANGE:

<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><font face=&quot;Arial&quot; size=&quot;2&quot;><%=rs(&quot;Severity&quot%></font></td>

TO:

<td width=&quot;13%&quot; align=&quot;center&quot; bgcolor=&quot;#ECECFF&quot;><%if rs(&quot;Severity&quot <> 0 then%><img src=&quot;Severe.jpg&quot; border=&quot;0&quot;><%end if%></td>

Using the correct file path and image name for your Severity image. Same this with Status.