Problem Parameterizing SQL Query

[billmack30]

Having an issue parameterizing an SQL command.

Backround-
Our website is made up of 3 sections. At any time 0,1,2, or 3 sections could be down for "Maintenance, etc."

The 3 sections are "Football", "Baseball", and "Basketball"

We keep the site status information in a table with the following columns (not designed the best way, but I didn't make it)

Football (have a value of either 'O' for Open or 'C' for Closed)
Baseball (have a value of either 'O' for Open or 'C' for Closed)
Basketball (have a value of either 'O' for Open or 'C' for Closed)
Football_Time (expected time for site to be open if closed)
Baseball_Time (expected time for site to be open if closed)
Basketball_Time (expected time for site to be open if closed)
Football_Msg (message to be displayed if site is closed)
Baseball_Msg (message to be displayed if site is closed)
Basketball_Msg (message to be displayed if site is closed)

The original way we got the site status information was:
sql = "SELECT " & thesite & " AS Status," & thesite & "_Time AS theTime, " & thesite & "_Msg FROM SITESTATUS"

for example
Call SiteStatus("Football") would produce the sql:
"Select Football as Status, Football_Time as thetime, Football_Msg From Sitestatus"

we would have a recordset return
rs("Status") = O or C
rs("thetime") = time site was to be back up

If status is O do nothing, if C display message with time and msg

My job is to secure all queries so I need to parameterize the sql statement.

So I wrote:

sql=""SELECT ? AS Status, ? AS theTime, ? FROM SiteStatus"

Set cmdObj = Server.CreateObject("ADODB.Command")
cmdObj.ActiveConnection = Conn
cmdObj.CommandType = adCmdText
cmdObj.Commandtext = sql
cmdObj.Parameters.Append(cmdObj.Createparameter("@THESITE",adChar,adParamInput,10,thesite))
cmdObj.Parameters.append(cmdObj.Createparameter("@THETIME",adChar,adParamInput,15,thesite & "_Time"))
cmdObj.Parameters.append(cmdObj.Createparameter("@THEMSG",adChar,adParamInput,14,thesite & "_Msg"))
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 'adUseClient
rs.CursorType = 0 'adOpenForwardOnly
rs.LockType = 1 'adLockReadOnly
Set rs = cmdObj.Execute

The values I get back from the recordset are:
rs("Status") = Football (supposed to be O or C)
rs("thetime") = Football_Time (supposed to be datetime)

So now the test:
status = rs("Status")
IF TRIM(Status)="O" THEN
EXIT SUB
ELSE
//DISPLAY MESSAGE
END IF

Always displays the message even if the site is open. Any ideas?

 

[anon007]

Try creating a bit colum to showing the status for the open and close. Then use True or False or 0 or 1:

IF TRIM(Status)=True THEN
EXIT SUB
ELSE
//DISPLAY MESSAGE
END IF