I am more than just a survey tool : Listing Records

In this article we will look at how we can display some data in a list as a menu item action. Let us pretend that we need to see all the records in a particular survey. At this stage , we will just display it , but in future articles we will show how you could update them.

So, what do we need , well we need our menu system from previous examples and we will place the code under the menu option B.

Select Case Option1Menu
	Case = {mBack}
		Goto pMainMenu
	Case = {mOption1_OptionA}
		message.Label.Inserts["MESSAGE"] = MakeIFrame("http://www.smarterDataCollection.com")
	Case = {mOption1_OptionB}
		message.Label.Inserts["MESSAGE"] = MakeDataList(gFMROOT,gSQLServer,gUserDB,"*","")
End Select

As you can see we are using our “User” survey that we created in [This article], we are passing in the fmroot folder name and the name of the SQL server. The last two paramaters are the fields to show ( astrix means all ) and then a where clause, Blank means all records. So what does the function look like,

Function MakeDataList(gFMROOT,gSQLServer,sDB,sFields,sWhere)
	Dim oConnection , oRecordset,oField
	Dim sHTML
	Set oConnection = CreateObject("ADODB.Connection")
	oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrRdbDsc2;Location='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=" + sDB + ";Data Source=" + gSQLServer + "';Initial Catalog=\\" + gFMROOT + "\Master\" + sDB + "\" + sDB + ".mdd;MR Init Category Names=1;MR Init Project=" + sDB
	If ( oConnection.State = 1 ) Then
		Set oRecordset = CreateObject("ADODB.Recordset")
		oRecordset.Open("select " + sFields + " from vdata " + sWhere ,oConnection,3,1)
			sHTML = MakeHTMLTableFromRS(oRecordset)

		Set oRecordset = Null
	End If

	Set oConnection = Null

	MakeDataList = sHTML
End Function

So what does MakeDataList actualy do , well the first thing , after the declarations is to connect to the Data Collection survey. This is done with a standard ADODB connection. Next if the conection is open we create a recordset based on the fields and where clause that we pass in. In out next article we will see how we can paramatarize this.

After we have opened the recordset we decide what we want to do with the records we have. In this case we are going to convert them to a HTML table.

Function MakeHTMLTableFromRS(oRecordSet)
Dim sHTML,oField

sHTML = "" + mr.cr
sHTML = sHTML + "" + mr.cr

For Each oField in oRecordset.Fields
	sHTML = sHTML + "" + mr.cr

sHTML = sHTML + "" + mr.cr

Do while oRecordset.EOF = false and oRecordset.bof = false
		sHTML = sHTML + "" + mr.cr
			For Each oField in oRecordset.Fields
				sHTML = sHTML + "" + mr.cr
		sHTML = sHTML + "" + mr.cr

sHTML = sHTML + "
" + oField.Name + "
" + MakeSpaceIfBlank(oField) + "
" + mr.cr MakeHTMLTableFromRS = sHTML End Function

So what does this new funtion do. First off it loops the names of the fields in the recordset and places them on the first row of the table, next it loops through all the records in the table and displays the content of each field in its own cell. Finaly it closes the table and returns the created HTML.

The key thing to notice here / think about here is that we are taking something and converting it to an HTML item. Why do we need to think about this , well perhaps rather than produce a html table of the data you wanted to produce a dropdown of one of the fields. You might say why would you do this and in later articles you will see how , but you could rather than render an HTML dropdown you could render a Dojo spinner.

Anyway back to our table. You will see in the makeHtmlTableFromRS function we call a function MakeSpaceIfBlank the content of that function is as follows.

Function MakeSpaceIfBlank(oField)
Dim sValue
	sValue = CText(oField.Value)
	if ( sValue = "" ) Then sValue = "."
MakeSpaceIfBlank = sValue
End Function

This function is simple , all it does is to convert the field value passed in to a string and if the value is nothing convert it to a full stops. In this example, it just makes the table look a little neater and could eaily convereted to return any value you like. When you run the script and select option B this is what you should see.


And there you have it, code that can be used to show on a web page the content of a survey. In the next article we will see how we can possibly take this a stage further and allow the user enter the field names and where clause.

Leave a Comment

%d bloggers like this: