Stop me before I use DSN again!

  • Thread starter Thread starter bender
  • Start date Start date
B

bender

I have an application on a client's server that is progressing nicely;
I've used a DSN connection to the access d/b

So as an excercise in going DSNless, i've published the site to my
home server that has no such DSN defined.

I've edited my global.asa with the OLEDB string, and done some other
adjustments, and all seems to be working ok, except.....

when i go to one of the search pages i've created with the DRW, it
gives me the old error:

Database Results Wizard Error
The operation failed. If this continues, please contact your server
administrator.

however, the search works perfectly?!

this page takes a long time to re-do, due to the way i reformat the
tables, etc., and the fact that it's drawn from a query that uses a
linked table, (which makes me have to use a dummy query first that
uses an imported table, then switch the name of the real query after
the drw is finished). so i'd like to avoid re-doing.

any ideas how i can just get rid of this error? i'd like, someday to
be able to do these search pages without using the DRW (if only as an
excercise), but i can't seem to figure out a way to do it in ASP and
have one of the results columns be a hyperlink.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
 
Hi,
you're right not to want to use DSNs - 1/they're deprecated(may or may not
be supported in future but certainly won't be updated) 2/they're slower

I don't use the FP wizard myself so somebody else could probably help you
better on the specifics, but I just tried setting up a connection setting
"file or folder in current web" as the database location. FP wrote out this
connection string
Application("Sample_ConnectionString") = "DRIVER={Microsoft Access Driver
(*.mdb)};DBQ=URL=fpdb/fpnwind.mdb"
which I edited to
Application("Sample_ConnectionString") =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("fpdb/fpnwind.mdb")
tried a few different queries and all seemed to work fine.

A better option still would be to move away from the wizard completely and
write some asp(or even asp.net) yourself - you'll get a faster more flexible
solution

Jon
Microsoft MVP - FP
 
A better option still would be to move away from the wizard completely and
write some asp(or even asp.net) yourself - you'll get a faster more flexible
solution

Jon
Microsoft MVP - FP
Click to expand...
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Thanks, that's exactly what I want to be doing...having had a
hate/hate relationship with the DRW for some time now. So many
workarounds to get various things done. I've purchased 3 books on ASP
3, but still have not found out how I can have it generate tables that
include hyperlinks in one of the columns, which, I admit, is easy to
do using the DRW. Also am wondering about using some sort of style
sheet to help with the way i always like to format my tables, since
having to constantly reformat DRW tables every time the table is
re-generated.

I've used ASP to create all of my forms that add files or update files
and I do feel i'm starting to get the hang of it. It's just creating
search pages that display tables; still have not mastered it in ASP.

Since i'm familiar with Access and don't have the need (or money) to
use MS-SQL yet, I'm putting off any exploration of ASP.Net (tho i'm
sure i'll go there sometime in the future). The sites I create have
relatively small d/b's and limited users, so Access works just fine
for now. Also thinking of investing in J-Bots.

bender
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
 
Hi,
for a simple asp example let's say you've got some data like this
name website
Me http://www.me.com
Him http://www.her.com
etc....
and you want to generate a table showing name and a clickable link to the
website.
Start with some CSS to style the table
<style type="text/css">
#myTable{
font: 14px Verdana, Arial, Helvetica, sans-serif;
border: 1px solid #333;
width: 80%;
}
#myTable th{
border-bottom: 1px solid #666;
padding: 2px;
background-color: blue;
color: #fff;
text-align:left;
}
#myTable td{
border-bottom: 1px solid #999;
padding: 1px 2px;
background-color: #fff;
color: #333;
}
#myTable a{
color: #333;
}
</style>
Now get the data and build the table

<table broder=0 cellspacing=0 cellpadding=0 id="myTable">
<tr><th>Name</th><th>Web site</th></tr>
<%
set oRs=server.createobject("adodb.recordset")
oRs.open "select name,website from table", ' your connection string
aData = oRs.getrows
oRs.close: set oRs=nothing
for i = 0 to ubound(aData,2)
%>
<tr><td><%=aData(0,i)%></td><td><a href="<%=aData(1,i)%>"><%=aData(0,i)%>'s
Site</a></td></tr>
<%next%>
</table>

You should find doing this way to be easier, and certainly more efficient,
than using the DRW.

Jon
microsoft MVP - FP
 
-----Original Message-----
I have an application on a client's server that is
progressing nicely; I've used a DSN connection to the
access d/b

So as an excercise in going DSNless, i've published the
site to my home server that has no such DSN defined.

I've edited my global.asa with the OLEDB string, and
done some other adjustments, and all seems to be working
ok, except.....

when i go to one of the search pages i've created with
the DRW, it gives me the old error:

Database Results Wizard Error
The operation failed. If this continues, please contact
your server administrator.

however, the search works perfectly?!

this page takes a long time to re-do, due to the way i
reformat the tables, etc., and the fact that it's drawn
from a query that uses a linked table, (which makes me
have to use a dummy query first that uses an imported
table, then switch the name of the real query after
the drw is finished). so i'd like to avoid re-doing.

any ideas how i can just get rid of this error? i'd
like, someday to be able to do these search pages
without using the DRW (if only as an excercise), but i
can't seem to figure out a way to do it in ASP and
have one of the results columns be a hyperlink.
Click to expand...

Very well. Stop, Larry; please stop!!

As to doing DRW-tyle stuff in native ASP, here's a sample
page that does just that with no ODBC DSN. It should run
in any FrontPage Web that contains the
sample "fpnwind.mdb" database, provided that you correct
the path "../fpdb/fpnwind.mdb" on about line 20.

<%option explicit%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;
charset=windows-1252">
<title>Category Query</title>
</head>
<body>
<table>
<tr>
<th>ID</th>
<th>Name</th>
<th>Description</th>
</tr>
<%
Dim cnNwind
Dim sql
Dim rsCats
Set cnNwind= Server.CreateObject("ADODB.Connection")
cnNwind.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Server.MapPath("../fpdb/fpnwind.mdb") & ";"
sql = "SELECT * FROM categories"
Set rsCats = Server.CreateObject("ADODB.Recordset")
rsCats.Open sql, cnNwind
Do While Not rsCats.eof
%>
<tr>
<td><%=rsCats("CategoryID")%></td>
<td><%=rsCats("CategoryName")%></td>
<td><%=rsCats("Description")%></td>
</tr>
<%
rsCats.MoveNext
Loop
rsCats.Close
Set rsCats = Nothing
cnNwind.close
Set cnNwind = Nothing
%>
</table>
</body>
</html>

Note the loop Do While Not rsCats.eof ... Loop. This runs
once for each record that the SELECT statement returns,
and emits a table row each time.

To display a hyperlink in one column, use code like this:

<td><a href="whatever.asp?cat=<%=rsCats("CategoryID")%>">
<%=rsCats("CategoryName")%></a></td>

You lost me with the dummy query and importing tables and
whatever else that is, but if its a header/detail
arrangement, should be able to automat that with a query
that does a join.

Jim Buyens
Microsoft FrontPage MVP
http://www.interlacken.com
Author of:
*----------------------------------------------------
|\---------------------------------------------------
|| Microsoft Office FrontPage 2003 Inside Out
||---------------------------------------------------
|| Web Database Development Step by Step .NET Edition
|| Microsoft FrontPage Version 2002 Inside Out
|| Faster Smarter Beginning Programming
|| (All from Microsoft Press)
|/---------------------------------------------------
*----------------------------------------------------
 
Very well. Stop, Larry; please stop!!
Click to expand...
--snip--
++++++++++++++++++++++++++++++++++++++++++++
Whoa. I am not worthy......

Works great Jim, I just need to work on a lil table formatting and it
will do the trick nicely. In your FP2003 Inside Out, you mention the
pain of formatting tables in the DRW over and over again if you're not
sure you're really done with the thing. Hopefully using ASP like this
will set me free from that, finally. Now I just have to study-up on
creating these nice looking styles for tables and I can get on with my
life (for now, anyway)

Thanks again,

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
 
My web site (http://www.takempis.com) has some articles and tutorials that
can help you.
Click to expand...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
thanks; been there several times and it's a great help. half way thru
your in-depth ADO article....excellent.

One of the reasons I was getting the error after publishing the site
locally was that my home server runs Access XP, while my client's runs
Access 2K. several tables contain fields named "container", which is
not reserved in 2K, but IS in XP. So I had to use [ ]'s to stop
those queries from misbehaving. Gonna have to carve these words on my
desk so I don't miss that again.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
 
.... or just get into the habit of using those square brackets! ;-)

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

bender said:
My web site (http://www.takempis.com) has some articles and tutorials that
can help you.
Click to expand...
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
thanks; been there several times and it's a great help. half way thru
your in-depth ADO article....excellent.

One of the reasons I was getting the error after publishing the site
locally was that my home server runs Access XP, while my client's runs
Access 2K. several tables contain fields named "container", which is
not reserved in 2K, but IS in XP. So I had to use [ ]'s to stop
those queries from misbehaving. Gonna have to carve these words on my
desk so I don't miss that again.

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
Click to expand...
 
Barry,

Ok, this was a while back, but it seems like I once had a
need to on a website store URL's in an access database,
and so people could do search's for various files, and
get a list of URL's to that file.

But, I couldn't get access to store them as links the
browser could deal with.

So what I did was store the URL as text in the database
and in the ASP code change it a bit so that when it
started to output the text from the database I hade it
instead output href="<text from database query>".

Actualy, I might have used the database wizard in Front
Page rather than pure ASP, and just modified it a bit
afterwards.

Seems I had to write the <a /a> tags too.

I don't know if that is the best way, but it seemed to
work for me.

Just food for thought, sorry I wasn't more specific. I
might have the old page on a CD someplace, I'll dig
around for it.

Best of luck,

David B.
 
I don't know if that is the best way, but it seemed to
work for me.
Just food for thought, sorry I wasn't more specific. I
might have the old page on a CD someplace, I'll dig
around for it.
Best of luck,
David B.
Click to expand...
++++++++++++++++++++++++++++++++++++++++
Thanks for the offer, David. I actually have slain this beast :)
I got some code from the book "beginning ASP 3.0" that created a
procedure that puts anything you feed it into tables.
then, within the proc, i added some logic that said, basically, "if
the field comin' at ya is the field i want to be a link, format it
with the href stuff, and if not, just format regular". I used a
variable that includes the target page for the link to send along with
the data, so i can continue to use this procedure over and over again
without modification.

Using this, i can make tables of d/b results with links, not have to
use the DRW. I am a happy boy. :)

Larry
- - - - - - - - - - - - - - - - - -
"Forget it, Jake. It's Chinatown."
 
Back
Top