rodsdot.com :: database to client side array with form autofill

<% Option Explicit %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta name="author" content="Roderick Divilbiss">
<meta name="copyright" content="? 2005 Roderick Divilbiss">

<%
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = 1

' To separate the complexity of opening the recordset from
' the function of this example, I am going to implement a
' subroutine to get the recordset and drop out if successful.

Dim rs

Sub getRecordSet
    Dim conn, command, connection
    command = "SELECT company, address, address1, city, state, "&_
          "postalCode, phoneNumber FROM companys ORDER BY company;"

    ' We do NOT want to show dtatbase errors to the browsing public
    ' so we will trap the errors with on error resume next.
    ' 
    ' This means we will need to check for errors after each command
    ' and display some, less detailed information to the public.
    '
    ' If you need to troubleshoot, you will need to uncomment the
    ' lines which write the err.number, description and source.
    '
    on error resume next

    ' Create an ActiveX Data Object for the database connection
    Set conn = Server.CreateObject("ADODB.Connection")

    if err then
        Response.Write "There was an error creating the "&_
          "database connection<br>" & vbLF
        '* developer only * Response.Write err.number &_
             "<br>" & err.description & "<br>" & err.source
        Response.End
    else        ' Specify the connection string for the database. 
        ' This really should not be in your code...it should be 
        ' stored in the registry or at least as an application
        '  object in the global.asa file.
        '
        ' OLEDB connections offer better performance and 
        ' stability than ODBC. Refer to these Microsoft 
        ' Knowledge Base articles: KB Q10191, KB Q10023

        connection = Application("examples")

        ' Try to open a connection to the database
        conn.open connection
        if err then
            Response.Write "There was an error opening the "&_
                    "database connection<br>" & vbLF
            '* developer only * Response.Write err.number &_
              "<br>" & err.description & "<br>" & err.source
            Response.End
        else
            ' Create an ActiveX Data Object for the recordset in
            ' which we will return the results of our query. 
            Set rs = Server.CreateObject("ADODB.RecordSet")
            if err then
                Response.Write "There was an error creating the"&_
                     " recordset object.<br>" & vbLF
                '* developer only * Response.Write err.number &_
                  "<br>" & err.description & "<br>" & err.source
                Response.End
            else
                ' Execute the query (command) as read only
                rs.open command, conn, adOpenStatic, adLockReadOnly, adCmdText
                if err then
                    Response.Write "There was an error opening the"&_
                    " recordset to retrieve the requested data.<br>" & vbLF
                    '* developer only * Response.Write err.number &_
                        "<br>" & err.description & "<br>" & err.source
                    Response.Endelse
                    ' we have our recordset or results, lets go.
                    Exit Sub
                end if
                set rs = nothing
            end if 
            conn.close
        end if
        Set conn = nothing
    end if
    on error goto 0
End Sub

call getRecordSet
' Was the subroutine successful? (Did we get a recordset object)
if isObject(rs) then

    ' And do we have records?
    if NOT (rs.bof AND rs.eof) then
        '*******************************************
        '* MAIN ROUTINE BEGIN *
        '*******************************************

        ' We have to break the <script> tag to avoid an error
        ' begin a client side script block
        Dim tmpStr
        tmpStr = "<scr" & "ipt language=" & chr(34) & "javascr" & "ipt" &_
          chr(34) & " type=" & chr(34) & "text/javascr" & "ipt" &_
          chr(34) & ">" & vbLF


        ' declare a client side array
        tmpStr = tmpStr & " var csArray=new Array();" & vbLF & vbLF
        Dim idx, item
        idx = 0 ' JavaSccript arrays are zero based.
        tmpStr = tmpStr & " var csArrayMaxIdx=" & rs.recordcount-1 & ";" &_
            vbLF & vbLF

        ' fill the array from our recordset
        While NOT rs.eof
            tmpStr = tmpStr & " csArray["& idx &"]=['"
            for each item in rs.fields
                tmpStr = tmpStr & item & "','"
            next


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


            ' increment our array index
            idx = idx + 1
            rs.movenext
        Wend

        tmpStr = tmpStr & "</scr" & "ipt>" & vbLF
        response.write tmpStr

    end if
    rs.close
    Set rs = nothing 
end if
%>

<script language="javascript" type="text/javascript">
<!--
// currentIdx will hold the array index currently selected so
// we can tell if the form fields are already filled with a
// value from our database array. This allows us to confirm
// any changes if a new value is selected.

var currentIdx='';

function createSelect() {
    var tmpHTML = '<select id="companySelect" name="companySelect"');
    tmpHTML += ' onchange="fillFormFields(this.selectedIndex);">';

    for (var idx=0; idx<=csArrayMaxIdx; idx++) {
        tmpHTML = tmpHTML + ' <option>' + csArray[idx][0] + '</option>\n';
    }
    tmpHTML = tmpHTML + '</select>';
    return tmpHTML;
	}

function fillFormFields(idx) {
    if (currentIdx!='') {
        var cnfrm = confirm('Do you want to replace the form information\nfor '
         +document.getElementById('company').value+' with\nthe information for '
         +csArray[idx][0]+'?');
    }
    if ((currentIdx=='')||(cnfrm)) {
        document.getElementById('company').value=csArray[idx][0];
        document.getElementById('address').value=csArray[idx][1];
        document.getElementById('address1').value=csArray[idx][2];
        document.getElementById('city').value=csArray[idx][3];
        document.getElementById('state').value=csArray[idx][4];
        document.getElementById('postalCode').value=csArray[idx][5];
        document.getElementById('phoneNumber').value=csArray[idx][6];
        currentIdx=idx;
    } 
	}
//-->
</script>
<title>ASP Database RecordSet to Client Side Array For Form Select Example</title>
<style>
<!--
body {
font-family:Verdana, Arial, Helvetica, Sans Serif;
font-size: x-small;
}

#container {
width:600px;
margin-left:auto;
margin-right:auto;
}

#selCompany {
width:250px;
float:left;
margin:0px 10px 10px 0px;
}

#compDetails {
width:340px;
float:right;
}

h4 {
color: #000000;
border: 1px solid #000000;
background-color: #FF9900;
text-align:center;
font-size:small;
font-weight:bold;
margin: 0px 0px 2px 0px;
}

input, select {
font-size:x-small;
color:#333333;
border:1px solid #000099;
margin-bottom:8px;
}

fieldset {
padding: 5px;
border:1px solid #000000;
}

legend {
display:none;
}

label {
color:#000099;
font-weight:bold;
/*display:block;*/
float:left;
}

#lCompanySelect {
width:70px;
}

#companySelect {
width:150px;
}

#lCompany {
width:70px;
}

#company {
width:230px;
}

#lAddress {
width:70px;
}

#address {
width:230px;
}

#lAddress1 {
width:70px;
}

#address1 {
width:230px;
}

#lCity {
width:70px;
}

#city {
width:120px;
}

#lState {
width:40px;
}

#state {
width:20px;
} 

#lPostalCode {
width:10px;
}

#postalCode {
width:60px;
}

#lPhoneNumber {
width:70px;
}

#phoneNumber {
width:100px;
}
.breaker {
clear:both;
padding:1px;
}

.code {
width: 600px;
height: 1000px;
margin-left:auto;
margin-right:auto;
border:1px solid #000000;
padding:5px;
background-color: #FFFFCC";
font-size: xx-small;
color:#000000;
overflow:scroll;
}
-->
</style>
</head>
<body>
<div id="container">
  <form method="post">
    <div id="selCompany">
      <h4>Select Company</h4>
      <fieldset>
        <legend>Select Company</legend>
        <label for="companySelect" id="lCompanySelect">Company Name</label>
        <script language="javascript" type="text/javascript">
          document.write(createSelect());
        </script>
      </fieldset>
    </div>

    <div id="compDetails">
      <h4>Company Details</h4>
      <fieldset>
        <legend>Company Details</legend>
        <label for="company" id="lCompany">Company</label>
        <input id="company" name="company" type="text" 
         title="Enter the company name or select a company from the company name list."
         size="35">
        <div class="breaker"></div>
        <label for="address" id="lAddress">Address (line one)</label>
        <input id="address" name="address" type="text" 
         title="Enter the company's first address line or select from the company list."
         size="35">
        <div class="breaker"></div>
        <label for="address1" id="lAddress1">Address (line two)</label>
        <input id="address1" name="address1" type="text" 
         title="Enter the company's second address line or select from the company list."
         size="35">
        <div class="breaker"></div>
        <label for="city" id="lCity">City, St., Zip</label>
        <input id="city" name="city" type="text" 
         title="Enter the company's city or select a company from the company name list."
         size="15">,
        <input id="state" name="state" type="text" 
         title="Enter the company's state or select a company from the company name list."
         size="2">.,
        <input id="postalCode" name="postalCode" 
         type="text" title="Enter the company's Zip Code or select from the company list." 
         size="10">
        <div class="breaker"></div>
        <label for="phoneNumber" id="lPhoneNumber">Phone Number</label>
        <input id="phoneNumber" name="phoneNumber" type="text" 
         title="Enter the company's telephone number or select from the company list." 
         size="15">
      </fieldset>
    </div> 
  </form>
</div>
</body>
</html>
How can you create a drop down list box from a database and populate form fields from the database depending on what was selected from the drop down list box?

Select Company

Company Details

Most Popular Pages On rodsdot.com
