• Toll-free  888-665-8637
  • International  +1 717-220-0012
Welcome Guest! To enable all features please Login or Register.

Notification

Icon
Error
Need help integrating a new search function
Options Go to last post Go to first unread
Previous Topic Next Topic
emunah
#1 Posted : Friday, April 11, 2008 12:16:42 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
I need someone to point me in the right direction. I have an alphabit a-z under my custom search function. I want that when someone clicks on a letter like c all the products that start with that letter come up. I know the code in asp would look like this
[2]
Dim strQuery, rsProducts

Set rsProducts = Server.CreateObject("ADODb.Recordset")

If Request.QueryString ("letter") <> "" Then

strQuery = "SELECT * FROM tblbvc_Product WHERE ProductName LIKE('" & Replace(Request.QueryString ("letter")&"", "'", "''") & "%')"

rsProducts.open strQuery, con, adUseClient

End If

But I am not sure where to place the code,

BVModules\Controls\Search.ascx

CBVAdmin\Configuration\Search.aspx.vb

or

BVModules\Controls\SearchCriteria.ascx.vb

and how to handle this, I am including a screen shot of the alphabit


[/2]
emunah attached the following image(s):
screenshot.JPG (118kb) downloaded 41 time(s).

You cannot view/download attachments. Try to login or register.
CorneliuTusnea
#2 Posted : Friday, April 11, 2008 10:05:18 PM(UTC)
CorneliuTusnea

Rank: Member

Joined: 8/17/2006(UTC)
Posts: 681
Emunah,
That type of code is the perfect place for some sql injection. I can find all the customers in your site and drop all your products in about 5 minutes once you implement that like that.
I would be VERY carefull writing and merging SQL statements as strings and then running them. Always use SP and paramters and don't build dynamic SQL. Simply DONT!

Ok, now to help you fix your issue:
1. Write the query in a SP and on't use dynamic SQL. Use parameters. You should have something like:
CREATE PROCEDURE usp_SearchByFirstLeter
@Letter VARCHAR(1)
AS
BEGIN
SELECT bvid FROM bvc_Product WHERE ProductName LIKE @Letter + '%'
END
GO
2. In your code call that SP to get the bvin of all your products:
Something like:

Dim request1 As New DataRequest
request1.Command = "usp_SearchByFirstLeter"
request1.CommandType = CommandType.StoredProcedure
Dim data As DataSet = SqlDataHelper.ExecuteDataSet(request1)
Dim table As DataTable = data.Tables.Item(0)

Now in table your have rows that have a single column with the bvin of your products. Foreach that and read the products via dim prod as Catalogue.Product = InternalProduct.FindByBvin ( bvin )

I hope the instructions are good enough for you to help you solve your issue. Please note I haven't tested any of this code so there might be issues.

Regards,
Corneliu.
http://www.bestgames.com.au
http://www.bestchess.com.au



BV Product Links, Details and Signatures: Improve your customer experience:

http://www.acorns.com.au/projects/bv/quicklink/
emunah
#3 Posted : Monday, April 14, 2008 8:28:22 AM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
I am sorry to ask but where do I put this? Which page do I add it to? or do I make a new page in vs for it.

Dim request1 As New DataRequest
request1.Command = "usp_SearchByFirstLeter"
request1.CommandType = CommandType.StoredProcedure
Dim data As DataSet = SqlDataHelper.ExecuteDataSet(request1)
Dim table As DataTable = data.Tables.Item(0)

Now in table your have rows that have a single column with the bvin of your products. Foreach that and read the products via dim prod as Catalogue.Product = InternalProduct.FindByBvin ( bvin )



Thanks for all your help
emunah
#4 Posted : Monday, April 14, 2008 10:38:52 AM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
here is what I have so far, I put the sp in the databse and in C:\Inetpub\wwwroot\BV Commerce 5b\BVAdmin\BVSql\CreateProcedures.sql
and now I have

dimas"InternalProduct.FindByBvin"DimAsNew"usp_SearchByFirstLeter""CommandType.StoredProcedure"
DimAs"SqlDataHelper.ExecuteDataSet(request1)"

DimAs"data.Tables.Item(0)"[2]

%>

and it is giving me error messages on catalogu.product not defined and on datarequest not defined

and dataset and datatable not defined
[/2]
jonm
#5 Posted : Monday, April 14, 2008 12:02:43 PM(UTC)
jonm

Rank: Member

Joined: 3/24/2005(UTC)
Posts: 311
I think there is a typo in your code there.

dim prod as Catalogue.Product = "InternalProduct.FindByBvin" ( bvin )

Should be

dim prod as Catalog.Product = "InternalProduct.FindByBvin" ( bvin )
-Jonathan Moore
www.tvps.com
emunah
#6 Posted : Monday, April 14, 2008 12:13:43 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
I still get an error saying bvin not defined
emunah
#7 Posted : Monday, April 14, 2008 12:47:06 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
DimAs"InternalProduct.FindByBvin"DimAsNew"usp_SearchByFirstLeter"</P>CType"CommandType.StoredProcedure"DimAs"SqlDataHelper.ExecuteDataSet(request1)"DimAs[2] Data.DataTable = data.Tables.Item(0)</P>
%&gt;
[/2]
says sql data helpercannot be converted to system.data.dataset
jonm
#8 Posted : Monday, April 14, 2008 1:06:47 PM(UTC)
jonm

Rank: Member

Joined: 3/24/2005(UTC)
Posts: 311
This is one of those things that if you don't understand asp.net programming concepts that you should get someone to help you.

As stated above Corneliu said that the example he gave was not tested but just an example on how you might do it. There is probably a little bit of extra that you have to do to get this to work.
-Jonathan Moore
www.tvps.com
emunah
#9 Posted : Monday, April 14, 2008 1:07:26 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
screen shot
emunah attached the following image(s):
screenshot.JPG (104kb) downloaded 44 time(s).

You cannot view/download attachments. Try to login or register.
emunah
#10 Posted : Monday, April 14, 2008 1:27:47 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
I dont understand this error is all
DimAs'= "InternalProduct.FindByBvin"'</P>""DimAsNew"usp_SearchByFirstLeter"</P>CType"CommandType.StoredProcedure"DimAs"SqlDataHelper.ExecuteData.Dataset(request1)"DimAsCType"data.Tables.Item(0)"[2], System.Data.Rule)</P>
%&gt;
[/2]
sql data helper cannot be converted to this string
jonm
#11 Posted : Monday, April 14, 2008 1:49:56 PM(UTC)
jonm

Rank: Member

Joined: 3/24/2005(UTC)
Posts: 311
Dim data As Data.DataSet = ("SqlDataHelper.ExecuteData.Dataset(request1)")

Should be

Dim data As Data.DataSet = (SqlDataHelper.ExecuteData.Dataset(request1))

The reason for the error is when you put quotes around something it makes it a string. When you pass a string to an object not expecting a string you get that type of error message.

If your using Visual Studio the intellisense should tell you what the object is expecting you to pass.

Good Luck
-Jonathan Moore
www.tvps.com
emunah
#12 Posted : Monday, April 14, 2008 2:24:38 PM(UTC)
emunah

Rank: Member

Joined: 2/25/2008(UTC)
Posts: 111
now it says sqlDataHelper not declared thanks jonathon
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Powered by YAF | YAF © 2003-2024, Yet Another Forum.NET
©2024 Develisys. All rights reserved.
  • Toll-free  888-665-8637
  • International  +1 717-220-0012