problem with querystring value as a database field name in SELECT statement?

Report · Mar 18, 2008

Hi,

I'm trying to create a SELECT statement using a querystring value as one of
the field names, like this:

<%
Dim rsRouteGallery__MMColParam
rsRouteGallery__MMColParam = "1"
If (Request.QueryString("galleryimageID") <> "") Then
rsRouteGallery__MMColParam = Request.QueryString("galleryimageID")
End If
%>
<%
Dim rsRouteGallery__MMColParam1
rsRouteGallery__MMColParam1 = "%"
If (Request.QueryString("route") <> "") Then
rsRouteGallery__MMColParam1 = Request.QueryString("route")
End If
%>
<%
Dim rsRouteGallery__MMColParam2
rsRouteGallery__MMColParam2 = "1"
If (Request("MM_EmptyValue") <> "") Then
rsRouteGallery__MMColParam2 = Request("MM_EmptyValue")
End If
%>
<%
Dim rsRouteGallery
Dim rsRouteGallery_cmd
Dim rsRouteGallery_numRows

Set rsRouteGallery_cmd = Server.CreateObject ("ADODB.Command")
rsRouteGallery_cmd.ActiveConnection = MM_dsnMOFSQL_STRING
rsRouteGallery_cmd.CommandText = "SELECT galleryimageID, galleryimage,
imagetitle, imagedesc, route, moffat, scotcastles, borderloop, threelochs,
biggar, gallquickie, southcoast, thelakes, brucerun, burns, bothworlds,
northumber, tastegall, stabbshug FROM dbo.MOFthumbnails WHERE galleryimageID
<> ? AND ? = ?"
rsRouteGallery_cmd.Prepared = true
rsRouteGallery_cmd.Parameters.Append
rsRouteGallery_cmd.CreateParameter("param1", 5, 1, -1,
rsRouteGallery__MMColParam) ' adDouble
rsRouteGallery_cmd.Parameters.Append
rsRouteGallery_cmd.CreateParameter("param2", 200, 1, 255,
rsRouteGallery__MMColParam1) ' adVarChar
rsRouteGallery_cmd.Parameters.Append
rsRouteGallery_cmd.CreateParameter("param3", 5, 1, -1,
rsRouteGallery__MMColParam2) ' adDouble

Set rsRouteGallery = rsRouteGallery_cmd.Execute
rsRouteGallery_numRows = 0
%>

The "route" querystring will be one of the field names in the
dbo.MOFthumbnails table, but whenever I try it it tells me there's either:
Line 1: Incorrect syntax near '='
...or when I switch the field to type "Boolean", it says:
Application uses a value of the wrong type for the current operation.

Hope someone can offer some advice. Much appreciated.
Nath.

Report · Mar 18, 2008

Hi,

Sorry to answer my own question, but I've just tried this:

<%
Dim rsRouteGallery__MMColParam
rsRouteGallery__MMColParam = "1"
If (Request.QueryString("galleryimageID") <> "") Then
rsRouteGallery__MMColParam = Request.QueryString("galleryimageID")
End If
%>
<%
Dim rsRouteGallery__MMColParam2
rsRouteGallery__MMColParam2 = "1"
If (Request("MM_EmptyValue") <> "") Then
rsRouteGallery__MMColParam2 = Request("MM_EmptyValue")
End If
%>
<%
Dim rsRouteGallery
Dim rsRouteGallery_cmd
Dim rsRouteGallery_numRows

Set rsRouteGallery_cmd = Server.CreateObject ("ADODB.Command")
rsRouteGallery_cmd.ActiveConnection = MM_dsnMOFSQL_STRING
rsRouteGallery_cmd.CommandText = "SELECT galleryimageID, galleryimage,
imagetitle, imagedesc, route, moffat, scotcastles, borderloop, threelochs,
biggar, gallquickie, southcoast, thelakes, brucerun, burns, bothworlds,
northumber, tastegall, stabbshug FROM dbo.MOFthumbnails WHERE galleryimageID
<> ? AND "& Request.QueryString("route") &" = ?"
rsRouteGallery_cmd.Prepared = true
rsRouteGallery_cmd.Parameters.Append
rsRouteGallery_cmd.CreateParameter("param1", 5, 1, -1,
rsRouteGallery__MMColParam) ' adDouble
rsRouteGallery_cmd.Parameters.Append
rsRouteGallery_cmd.CreateParameter("param3", 5, 1, -1,
rsRouteGallery__MMColParam2) ' adDouble

Set rsRouteGallery = rsRouteGallery_cmd.Execute
rsRouteGallery_numRows = 0
%>

...and it's worked! Yay!

Thanks anyway.
Nath.

"tradmusic.com" <sales@NOSHPAMtradmusic.com> wrote in message
news:fro606$5ou$1@forums.macromedia.com...
> Hi,
>
> I'm trying to create a SELECT statement using a querystring value as one
> of the field names, like this:
>
> <%
> Dim rsRouteGallery__MMColParam
> rsRouteGallery__MMColParam = "1"
> If (Request.QueryString("galleryimageID") <> "") Then
> rsRouteGallery__MMColParam = Request.QueryString("galleryimageID")
> End If
> %>
> <%
> Dim rsRouteGallery__MMColParam1
> rsRouteGallery__MMColParam1 = "%"
> If (Request.QueryString("route") <> "") Then
> rsRouteGallery__MMColParam1 = Request.QueryString("route")
> End If
> %>
> <%
> Dim rsRouteGallery__MMColParam2
> rsRouteGallery__MMColParam2 = "1"
> If (Request("MM_EmptyValue") <> "") Then
> rsRouteGallery__MMColParam2 = Request("MM_EmptyValue")
> End If
> %>
> <%
> Dim rsRouteGallery
> Dim rsRouteGallery_cmd
> Dim rsRouteGallery_numRows
>
> Set rsRouteGallery_cmd = Server.CreateObject ("ADODB.Command")
> rsRouteGallery_cmd.ActiveConnection = MM_dsnMOFSQL_STRING
> rsRouteGallery_cmd.CommandText = "SELECT galleryimageID, galleryimage,
> imagetitle, imagedesc, route, moffat, scotcastles, borderloop, threelochs,
> biggar, gallquickie, southcoast, thelakes, brucerun, burns, bothworlds,
> northumber, tastegall, stabbshug FROM dbo.MOFthumbnails WHERE
> galleryimageID <> ? AND ? = ?"
> rsRouteGallery_cmd.Prepared = true
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param1", 5, 1, -1,
> rsRouteGallery__MMColParam) ' adDouble
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param2", 200, 1, 255,
> rsRouteGallery__MMColParam1) ' adVarChar
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param3", 5, 1, -1,
> rsRouteGallery__MMColParam2) ' adDouble
>
> Set rsRouteGallery = rsRouteGallery_cmd.Execute
> rsRouteGallery_numRows = 0
> %>
>
> The "route" querystring will be one of the field names in the
> dbo.MOFthumbnails table, but whenever I try it it tells me there's either:
> Line 1: Incorrect syntax near '='
> ...or when I switch the field to type "Boolean", it says:
> Application uses a value of the wrong type for the current operation.
>
> Hope someone can offer some advice. Much appreciated.
> Nath.
>
>
>
>
>

Report · Mar 18, 2008

> I'm trying to create a SELECT statement using a querystring value as one
> of the field names, like this:
>
> <%
> Dim rsRouteGallery__MMColParam
> rsRouteGallery__MMColParam = "1"
> If (Request.QueryString("galleryimageID") <> "") Then
> rsRouteGallery__MMColParam = Request.QueryString("galleryimageID")
> End If
> %>
> <%
> Dim rsRouteGallery__MMColParam1
> rsRouteGallery__MMColParam1 = "%"
> If (Request.QueryString("route") <> "") Then
> rsRouteGallery__MMColParam1 = Request.QueryString("route")
> End If
> %>
> <%
> Dim rsRouteGallery__MMColParam2
> rsRouteGallery__MMColParam2 = "1"
> If (Request("MM_EmptyValue") <> "") Then
> rsRouteGallery__MMColParam2 = Request("MM_EmptyValue")
> End If
> %>
> <%
> Dim rsRouteGallery
> Dim rsRouteGallery_cmd
> Dim rsRouteGallery_numRows
>
> Set rsRouteGallery_cmd = Server.CreateObject ("ADODB.Command")
> rsRouteGallery_cmd.ActiveConnection = MM_dsnMOFSQL_STRING
> rsRouteGallery_cmd.CommandText = "SELECT galleryimageID, galleryimage,
> imagetitle, imagedesc, route, moffat, scotcastles, borderloop, threelochs,
> biggar, gallquickie, southcoast, thelakes, brucerun, burns, bothworlds,
> northumber, tastegall, stabbshug FROM dbo.MOFthumbnails WHERE
> galleryimageID <> ? AND ? = ?"
> rsRouteGallery_cmd.Prepared = true
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param1", 5, 1, -1,
> rsRouteGallery__MMColParam) ' adDouble
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param2", 200, 1, 255,
> rsRouteGallery__MMColParam1) ' adVarChar
> rsRouteGallery_cmd.Parameters.Append
> rsRouteGallery_cmd.CreateParameter("param3", 5, 1, -1,
> rsRouteGallery__MMColParam2) ' adDouble
>
> Set rsRouteGallery = rsRouteGallery_cmd.Execute
> rsRouteGallery_numRows = 0
> %>
>
> The "route" querystring will be one of the field names in the
> dbo.MOFthumbnails table, but whenever I try it it tells me there's either:
> Line 1: Incorrect syntax near '='
> ...or when I switch the field to type "Boolean", it says:
> Application uses a value of the wrong type for the current operation.

http://kb.adobe.com/selfservice/viewContent.do?externalId=kb402875