Expression Web and SQL server 2

[lookup13]

I have designed a webpage for part info , a search and results
Linked to a Part database , but I want to search Data base for part numbers, I can search for exact using

Select * from Parts
Where Part_number like part_number

But I want to use wild cards *123* results 123456 Screw etc

Any Ideals , simple is best

Thank you in advance

 

[markros]

I wanted to be smart and use [link] tag.

Ok, try

http://blogs.lessthandot.com/index....ling/setting-sqldatasource-parameter-from-the

 

[lookup13]

In SQL Server ,Declare @Part_Num varchar(50)
set @Part_Num = 'BC'

select * from Parts where [Part_Number] LIKE @Part_Num + '%'

Works , bring up all the BC part numbers

What do we set Declare for a saerch box ?

 

[markros]

Now one more try with a link tag

[link =

http://blogs.lessthandot.com/index....ling/setting-sqldatasource-parameter-from-the

]Link Test[/url]

 

[markros]

Check the referenced link while I'll try to play with Link tag here. That link tells you how to set the parameter in the code.

In your case you may just need to specify it as a control parameter - set it to use your search textbox.

I'll give you another link to a thread on this topic later.

[link a href="

http://blogs.lessthandot.com/index....ing/setting-sqldatasource-parameter-from-the"

]Would this work?[/url]

 

[markros]

Ok, try this link for a relevant thread and hopefully I would be able to make it a link

ASP.NET thread

 

[markros]

The link worked, but now I see, that it was not the thread I wanted

Anyway, just google on "ControlParameter ASP.NET" - it should give you the right link.

 

[lookup13]

Tried Code on web page and stated @part_num was already delcared ?...
Declare @Part_Num varchar(50)
set @Part_Num = 'BC'

select * from Parts where [Part_Number] LIKE @Part_Num + '%'

 

[markros]

Sorry, that code was just for SSMS test.

I'm ready to go home right now, please make a search on "ControlParameter ASP.NET"

 

[markros]

Ok, I made a quick search and found

http://forums.asp.net/p/1447977/3298636.aspx#3298636

Leave your original SelectCommand as it was before

"select * from parts where Part_Number like @Part_Num +'%'"

and put

<asp:ControlParameter Name="Part_Num" ControlID="txtSearch" PropertyName="Text" />

 

[jbenson001]

As an ASP.NET programmer myself, I strongly suggest getting rid of any datasource controls. They might be fine for a simple select, update etc, but once you need to do something a little more complex, there are problems, as you have seen. Also, there is NO way to debug them.

Get rid of the control and write a stored procedure.

 

[lookup13]

What do you suggest for a search Box ? The simple does work,I know it something still simple.It just a setting that we are missing?...

 

[jbenson001]

What I am saying is to write a stored procedure as others here have given you examples using LIKE and wildcards. Once you have that working properly, then call it from your asp.net app, by writing the code yourself and not using the datasource controls.

 

[markros]

If you have a gridview, do you bind it in code using ADO.NET to get dataset?

Or do you use ObjectDataSource? Or what you're using?

I know that SQLDataSource is attractive and simple choice for beginners.

 

[jbenson001]

We use Microsoft's Data Access Application Block for .NET v2:

http://www.microsoft.com/downloads/...0a-9877-4a7b-88ec-0426b48df275&displaylang=en

 

[lookup13]

Expression Web you use SQLDatasource and you can chose grid, page or form view.It lock the SQL server in and you can chose what you want to do..show all data or some columns ..

 

[jbenson001]

But as you see using the datasource controls is limited, and not debuggable at all.

 

[markros]

Hi Jason,

Is it still recommendable or what's the latest and the greatest approach?

When I worked on existing ASP.NET ~1+ ago, it used mostly SQLDataSource and direct SQL Statements. I converted some of them to SPs and in one page (that was used for use profiles stuff) I used a ObjectDataSource which I build using Peter Keller's excellent article and builder I found researching this.

 

[jbenson001]

The datasource controls came out with the 2.0 framework. Unfortunately, for anyone just learning, all the examples out there were using the datasource controls. Yes they are quick and work well for simple pages, like just showing data in a grid for example.
The problem comes in once you want to do more than a simple select or update etc. Passing parameters becomes a problem. The biggest problem is degubbing. You simply can't figure out what is going on when a problem occurs. If you are writing the code yourself with ADO, or using a DAL, you can step in and see what is happening to fix the problem.
Most of the posts that I answer in the ASP.NET forum involving the DB are related to the datasource controls. My answer is simply don't use them. If you write the code yourself, you learn how to access the db, learn what is going on behind the scenes, and also debug when a problem arises, which, they always do. This post here is a perfect example of how the datasource control is clouding the actual issue. If the person just wrote a simple SP, they would have found the issue easily.

Jim

 

[lookup13]

Update the code Markros provide worked :
select * from Parts
where Part_Number like '%' + @Part_Number + '%'

I forgot to pick form view :

ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:ConnectionStringparts %>" SelectCommand="SELECT * FROM [Parts] WHERE ([Part_number] Like '%' + @Part_number + '%')">
<SelectParameters>
<asp:formparameter FormField="Parts" Name="Part_number" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Which cause some head aches , problem solved !!!!

 

[markros]

I actually was suggesting to not use '%' in front if you want parts with start with the number, e.g.

do you want your search to return

type "P"

BCP0001
BCP0002

etc.

Or you want to type
'BC'
and return

BCP0001
BCP0002

Jim, thanks for the explanation. Not sure why I thought your name is Jason.