SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Enthusiast hombre_de_las_ca's Avatar
    Join Date
    Apr 2005
    Location
    Bangalore, India
    Posts
    90
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Combobox disaster __need help!!!

    i have this code from ATGconsulting which i want to use for triple combobox dynamically populated from the database but the data base is quite different from the one used by them (pubs.mdb-= comes with SQL server but this is a converted sample and is there on the site to download http://www.atgconsulting.com/triplelist.asp)
    ia m attaching my database here (MIS.zip)... i need help with the SQL statement as well as a parameter passed in the function TripleLinkedList->" sDBFieldResult "

    i am also attaching the pubs database (pubs.zip)...
    all i can't figure out is how to get 3 comboboxes having CatName >> Machname >> PartName from my database all of which are related and are in different tables!!!

    Code:
    ' -----------------------------------------------
    ' the whole thing should be inside a <FORM> def
    ' -----------------------------------------------
    %>
    <FORM id=form1 name=form1>
    <CENTER>
    <%
    ' -----------------------------------------------
    ' the function will return a string containing
    ' the three listbox/combobox form elements
    ' that you can print or deal with however you want.
    '
    ' see comments in the function definition
    ' for info on calling parameters...
    ' -----------------------------------------------
    Response.Write TripleLinkedList(Conn, sQuery, "fieldname", 10, "pub_name", "title_name", "au_name", "au_id")
    ' -----------------------------------------------
    ' make sure you close and free your connection
    ' -----------------------------------------------
    Conn.Close
    Set Conn = Nothing
    ' -----------------------------------------------
    ' add anything else you want...
    ' -----------------------------------------------
    %>
    <P>
    <INPUT TYPE=SUBMIT NAME="foo" VALUE="Buy This Car!">
    </FORM>
    </BODY>
    </HTML>
    
    <%
    ' this section of code is self-contained and suitable to be moved to an include file
    Function TripleLinkedList(oCon, sQuery, sFormFieldName, nSize, sDBField1, sDBField2, sDBField3, sDBFieldResult)
    	' this is a general-purpose routine that implements triple-linked
    	' listboxes. here is the drill
    
    	' the function is passed the following parameters:
    	'	oCon			object reference to an open ADODB.Connection object
    	'	sQuery			string containing the fully-articulated SQL query
    	'	sFormFieldName	string containing the name for these fields inside the HTML form definition
    	'					note: the fields are named with suffixes 1, 2, 3
    	'	nSize			the size (number of entries) for the listboxes
    	'	sDBField1		field name that goes into the first listbox
    	'	sDBField2		field name that goes into the second listbox
    	'	sDBField3		field name that goes into the third listbox
    	'	sDBFieldResult	the name of the field returned a the result of
    
    	' here is how it works...
    	' the routine creates a series of three HTML SELECT statements... the first one
    	' is filled in with the values from database field sDBField1, along with arbitrary integer
    	' values; the second and third SELECT statements are initially empty.
    	' the contents from sDBField1 and sDBField2 are placed into JavaScript arrays
    	'
    	' when a user clicks an entry in the first or second listbox/combobox, a client-side
    	' javascript is invoked which reads the value clicked on, and populates the remaining
    	' boxes with the appropriate values. What we have to do in this function is use the
    	' database to populate the structure that will be used for the client-side scripts
    	' ------------------------------------------------------------------------------------------
    	' originally created 10/25/1999 by ATG Consulting, Inc.
    	' major improvements 11/02/2001 by ATG Consulting, Inc.
    	' for further info about this or other stuff contact us by email at kevin@atgconsulting.com
    	' ------------------------------------------------------------------------------------------
    
    	Dim sTemp		' general-purpose temp variable
    	Dim sScript		' bucket for holding the script structure
    	Dim sSelect		' bucket for the <SELECT> statement
    	Dim sArray1		' bucket to store the DBField2 array
    	Dim nField1		' counter for the primary array
    	Dim sArray2		' bucket to store the DBField3 array
    	Dim nField2		' counter for the secondary array
    	Dim rs			' recordset
    	Dim sLastVal1	' comparison string to test for record changes
    	Dim sLastVal2	' comparison string to test for record changes
    
    	On Error Resume Next
    	If Not IsObject(oCon) Then
    		sScript = "error processing triplelist -- need a connection object."
    	ElseIf oCon.State <> 1 Then
    		If Err.number <> 0 Then
    			sScript = "error processing triplelist -- invalid connection object."
    		Else
    			sScript = "error processing triplelist -- connection is not open."
    		End If
    	Else
    		Set rs = oCon.Execute(sQuery)
    		If Err.number <> 0 Then
    			sScript = "error processing query. Error " & Hex(Err.number) & ": " & Err.Description
    		ElseIf rs.EOF Then
    			sScript = "no records found -- seems wrong"
    		Else
    			On Error Goto 0	' reset the error handler.
    			' okay, we got some records... good. Now let's process them.
    			' general-purpose header information goes here...
    			' -----------------------------------------------------------
    			' first write the script header into the "sScript" bucket
    			' then declare the arrays into the script...
    			' -----------------------------------------------------------
    			sScript = "<SCR" & "IPT LANGUAGE=""JavaScript"">" & vbCrlf
    			sScript = sScript & "var array1 = new Array();" & vbCrlf
    			sScript = sScript & "var array2 = new Array();" & vbCrlf
    			' -----------------------------------------------------------
    			' create the first of the input field list box/combo box
    			' and set its values
    			' -----------------------------------------------------------
    			sSelect = "<SELECT NAME=""" & sFormFieldName & "1"" SIZE=""" & nSize & _
    				""" ONCHANGE=""return(clickcombo(1,document.forms[0]." & _
    				sFormFieldName & "1,document.forms[0]." & sFormFieldName & _
    				"2,document.forms[0]." & sFormFieldName & "3));"">" & vbCrlf
    
    			' -----------------------------------------------------------
    			' okay, now let's start to loop through the query
    			' then declare the arrays into the script...
    			' -----------------------------------------------------------
    			sLastVal1 = "empty" ' set up a default test value...
    		    Do Until rs.EOF
    		        If rs(sDBField1) <> sLastVal1 Then
    					' if the contents of field 1 changed, then
    					' close any previous entries for field1, 2, 3
    					If Right(sArray1, 3) = "," & vbCrlf Then
    						' pull off any trailing commas
    						sArray1 = Left(sArray1, Len(sArray1) - 3)
    					End If
    					If Len(sArray1) > 0 Then
    						sArray1 = sArray1 & ");" & vbCrlf
    					End If
    					' pick up new information for this row...
    					nField1 = nField1 + 1
    					sLastVal1 = rs(sDBField1)
    					' write the new contents of field 1 to the select statement
    					sSelect = sSelect & "<OPTION VALUE=" & nField1 & ">" & sLastVal1 & "</OPTION>"
    					' write a new entry in array1 for the field 2 values...
    					sArray1 = sArray1 & "array1[" & nField1 & "] = new Array(" & vbCrlf
    					' and reset the test values for field 2
    					nField2 = 0
    					sLastVal2 = "empty"
    				End If
    				If sLastVal2 <> rs(sDBField2) Then
    					' if the contents of field 2 changed, then
    					' close the previous entry for sArray2
    					If Right(sArray2, 3) = "," & vbCrlf Then
    						' pull off any trailing commas
    						sArray2 = Left(sArray2, Len(sArray2) - 3)
    					End If
    					If Len(sArray2) > 0 Then
    						sArray2 = sArray2 & ");" & vbCrlf
    					End If
    					' pick up new information for this row...
    					sLastVal2 = rs(sDBField2)
    					nField2 = nField2 + 1
    					' write a new entry in array1 containing this set of field 2 values...
    					sArray1 = sArray1 & "    " & 1000 * nField1 + nField2 & ",""" & sLastVal2 & """," & vbCrlf
    					' write a new entry in array2 for this set of field 3 values...
    					sArray2 = sArray2 & "// values for " & sLastVal2 & vbCrlf
    					sArray2 = sArray2 & "array2[" & 1000 * nField1 + nField2 & "] = new Array(" & vbCrlf
    				End If
    				' write the field3 values to the field2 array...
    				sArray2 = sArray2 & "    " & rs(sDBFieldResult) & ",""" & rs(sDBField3) & """," & vbCrlf
    				rs.MoveNext ' move on to the next record...
    			Loop
    
    			' if these arrays weren't previously closed out, then close them now
    			If Right(sArray2, 3) = "," & vbCrlf Then
    				' pull off any trailing commas
    				sArray2 = Left(sArray2, Len(sArray2) - 3)
    			End If
    			If Len(sArray2) > 0 Then
    				sArray2 = sArray2 & ");" & vbCrlf
    			End If
    			' if these arrays weren't previously closed out, then close them now
    			If Right(sArray1, 3) = "," & vbCrlf Then
    				' pull off any trailing commas
    				sArray1 = Left(sArray1, Len(sArray1) - 3)
    			End If
    			If Len(sArray1) > 0 Then
    				sArray1 = sArray1 & ");" & vbCrlf
    			End If
    			' close out the listbox/combobox and add the second and third listbox/combobox entries...
    			sSelect = sSelect & "</SELECT>" & vbCrlf
    			sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "2"" SIZE=""" & nSize & _
    				""" ONCHANGE=""return(clickcombo(2,document.forms[0]." & sFormFieldName & _
    				"1,document.forms[0]." & sFormFieldName & "2,document.forms[0]." & _
    				sFormFieldName & "3));""><OPTION>Nothing Selected</OPTION></SELECT>" & vbCrlf
    			sSelect = sSelect & "<SELECT NAME=""" & sFormFieldName & "3"" SIZE=""" & nSize & _
    				"""><OPTION>Nothing Selected</OPTION></SELECT>" & vbCrlf
    			' finally clean up the script and write the whole schmear out as a block
    			sScript = sSelect & vbCrlf & _
    				sScript & vbCrlf & _
    				sArray1 & vbCrlf & _
    				sArray2 & vbCrlf & _
    				"</SCR" & "IPT>" & vbCrlf
    		' -----------------------------------------------------------------
    		End If
    		' close and free the recordset
    		rs.Close
    		Set rs = Nothing
    	End If
    	' and get the fleep outta here
    	TripleLinkedList = sScript
    End Function
    %>
    can any one please help!!!!
    Attached Files Attached Files

  2. #2
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    25
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It's all in the query. What problems are you having or do you just not know where to start?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •