ASP & SQL Question

[newmediaguy]

Hi Guys

I have a Function in an include file that searches across multiple tables to find product in formation. What i would like to do is to show a results total on the screen when a sub parameter has been chosen.

What currently is returned is the total for the initial search, when a sub select is specified this total doesnt update........

Here is the code

FUNCTION ResultValue(TPID, Requested_Field)

	'no cnet content found.
	'Response.Write("TPID: "&TPID&"<br>")
		'look for this product id in the main product table.
		set ProductTable = server.CreateObject("ADODB.Recordset")
			ProductTable.ActiveConnection = MM_tpsqltest_STRING
			ProductTable.Source = "SELECT  * FROM ProductTable WHERE TPID = '"&TPID&"'"
			ProductTable.Open()
			ProductTable_Counter = 0
			While (NOT ProductTable.EOF)
			
				'set the supplier id varialbe
				SupplierID = ProductTable("SupplierID")
			
				ProductTable_Counter = ProductTable_Counter + 1
				ProductTable.MoveNext
			Wend
				ProductTable.Close()
				
				'Response.Write("SupplierID: "&SupplierID&"<br>")
		
			'we have the supplier ID....
			'so we now need to look in the product supplierdatacolumns to get the table name.
			set the_table = server.CreateObject("ADODB.Recordset")
				the_table.ActiveConnection = MM_tpsqltest_STRING
				the_table.Source = "SELECT DISTINCT ProductTable FROM SupplierDataColumns WHERE SupplierID = '"&SupplierID&"'"
				the_table.Open()
				the_table_counter = 0
				While (NOT the_table.EOF)
				
					'we have found the table name
					DataTable = the_table("ProductTable")
				
					the_table_counter = the_table_counter + 1
					the_table.MoveNext
				Wend
					the_table.Close()
					
					
			'we need to get the related column name from the column name requested.
			set related_data = server.CreateObject("ADODB.Recordset")		
				related_data.ActiveConnection = MM_tpsqltest_STRING
				related_data.Source = "SELECT  * FROM SupplierDataColumns WHERE HigherName = '"&Requested_Field&"' AND SupplierID = '"&SupplierID&"'"
				related_data.Open()
				related_data_counter = 0
				While (NOT related_data.EOF)
				
					Output_Field = related_data("FieldName")
				
					related_data_counter = related_data_counter + 1
					related_data.MoveNext
				Wend
					related_data.Close()
					
			'get the prodid from the table.
			set getid = server.CreateObject("ADODB.Recordset")		
				getid.ActiveConnection = MM_tpsqltest_STRING
				getid.Source = "SELECT  * FROM SupplierDataColumns WHERE HigherName = 'TPID' AND SupplierID = '"&SupplierID&"'"
				getid.Open()
				While (NOT getid.EOF)
					IDColumn = getid("FieldName")
					getid.MoveNext
				Wend
					getid.Close()
					
				If ProductTable_Counter = 0 then
					Final_Output_Value = "Sorry"
				Else
					
						'we have found the column that we need to look up in the certain table to get the results required.
						set myOutput = server.CreateObject("ADODB.Recordset")	
							myOutput.ActiveConnection = MM_tpsqltest_STRING
							myOutput.Source = "SELECT  * FROM "&DataTable&" WHERE "&IDColumn&" = '"&TPID&"'"
							'Response.Write("SELECT  * FROM "&DataTable&" WHERE "&IDColumn&" = '"&TPID&"'")
							myOutput.Open()
							
							While (NOT myOutput.EOF)
							
								Final_Output_Value = myOutput(""&Output_Field&"")
							
								myOutput.MoveNext
							Wend
								myOutput.Close()
				End if
				
				'output	
				ResultValue	= Final_Output_Value


END FUNCTION

Any help or advice would be very much appreciated.

Many thanks

Glen

 

[simonchristieis]

Try writing your question so that other people can understand what you want, this isn't clear at all.

 

[newmediaguy]

Thanks Simon,

Ok The above function takes a product id and selects all associated details for that product(s).

It also returns dynamically specified search criteria:

For example a search for 'Palms'
returns:
25 Results

Also returned is
Manufacturer (Values eg Compaq, Palm)
Screen Size (values eg 4", 3")
etc

These populate select boxes. that are used to filter the results down.

What i cant do is recalculate the number of results

ALL results:25
Filtered: 10 results
But the results total doesnt recalculate?

Thanks

Glen

 

[simonchristieis]

No - still don't get it - you have 12 results and 12 is shown, or am I missing the point?

 

[newmediaguy]

no your right, but when you select a sub search eg manufacturer it returns 6 but the total shown still says 12 and not the new total of 6

 

[simonchristieis]

Dont you just need to increment this variable (ProductTable_Counter) in your secondary loops that run the sub search (myOutput ?)

 

[newmediaguy]

Thanks Simon, i think your right, let me try that

Glen

 

[simonchristieis]

Dont forget to set to 0 again.

I would personally clarify my code so that you could see what each section is doing, or dynamically create the sql statement and just use one recordset.

simplicity is always the key (or is that laziness?)

simon

 

[newmediaguy]

how very right you are.........only fools rush in, am going through it now to make it read better and cut some of the db connections down to get the performance up as it runs very slow on 'large' result sets.

Thanks again

Glen