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

 

[gmmastros]

Use % for your wild cards.

Select * From Parts Where Part_Number Like '%123%'



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

If you want them to start with the number you specify, then

select * from Parts where Part_Number like @Part_Num + '%'

 

[lookup13]

select * from Parts
where Part_Number like @Part_Number + '%'

It comes up blank on results page,
I enter BC154 - complete part number and blank results
page.I put BC1 same result. I remove + '%' and back to
excat match again ....??

 

[gmmastros]

select * from Parts
where Part_Number like [!]'%' + [/!]@Part_Number + '%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I don't see a problem with the above suggestion as long as you type the part starting with the exact string.

How @Part_Number parameter is defined? It should be varchar(10) (or the maximum for Part_Number field).

 

[lookup13]

Same results , Blank Results page
More Info in the search Box enter BC,
I would like it to pull up all part numbers
with BC154,BC155,BC15 Etc.

I know were close ...

 

[markros]

Do you trim the value?

Make sure to trim the value after getting it from the textbox, may be there are spaces at the end.

 

[gmmastros]

Are you using a stored procedure for the search? If so, take a quick look at this:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-stored-procedure-with-nvarcha

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lookup13]

Ok update , varchar(50)

 

[gmmastros]

Can you show the code? If this is a stored procedure, then show that. If you are building sql in your front end, then show that. It'll make it a lot easier for us to help you.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lookup13]

I not using a stored procedure for the search.The string
doesnt error out , so I know it something small.
Trim , how would I do that ?

 

[gmmastros]

You could try...

select * from Parts
where Part_Number like '%' + [!]LTrim(RTrim([/!]@Part_Number[!]))[/!]+ '%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lookup13]

Here the code :

<asp:GridView runat="server" id="GridView1" AutoGenerateColumns="False" DataSourceID="SqlDataSource1" AllowPaging="True">
<Columns>
<asp:boundfield DataField="Cat_Id" HeaderText="Cat_Id" ReadOnly="True" InsertVisible="False" SortExpression="Cat_Id">
</asp:boundfield>
<asp:boundfield DataField="Vcode" HeaderText="Vcode" SortExpression="Vcode">
</asp:boundfield>
<asp:boundfield DataField="VPcode" HeaderText="VPcode" SortExpression="VPcode">
</asp:boundfield>
<asp:boundfield DataField="Part_number" HeaderText="Part_number" SortExpression="Part_number">
</asp:boundfield>
<asp:boundfield DataField="Description" HeaderText="Description" SortExpression="Description">
</asp:boundfield>
<asp:boundfield DataField="QfxpPrice" HeaderText="QfxpPrice" SortExpression="QfxpPrice">
</asp:boundfield>
<asp:boundfield DataField="Images" HeaderText="Images" SortExpression="Images">
</asp:boundfield>
</Columns>
</asp:GridView>
<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:ConnectionStringparts %>" SelectCommand="SELECT * FROM [Parts]
WHERE [Part_number] like '%' + @Part_number + '%'">
<SelectParameters>
<asparameter Name="Part_number" />
</SelectParameters>
</asp:SqlDataSource>

 

[lookup13]

No luck with trim code ..

 

[gmmastros]

I'm not at all familiar with that code (asp.net)?

I suspect the problem is with the way you set the value of @Part_number. You should be able to test it by hard-coding a value (just to see).

<asp:SqlDataSource runat="server" ID="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:ConnectionStringparts %>" SelectCommand="SELECT *  FROM [Parts]
WHERE [Part_number] like '%[!]BC[/!]%'">

If that returns all the BC part numbers, then the problem must be in the asp code.... specifically the way you set the value.

Sorry I can't help you more.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Where do you set the @Part_Num? If this is just a parameter (not a control parameter), do you set it in SQLDataSource_Selecting event?

You may want to check

[link ="

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

]Setting SQLDataSource parameter from the code-behind
[/url]

 

[lookup13]

I didnt set the value of @part_number,I know if you try the
code on SQl Server it ask for a Scaler Variable for @part_num ?

 

[markros]

Try

Declare @Part_Num varchar(50)
set @Part_Num = 'BC'

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

 

[lookup13]

Link is not working (

http://=/)You

may want to check

Setting SQLDataSource parameter from the code-behind