Experts Round Table Network

keneso

Site Builder

Offline

Offline

Posts: 32

master/detail

« on: March 14, 2007, 08:02:15 AM »

Hi,

I need a great deal of help and patience, as some of you know I am not a coder.
Please let me first tell you what I am trying to achieve:

I need to get data to and from an access db; adding user info to the db, having the info shown on a "log" page.

With googeling, and various help I got to a basic setup that works fine.
Now I need to bring it a step further, you can view what I have so far here:
http://www.internetetc.it/ert/keneso/db_clienti.html

I need default.asp to keep updating the log table of the db, and update the master table of it, only when a new user has to be added.
Right now, it doesn't add at all to master
After achieving this, I would need to be able to call a name from the dropdown list, and populate the form's 5 related fields, leaving me only the other 4 fields to be filled manually, and once done to add all the info to the log table.
Of course if no record found in the dropdown, I will fill all 9 fields and it'll update both the log, and master

Thank you


	Logged

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #1 on: March 14, 2007, 08:58:42 AM »

Here's part two. http://www.rodsdot.com/ee/ASP2ClientSideFormSelect.asp


	Logged

Rod

keneso

Site Builder

Offline

Offline

Posts: 32

Re: master/detail

« Reply #2 on: March 14, 2007, 12:28:59 PM »

Thank you.

Give me few days to play with it, I might need more help, and explanations.


	Logged

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #3 on: March 14, 2007, 12:43:23 PM »

Yes, I'm looking at your code and will make some suggestions soon.

First suggestion is to get the ASP/VBScript out of the HTML...which you see in my example for the drop down list and partial form completion.

I prefer a data abstraction library (perform the calls to the db with error checking and SQL injection prevention).

A business logic layer, specific to the page...

e.g. addCustomer(field1, field2, ....) which calls the db through the db abstraction library,

and of course a input filtering library so you don;t see the horrible security problems in the example code,

Code

Language: asp (GeSHi-highlighted)
rs(1) = Request.Form("UID")
rs(2) = Request.Form("Password")
rs(3) = Request.Form("DocType")
rs(4) = Request.Form("DocNo")

which is taking form input unfiltered and putting it into a database. This justs screams SQL injection and XSS vulnerability (when the record is retrieved and displayed again).

Not to mention opening a jet (Access) database and keeping the recordset open, updating its fields and then updating and closing the database locks the whole database and will lead to db corruption, deadlocks and prevent increase the likelihood of collisions with other users. (Access via the jet engine in ADO/ASP is not multiuser).

So...it will take me a bit to located the correct examples for you, and/or make them very specific to your problem.

Rod


	Logged

Rod

keneso

Site Builder

Offline

Offline

Posts: 32

Re: master/detail

« Reply #4 on: March 14, 2007, 01:06:47 PM »

Hmmm ... this is going to be harder than I thought ... no surprise.

Please be patient as I am 0 on this, so even some terms you are using is new to me.
I don't know if it can be of help, don't know if it can make a difference, but this is going to be something I will use myself for now, so no multi-user at this stage.

It is going to be run on local, and every single entry/update will be made by myself; though the machine is connected to the net 24/24. The security concern is to protect the data from others to retrive.

Going home now, bye.


	Logged

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #5 on: March 14, 2007, 01:33:14 PM »

Quote from: keneso on March 14, 2007, 01:06:47 PM

Hmmm ... this is going to be harder than I thought ... no surprise.

Probably not.

I'm talking about including some pre-written libraries to handle the heavy lifting, so you can concentrate only on performing the task specific to your page.

Quote from: keneso on March 14, 2007, 01:06:47 PM

Please be patient as I am 0 on this, so even some terms you are using is new to me. I don't know if it can be of help, don't know if it can make a difference, but this is going to be something I will use myself for now, so no multi-user at this stage.

I'm not going to try to blow you away with technical details. I will give you some solid code so you won't have to worry about the details so much. Its always good if you get a little understanding about the underlying code, but is not essential.

For example, your form posts a first name field.

You can get the raw, unfiltered, and possibly dangerous field, using

Code:

Request.Form("first")

or you can include a filter library  at the begining of your page, then do

Code:

getPostedField("first","name")

where name is a predefined format for allowing safe name characters.

Not hard.

Quote from: keneso on March 14, 2007, 01:06:47 PM

It is going to be run on local, and every single entry/update will be made by myself; though the machine is connected to the net 24/24. The security concern is to protect the data from others to retrive.

The main issue is the location of the database as it has to be read - write to the web server. If you put the database inside the web root, the database can be downloaded by anyone who can guess the name and location (which is why we have a database access library, which traps detailed error messages from revealing this to any browser, yet also allows you to get the messages you need for debugging. Again, that's already written and has been in production for many years and is stable.

Instead of doing

Code:

Set conn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")
conn.Open ConnStr
rs.Open "SELECT * FROM log", conn

Any line of which could error out and reveal the details of your database name, path and connection string info, you will do something like:

Code:

if openCommand(myConnStr,"This page name location 1") then
getRS(db_rs,"SELECT * FROM log", conn,"This page name location 2")
'... do whatever with db_rs
closeRS
closeCommand
end if

If there is an error, it will say something generic like DB Access Error at This page name location 1, which reveals nothing to a user. You change one variable on your page, say dbDebug=true, and you can see the detailed error message.

Also, you don't need to worry about the underlying details of error trapping, or even how to access the db using ADO. Just open the command, execute a query or get a recordset and close the resources when finished. Separates the details from the task logic. You concentrate on what you want to do with the records.

Won't be harder...will be easier to maintain. Hard is using the code examples you have and trying to separate the task at hand from the db access and the HTML. It's all smooshed together in a big mess.


	Logged

Rod

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #6 on: March 14, 2007, 02:48:02 PM »

Your example code has the HTML, ASP and Database access code all mixed together.

Here is a similar example that builds a table of US States and their postal code abbreviations.

http://www.rodsdot.com/ee/database1.asp

Code

Language: geshi (GeSHi-highlighted)
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>rodsdot.com :: generic database page - states table</title>
<meta name="author" content="Roderick Divilbiss">
<meta name="copyright" content="&copy; 2005 Roderick Divilbiss">
</head>
<body>
<table id="statesTable" border="1" cellpadding="2" style="border-collapse: collapse" bordercolor="#000000">
<%
    Dim rs, conn, command, connection, tableHTML
    command = "SELECT Code, Description FROM states ORDER BY Description;"
    Set conn = Server.CreateObject("ADODB.Connection")
    connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\inetpub\mysite\database\examples.mdb'"
    conn.open connection
    set rs = Server.CreateObject("ADODB.RecordSet")
    rs.open command, conn, 3, 1, 1

    if NOT (rs.bof AND rs.eof) then
        while NOT rs.eof
            response.write "<tr><td>" & rs("Code") & "</td><td>" & rs("Description") & "</td></tr>"
            rs.movenext
        wend
        rs.close
        set rs = nothing 
    end if

    conn.close
    set conn=nothing
%>
</table>
</body>
</html>

...


	Logged

Rod

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #7 on: March 14, 2007, 03:19:01 PM »

So... the first thing you want to do is separate the ASP and Database access from your HTML.

like this: http://www.rodsdot.com/ee/database2.asp

Code:

<%
Option Explicit
Dim rs, conn, command, connection, tableHTML
command = "SELECT Code, Description FROM states ORDER BY Description;"
Set conn = Server.CreateObject("ADODB.Connection")
connection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\path\to\database\examples.mdb'"
conn.open connection
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.open command, conn, 3, 1, 1

if NOT (rs.bof AND rs.eof) then
tableHTML = "<table id=""statesTable"">"
while NOT rs.eof
tableHTML = tableHTML & "<tr><td>" & rs("Code") & "</td><td>" & rs("Description") & "</td></tr>"
rs.movenext
wend
rs.close
Set rs = nothing
tableHTML = tableHTML & "</table>"
end if

conn.close
Set conn=nothing
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>rodsdot.com :: generic database page - states table</title>
</head>
<body>
<%=tableHTML%>
</table>
</body>
</html>

Where we build the table as a string inside our ASP, and just write the string inside the HTML structure. Much cleaner and easier to maintain.

But we have no error checking, so if anything goes wrong, we will write sensitive information to the browser window. Also, if by chance someone is able to view our code, they will get your database connection string, which should never be in an ASP page, nor in an INC or ASP included page.

It belongs in the global.asa page in the web root, and IIS enforces special protections on that file...which you do not need to include in your pages. So inside the global.asa you add something like:

Code:

<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
Application("myConnection") = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\path\to\database\examples.mdb'"
</SCRIPT><head><title>Web Site Settings for Active Server Pages</title><html>
</head>

Also, doing all the ADO database stuff in the code is a pain, and you don't have any error checking, etc. as I mentioned before. So I will use the database access layer library.

The displaying of the states table is completely independent of how we access the database, and even what database we choose to use. That is our local business logic and it ought to be in a function or subroutine to keep it out of the way of any other ASP, ergo example 3.

Also we need to add some style to the table.

http://www.rodsdot.com/ee/database3.asp

Code:

<%
Option Explicit
Session.CodePage=65001
Response.Charset="UTF-8"

' no browser caching of this page !! to be used on all pages
Response.Expires=-1
Response.ExpiresAbsolute = Now() - 1

' do not allow proxy servers to cache this page !! to be used on all pages
Response.CacheControl="private"
Response.CacheControl="no-cache"
Response.CacheControl="no-store"
%>

<%
function writeStateTable()
' db_cmdText and db_rs are predefined

' our SQL command
db_cmdText = "SELECT Code, Description FROM states ORDER BY Description;"

' open a command object on our database
openCommand Application("examples"), "database3 1"

' get our recordset using the open command object
getRS db_rs, db_cmdText, "database3 2"

Dim tableHTML

if NOT (db_rs.bof AND db_rs.eof) then
tableHTML = "<table id=""statesTable"">"
while NOT db_rs.eof
tableHTML = tableHTML & "<tr><td>" & db_rs("Code") & "</td><td>" & db_rs("Description") & "</td></tr>"
db_rs.movenext
wend
tableHTML = tableHTML & "</table>"
end if

' clean up
closeRS
closeCommand
writeStateTable = tableHTML
end function
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html lang="en">
<head>
<title>rodsdot.com :: generic database page - states table</title>
<meta name="author" content="Roderick Divilbiss">
<meta name="copyright" content="© 2005 Roderick Divilbiss">
<style>

</style>
</head>
<body>
<%=writeStateTable%>
</table>
</body>
</html>

... I'll explain all this next post.


	Logged

Rod

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #8 on: March 14, 2007, 03:58:24 PM »

At the beginning of the ASP page I added a reference to the type library for ADODB (the database access objects library's pre-defined constants.) That will be at the beginning of any database access page.

Never begin an ASP page without

Code:

Option.Explicit

That forces you to declare variables and prevents scope problems.

While not important in my states table example...when we start talking about forms, it is important to explicitly define the codepage and charset which VBScript will use. This allows us to avoid certain encoding attacks on form pages.

Code:

Session.CodePage=65001
Response.Charset="UTF-8"

I use UTF-8 but you could use Western European, "iso-8859-1" which is CodePage=28591.
http://msdn.microsoft.com/library/default.asp?url=/workshop/author/dhtml/reference/charsets/charset4.asp

Again, not important for non-forms pages, but in my template already are the commands to add headerrs to prevent caching of the pages. We want that on forms pages.

Finally, I include the database access library...

Code:

which is here:

Code:

<%
'****************************************************************
'* paramSQL.asp © 2005, 2006 Roderick Divilbiss *
'* http://www.rodsdot.com *
'****************************************************************
'* TERMS OF USE *
'*--------------------------------------------------------------*
'* Except where otherwise noted, this source code and markup is *
'* licensed under a Creative Commons License Creative Commons *
'* License. http://creativecommons.org/licenses/by-nc/2.0/ *
'* *
'* Complete Terms of Use my be found at: *
'* http://www.rodsdot.com/termsofuse.asp *
'* *
'* No part of this application code may be used for commercial *
'* purposes without prior written permission from the author, *
'* Roderick W. Divilbiss of Overland Park, Kansas, United States*
'* of America. *
'* *
'* Non-commercial use of this application code requires this *
'* notice be kept intact. *
'* *
'* © 2000-2005, Roderick W. Divilbiss, All Rights Reserved *
'* Used by permission. *
'* Original source code may be found at www.rodsdot.com. *
'* *
'* The following notice and hyperlink must be included on one *
'* page on the web site accessible to the public and linked *
'* directly to the website's home page. *
'* *
'* This site contains code used by permission of Rod Divilbiss, *
'* http://www.rodsdot.com. *
'* **************************************************************

'****************************************************************
'* GLOBAL VARIABLES *
'****************************************************************
Dim db_cmd, db_conn, db_rs, db_cmdText, numAffected

Sub openCommand(pConnStr,pSource)
if pConnStr="" then
Response.Write "There was an openCommand error: "& pSource &" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
on error resume next

Set db_cmd = Server.CreateObject("ADODB.Command")
if err then
Response.Write "There was an openCommand error: "& pSource &" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if

Set db_conn = Server.CreateObject("ADODB.Connection")
if err then
Response.Write "There was an openCommand error: "& pSource &" 3.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
db_conn.open pConnStr

if err then
Response.Write "There was an openCommand error: "& pSource &" 4.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if

Set db_cmd.ActiveConnection = db_conn
if err then
Response.Write "There was an openCommand error: "& pSource &" 5.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
End Sub

Sub closeCommand()
on error resume next
if IsObject(db_cmd) then
Set db_cmd = nothing
end if
if IsObject(db_conn) then
if db_conn.open then
db_conn.close
end if
Set db_conn = nothing
end if
End Sub

Sub addParam(pName,pType,pDir,pSize,pValue,pSource)
Dim param
on error resume next
Set param = db_cmd.CreateParameter(pname,ptype,pdir,psize,pvalue)
if err then
Response.Write "There was an error creating a database parameter: "& pSource &" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if

db_cmd.Parameters.Append param
if err then
Response.Write "There was an error appending the database parameter: "& pSource &" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if

Set param = nothing
End Sub

Sub getRS(db_rs,db_cmdText,pSource)
on error resume next
Set db_rs = Server.CreateObject("ADODB.Recordset")
if err then
Response.Write "There was an error: "&pSource&" 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
db_cmd.CommandText = db_cmdText
if err then
Response.Write "There was an error: "&pSource&" 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
' db_rs.Open db_cmd, adOpenStatic, adLockOptimistic
db_rs.Open db_cmd
if err then
Response.Write "There was an error "&pSource&" 3.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
end if
end if
end if
End Sub

Sub execCmd(db_cmdText)
on error resume next
db_cmd.CommandText = db_cmdText
if err then
Response.Write "There was an error setting command text. execCmd 1.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & "<br>" & vbLF
Response.End
else
db_cmd.Execute numAffected,, adExecuteNoRecords
if err then
Response.Write "There was an error executing command. execCmd 2.<br>" & vbLF
'* Developer Only * Response.Write err.number & " " & err.description & " " & err.source & " Command: " &db_cmdText& "<br>" & vbLF
Response.End
end if
end if
End Sub

Sub closeRS
on error resume next
if IsObject(db_rs) then
db_rs.close
end if
End Sub
%>

Two things to notice are:

the error checking, specifically the '* developer only comments, which you can un-comment if you are having trouble and need to see the actual error messages, and
The use of parameterized SQL statements, which help us prevent SQL injection attacks.

Also you have several global variables defined,

db_cmd - the ADODB command object used to connect to the database
db_conn - the ADODB connection to the database, which you probably will never access in your code
db_rs - the ADODB resordset which hold the results of your query, you will access in your code
db_cmdText - The SQL command you want to execute
numAffected - the number of rows affected in an update or delete transaction


	Logged

Rod

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #9 on: March 14, 2007, 04:23:50 PM »

Within the basic framework I have discussed, this is another example of page specific business logic...which you will see is nicely separated from the nasty details of database access, as well as the results of obtaining the posted values from the form page. This shows nicely not only how to use a parameterized query, which is so much safer than building SQL on the fly, but also shows how we can retrieve the identity of the row we just inserted by reusing our command object to execute a "SELECT @@IDENTITY;" command immediately following the INSERT. Almost any conceivable SQL injection is going to fail because we only specify the parameters to the SQL query, they must be of a specific type, we are executing a command rather than a recordset returning function, AND there is no way to inject closures, logical clauses or UNIONs into our command object.

All the parameters, saveComments 1...saveComments 3 are simply to assist the developer, should an error occur. e.g.

Quote

DB Access error page whatever.asp at saveComments 2f.

does not reveal the details of the error to a user, but is enough information for you, the developer/administrator to quickly find out what the problem was.

The parameters, such as adVarChar and adParamInput are the pre-defined ADODB constants I was refering to when I said you alway include a reference to the ADODB type library at the begining of your pages.

Code

Language: asp (GeSHi-highlighted)
function saveComments(pName,pEmail,pPage,pPageSearch,pIP,pComments)
	Dim cmdText
	
	'* Add the new row
	openCommand Application("comments"),"saveComments 1"
	cmdText = "INSERT INTO comments (status, remoteAddress, sessionId, name, email, page, pageSearch, datePosted, comments) VALUES (@status, @remoteAddress, @sessionid, @name, @email, @page, @PageSearch, @datePosted, @comments)"
 
	addParam "@status",adInteger,adParamInput,CLng(4),0,"saveComments 2a"
	addParam "@remoteaddress",adVarChar,adParamInput,CLng(50),pIp,"saveComments 2b"
	addParam "@sessionid",adVarChar,adParamInput,CLng(50),Session.SessionID,"saveComments 2c"
	addParam "@name",adVarChar,adParamInput,CLng(50),pName,"saveComments 2d"
	addParam "@email",adVarChar,adParamInput,CLng(254),pEmail,"saveComments 2e"
	addParam "@page",adVarChar,adParamInput,CLng(254),pPage,"saveComments 2f"
	addParam "@pageSearch",adVarChar,adParamInput,CLng(254),pPageSearch,"saveComments 2f"
	addParam "@datePosted",adDate,adParamInput,CLng(now()),now(),"saveComments 2g"
	addParam "@comments",adLongVarWChar,adParamInput,CLng(Len(pComments)),pComments,"saveComments 2h"
	execCmd cmdText
	
	cmdText = "SELECT @@IDENTITY;"
	getRS db_rs, cmdText, "saveComments 3"
	saveComments = db_rs(0)
	
	'* clean up
	closeRS
	closeCommand
end function

It is very simple, to take this perfectly safe routine and copy and paste it to perform a different insert function, simply by changing the parameters to suit.

So you have most of what you need to add a master detail record.

pseudo code

Code:

if this is a form post
get posted values - filtered for safety
newid = saveMaster(first, last, email, whatever)
detailID = saveDetail(masterID, details, details2)
end if
...
HTML for the form here.


	Logged

Rod

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #10 on: March 14, 2007, 08:39:37 PM »

And... separating everything nicely, gives us a new default.asp. (I'm getting an error on the addDetailRecord, but I'll figure it out later, (I'm sleepy, sorry). But this shows you how much easier your code is to maintain if you keep the majority of your code out of your markup. I think it is logical and easy to follow...even if you come back to it months later.

http://www.rodsdot.com/keneso/default.asp

Code

Language: geshi (GeSHi-highlighted)
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Option Explicit
Session.CodePage=28591
Response.Charset="iso-8859-1"

' no browser caching of this page !! to be used on all pages
Response.Expires=-1
Response.ExpiresAbsolute = Now() - 1

' do not allow proxy servers to cache this page !! to be used on all pages
Response.CacheControl="private"
Response.CacheControl="no-cache"
Response.CacheControl="no-store"
%>
<!--#include virtual="/include/generalPurpose.asp"-->
<!--#include virtual="/include/thisPageClass.asp"-->
<!--#include virtual="/include/paramSQL.asp"-->
<%
'*******************************************************************************
'                                  PAGE ROUTINES
'*******************************************************************************

function toNumber(x)
     Dim RegX
     Set RegX = NEW RegExp
     RegX.Pattern = "\D"
     RegX.Global = True
     x = RegX.Replace(x,"")
     IF x="" THEN toNumber = 0 ELSE toNumber=CInt(x)
end function

function toDate(strDay,strMonth,strYear)
     Dim d, m, y
     d = toNumber(strDay)
     m = toNumber(strMonth)
     y = toNumber(strYear)
     toDate = DateSerial(y,m,d)
end function

function writeDateSelect(pName,pMDY)
    Dim mdy, idx, lowIdx, maxIdx, out
    lowIdx=1
	out = "<select name=""" & pName & """ size=""1"">"
	Select Case LCase(pMDY)
		Case "d"
			mdy = day(now())
			maxIdx=31
		Case "m"
			mdy = month(now())
			maxIdx=12
		Case "y"
			mdy = year(now())
			lowIdx = mdy-1
			maxIdx = mdy+7
	End Select

	for idx = lowIdx to maxIdx
		if idx=mdy then
			out=out& "<option selected value=""" & idx & """>"& idx &"</option>"
		else
			out=out& "<option value=""" & idx & """>"& idx &"</option>"
		end if
	next	
	writeDateSelect = out& "</select>"	
end function

function writeCustomersArray(pName)
	db_cmdText = "SELECT * from customer ORDER BY customerId;"

	' open a command object on our database
	openCommand Application("keneso"), "writeCust 1"

	' get our recordset using the open command object
	getRS db_rs, db_cmdText, "writeCust 2"

	Dim out, idx, item

	if NOT (db_rs.bof AND db_rs.eof) then
		out = "var "& pName &" = new Array();" & vbLF
		idx = 0
		while NOT db_rs.eof
            out = out & " "& pName &"["& idx &"]=['"
            for each item in db_rs.fields
                out = out & item & "','"
            next

            ' backoff the trailing ,' and terminate the line with ]);
            out = Left(out, Len(out)-2) & "];" & vbLF

            ' increment our array index
            idx = idx + 1

			db_rs.movenext
		wend
		writeCustomersArray = out & vbLF	
	end if

	' clean up
	closeRS
	closeCommand
end function


function addDetailRecord(pUID, pPassword, pDocType, pDocNo, pDate, pIn, pOut, pPosition, pAddress)
	Dim cmdText
	
	'* Add the new row
	openCommand Application("keneso"),"addDetail 1"
	cmdText = "INSERT INTO log (customerid, key, docType, docNumber, position, address, date, in, out ) VALUES (@customerid, @key, @docType, @docNumber, @position, @address, @date, @in, @out)"

	addParam "@customerid",adVarChar,adParamInput,CLng(10),pUID,"addDetail 2a"
	addParam "@key",adVarChar,adParamInput,CLng(10),pPassword,"addDetail 2b"
	addParam "@docType",adVarChar,adParamInput,CLng(10),pDocType,"addDetail 2c"
	addParam "@docNumber",adVarChar,adParamInput,CLng(30),pDocNo,"addDetail 2d"
	addParam "@position",adVarChar,adParamInput,CLng(50),pPosition,"addDetail 2e"
	addParam "@address",adVarChar,adParamInput,CLng(100),pAddress,"addDetail 2f"
	addParam "@date",adDate,adParamInput,CLng(16),pDate,"addDetail 2g"
	addParam "@in",adVarChar,adParamInput,CLng(10),pIn,"addDetail 2h"
	addParam "@out",adVarChar,adParamInput,CLng(10),pOut,"addDetail 2i"
	execCmd cmdText
	
	'* clean up
	closeRS
	closeCommand
	addDetailRecord = "Record Added"
end function

'*******************************************************************************
'                                  FORM LOGIC
'*******************************************************************************
if thisPage.isPost then
	' Day, Month, Year, Date, In are all reserved words in VBScript!
	
	' Declare variables to hold posted fields
	Dim UID, Password, DocType, DocNo, tDate, tDay, tMonth, tYear, timein, timeout, position, address, posted
	
	' Get the posted fields via our filter function
	UID = getField("UID,rXalpha")
	Password = getField("Password,rXsafe")
	DocType = getField("DocType,rXsafe")
	DocNo = getField("DocNo,rXsafe")
	tDay = getField("InD,rXint")
	tMonth = getField("InM,rXint")
	tYear = getField("InY,rXint")
	tDate = toDate(tDay, tMonth, tYear)
	timein = getField("in,rXsafe")
	timeout = getField("out,rXsafe")
	position = getField("position,rXsafe")
	address = getField("address,rXaddress")
	
	' A message string to communicate results
	Dim Message
	Message=""
	
	' check for required fields here
	if (UID="") then
		Message = "The UID is required."
	end if
	' check more if you want
	
	
	if Message="" then
		' Message = addDetailRecord(UID, Password, DocType, DocNo, tDate, timein, timeout, position, address)
               Message = "UID, "&UID&"<br>Password, "&Password&"<br>DocType, "&DocType&"<br>DocNo, "&DocNo&"<br>tDate, "&tDate&"<br>tDay, "&tDay&"<br>tMonth, "&tMonth&"<br>tYear, "&tYear&"<br>timein, "&timein&"<br>timeout, "&timeout&"<br>position, "&position&"<br>address"&address&"<br>" & vbLF

	end if		
else
	Message = "&nbsp;"	
end if
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="content-type" content="text/html;charset=iso-8859-1">
<meta name="author" content="Farzin">
<title>::::CLIENTI::::</title>
<link href="main.css" rel="styleSheet" type="text/css">
<script type="text/javascript">
<!--
<%=writeCustomersArray("customers")%>

var currentIdx='';

function createCustomerSelect() {
    var tmpHTML = '<select id="myOptions" name="myOptions"';
    tmpHTML += ' onchange="fillFormFields(this.selectedIndex);">';
	tmpHTML = tmpHTML + ' <option>Select...</option>\n';
    for (var idx=0; idx<customers.length; idx++) {
        tmpHTML = tmpHTML + ' <option>' + customers[idx][0] + '</option>\n';
    }
    tmpHTML = tmpHTML + '</select>';
    return tmpHTML;
}

function fillFormFields(idx) {
	if ((idx!=0)&&(idx!=currentIdx)) {
	    if (currentIdx!='') {
	        var cnfrm = confirm('Do you want to replace the form information\nfor '+document.getElementById('UID').value+' with\nthe information for '+customers[idx-1][0]+'?');
	    }
	    if ((currentIdx=='')||(cnfrm)) {
	        document.getElementById('UID').value=customers[idx-1][0];
	        document.getElementById('Password').value=customers[idx-1][1];
	        document.getElementById('DocType').value=customers[idx-1][2];
	        document.getElementById('DocNo').value=customers[idx-1][3];
	        document.getElementById('address').value=customers[idx-1][4];
	        //document.getElementById('postalCode').value=customers[idx][5];
	        //document.getElementById('phoneNumber').value=customers[idx][6];
	        currentIdx=idx;
	    }
	}  
}
//-->
</script>
</head>
<body>
<p><%=Message%></p>
<center>
<div class="main">
  <table border="1" style="border-collapse:collapse; background-color:#EEE" cellpadding="3" width="700" height="32">
    <tr>
      <td><div class="titolo">Aggiungi nuovo utente - Add Customer</div></td>
      <td style="text-align:right"><span class="link"><a href="datiutenti.asp">dati utenti - customer data</a></span></td>
    </tr>
    <tr>
      <td><script language="javascript" type="text/javascript">document.write(createCustomerSelect());</script></td>
      <td style="text-align:right">&nbsp;</td>
    </tr>
  </table>
  <br>
  <hr>
  <br>
  <form action="default.asp" method="post">
    <table border="1" style="border-collapse:collapse; background-color:#EEE" cellpadding="3" width="700">
      <tr>
        <td><span class="testo">Cognome-UID </span></td>
        <td><input id="UID" name="UID" type="text" size="30"></td>
        <td><span class="testo">Nome-Pass </span></td>
        <td><input id="Password" name="Password" type="text" size="30" ></td>
      </tr>
      <tr>
        <td><span class="testo">Tipo documento </span></td>
        <td><input id="DocType" name="DocType" type="text" size="10"></td>
        <td><span class="testo">N° documento </span></td>
        <td><input id="DocNo" name="DocNo" type="text" size="30"></td>
      </tr>
      <tr>
        <td><span class="testo">Data-Date </span></td>
        <td colspan="3"><%=writeDateSelect("InD","d")%> / <%=writeDateSelect("InM","m")%> / <%=writeDateSelect("InY","y")%></td>
      </tr>
      <tr>
        <td><span class="testo">In </span></td>
        <td colspan="3"><input id="in" name="in" type="text" size="24"></td>
      </tr>
      <tr>
        <td><span class="testo">Out </span></td>
        <td colspan="3"><input id="out" name="out" type="text" size="24"></td>
      </tr>
      <tr>
        <td><span class="testo">Postazione </span></td>
        <td colspan="3"><input id="position" name="position" type="text" size="24"> Postal Zone?</td>
      </tr>
      <tr>
        <td><span class="testo">Indirizzo </span></td>
        <td colspan="3"><input id="address" name="address" type="text" size="70"></td>
      </tr>
      <tr>
        <td style="text-align:center" colspan="4"><input type="submit" name="submit" value="Add"></td>
      </tr>
    </table>
  </form>
</div>
</center>
</body>
</html>

Regards,
Rod


« Last Edit: March 14, 2007, 08:42:46 PM by rdivilbiss »	Logged

Rod

keneso

Site Builder

Offline

Offline

Posts: 32

Re: master/detail

« Reply #11 on: March 15, 2007, 07:12:57 AM »

Wow.

Thank you very much, I mean it, thanks a lot; very true to the spirit of the site.

I 'll really need some time to digest and study all you have posted, but I had to post my appreciation.

Meanwhile will you please tell me what is the Dim, some of the code is pretty self explanatory, some will reman obscure forever (for me), but I couldn't come find what is Dim.

Thanks again.


	Logged

rdivilbiss

Moderator

Offline

Posts: 414

Re: master/detail

« Reply #12 on: March 15, 2007, 09:02:41 AM »

Dim is Diminsion...e.g. the VBScript version of declaring a variable.

All variables in VBScript are variants and have no fixed type.

It occured to me when I woke up, that you said if the user was non-existant, you wanted to add the user to the master table (which I called customer in my example).

So, on the last page...you simply add two functions to the business logic.

Code

Language: asp (GeSHi-highlighted)
fuction customerExists(pUID)
	db_cmdText = "SELECT customerId from customer WHERE (customerId="& pUID &");"
 
	' open a command object on our database
	openCommand Application("keneso"), "customerExists 1"
 
	' get our recordset using the open command object
	getRS db_rs, db_cmdText, "customerExists 2"
 
	Dim out, idx, item
 
	if NOT (db_rs.bof AND db_rs.eof) then
		customerExists=true
	else
		customerExists=false	
	end if
 
	' clean up
	closeRS
	closeCommand
end function
 
 
function addCustomer(pUID, pPassword, pDocType, pDocNo, pAddress)
	Dim cmdText
	
	'* Add the new row
	openCommand Application("keneso"),"addCustomer 1"
	cmdText = "INSERT INTO customer (customerid, key, docType, docNumber, address ) VALUES (@customerid, @key, @docType, @docNumber, @address)"
 
	addParam "@customerid",adVarChar,adParamInput,CLng(10),pUID,"addCustomer 2a"
	addParam "@key",adVarChar,adParamInput,CLng(10),pPassword,"addCustomer 2b"
	addParam "@docType",adVarChar,adParamInput,CLng(10),pDocType,"addCustomer 2c"
	addParam "@docNumber",adVarChar,adParamInput,CLng(30),pDocNo,"addCustomer 2d"
	addParam "@address",adVarChar,adParamInput,CLng(100),pAddress,"addCustomer 2e"
	execCmd cmdText
	
	'* clean up
	closeRS
	closeCommand
end function

And, slightly tweak the form handling logic...

Code

Language: asp (GeSHi-highlighted)
if Message="" then
		if customerExists(UID) then
			 Message = addDetailRecord(UID, Password, DocType, DocNo, tDate, timein, timeout, position, address)
		else
			addCustomer(UID, Password, DocType, DocNo, address)
			Message = addDetailRecord(UID, Password, DocType, DocNo, tDate, timein, timeout, position, address)
		end if	
	end if

So, because we have everything arranged logically, it is easy to add a couple of new functions and modify the form handling logic.

FWIW.

I have to find some of my pages using MS-Access and parameterized SQL to see what the problem is with the add functions. Access is very picky. I usually use SQL or mySQL, so it may take me a while to debug those routines.

Also, I strongly advise you to reconsider your database field names to make sure they are not SQL, ASP or VBScript reserved words and don't use spaces or hypens in the field names. Use camel case, like documentNumber instead of Document Number...

The primary key of the customer table should be the customerID not an Autonumber.


« Last Edit: March 15, 2007, 09:14:30 AM by rdivilbiss »	Logged

Rod

keneso

Site Builder

Offline

Offline

Posts: 32

Re: master/detail

« Reply #13 on: March 15, 2007, 10:08:29 AM »

Quote from: rdivilbiss on March 15, 2007, 09:02:41 AM

Also, I strongly advise you to reconsider your database field names to make sure they are not SQL, ASP or VBScript reserved words and don't use spaces or hypens in the field names. Use camel case, like documentNumber instead of Document Number...

Already done, indeed I was replying to your previous post with some questions, while you cross posted, so I'll make the changes, and reply in one post.

Quote

The primary key of the customer table should be the customerID not an Autonumber.

I think I understand this, but I'd better check in the db, and let you know.

Thanks for the zip files, I'll see if I understand them.


	Logged

keneso

Site Builder

Offline

Offline

Posts: 32

Re: master/detail

« Reply #14 on: March 15, 2007, 12:07:12 PM »

Quote from: rdivilbiss on March 14, 2007, 08:39:37 PM

Let me go thru the code with you, there are parts which even if I don't get, I don't think is vital as of now, and parts that I get, and parts I need to get.
I have made some changes to the db, and consequently changed the parts of the code, but I am not sure about some of it; I'll upload the new db, if you want to check it.
The reason of the change was to better identify the fields, like you already suggested (I hope I haven't missed any)

Did I get it right to put the last piece of code in the default page?
We'll see.

I'll put in red the parts that are not quite clear to me

================================================================

<%
Option Explicit
Session.CodePage=28591
Response.Charset="iso-8859-1"

' no browser caching of this page !! to be used on all pages
Response.Expires=-1
Response.ExpiresAbsolute = Now() - 1

' do not allow proxy servers to cache this page !! to be used on all pages
Response.CacheControl="private"
Response.CacheControl="no-cache"
Response.CacheControl="no-store"
%>



<%
'*******************************************************************************
' PAGE ROUTINES
'*******************************************************************************

function toNumber(x)
Dim RegX
Set RegX = NEW RegExp
RegX.Pattern = "\D"
RegX.Global = True
x = RegX.Replace(x,"")
IF x="" THEN toNumber = 0 ELSE toNumber=CInt(x)
end function

function toDate(strDay,strMonth,strYear)
Dim d, m, y
d = toNumber(strDay)
m = toNumber(strMonth)
y = toNumber(strYear)
toDate = DateSerial(y,m,d)
end function

function writeDateSelect(pName,pMDY)
Dim mdy, idx, lowIdx, maxIdx, out
lowIdx=1
   out = "<select name=""" & pName & """ size=""1"">"
   Select Case LCase(pMDY)
      Case "d"
         mdy = day(now())
         maxIdx=31
      Case "m"
         mdy = month(now())
         maxIdx=12
      Case "y"
         mdy = year(now())
         lowIdx = mdy-1
         maxIdx = mdy+7
   End Select

   for idx = lowIdx to maxIdx
      if idx=mdy then
         out=out& "<option selected value=""" & idx & """>"& idx &"</option>"
      else
         out=out& "<option value=""" & idx & """>"& idx &"</option>"
      end if
   next
   writeDateSelect = out& "</select>"
end function

function writeCustomersArray(pName) Is that the table name, and do I have to change it to master, or viceversa?
   db_cmdText = "SELECT * from master ORDER BY Cognome;"

   ' open a command object on our database
   openCommand Application("keneso"), "writeCust 1"

   ' get our recordset using the open command object
   getRS db_rs, db_cmdText, "writeCust 2"

   Dim out, idx, item

   if NOT (db_rs.bof AND db_rs.eof) then
      out = "var "& pName &" = new Array();" & vbLF What is that referred to, shall I change it to some fileds I named differently?
  &n