asked on

Populating form oblects dynamically

All my data resides on MS-SQL database. I have a form that contains a listbox (its values populated from the database when the form loads) The form has many other objects (mostly text boxes) that need to be populated with appropriate values from the database based on selecting an entry from the pre-loaded list box. I do not want the page to re-load. Could someone gimme an example (hardcoded values are fine) with 1 list box and 2-3 textboxes that get populated when I select a value from the list box.....

tellis_george

ASKER

Hi, On second thoughts it would be great if a hypothetical DBase could be used :-))) Assuming the fields EmpId, Name and PhoneNo, I would like the page to load with the list box populated with all EmpID's and on selecting one item from the list the text boxes get filled with the corresponding name and PhoneNo.
I am pretty confused as to how all the controls flow so it would really fab if the solution addresses the above scenario.....

Thanks & Regards

Member_2_242582

I think that the only way to do something like that would be by using two different frames... one containing the dropdown box with the EmpIDs and the other containing the fields which correspond with that particular EmpID.

Arjan.

jbrugman

1 ->
If you put the values in a frame that 'stays' on the browser:

In the left frame you can make hidden fields.
<HTML>
<INPUT TYPE=HIDDEN NAME=firstname
</HTML>

In the second frame, make the form
<HTML>
<SCRIPT>
function passvalue() {
document.frames[leftframename].firstname.value=document.formname.firstname.value;
document.rightframename.location="confirm.htm"
}
</SCRIPT>
<FORM NAME=formname>
<INPUT TYPE=TEXT NAME=firstname>
<INPUT TYPE=BUTTON onClick=passvalue()>
</FORM>
</HTML>

Then the next screen
<HTML>
<SCRIPT>
function passback() {
document.formname.firstname.value=document.frames[leftframename].firstname.value;
var aaa=document.frames[leftframename].firstname.value
document.write(aaa);
}
</SCRIPT>
<BODY ONLOAD=passback()>
<FORM NAME=formname ACTION=mail.cgi METHOD=post>
<INPUT TYPE=HIDDEN NAME=firstname>
<INPUT TYPE=SUBMIT VALUE='click if ok'>
</FORM>
</BODY>
</HTML>

2->
To pass values from one page to another:

<SCIPT LANGUAGE="JavaScript">

function clacstart() {
// do your calculation
// set it in var theValue
// then go to next page...

document.location="B.html?"+theValue;
}

</SCRIPT>

<html>
<head>
<script language="javascript"> 
</script>
</head>
</html>

jbrugman

And to read a sql database you could use perl:

#! F:\perl\bin\perl.exe
use Win32::ODBC;
$db = new Win32::ODBC("DSN=HAB;");

$Sqlkop = "SELECT LVLVNM,LVZKGG,LVTLNR,LVFXNR FROM Leveranciers ORDER BY LVLVNM";

if ($db->Sql($Sqlkop)) {
      print "SQL failed.\n";
      print "Error: " . $db->Error() . "\n";
}
else {
while($db->FetchRow()) {
%Data = $db->DataHash();
      print "
       <TR>
       <TD nowrap>
            $Data{LVZKGG} 
       </TD><TD nowrap>
            <A HREF='/cgi-bin/bedcode.cgi?$Data{LVZKGG}'>$Data{LVLVNM}</A> 
       </TD><TD nowrap>
            $Data{LVTLNR} 
       </TD><TD>
            $Data{LVFXNR} 
       </TD>
       </TR>
      \n";
}
}

brigmar

<html>
<head>
<script language="JavaScript">
</script>
</head>
<body>
<form name="myform">
<select name="myselect" onchange="myselect_onchange(this)">
<script language="JavaScript">
</script>
</select>
<script language="JavaScript">
</script>
</form>
</body>
</html>

Brian

tellis_george

ASKER

jbrugman's solution was very exhaustive and I greatly appreciate the effort. While the solution given was real good it was unsuitable for my project for a few reasons. (The above page is supposed to be a printable report and so frames are not possible, also I am using ASP and not CGI scripts)
Thank you very much jbrugman.

brigmar's comment appeared to be a more suitable solution for the problem and here I must say that the solution provided works like a dream :-) I really appreciate the quick response and elegance of the solution. I still have a problem however.... All my DB access is with ASP, How do I integrate ASP select statement with the above solution ??

brigmar

it's a little different to run thru it with ASP..

perform your SQL query, as you normally would. Something like..

SELECT EmpID, Name, PhoneNo FROM employees ORDER BY EmpID

Now convert the resulting recordset (rs) into an array, using:

myDb = rs.GetRows()

Now, as we no longer have to dynamically create the HTML on the client side, we can dispense with the writeOptions() and writeTextBox() functions from the javascript.

The javascript database becomes:
<%
'
' Dynamically create the javascript
' that creates the client-side Db.
'
For i = 1 To UBOUND(myDb)
Response.write "myDb[myDb.length] = new DbRec(""" & myDb(i)(1) & """,""" & myDb(i)(2) & """,""" & myDb(i)(3) & """);"
Next
%>

The code inside the form becomes:

<select name="myselect" onchange="myselect_onchange(this)">
<%
'
' Dynamically output the records from
' the Db as html <option>'s
'
For i = 1 to UBOUND(myDb)
Response.write "<OPTION VALUE=""" & i & """>" & myDb(i)(1)
Next
%>
</SELECT>
<INPUT TYPE="TEXT" NAME="empid" VALUE="<%= myDb(1)(1) %>">
<INPUT TYPE="TEXT" NAME="sname" VALUE="<%= myDb(1)(2) %>">
<INPUT TYPE="TEXT" NAME="phoneno" VALUE="<%= myDb(1)(3) %>">

Brian

brigmar

scrub that (way too many errors!!)

<%
Dim dbRec (5,3)

dbRec(1,1) = "001"
dbRec(1,2) = "Andrew"
dbRec(1,3) = "111-222-3333"
dbRec(2,1) = "002"
dbRec(2,2) = "Bill"
dbRec(2,3) = "111-222-4444"
dbRec(3,1) = "003"
dbRec(3,2) = "Charles"
dbRec(3,3) = "113-222-5555"
dbRec(4,1) = "004"
dbRec(4,2) = "David"
dbRec(4,3) = "111-222-6666"
dbRec(5,1) = "005"
dbRec(5,2) = "Ewan"
dbRec(5,3) = "111-222-7777"
%>
<html>
<head>
<script language="JavaScript">
</script>
</head>
<body>
<form name="myform">
<select name="myselect" onchange="myselect_onchange(this)">
<%
For i = 1 To UBOUND(dbRec,1)
Response.write "<OPTION VALUE=""" & i & """>" & dbRec(i,1) & chr(13)
Next
%>
</select>
<INPUT TYPE="TEXT" NAME="empid" VALUE="<%= dbRec(1,1) %>">
<INPUT TYPE="TEXT" NAME="sname" VALUE="<%= dbRec(1,2) %>">
<INPUT TYPE="TEXT" NAME="phoneno" VALUE="<%= dbRec(1,3) %>">
</form>
</body>
</html>

This uses a static Array on the server side..

To change that to a DB generated array,
create your recordset using whatever select statement, and then use :

dbRec = rs.GetRows()

...where rs is your recordset object..

Brian

tellis_george

ASKER

Hi brigmar,
The solution looks great again :-) but there is a slight hitch. I will not be retrieving all 3 fields at once. I would be using 2 select statements since the actual table has more fields, one select statement at first to fill the listbox with EmpID, and the second one is to retrieve name and phone details when an empID is selected from the listbox.
I dunno where to place this second select statement! Ideally it would be in function myselect_onchange() but can this be done ? Can I insert a <% select.... %> inside this function and the assign retrieved values to f.sname and f.phoneno. I'm sorry I should have been more explicit in framing the question.
In fact this solution will help me solve a large class of similar issues. In all cases I will be running 2 select statements.. One to fill a text box (done once when the page loads) and one executed each time a user selects a value from the listbox...

tellis_george

ASKER

Hi brigmar, Could you re-post the solution as an answer.

ASKER CERTIFIED SOLUTION

brigmar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TTom

tellis_george:

FWIW, you can check out the possibility of "remote scripting", since you are using ASP. This allows a client side action to cause the execution of some server side script. Although there is a round trip made to the server, it is not necessary to rewrite the entire page to repopulate a dropdown list. Take a look at:

http://msdn.microsoft.com/workshop/c-frame.htm?943213353280#/workshop/languages/vinterdev/default.asp

It's an online book about using Visual InterDev. There is a link for "Run Functions Remotely" which discusses the use of remote scripting.

Hope this helps some,

Tom

brigmar

very nice

tellis_george

ASKER

Thanks a lot for all the quick solutions.... :)