Solved

SQL Syntax within Dreamweaver Recordset

Posted on 2011-03-18
8
466 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 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

713 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