Solved

SQL Syntax within Dreamweaver Recordset

Posted on 2011-03-18
8
467 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 143

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

739 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