Solved

SQL Syntax within Dreamweaver Recordset

Posted on 2011-03-18
8
461 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]
Comment Utility
>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
Comment Utility
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
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:grantballantyne
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

Suggested Solutions

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

772 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now