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_georgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tellis_georgeAuthor Commented:
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
0
aarieCommented:
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.
0
jbrugmanCommented:
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"> <!--
  var theValue = document.location.search.substr(1);
  alert("You passed :'"+theValue+"'");
  // -->
  </script>
 </head>
</html>
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jbrugmanCommented:
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}&nbsp;
          </TD><TD nowrap>
            <A HREF='/cgi-bin/bedcode.cgi?$Data{LVZKGG}'>$Data{LVLVNM}</A>&nbsp;
          </TD><TD nowrap>
            $Data{LVTLNR}&nbsp;
          </TD><TD>
            $Data{LVFXNR}&nbsp;
          </TD>
        </TR>
      \n";
  }
}

0
brigmarCommented:
<html>
 <head>
  <script language="JavaScript"><!--

function DbRec( EmpID, Name, PhoneNo ) {
  this.EmpID   = EmpID;
  this.Name    = Name;
  this.PhoneNo = PhoneNo;
  return;
}

var myDb = new Array();

// populate the database..
myDb[myDb.length] = new DbRec("001","Andrew","111-222-3333");
myDb[myDb.length] = new DbRec("002","Bill","111-222-4444");
myDb[myDb.length] = new DbRec("003","Charles","111-222-5555");
myDb[myDb.length] = new DbRec("004","David","111-222-6666");
myDb[myDb.length] = new DbRec("005","Ewan","111-222-7777");

function writeOptions() {
  var html = '';
  for(i=0; i< myDb.length; i++)
    html += '<option value="' + i + '">' + myDb[i].EmpID;
  document.write( html );
}

function writeTextBox(n,v) {
  document.write( '<input type="text" name="'+n+'" value="'+v+'">' );
}

function myselect_onchange(o) {
  var f = o.form,
      r = myDb[o.options[o.selectedIndex].value];
  f.empid.value = r.EmpID;
  f.sname.value = r.Name;
  f.phoneno.value = r.PhoneNo;
  return;
}

  // -->
  </script>
 </head>
 <body>
  <form name="myform">
   <select name="myselect" onchange="myselect_onchange(this)">
  <script language="JavaScript"><!--
writeOptions();
  // -->
  </script>
   </select>
  <script language="JavaScript"><!--
writeTextBox( "empid", myDb[0].EmpID );
writeTextBox( "sname", myDb[0].Name );
writeTextBox( "phoneno", myDb[0].PhoneNo );
  // -->
  </script>
  </form>
 </body>
</html>


Brian
0
tellis_georgeAuthor Commented:
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 ??
0
brigmarCommented:
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
0
brigmarCommented:
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"><!--

function DbRec( EmpID, Name, PhoneNo ) {
 this.EmpID = EmpID;
 this.Name = Name;
 this.PhoneNo = PhoneNo;
 return;
}

var myDb = new Array();

// populate the database..
<%
  For i = 1 To UBOUND(dbRec,1)
    Response.write "myDb[myDb.length] = new DbRec(""" & dbRec(i,1) & """,""" & dbRec(i,2) & """,""" & dbRec(i,3) & """);" & chr(13)
  Next
%>

function myselect_onchange(o) {
 var f = o.form,
 r = myDb[o.options[o.selectedIndex].value-1];
 f.empid.value = r.EmpID;
 f.sname.value = r.Name;
 f.phoneno.value = r.PhoneNo;
 return;
}

// -->
</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
0
tellis_georgeAuthor Commented:
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...
 
0
tellis_georgeAuthor Commented:
Hi brigmar, Could you re-post the solution as an answer.
0
brigmarCommented:
telli_george,

What you're looking for is to re-query the database whenever the user changes the select box ? Ouch !

This means going back to the server, and re-generating the page each time. Very bad. How large is your database ?

The way you framed the previous comments shows you dont quite understand the difference between the server side processing and the client side processing.

The server side (ASP) generates HTML (including Javascript & VBScript, if wanted) that is sent to the client (your browser). Once at the browser, that's where it stays. Any scripts sent to the browser run on that machine. The client never sees ASP code.

The way a client interacts with a server is by requesting a page, or graphic, or submitting a form.

My "solutions" moved the entire recordest to the client in the form of a javascript array, so that no further requests are made of the server, and the response time to changes in the select box is fast. The obvious drawback is downloading the recordset to the client.

There may be a trade-off solution, which involves having the select box in one frame, and the data fields in another. Instead of triggering a re-query of the database whenever the select box changes, put a "go" button next to the select box. Pressing the "go" button will submit the form targetting the other frame. That form response page queries the database for the employee record and displays it.

Brian

(and btw, I don't know which comment you want as an answer, so I wont submit any as an answer. If you think a comment is acceptable as an answer, you can mark it as an accepted answer)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TTomCommented:
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

0
brigmarCommented:
very nice
0
tellis_georgeAuthor Commented:
Thanks a lot for all the quick solutions.... :)

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
JavaScript

From novice to tech pro — start learning today.