fnDataSELECT() DESCRIPTION This function returns an HTML string which is a data-bound HTML SELECT box. The box is bound to a query string (or table name or stored procedure string) which is passed to the function. The Select Box is only 'bound' in the sense that the elements and values of its list are derived from a SQL (Structured Query Language) query which is submitted to a database. The Select-Box is not 'bound' in the sense that changes made to the state of the Select-Box would be submitted to the database. SYNTAX <%= fnDataSELECT(adoConnection, sLookupString, [sValueField], [sDisplayField], [sSelectedValue], [sName], [aaConnect], [bHasBlank]) %> EXAMPLE <%@ Language=VBScript %> <% Response.Buffer = true %>

<% '-->-->-->-->-->-->-->-->--> dim adoConTest dim aaCon(2,5) dim aa(7) dim iTest dim sTest iTest = 4 sTest = "hello test" aa(0) = "2" aa(1) = "4" aaCon(0, 1) = "0 1" aaCon(0, 2) = "0 2" aaCon(0, 3) = "mjb" aaCon(0, 4) = "mjbData" aaCon(1, 1) = "203.62.157.199" aaCon(1, 2) = "mjb" aaCon(1, 3) = "mjb" aaCon(1, 4) = "mjbData" sConn = "DRIVER={sql server};SERVER=(local)" set adoCon = Server.CreateObject("ADODB.connection") adoCon.Open sConn, "sa", "" adoCon.DefaultDatabase = "pubs" set Rs = server.CreateObject("Adodb.Recordset") rs.ActiveConnection = adoCon 'sQ = "select * from tblratestran" 'sQ= "select * from coldfusetoasp" 'Rs.Open(sQ) Response.Write fnDataSELECT(adoCon, "publishers") & "
" Response.Write fnDataSELECT(adoCon, "authors") & "
" Response.Write fnDataSELECT(adoCon, "titles") & "
" '--<--<--<--<--<--<--<--<--< %>

RESULTS An Html 'Drop Down Box' should be displayed in a web page. The contents of the Drop-Down-Box (that is the items which make up the elements of the drop down list) should be extracted from the relevent database using the query SQL (Structured Query Language) query which was specified as one of the parameters. PARAMETERS adoConnection- an open ADO database connection. This allows connection pooling. If there is no open DB connection then the sDatabaseDSN parameter can be used instead. CHANGE BELOW TO A STRING PARAMETER sLookupString- {String} This is the SQL query string that provides the values and display elements for the list box. Alternatively, this string can consist of the name of a table or stored procedure (with or without parameters) in the database. [sValueField]-{String} This is the field that supplies the values for the VALUE attribute of the select box- which are submitted with the form. (Default: First Field) [sDisplayField]- This field supplies the elements/ strings which the user will see in the select box. [sSelectedValue]- The item in the select box which will be initially selected when the page loads. [sName]- The value for the NAME attribute of the select box [aaConnect]- an array of information which is used to connect to the database using ADO aaConnect[0] {string} The database driver (Default 'sql server') aaConnect[1] {string} The IP address of the database server (Default '(local)') aaConnect[2] {string} A valid user name for the database (Default 'sa') aaConnect[3] {string} A valid password for the User Name (Default 'max') aaConnect[4] {string} The database Name (Default- the default database) [bHasBlank] {boolean, default=true} determines whether the drop down box will have a blank element or not (NOTE: This parameter will change to an sFirstElement parameter STATUS This function appears to be working (bhasblank not implemented) but occasionally seems to have bugs as well. It doesn't really matter anymore considering that no one with any amount of sense it going to use ASP functions in this day and age. DEPENDENCIES fnErrorMessage (DebugUtilities.asp) fnConvertArray(), fnCheckForField() LAST MODIFIED 11 April 2000 PROGRAMMER Matthew Bishop matth3wbishop@yahoo.com LOCATION http://www.geocities.com/matth3wbishop/eg/asp/ SEE ALSO: fnDataRADIO(), fnDisplayRecordset(), COMMENTS: This was probably one of the few useful functions which I managed to write in Active Server Pages. The process of creating a 'drop down box' or Select Box whose list elements were filled from the results of a query on a database, was a very common task at the time I was writing these functions. It was surprising how difficult it was to find on the internet any set of functions that would do this, considering how basic an operation it seems. My function is decidedly dependent on the MS Sql Server database. In other words, in order to use a different database it would be necessary to recode this function (not from scratch though). The parameter fields are badly designed, I think, because of the aaConnect array parameter. I do not think this parameter is really necessary. It would be simpler just to assume that the programmer could create his/ her own database connection before calling this function. It would be very rare for this not to be the case. I named the function fnDataSELECT in order to try to get across that the 'SELECT' refered to an Html Select box. This is probably not a very good name. The fnDataRADIO function works in a almost identical manner to the current function. I am not sure that I ironed out all the bugs in the fnDataRADIO function. I probably attempted to pack too much 'customizability' into this function because bugs would keep popping up and the code became quite long. Perhaps the only valuable idea that this function contains is that of returning a string value rather than writing the result to the ASP page (with a 'response.write' or some such rubbish). This idea will probably appear blinding obvious to just about everybody but I was always surprised when looking at other peoples code how their 'functions' would write directly to the 'output device'. I was originally intending to write a 'all-bells-and-wistles' database table interface function, which get the data from a record in a given database table, create html form elements (in the form of output strings) and also provide a mechanism for updating the record in the database table, by, for example, using javascript to validate the form values, etc. However, I never got round to writing this function. This function was written at a time when I had very little experience with programming (apart from child-hood hacking in c and qbasic). I didn't really understand the whole client/server idea, nor internet protocols, nor unix ideas, nor anything.