Need help with URL variable in Select statement

Hi Lionstone,

Thanks for all the help! But i'm still getting errors.

If you visit
http://www.logitech.uk.com/css/applicationequipment.asp?application=semiconductors

I am still getting the error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'='.

/css/applicationequipment.asp, line 22

After your changes, the complete code for my SQL query is as follows:

<%
Dim Equipment__MMColParam
Equipment__MMColParam = "1"
If (Request.QueryString("application") <> "") Then
' Use brackets around the column name to prevent SQL injection. Limit
column name length.
Equipment__MMColParam = "[" &
Replace(Left(Request.QueryString("application"),255),"]","]]") & "]"
End If
%>
<%
Dim Equipment
Dim Equipment_numRows
Set Equipment = Server.CreateObject ("ADODB.Recordset")
Equipment.ActiveConnection = MM_LogiContent_SQL_STRING
Equipment.Source = "SELECT * FROM dbo.Product WHERE " & Equipment_MMColParam
& " = 1"
Equipment.CursorType = 0
Equipment.CursorLocation = 2
Equipment.LockType = 1
Equipment.Open()
Equipment_numRows = 0
%>

I forgot which function I used first and had the arguments backward:

Equipment__MMColParam = "[" &
Replace(Left(Request.QueryString("application"),255),"]","]]") & "]"

> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
> [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
> near '='.

You'll get this error if there's no value for QueryString("application").

Hi Lionstone,

Thanks for this. However, I can't seem to get ti to work. When I place your
code in my page and run it, I get the following error:

Microsoft VBScript runtime error '800a01c2'
Wrong number of arguments or invalid property assignment: 'Replace'
/css/applicationequipment.asp, line 10

I have tried removing the part about the SQL injection to see if I can get
ti to run without the 'replace' code so that line 10 reads:

Equipment__MMColParam = Request.QueryString("application")

but get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near
'='.

/css/applicationequipment.asp, line 23



Any ideas? I'm running DW8, SQL2000, ASP VBScript.

Thanks,

Darren




"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e938n2$bde$1@forums.macromedia.com...
> Using the same object names, here's how you'd use a traditional recordset:
>
> <%
> Dim Equipment__MMColParam
> Equipment__MMColParam = "1"
> If (Request.QueryString("application") <> "") Then
> ' Use brackets around the column name to prevent SQL injection. Limit
> column name length.
> Equipment__MMColParam = "[" &
> Left(Replace(Request.QueryString("application"),255),"]","]]") & "]"
> End If
> %>
> <%
> Dim Equipment
> Dim Equipment_numRows
>
> Set Equipment = Server.CreateObject ("ADODB.Recordset")
> Equipment.ActiveConnection = MM_LogiContent_SQL_STRING
> Equipment.Source = "SELECT * FROM dbo.Product WHERE " &
> Equipment_MMColParam & " = 1"
> Equipment.CursorType = 0
> Equipment.CursorLocation = 2
> Equipment.LockType = 1
> Equipment.Open()
> Equipment_numRows = 0
> %>
>
> You will still be able to use repeat regions, etc, just like any other
> recordset.
>
> "Mintyman" <mintyman@ntlworld.com> wrote in message
> news:e92t48$pvl$1@forums.macromedia.com...
>> Sure......i'm always keen to learn the code behind the frontend :o) Fire
>> away...........
>>
>> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
>> news:e92sv0$pqv$1@forums.macromedia.com...
>>> You can't do this with the kind of recordset that the 8.02 update uses.
>>> Are you willing to write your own and forego editing the recordset via
>>> the GUI?
>>>
>>> "Mintyman" <mintyman@ntlworld.com> wrote in message
>>> news:e92qca$mji$1@forums.macromedia.com...
>>>> Hi,
>>>>
>>>> I want to create a recordset that will return all the rows in a table
>>>> where a column (that is passed by URL variable) is equal to 1.
>>>>
>>>> What I have at the moment is a query that will do this where the column
>>>> called 'Semiconductors' is equal to 1. What I need to do is change the
>>>> statement so that it will query the database where the column called
>>>> (URL VARIABLE called 'application' GOES IN HERE) is equal to 1.
>>>>
>>>>
>>>> <%
>>>> Dim Equipment__MMColParam
>>>> Equipment__MMColParam = "1"
>>>> If (Request("MM_EmptyValue") <> "") Then
>>>> Equipment__MMColParam = Request("MM_EmptyValue")
>>>> End If
>>>> %>
>>>> <%
>>>> Dim Equipment
>>>> Dim Equipment_cmd
>>>> Dim Equipment_numRows
>>>>
>>>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>>>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>>>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>>>> Semiconductors = ?"
>>>> Equipment_cmd.Prepared = true
>>>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>>>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>>>
>>>> Set Equipment = Equipment_cmd.Execute
>>>> Equipment_numRows = 0
>>>> %>
>>>>
>>>> I have tried changing it to :
>>>>
>>>> <%
>>>> Dim Equipment__MMColParam
>>>> Equipment__MMColParam = "1"
>>>> If (Request("MM_EmptyValue") <> "") Then
>>>> Equipment__MMColParam = Request("MM_EmptyValue")
>>>> End If
>>>> %>
>>>> <%
>>>> Dim Equipment
>>>> Dim Equipment_cmd
>>>> Dim Equipment_numRows
>>>>
>>>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>>>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>>>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>>>> Request.Querystring("application") = ?"
>>>> Equipment_cmd.Prepared = true
>>>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>>>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>>>
>>>> Set Equipment = Equipment_cmd.Execute
>>>> Equipment_numRows = 0
>>>> %>
>>>>
>>>> Can anyone help me with this?
>>>>
>>>
>>>
>>
>>
>
>

Using the same object names, here's how you'd use a traditional recordset:

<%
Dim Equipment__MMColParam
Equipment__MMColParam = "1"
If (Request.QueryString("application") <> "") Then
' Use brackets around the column name to prevent SQL injection. Limit
column name length.
Equipment__MMColParam = "[" &
Left(Replace(Request.QueryString("application"),255),"]","]]") & "]"
End If
%>
<%
Dim Equipment
Dim Equipment_numRows

Set Equipment = Server.CreateObject ("ADODB.Recordset")
Equipment.ActiveConnection = MM_LogiContent_SQL_STRING
Equipment.Source = "SELECT * FROM dbo.Product WHERE " & Equipment_MMColParam
& " = 1"
Equipment.CursorType = 0
Equipment.CursorLocation = 2
Equipment.LockType = 1
Equipment.Open()
Equipment_numRows = 0
%>

You will still be able to use repeat regions, etc, just like any other
recordset.

"Mintyman" <mintyman@ntlworld.com> wrote in message
news:e92t48$pvl$1@forums.macromedia.com...
> Sure......i'm always keen to learn the code behind the frontend :o) Fire
> away...........
>
> "Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
> news:e92sv0$pqv$1@forums.macromedia.com...
>> You can't do this with the kind of recordset that the 8.02 update uses.
>> Are you willing to write your own and forego editing the recordset via
>> the GUI?
>>
>> "Mintyman" <mintyman@ntlworld.com> wrote in message
>> news:e92qca$mji$1@forums.macromedia.com...
>>> Hi,
>>>
>>> I want to create a recordset that will return all the rows in a table
>>> where a column (that is passed by URL variable) is equal to 1.
>>>
>>> What I have at the moment is a query that will do this where the column
>>> called 'Semiconductors' is equal to 1. What I need to do is change the
>>> statement so that it will query the database where the column called
>>> (URL VARIABLE called 'application' GOES IN HERE) is equal to 1.
>>>
>>>
>>> <%
>>> Dim Equipment__MMColParam
>>> Equipment__MMColParam = "1"
>>> If (Request("MM_EmptyValue") <> "") Then
>>> Equipment__MMColParam = Request("MM_EmptyValue")
>>> End If
>>> %>
>>> <%
>>> Dim Equipment
>>> Dim Equipment_cmd
>>> Dim Equipment_numRows
>>>
>>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>>> Semiconductors = ?"
>>> Equipment_cmd.Prepared = true
>>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>>
>>> Set Equipment = Equipment_cmd.Execute
>>> Equipment_numRows = 0
>>> %>
>>>
>>> I have tried changing it to :
>>>
>>> <%
>>> Dim Equipment__MMColParam
>>> Equipment__MMColParam = "1"
>>> If (Request("MM_EmptyValue") <> "") Then
>>> Equipment__MMColParam = Request("MM_EmptyValue")
>>> End If
>>> %>
>>> <%
>>> Dim Equipment
>>> Dim Equipment_cmd
>>> Dim Equipment_numRows
>>>
>>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>>> Request.Querystring("application") = ?"
>>> Equipment_cmd.Prepared = true
>>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>>
>>> Set Equipment = Equipment_cmd.Execute
>>> Equipment_numRows = 0
>>> %>
>>>
>>> Can anyone help me with this?
>>>
>>
>>
>
>

Sure......i'm always keen to learn the code behind the frontend :o) Fire
away...........

"Lionstone" <HIDElionstone@HIDEhushmail.com> wrote in message
news:e92sv0$pqv$1@forums.macromedia.com...
> You can't do this with the kind of recordset that the 8.02 update uses.
> Are you willing to write your own and forego editing the recordset via the
> GUI?
>
> "Mintyman" <mintyman@ntlworld.com> wrote in message
> news:e92qca$mji$1@forums.macromedia.com...
>> Hi,
>>
>> I want to create a recordset that will return all the rows in a table
>> where a column (that is passed by URL variable) is equal to 1.
>>
>> What I have at the moment is a query that will do this where the column
>> called 'Semiconductors' is equal to 1. What I need to do is change the
>> statement so that it will query the database where the column called (URL
>> VARIABLE called 'application' GOES IN HERE) is equal to 1.
>>
>>
>> <%
>> Dim Equipment__MMColParam
>> Equipment__MMColParam = "1"
>> If (Request("MM_EmptyValue") <> "") Then
>> Equipment__MMColParam = Request("MM_EmptyValue")
>> End If
>> %>
>> <%
>> Dim Equipment
>> Dim Equipment_cmd
>> Dim Equipment_numRows
>>
>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>> Semiconductors = ?"
>> Equipment_cmd.Prepared = true
>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>
>> Set Equipment = Equipment_cmd.Execute
>> Equipment_numRows = 0
>> %>
>>
>> I have tried changing it to :
>>
>> <%
>> Dim Equipment__MMColParam
>> Equipment__MMColParam = "1"
>> If (Request("MM_EmptyValue") <> "") Then
>> Equipment__MMColParam = Request("MM_EmptyValue")
>> End If
>> %>
>> <%
>> Dim Equipment
>> Dim Equipment_cmd
>> Dim Equipment_numRows
>>
>> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
>> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
>> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
>> Request.Querystring("application") = ?"
>> Equipment_cmd.Prepared = true
>> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
>> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>>
>> Set Equipment = Equipment_cmd.Execute
>> Equipment_numRows = 0
>> %>
>>
>> Can anyone help me with this?
>>
>
>

You can't do this with the kind of recordset that the 8.02 update uses.
Are you willing to write your own and forego editing the recordset via the
GUI?

"Mintyman" <mintyman@ntlworld.com> wrote in message
news:e92qca$mji$1@forums.macromedia.com...
> Hi,
>
> I want to create a recordset that will return all the rows in a table
> where a column (that is passed by URL variable) is equal to 1.
>
> What I have at the moment is a query that will do this where the column
> called 'Semiconductors' is equal to 1. What I need to do is change the
> statement so that it will query the database where the column called (URL
> VARIABLE called 'application' GOES IN HERE) is equal to 1.
>
>
> <%
> Dim Equipment__MMColParam
> Equipment__MMColParam = "1"
> If (Request("MM_EmptyValue") <> "") Then
> Equipment__MMColParam = Request("MM_EmptyValue")
> End If
> %>
> <%
> Dim Equipment
> Dim Equipment_cmd
> Dim Equipment_numRows
>
> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
> Semiconductors = ?"
> Equipment_cmd.Prepared = true
> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>
> Set Equipment = Equipment_cmd.Execute
> Equipment_numRows = 0
> %>
>
> I have tried changing it to :
>
> <%
> Dim Equipment__MMColParam
> Equipment__MMColParam = "1"
> If (Request("MM_EmptyValue") <> "") Then
> Equipment__MMColParam = Request("MM_EmptyValue")
> End If
> %>
> <%
> Dim Equipment
> Dim Equipment_cmd
> Dim Equipment_numRows
>
> Set Equipment_cmd = Server.CreateObject ("ADODB.Command")
> Equipment_cmd.ActiveConnection = MM_LogiContent_SQL_STRING
> Equipment_cmd.CommandText = "SELECT * FROM dbo.Product WHERE
> Request.Querystring("application") = ?"
> Equipment_cmd.Prepared = true
> Equipment_cmd.Parameters.Append Equipment_cmd.CreateParameter("param1",
> 11, 1, -1, Equipment__MMColParam) ' adBoolean
>
> Set Equipment = Equipment_cmd.Execute
> Equipment_numRows = 0
> %>
>
> Can anyone help me with this?
>