I'm fairly new to ASP but have used it successfuly a few times on some of
our intranet pages.

Recently I've been using Aspmaker (to link to our SQL database) and
modifying some of it's code to make the pages more interactive.

What's working at the moment is...

An ASP form is completed and submitted ok, adding a new record to the
database. One of the fields on the form has a dropdown list, linked to a
database table, for selecting the value of the field.

Use javascript to check the value of the field with the dropdown list, prior
to submission of the form, then display an alert box if the value of this
field matches certain criteria.

Using ASP, use the value of the field to lookup the value of a field in
another SQL table. This lookup is working fine, I can write the value to the
page.

The way I want it to work is...

Once the value has been looked up in the database, display an alert box
client-side if the value matches certain criteria. I need this to happen
before the form is submitted.

The reason why, is that users are entering jobs into the system and I need
to check that the customer they select doesn't have their account on hold,
prior to the form being submitted.

The problem I'm having is that a javascript function is called when the
value of the dropdown list changes, this contains the ASP to do the database
lookup and I then have a problem using the value from the database lookup to
display an alert, as I can't have a block of JavaScript (or VBScript) inside
the ASP, as the ASP in already inside the JavaScript function.

This probably sounds more messy than it needs to be, like I said, I'm fairly
new to ASP! Is there an easy solution to sorting this out, or is it best to
start from scratch.

If it's best to start again, how would you...

1. Get the value of a dropdown list (linked to a SQL database table) on an
ASP generated form
2. Use this value to lookup the value of a field in another SQL table
3. Display an alert (client-side) if the value looked up matches certain
criteria, before the form is submitted.

Andy - not expecting an easy answer to this, if any at all!

Message-ID: <GZidnUbLWaUG4W3eRVnytQ@pipex.net> from andy contained the
following:

all the information must have already been looked up and stored
somewhere within the Javascript. And if you have already looked up all
the information you don't need to run the query again.

Why not use ASP to deliver the alert. after the form is submitted? On
an intranet, you should barely notice the difference.
--
Geoff Berrow 0110001001101100010000000110
001101101011011001000110111101100111001011
100110001101101111001011100111010101101011

"Geoff Berrow" <blthecat@ckdog.co.uk> wrote in message
news:ft41v15r9b0r006qu7d652qkhc6esg472o@4ax.com...

Hi Geoff,

Yeah, thought it sounded like an "oops" situation!

I'll look again at doing things after the form's been submitted.

Not quite sure where to start, but I'll sit down and think it through.

andy wrote:

Get your head clear over the difference between "client side" and
"server side" code.

If you want posh, read a good book on Ajax (try "Ajax in Action")

There are a number of variations on the same theme to achieve this type of
functionality. A currently-popular incarnation is call AJAX, and another
version is called Remote Scripting (with Java).

I use a fairly simple technique using XML Data Islands (as follows):

1) Add the XML Data Island (just after the Body element)

<XML ID="XMLID"></XML>

2) Point your web page control to an event handler routine (eg. onChange for
your Listbox)

<select name="listbox" id="listbox"
onchange='myEventHandler(myForm.Listbox.value)>
etc....
</select>

3) Create your event handler (modify as necessary):

function myEventHandler(par) {

var currNode;

XMLID.async = false;

// Change Ddata Island source
strQuery = "Exec Select Field0, Field1 from myTable Where Field2=" + par;
//you can obviously call a Stored Procedure here....
XMLID.SRC="/xmlQuery.asp?strQuery=" + strQuery;

// Get all "names" from XML data
objNodeField0 = XMLID.getElementsByTagName("Field0");
objNodeField1 = XMLID.getElementsByTagName("Field1");

// Do stuff: eg, display values in msgbox
for (var i=0; i < objNodeList.length; i++) {
alert(objNodeField0.item(i).text + ", " + objNodeField1.item(i).text);
}

//alternatively, use this data to update other fields in your form
dynamically

}

4) Link your handler routine into your page:

<script language="javascript" src="/myEventHandler.js"></script>

5) Create the all-important xmlQuery.asp file, as...

<%@ Language="VBScript" %>
<%
' Declare all variables.
Option Explicit
Dim strSql,objRS,objField
Dim sConn, oConn
Dim strName, strValue


' Buffer and output as XML.
Response.Buffer = True
Response.ContentType = "text/xml"

' Start our XML document.
Response.Write "<?xml version=""1.0""?>" & vbCrLf

' Set SQL and database connection string.

set oConn=server.createobject("adodb.connection")
sConn=Application("Connection")
oConn.Open sConn
strSQL = Request.QueryString("strQuery")

set objRS=oConn.execute(strSQL)

' Output start of data.
Response.Write "<database>" & vbCrLf

' Loop through the data records.
While Not objRS.EOF
' Output start of record.
Response.Write "<record>" & vbCrLf
' Loop through the fields in each record.
For Each objField in objRS.Fields
strName = objField.Name
strValue = objField.Value
If Len(strName) > 0 Then strName = Server.HTMLEncode(strName)
If Len(strValue) > 0 Then strValue = Server.HTMLEncode(strValue)
Response.Write "<" & strName & ">" & vbCrLf
Response.Write strValue & vbCrLf
Response.Write "</" & strName & ">" & vbCrLf
Next
' Move to next city in database.
Response.Write "</record>" & vbCrLf
objRS.MoveNext
Wend

' Output end of data.
Response.Write "</database>" & vbCrLf
%>


To summarize the process...
1) your control calls its event handler
2) ...which calls xmlQuery.asp
3) ...which submits your SQL query to the DB
4) xmlQuery.asp takes the results from the DB and bundles it as an XML
segment
5) ...which is returned to your event handler
6) which updates the form/displays message boxes/etc

Simple!

Note: all this code is trimmed from real code that I'm using so you know a)
that it works in practice, but b) I've probably introduced a few typos in
there somewhere. So I'll let you debug it yourself!

Hope this helps...

Chris

Thanks everyone, lots of useful code to work through and suggestions.

This'll keep me busy for a while!

Andy