I am more than just a survey tool : Listing Records ( Select, Update and Delete )

In our last article we designed a menu option that allowed us to see the content of a particular survey. The information was displayed to the user in an HTML table and in some cases if there are two many fields in the file then the table was large and not very usable. In this example we will take this code a stage further and allow the user to enter the select statement that they would like to use to present the data.

The first thing we need to do is to change the way the function that creates the table is called. It currently looks like this,

Case = {mOption1_OptionB}
		message.Label.Inserts["MESSAGE"] = MakeDataList(gFMROOT,gSQLServer,gUserDB,"*","")

and we need to change it to this,

Case = {mOption1_OptionB}
		message.Label.Inserts["MESSAGE"] = MakeDataList(gFMROOT,gSQLServer,gUserDB,sSQL)

and we need to change the function, also adding some on error code to capture bad sql.

Function MakeDataList(gFMROOT,gSQLServer,sDB,sSQL)
	Dim oConnection , oRecordset,oField
	Dim sHTML
On error Goto LogIt	
	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
	oConnection.Open()
	
	If ( oConnection.State = 1 ) Then
	
		Set oRecordset = CreateObject("ADODB.Recordset")
		
		oRecordset.Open(sSQL ,oConnection,3,1)
			
			sHTML = MakeHTMLTableFromRS(oRecordset)
			
		if oRecordset.State	= 1 Then
			oRecordset.Close()
		End If
		
		Set oRecordset = Null
		
	End If

Goto SkipError

LogIt:

	sHTML = Err.Description
	
SkipError:
	On Error Goto 0
	
	oConnection.Close()
	
	Set oConnection = Null

	MakeDataList = sHTML
	
End Function

Now that the function is ready we need to capture the select statement. So in your Metadata , create a question called SQL, and a menu system for this tool , make it a text box as large as you can.

SQL "SQL : " text [1..4000];

sqlToolsMenu "" categorical [1]
    {
        mBack "Back"
            style(
                Width = "7em",
                Control(
                    Type = "Button"
                )
            ),
        mSearch "Search"
            style(
                Width = "7em",
                Control(
                    Type = "Button"
                )
            )

    };

make a page item of these two as and add the message question to it also,

    psqlTools ""
    page (sqlToolsMenu,SQL,Message);

and then in your routing we need to ask the page and supply the logic to decided what happens when a button is pressed. Dont forget to declare the sSQL var at the top of your script.

Case = {mOption1_OptionB}
								
	psqlTool:
	
		sqlToolsMenu.Style.Columns = 10
		psqlTools.Ask()
		
		Select Case sqlToolsMenu
	
			Case = {mBack}		
				Goto pMainMenu
			Case = {mSearch}
														
				sSQL = SQL.Response.value
				message.Label.Inserts["MESSAGE"] = MakeDataList(gFMROOT,gSQLServer,gUserDB,sSQL)
				Goto psqlTool
		End Select				

and there we have it , you can specify a sql statement to look at all or some of the records in your survey whilst they are still on the server. If you want to use this code to update and delete recors you will need to make the following change to the “MakeHTMLTableFromRS” fucntion. It will now need to look like this,

Function MakeHTMLTableFromRS(oRecordSet)
Dim sHTML,oField

On Error Goto LogIt

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

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

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
			Next
		sHTML = sHTML + "" + mr.cr
	oRecordset.MoveNext()
Loop

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

Goto SkipError

LogIt:

	sHTML =  "No Records"
	
SkipError:
	On Error Goto 0

MakeHTMLTableFromRS = sHTML

End Function

And that is it. You now have a web version of DMQuery that will allow you to look , edit and remove your survey records if you so wish.