using selected option from combobox in SQL to get specific row from database

[csarafin 0]

I'm new to ASP and have a form that lets users choose a division code from a dropdown box that is populated from an access database. Once they have selected the division, I'd like to be able to collect other fields (name, address, city, state, zip) from that same recordset and pass those fields to another asp page. I'm able to write the selected code to the database, but can't figure out how to collect the other fields from that specific line in the table. My code is

 

 

<%Set objconn = Server.CreateObject("ADODB.Connection")objconn.ConnectionString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("data/jobdata.mdb")objconn.OpenSet objRs = Server.CreateObject("ADODB.Recordset")strSQL = "SELECT * FROM Locations "objRs.Open strSQL, objconnResponse.Write "<select name=DCode><option value='' selected></option>"Do While Not objRS.EOFResponse.Write "<option value='" & objrs("DCode") &"'>"& objRs("DCity") &"</option>"objRS.MoveNextLoopResponse.Write "</select>"objRs.Closeobjconn.Close%>How do I get the page to also get the DName, DAddress1, DAddress2, DCity, DState, and DZIP fields from the selected DCode so it can be passed to another page for display or writing to a different database.Thanks much, csarafin

[justsomeguy 1,135]

If you want to do that without the page refreshing then you'll need to use Javascript. There are several options, one of them is to send an ajax request when they select a division to get the rest of the data, or another option is to write all of the data out when you're going through the recordset. You could write that data into something like a Javascript array and look it up there when they choose a division, or use data attributes on the option elements and look it up there.

[csarafin 0]

I like the idea of using data attributes on the option elements. How would I extract all this data on the next page so each attribute would be written to a separate column in a table in a different database?

 

Thanks,

csarafin

[justsomeguy 1,135]

Data attributes won't get sent along with the form data, so you would need a Javascript function that would run when they change the selection and do something like copy the various data attributes into hidden form fields that will be included when the form is submitted.

[csarafin 0]

thanks for the suggestion. I'm looking into arrays now.