search functions

e_bryant

New member
Joined
Jul 29, 2004
Messages
2
Location
Chico
Programming Experience
Beginner
Help?

I'm a newbie learning sql, vb.net, visual studios.net, etc.

I've been given an assignment to add search funcitons to a sql datareader/ datagrid.

i.e. I make it so that the page opens initially with a text box and a button that says Search. When you type something into the text box it and press the button it will find all the products with those letters in the name and then display the results below the text box.

I've googled my head off to no avail. Any ideas where to look? I may be google inept.
 

Paszt

Staff member
Joined
Jun 3, 2004
Messages
1,500
Location
Raleigh, NC - USA
Programming Experience
Beginner
Check out this post: Search form

In the SQL statement's WHERE clause, you can use only one criteria and replace the value with the value entered in the textbox: "WHERE someField = '" & textBox1.Text & '".

If you want to use a wildcard, use the percent symbol: "WHERE someField = '%" & textBox1.Text & '%".
 

e_bryant

New member
Joined
Jul 29, 2004
Messages
2
Location
Chico
Programming Experience
Beginner
declare textbox

I put it in... but now i need to declare textbox1.

ps. how do i put this in it's own window?

Here's what I got so far:
<%@ Page Language="vb" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<Script runat="server">
Sub Page_Load(Sender As Object, E As EventArgs)
Dim myConnection As SqlConnection
Dim myCommand AS SqlCommand
myConnection = New SqlConnection("server=***;UID=sa;PWD=***;database=Northwind;")
myCommand = New SqlCommand("SELECT ProductName FROM Products" , "WHERE somefield = '%" & textBox1.Text & '%", myConnection)
myConnection.Open()
Dim myDataReader As SqlDataReader = myCommand.ExecuteReader()
myDataGrid.DataSource = myDataReader
myDataGrid.DataBind()
myConnection.Close()
End Sub
</Script>
<html>
<Head>
<Title>Northwind Products</Title>
<Head>
<body>
<form runat="server" method="post">
<asp:DataGrid runat="server" id="myDataGrid"
HeaderStyle-Font-Bold="True"
HeaderStyle-Font-Name="Arial"
HeaderStyle-Font-Size="Small"
HeaderStyle-ForeColor="#663300"
HeaderStyle-BackColor="#CCCC66"

ItemStyle-Font-Names="Verdana, Arial, sans-serif"
ItemStyle-Font-Size="x-small"
AlternatingItemStyle-BackColor="#D4D0C8"
/>
</form>
</body>
</html>
 

levyuk

Well-known member
Joined
Jun 7, 2004
Messages
313
Location
Wales, UK
Programming Experience
3-5
Firstly I would change the line to this

VB.NET:
myCommand = New SqlCommand("SELECT ProductName FROM Products WHERE somefield = '% & textBox1.Text & '%", myConnection)

The reason it is saying that you need to declare a textbox is because there are no boxes on the page so you need to add them first
 

bloukewer

Well-known member
Joined
Jul 2, 2004
Messages
88
Programming Experience
3-5
Need some more info

The other guys's info is correct, but it depends on the method u use to "Fill" your datagrids * datasets. Do you make use of databindings (do you bind your controls to a datasource) or do you make use of explicit sql-command objects and statements? In case of the first you could make use of parameters inside your data-adapter's sql-statement. In case of the second, you should treat your data-table like a two-dimensional array and use one of the algorithms they tought you at school to search through it.
 

bloukewer

Well-known member
Joined
Jul 2, 2004
Messages
88
Programming Experience
3-5
Top Bottom