Gridview Search

I tried doing a text box search within Gridview. My code are as follows. However, when I clicked on the search button, nothing shown.

Any help would be appreciated. I'm using an ODBC connection to MySql database. Could it be due to the parameters not accepted in MySql?

Protected

Sub Page_Load(ByVal senderAsObject,ByVal eAs System.EventArgs)

SqlDataSource1.SelectCommand =

"SELECT * FROM carrier_list WHERE carrierName LIKE '%' + @.carrierName + '%'"

EndSub

Sub doSearch(ByVal SourceAsObject,ByVal EAs EventArgs)

GridViewCarrierList.DataSourceID ="SqlDataSource1"

GridViewCarrierList.DataBind()

EndSub

HTML CODES (Snippet)<asp:ButtonID="btnSearchCarrier"runat="server"onclick="doSearch"Text="Search"/>

' Gridview
<asp:GridViewID="GridViewCarrierList"runat="server"DataSourceID="SqlDataSource1">

</asp:GridView>

<

asp:SqlDataSourceID="SqlDataSource2"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"SelectCommand="SELECT * FROM carrier_list"></asp:SqlDataSource>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>"><SelectParameters><asp:ControlParameterControlID="txtSearchCarrier"Name="carrierName"PropertyName="Text"Type="String"></asp:ControlParameter></SelectParameters>

</asp:SqlDataSource>

It's a syntax error on your SQL statement.

Try this:

SqlDataSource1.SelectCommand = "SELECT *FROM carrier_listWHERE carrierNameLIKE'%" + @.carrierName + "%'"

You had an extra ' after first '% and before last %'

Hope this helps and let me know if it worked.

Jae.

|||

It reverted with a :

"Character is not valid." error.

Line 33: SqlDataSource1.SelectCommand = "SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'"

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE %yourvalue%

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

check what your server is passing to sqldatasource by doing following:

response.write("SELECT * FROM carrier_list WHERE carrierName LIKE '%" + @.carrierName + "%'")

it print out:

SELECT * FROM carrier_list WHERE carrierName LIKE '%yourvalue%'

Also, note that your value (carrierName) should not contain any single quote or double quote.

Hope this helps.

Jae.

|||

Hi it still prompts the same error:

response.write(

"SELECT * FROM carrier_list WHERE carrierName'%" + @.carrierName + "%'")|||

i just looked at your code from beginning again.

1. it's VB, so why should you use +? instead of &? (sorry i thought of C#)
2. you're mising LIKE on above statement.
3. you can't LITERALLY pass @.carrierName as value. Your value is txtSearchCarrier.text
4. I don't understand why you have 2 sqldatasource (Delete sqldatasource2 - this will show same effect, read on)

So, let's write it again and clean up a bit:

SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"

Also, you don't need <controlparameter> tag within "select parameter", try this approach:

HTML CODES (Snippet)

<asp:Textbox id="txtSearchCarrier" runat="server"/>
<asp:Button ID="btnSearchCarrier" runat="server" onclick="doSearch" Text="Search" />

' Gridview
<asp:GridView ID="GridViewCarrierList" runat="server" DataSourceID="SqlDataSource1" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" />

That's it.

This will show ALL carriers like you had with sqldatasource2, but with only one sqldatasource1 (because it will pass like '%%' whichi will return all)
*** Also, when carrier name is typed into txtSearchCarrier (obviously a textbox), it will return result sets with characters displayed in textbox.
*** Also, you should have your LOAD_PAGE empty.

If you don't want to diplay anything at first, don't bind DataSourceID = "sqldatasource1", but rather do it on btnSearchCarrier_OnClick handler.
Something like this:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

This way, you only retrieve data when you click "searchcarrier" button.

Hope this helps and if it doesn't send me the aspx page and I will help you with it. (send it to my email,jae.lee@.jaeleeandco.com)

Jae.

|||

just in case, you HAVE to do following:

1. delete PAGE_LOAD

2. add select command to on_click look at below:

sub btnSeachCarrier_OnClick (....)
.... you other code ...
SqlDataSource.SelectCommand = "select * from carrier_list where carriername like '%" & txtSearchCarrier.text & "%'"
gridViewCarrierList.datasourceid = "sqlDataSouce1"
gridViewCarrierList.databind()
end sub

|||

Thanks Jae,

Actually I used

SqlDataSource1.SelectCommand ="SELECT * FROM carrier_list WHERE carrierName LIKE ? '%' ORDER BY carrierName ASC"

instead and it works.

Will try your suggestion too! Thanks!