VB to ASP

[rtshort]

I'm not really sure which forum this belongs in so I'll put it here. Seems fitting to me.

I, with the help of most of you here and other forums, developed an app in VB. It's working OK, could use a little touching up here and there, but the "boss" is tickled to death with it. Of course if he's happy so am I. Now he wants it to be a web app. I've started reading a little on ASP and VBScript. I asked the question here about weather to use Front Page or Interdev. After everyone recommending Interdev I leaned towards Front Page out of "fear" I guess. Now I think I like Interdev better, so I'm starting to use it.

I'm wondering if someone could point me in the right direction of a few commands to use to accomplish what I need to over the web.

1) In my VB app I passed SQL Queries, depending on the user selection, to Hidden Text Boxes on the next form.

2) When the parts list loads in a list box and is "double clicked" I have a flex grid with just 2 columns to list part names from the above SQL query (1). The same part name has different types of parts. (OEM, AfterMarket, Re/man, & etc.) When the PartName in that grid is "double clicked" it opens another small form with yet another Flex Grid to show the Part choosen with the different prices for the type (OEM &etc). When the user double clicks on the part in the open form grid, it adds it to the main grid on the original form. The main grid is in the center of the form. It holds PartPrice PartNumber, PartName, Labor, Materials, & etc.

3) When the main form is finished, I write all of the grid info to a database.

I need a little direction to get started doing something similar with ASP and VBScript. I've read about both. The VBScript I can handle, I think, and the ASP I've started learning. I'm confused as to how to accomplish what I did in VB with forms on the web. How to load them or whatever.

Well, I think I've rambled enough.

TIA

[monte96]

First off, Interdev is the way to go. Frontpage is really designed for casual users not developers.

Ok.. it's slightly complicated but I'll see if I can explain it. What I think you are looking for is dynamic listboxes/comboboxes.

Make a selection on drop down #1 and it changes the contents of drop down #2. Make a selection on #2 and it changes the contents of #3 and so on. I do this sort of thing alot.

There are two approaches. Which one to use is based on:
a) The connection that your users will be viewing the page on. i.e. How fast. If you have all 56k Users it changes the dynamics a bit.
b) How much data you have to display. If you have thousands of records, the only viable way is to refresh the page from the server (Method A).
c) Your comfort level with Javascript since one of the methods involve some fairly advanced JScript.


That said.. here's method A:
Create a javascript function that recalls the page, passing the value(s) in the select tags back to itself. On the page, check for these passed values that should only be present under these circumstances. (use Request.Querystring("myselectvalue") ) If the value(s) are present, change the where clause for the query that generates the data for the 2nd/3rd whatever select tags.
The downside to this approach is the page has to be reloaded. Downsides:
o Another roundtrip to the server.
o User will see the screen 'blink' when it is reloaded.
Advantages:
o HTML that is generated is small so slow connections won't choke on a large page.
o Ideal for large data sets since the data is all kept server side except what is being used in the page.

Method B is in the next post..

[monte96]

Method B: (My preference)
First, you have to store all of your data in the page itself in an array. This means the data is sent to the client. Which is why I don't recommend this method for use with large data sets.

First, you have to have a field in each query that creates a relationship to the next one. Like for instance:

query1:
Select Name, partid From PartsTable

query2:
Select Name, partid, parentpartid From PartsTable

Where parentpartid in query2 matches a partid from query1. This way you can link them later on.

Then I create server side arrays to hold my data sets using the rs.GetRows() method. Then once I have all the sets in arrays, I use Response.write to create client side javascript code with server side script:

<html>
<head>
<title>
	Employee Time Reports
</title>
<%
	arrTemp = rsProjects.GetRows
	
	Response.Write "<SCRIPT ID=clientEventHandlersJS LANGUAGE=javascript>" & vbCrLf
	Response.Write "<!--" & vbCrLf
	Response.Write "var arrProject = new Array();" & vbCrLf
	Response.Write "var arrEng = new Array();" & vbCrLf
	
	For intRow = 0 To Ubound(arrTemp, 2)
		Response.Write "arrProject[" & intRow & "] = new Array(" & UBound(arrTemp, 1) & ");" & vbCrLf
		For intCol = 0 To UBound(arrTemp, 1)
			Response.Write "arrProject[" & intRow & "][" & intCol & "] = " & Chr(34) & arrTemp(intCol, intRow) & Chr(34) & ";" & vbCrLf
		Next
	Next
	Response.Write "</script>"
%>

Basically repeat this process for query #2.

Then create your page and populate the select tags using the arrays as well. (You can use the recordsets to do this too but once you have the data in arrays, you can close the recordset and set it to nothing and just use the arrays throughout the page)

In your select tag, include an event handler:

<select name=MySelectList onChange="populateNextList();">

Then in a client side script block, you will create a function that uses the client side arrays that we created earlier using Response.write to populate the next select list using that foriegn key to determine which ones to include:

function populateNextList()
{
	var colOptions = frmSelect.lstClients.options;
	var colTarget = frmSelect.lstProjects.options;
	var colMid = frmSelect.lstEngagements.options;
	var intArr;
	var intList;
	var objOption;
	
	//Populate Projects
	//First Clear Project List
	for (intList=colTarget.length - 1; intList > -1; intList--)//It's important to do this from the last item and remove backwards
		colTarget.remove(intList);
	
	if ((colOptions[0].value=='ALL' && colOptions[0].selected) && (colMid[0].value=='ALL' && colMid[0].selected))
	{
		//If ALL is seleted, then populate all projects
		objOption = document.createElement("OPTION");
		colTarget.add(objOption);
		objOption.innerText='All';
		objOption.value='ALL';
		for (intArr=0; intArr < arrProject.length; intArr++)
		{
			objOption = document.createElement("OPTION");
			colTarget.add(objOption);
			objOption.innerText=arrProject[intArr][1];
			objOption.value=arrProject[intArr][0];
		}
	}
	else if ((colOptions[0].value=='ALL' && !(colOptions[0].selected)) && (colMid[0].value=='ALL' && colMid[0].selected))
	{
		//Need to check for the client only
		objOption = document.createElement("OPTION");
		colTarget.add(objOption);
		objOption.innerText='All';
		objOption.value='ALL';
		for (intArr=0; intArr < arrProject.length; intArr++)
		{
			for (intList=0; intList < colOptions.length; intList++)
			{
				if (arrProject[intArr][3]==colOptions[intList].value && colOptions[intList].selected)
				{
					objOption = document.createElement("OPTION");
					colTarget.add(objOption);
					objOption.innerText=arrProject[intArr][1];
					objOption.value=arrProject[intArr][0];
				}
			}
		}
	}
	else
	{	
		objOption = document.createElement("OPTION");
		colTarget.add(objOption);
		objOption.innerText='All';
		objOption.value='ALL';
		for (intArr=0; intArr < arrProject.length; intArr++)
		{
			for (intList=0; intList < colMid.length; intList++)
			{
				if (colMid[intList].selected && arrProject[intArr][2]==colMid[intList].value)//arrProject[intArr][2] is the foriegn key on the array that links it to the first query's ID which is stored as the 'value' in select #1
				{
					objOption = document.createElement("OPTION");
					colTarget.add(objOption);
					objOption.innerText=arrProject[intArr][1];
					objOption.value=arrProject[intArr][0];
				}
			}
		}
	}
	
	if (colTarget.length > 0)
		colTarget(0).selected = true;//Select the first item in the new list
	//Clean up after yourself
	colTarget	= null;
	colOptions	= null;
	colMid		= null;
	objOption	= null;	
}
</script>

That's it. It is alot more complicated that method A but it looks much nicer to the user because the page is changed all client side. This also means no extra roundtrips to the server and no blinking of the page while it refreshes.

The only downside is, storing the data client side like this makes the size of the page balloon a bit. 30k - 40k or more are not uncommon so if you have lots of 56k users, they will probably complain of slow response times on the page because it has to generate all that data on the page then send it to the clients. Not a big deal on LAN connections or other broadband connections.

[rtshort]

Thanks, Monte96. That will get me on the right track. I was confused about which way to go with it. This is also in VBGarage. I go there too.

Thanks again for taking the time to reply. I know it had to have taken some time.