dropdown variable...

[mjonson]

a dropdown is populated from a table but one of the fields is an id field for a lookup table.
i want a field from the lookup table to be displayed in the dropdown instead of the id.

ne advice welcome
my attempt

<%

'codefor dropdown
x_partneridList = "<select name='x_partnerid'><option value=''>Please Select</OPTION>"
cbo_x_partnerid_js = "" ' Initialise
sqlwrk = "SELECT `partnersid`, `partmodid`, `partnotes`, `stype` FROM `tbl_partners`"

Set rswrk = Server.CreateObject("ADODB.Recordset")
rswrk.Open sqlwrk, conn, 1, 2

If Not rswrk.Eof Then
    datawrk = rswrk.GetRows
    rowswrk = UBound(datawrk, 2)
    For rowcntwrk = 0 To rowswrk
        x_partneridList = x_partneridList & "<option value='" & datawrk(0, rowcntwrk) & "'"
        If CStr(datawrk(0, rowcntwrk)&"") = CStr(x_partnerid&"") Then
            x_partneridList = x_partneridList & " selected"
        End If
        x_partneridList = x_partneridList & ">" & datawrk(1, rowcntwrk) & ", " & datawrk(2, rowcntwrk) & "</option>"
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4 & "] = '" & Replace(datawrk(0, rowcntwrk)&"","'","\'") & "';" ' Value
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+1 & "] = '" & Replace(datawrk(1, rowcntwrk)&"","'","\'") & "';" ' Display field 1
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+2 & "] = '" & Replace(datawrk(2, rowcntwrk)&"","'","\'") & "';" ' Display field 2
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+3 & "] = '" & Replace(datawrk(3, rowcntwrk)&"","'","\'") & "';" ' Filter field

'my code for getting data from linked table
sqlcombomake = "SELECT modid, model FROM tbl_model WHERE modid = " & partmodid
Set rscombomake = Server.CreateObject("ADODB.Recordset")
rscombomake.open sqlcombomake, conn, 1, 2

    Next
End If
rswrk.Close
Set rswrk = Nothing
x_partneridList = x_partneridList & "</select>"
Response.Write x_partneridList
%>

<script language="JavaScript">
<!--
var ar_x_partnerid = new Array();
<%= cbo_x_partnerid_js %>
//--></script>

 

[FesterSXS]

try something like this then...

SELECT l.myLookupField, p.partnersid, p.partmodid, p.partnotes, p.stype FROM tbl_lookup l, tbl_partners p WHERE l.someID=p.someID;"

I'm afraid without knowing the structure of your lookup table you will have to put up with my semi-psuedo code example.

I hope this makes sense

Tony
________________________________________________________________________________

http://www.SpiraxSarco.com

 

[mjonson]

thanks tony,

sorry for not giving more detail...

parent table(joined on partmodid)
tbl_partners
---------------------------------------
partnersid partmodid partnotes stype

lookup table(joined on modelid)
tbl_model
---------------
modelid model

"SELECT tbl_model.modelid, tbl_partners.partnersid, tbl_partners.partmodid, tbl_partners.partnotes, tbl_partners.stype FROM tbl_model, tbl_partners WHERE tbl_model.modelid=tbl_partners.partmodid;"

have tried the above sql in my code but get error

Microsoft OLE DB Provider for ODBC Drivers (0x80040E21)
ODBC driver does not support the requested properties.
/dfasp/dealaddmain.asp, line 623

will carry on - am sure its someting simple

 

[mjonson]

some sample data

parent table(joined on partmodid)
tbl_partners
---------------------------------------
partnersid partmodid partnotes stype
1 1 notes1 2
2 2 notes2 2
3 2 notes3 3

lookup table(joined on modelid)
tbl_model
---------------
modelid model
1 model1
2 model2
3 model3

 

[Lothario]

Try this:

"SELECT tbl_model.modelid, tbl_partners.partnersid, tbl_partners.partmodid, tbl_partners.partnotes, tbl_partners.stype FROM tbl_model
INNER JOIN tbl_partners
ON tbl_model.modelid=tbl_partners.partmodid;"

-L

 

[mjonson]

i think i need to explain a bit more wat is goin on...

i have dynamic combos that are not linked on uniqueID fields.

so when a user picks an option from tbl_stocktype it shows all records in tbl_partners where tbl_stocktype.stypeid and tbl_partners.stype match.
this is because there is more than one option in tbl_stocktype that displays the same records from tbl_partners.


1)i would like the tbl_partners combo to show the results of a lookup on tbl_model.modelid using tbl_partners.partmodid

2)i need to pass the tbl_stocktype.stypeidmain value instead of tbl_stocktype.stypeid

ne ideas welcome

parent table(joined on stypeid)
tbl_stocktype
--------------------------------------
stypeidmain stocktype stypeid snotes
--------------------------------------
1 type1 2 notes1
2 type2 2 notes2
3 type3 2 notes3
4 type4 3 notes4
5 type5 3 notes5

parent table(joined on partmodid)
tbl_partners
----------------------------------------
partnersid partmodid partnotes stype
----------------------------------------
1 1 notes1 2
2 2 notes2 2
3 2 notes3 3

lookup table(joined on modelid)
tbl_model
---------------
modelid model
1 model1
2 model2
3 model3

tbl_stocktype dropdown

<%
x_stypeidList = "<select name='x_stypeid' onChange='EW_updatecombo(this.form.x_partnerid, ar_x_partnerid, this.options[this.selectedIndex].value);'><option value=''>Please Select</OPTION>"
cbo_x_stypeid_js = "" ' Initialise
sqlwrk = "SELECT `stypeid`, `stocktype`, `snotes` FROM `tbl_stocktype` ORDER BY stocktype"

Set rswrk = Server.CreateObject("ADODB.Recordset")
rswrk.Open sqlwrk, conn, 1, 2
If Not rswrk.Eof Then
    datawrk = rswrk.GetRows
    rowswrk = UBound(datawrk, 2)
    For rowcntwrk = 0 To rowswrk
        x_stypeidList = x_stypeidList & "<option value='" & datawrk(0, rowcntwrk) & "'"
        If CStr(datawrk(0, rowcntwrk)&"") = CStr(x_stypeid&"") Then
            x_stypeidList = x_stypeidList & " selected"
        End If
        x_stypeidList = x_stypeidList & ">" & datawrk(1, rowcntwrk) & " " & datawrk(2, rowcntwrk) & "</option>"
cbo_x_stypeid_js = cbo_x_stypeid_js & "ar_x_stypeid[" & rowcntwrk*4 & "] = '" & Replace(datawrk(0, rowcntwrk)&"","'","\'") & "';" ' Value
cbo_x_stypeid_js = cbo_x_stypeid_js & "ar_x_stypeid[" & rowcntwrk*4+1 & "] = '" & Replace(datawrk(1, rowcntwrk)&"","'","\'") & "';" ' Display field 1
cbo_x_stypeid_js = cbo_x_stypeid_js & "ar_x_stypeid[" & rowcntwrk*4+2 & "] = '" & Replace(datawrk(2, rowcntwrk)&"","'","\'") & "';" ' Display field 2
'cbo_x_stypeid_js = cbo_x_stypeid_js & "ar_x_stypeid[" & rowcntwrk*4+3 & "] = '" & Replace(datawrk(3, rowcntwrk)&"","'","\'") & "';" ' Filter field
    Next
End If
rswrk.Close
Set rswrk = Nothing
x_stypeidList = x_stypeidList & "</select>"
Response.Write x_stypeidList
%>

tbl_partners dropdown

<%
x_partneridList = "<select name='x_partnerid'><option value=''>Please Select</OPTION>"
cbo_x_partnerid_js = "" ' Initialise
sqlwrk = "SELECT `partnersid`, `partmodid`, `partnotes`, `stype` FROM `tbl_partners`"
Set rswrk = Server.CreateObject("ADODB.Recordset")
rswrk.Open sqlwrk, conn, 1, 2

If Not rswrk.Eof Then
    datawrk = rswrk.GetRows
    rowswrk = UBound(datawrk, 2)
    For rowcntwrk = 0 To rowswrk
        x_partneridList = x_partneridList & "<option value='" & datawrk(0, rowcntwrk) & "'"
        If CStr(datawrk(0, rowcntwrk)&"") = CStr(x_partnerid&"") Then
            x_partneridList = x_partneridList & " selected"
        End If
        x_partneridList = x_partneridList & ">" & datawrk(1, rowcntwrk) & ", " & datawrk(2, rowcntwrk) & "</option>"
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4 & "] = '" & Replace(datawrk(0, rowcntwrk)&"","'","\'") & "';" ' Value
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+1 & "] = '" & Replace(datawrk(1, rowcntwrk)&"","'","\'") & "';" ' Display field 1
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+2 & "] = '" & Replace(datawrk(2, rowcntwrk)&"","'","\'") & "';" ' Display field 2
cbo_x_partnerid_js = cbo_x_partnerid_js & "ar_x_partnerid[" & rowcntwrk*4+3 & "] = '" & Replace(datawrk(3, rowcntwrk)&"","'","\'") & "';" ' Filter field
    Next
End If
rswrk.Close
Set rswrk = Nothing
x_partneridList = x_partneridList & "</select>"
Response.Write x_partneridList
%>

NASA scientists spent three years and $7 million to develop a pen that could write in zero gravity, upside down, on
almost any surface and at any temperature...
the Russians used a pencil