Solved

SQL Syntax within Dreamweaver Recordset

Posted on 2011-03-18
8
463 Views
Last Modified: 2012-05-11
Dear Experts

I have a dreamweaver recordset (ASP , VbScript) as follows:

'SELECT * FROM dbo.menu_categories WHERE subcategory LIKE ?'

I have a problem with the following subcategory values:  

Forage Seeds
Grass Seeds

The problem is that the recordset is returning records for both.  I guess this is due to the 'LIKE' in the SQL statement.

Can anyone suggest a way around this.  Using '=' does not return any values.

Thanks
0
Comment
Question by:grantballantyne
8 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35164096
>The problem is that the recordset is returning records for both.
well, what value do you pass as argument?
and what do you expect to get as output?
0
 

Author Comment

by:grantballantyne
ID: 35164107
thanks angel,

see code below for the recordset:

<%
Dim categories__MMColParam
categories__MMColParam = "1"
If (Request.QueryString("subcategory") <> "") Then
  categories__MMColParam = Request.QueryString("subcategory")
End If
%>



<%
Dim categories
Dim categories_cmd
Dim categories_numRows

Set categories_cmd = Server.CreateObject ("ADODB.Command")
categories_cmd.ActiveConnection = MM_borderfarmsupplies_STRING
categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory like ? ORDER BY linksupplierrange ASC"
categories_cmd.Parameters.Append categories_cmd.CreateParameter("param1", 200, 1, 30, "%" + categories__MMColParam + "%") ' adVarChar
categories_cmd.Prepared = true

Set categories = categories_cmd.Execute
categories_numRows = 0
%>

thanks
0
 
LVL 4

Expert Comment

by:coolcurrent4u
ID: 35164115
This

'SELECT * FROM dbo.menu_categories WHERE subcategory LIKE %Forage%'

will return all results that has Forage in the subcategory names

and this

'SELECT * FROM dbo.menu_categories WHERE subcategory LIKE %Grass%'

for the other subcategories
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35164118

If you want any perticular record then query must be like this

SELECT * FROM dbo.menu_categories WHERE subcategory='Forage Seeds'

I think you need something like this

categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory =" & categories__MMColParam "' ORDER BY linksupplierrange ASC"
0
 

Author Comment

by:grantballantyne
ID: 35164142
thanks

I have changed the recordset to the following:

<%
Dim categories__MMColParam
categories__MMColParam = "1"
If (Request.QueryString("subcategory") <> "") Then
  categories__MMColParam = Request.QueryString("subcategory")
End If
%>



<%
Dim categories
Dim categories_cmd
Dim categories_numRows

Set categories_cmd = Server.CreateObject ("ADODB.Command")
categories_cmd.ActiveConnection = MM_borderfarmsupplies_STRING
categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory =" & categories__MMColParam "' ORDER BY linksupplierrange ASC"
categories_cmd.Parameters.Append categories_cmd.CreateParameter("param1", 200, 1, 30, "%" + categories__MMColParam + "%") ' adVarChar
categories_cmd.Prepared = true

Set categories = categories_cmd.Execute
categories_numRows = 0
%>


however this produces the error as follows:

Microsoft VBScript compilation error '800a0401'

Expected end of statement

/countrystores/categorypage.asp, line 48

categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory =" & categories__MMColParam "' ORDER BY linksupplierrange ASC"
--------------------------------------------------------------------------------------------------------------------^


thanks
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35164155
change it like this

categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory =" & categories__MMColParam  & "' ORDER BY linksupplierrange ASC"
0
 

Author Comment

by:grantballantyne
ID: 35164180
thanks

i have updated the recordset as follows:

<%
Dim categories__MMColParam
categories__MMColParam = "1"
If (Request.QueryString("subcategory") <> "") Then
  categories__MMColParam = Request.QueryString("subcategory")
End If
%>



<%
Dim categories
Dim categories_cmd
Dim categories_numRows

Set categories_cmd = Server.CreateObject ("ADODB.Command")
categories_cmd.ActiveConnection = MM_borderfarmsupplies_STRING
categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory =" & categories__MMColParam  & "' ORDER BY linksupplierrange ASC"
categories_cmd.Parameters.Append categories_cmd.CreateParameter("param1", 200, 1, 30, "%" + categories__MMColParam + "%") ' adVarChar
categories_cmd.Prepared = true

Set categories = categories_cmd.Execute
categories_numRows = 0
%>

however I am now getting the following error when passing the perameter 'Grass Seeds'

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][SQL Server Native Client 10.0][SQL Server]Incorrect syntax near 'Seeds'.

/countrystores/categorypage.asp, line 52


thanks again

0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 35164190

categories_cmd.CommandText = "SELECT * FROM dbo.subcategory_links WHERE linksubcategory ='" & categories__MMColParam  & "' ORDER BY linksupplierrange ASC"
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question