🚀 go-pugleaf

Prøver lige det der 'google-noget' og poster noget kode:

* At bygge en Access database fra scratch:
<%@ LANGUAGE = VBScript %><%  Option Explicit     %><!--#include virtual="/include_files/wopr.first.level.inc.asp"--><!--#include virtual="/include_files/wopr.post.logger.inc.asp"--><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="da">

<head>
<title>W.O.P.R Building new Access database</title>
<link rel="shortcut icon" href="/favicon.ico">
   <style type="text/css">
     p,div,code {white-space: pre; padding-left: 1.5em;}
     code {border: 2px inset Gold ; display: block ; padding-left:0.3em;}
     body {font-family: Tahoma,Arial;font-size: 9pt;font-weight: bold;}
     img {border:none;}
   </style>
</head>

<body>
<h2><a href="/" title="W.O.P.R home"><img src="/images/wopr2.gif" alt="W.O.P.R" title="W.O.P.R - War Operation Plan Response" /></a>&nbsp;&nbsp;
W.O.P.R Building new Access database &nbsp;&nbsp; <img src="/images/help.gif" alt="Help" title="Help" />
</h2>
<hr/><!--#include virtual="/include_files/pathinfo.asp"--><hr/>

<%
   if Request.Servervariables("REQUEST_METHOD") = "GET" then
%><p>
Building the database.
You need to know your directory layout from your ISP.
On this server, there is a separate database directory outside webscope called <i>database</i>.

So the path in ASP code is:
<code>Server.MapPath("/") + "\..\database\"
</code>
Where Server.MapPath("/") is the webroot, and "\..\" takes you one level above the root.
Only the internal ASP user has access to this directory, thereby preventing anybody from downloading
the database directly.
The field name in the form is database, so the code that creates the Database is:
<code>Dim databasename
   databasename = Request.Form("databasename")
   Response.Write " " + databasename
Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/") + "\..\database\" + databasename
</code>
</p>

<form action="build.new.access.asp" method="post"><p>
<input type="text" name="databasename" value="commentdb.mdb"/>
<input type="submit" value="Build the database"/>
</p>
</form>
<p>
When the database is created, you want to create an include file to be used everywhere you need access to the database.
Here we call it <i>commentdb.inc.asp</i>, and place it in the /include_files directory.
The include file will look like this:
<code>
&lt;%
   Dim oConn
   Dim oConnString

   Set oConn = Server.CreateObject("ADODB.Connection")
   oConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/") + "/../database/commentdb.mdb;Persist Security Info=False"
   oConn.Open oConnString
%&gt;
</code>

There is a short intro <a href="http://www.4guysfromrolla.com/webtech/013101-1.shtml">here</a>
</p>
<%
else
%><!--#include virtual="/include_files/wopr.ipcheck.inc.asp"-->
<p> Trying to build the database
<%
Dim databasename
   databasename = Request.Form("databasename")
   Response.Write " " + databasename
Dim objADOXDatabase
Set objADOXDatabase = Server.CreateObject("ADOX.Catalog")
objADOXDatabase.Create "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/") + "\..\database\" + databasename

%></p><%
end if
%>
</body>
</html>

Bemærk at DB directoriet ligger uden for webscope, og kaldes 'database'.

General SQL query tool.

Består af flere dele, da det er lidt genbrugeligt, men:
Fil 1, som er præsentationen:

<!--#include virtual="/include_files/wopr.first.level.inc.asp"--><!--#include virtual="/include_files/wopr.post.logger.inc.asp"--><!--#include virtual="/include_files/wopr.ipcheck.inc.asp"--><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="da">

<head>
<title>W.O.P.R General SQL Query tool</title>
<link rel="shortcut icon" href="/favicon.ico">
   <style type="text/css">
     p,div,code {white-space: pre; padding-left: 1.5em;}
     code {border: 2px inset Gold ; display: block ; padding-left:0.3em;}
     body {font-family: Tahoma,Arial;font-size: 9pt;font-weight: bold;}
     img {border:none;}
   </style>
</head>

<body>
<h2><a href="/" title="W.O.P.R home"><img src="/images/wopr2.gif" alt="W.O.P.R" title="W.O.P.R - War Operation Plan Response" /></a>&nbsp;&nbsp;
W.O.P.R General SQL Query tool &nbsp;&nbsp; <img src="/images/help.gif" alt="Help" title="Help" />
</h2>
<hr/><!--#include virtual="/include_files/pathinfo.asp"--><hr/>
<form method="GET" action="general.QueryResult.asp">
<p>
Database <input type="text" name="databasename" value="commentdb.mdb"/>
Enter SQL<textarea cols="80" rows="23" name="SQL" id="SQL" ></textarea>
<input type="submit" value="Submit SQL to database" />
</p>
</form>

</body>

</html>
..............
som kalder "general.QueryResult.asp" :

<%@ LANGUAGE = VBScript %><%  Option Explicit     %><!--#include virtual="/include_files/wopr.first.level.inc.asp"--><!--#include virtual="/include_files/wopr.post.logger.inc.asp"--><%
'Note theese are for debug purposes, copy paste where it should be used don't remove
'Response.Write sql
'Response.End
%><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="da">
    <head>
        <title>W.O.P.R [<% =Request.QueryString("title") %>] Query results</title>
        <link href="/css/query.result.css" rel="stylesheet" type="text/css" />
    </head>

<body>
<h2><a href="/" title="W.O.P.R home"><img src="/images/wopr2.gif" alt="W.O.P.R" title="W.O.P.R - War Operation Plan Response" /></a>&nbsp;&nbsp;
W.O.P.R [<% =Request.QueryString("title") %>] Query results &nbsp;&nbsp; <img src="/images/help.gif" alt="Help" title="Help" />
</h2>
<hr/><!--#include virtual="/include_files/pathinfo.asp"--><hr/>
<!--#include virtual="/include_files/general.QueryResult.inc.asp"-->
</body>
</html>
.............
som egentlig kun sætter en præsentation op, men kalder (include) general.QueryResult.inc.asp :
<%
Dim SQL
Dim IsSelect
Dim databasename
Dim oConn
   databasename = Request.QueryString("databasename")
   Set oConn = Server.CreateObject("ADODB.Connection")
   oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("/") + "/../database/" + databasename + ";Persist Security Info=False"

   SQL = Request.QueryString("SQL")
   if lcase(left(ltrim(SQL),6)) = "select" then IsSelect = true else IsSelect = false
   if Request.QueryString("showsql") <> "no" then
      %>This is result from <b>"<% = SQL %>":</b><hr/><%
   end if

Dim oRs
Dim filePath
Dim Index
Dim FieldName
Dim Counter

   Counter = 0
   Set oRs = oConn.Execute(SQL)
   if not IsSelect then
      Response.End
   end if
   %><table id="queryresult"><%

'Display the field attributes of the Table
   %><tr><%
   for each FieldName in oRs.Fields
      %><th><% = FieldName.Name %></th><%
   next

   %></tr><%
   Response.Flush
   Do while (Not oRs.eof)
      Counter = Counter + 1
      %><tr><%
      For Index=0 to (oRs.fields.count-1)
         %><td><%
         if not isnull(oRs(Index)) then
         if Request.QueryString("safe") = "off" then
            Response.Write oRs(Index)
         else
            Response.Write Replace(Replace(Replace(oRs(Index),"&","&amp;") _
                               ,"<","&lt;") _
                               ,">","&gt;")
         end if
      end if
      %></td><%
      Next
      %></tr><%
      if right(cStr(Counter),2) = "00" then Response.Flush
      oRs.MoveNext
   Loop
   %><tr><th>Count = <% = Counter
   %></th></tr></table><%
   oRs.close
   oConn.close
   Set oRs = nothing
   Set oConn = nothing
%>
...........

Det er ikke fuldstændige færdige 'projekter', så lave dem selv så de passer til behovet.


Men for at vende tilbage til de sk*de paranteser, så har oprettet en database med tabeller i Access, og denne her _virker_:

SELECT 
KF.Fornavn AS KFfornavn, KF.Efternavn AS KFefternavn,
KN.Fornavn AS KNfornavn, KN.Efternavn AS KNefternavn,
KS.Fornavn AS KSfornavn, KS.Efternavn AS KSefternavn,
S.Saeson 
FROM 
(( Stamdata S
INNER JOIN Kunder KF ON KF.Kundeid=S.Formand )
INNER JOIN Kunder KN ON KN.Kundeid=S.Naestformand)
INNER JOIN Kunder KS ON KS.Kundeid=S.Kasserer
ORDER BY S.Saeson Desc

Her har jeg taget kasserer med for fuldstændighedens skyld.

Som Leif var inde på, så returneres kun records, der eksisterer, og hvis man vil have 'alle med', skal man bruge LEFT OUTER JOIN i stedet for INNER JOIN.

Til John:
Når man benytter den slags SQL så skal du bruge rs("KFfornavn"),rs("KNfornavn").. osv, da disse 'feltnavne' bliver returneret som skrevet i SQL'et.

Du kan kalde dem hvad du vil, men det navn der står efter 'AS' er det samme som ASP (og andre sprog) 'ser'.

Håber du kommer videre.