need to create a table search

[ethicalhacker]

I want to show details from a table depending on the fields specified.
Supposing i need to display details from the airlines table according to the ticket no then I will use select * from airlines where ticket_no = "t4263"
but i want this data to show in a crystal report or a data grid view. How would i do so. How do i run a particular query. The condition should be able to change for example if the user wants to search according to name or some other field.

 

[.paul.]

put the table info in a datatable

 

[ethicalhacker]

you dint understand i have made the database and the table i Just want to create a search option to search for records according to the fields specified in a textbox

 

[giadich]

put a textbox on your form and use it to build your sql statement.

something like:

ssql="select * from tablename where criteria=" & textbox1.text

dont forget to validate user input.

 

[Arg81]

better to create a FillBy query on your tableAdapter and use a textbox or some kind of input as the query parameter.

put the table info in a datatable

you dint understand i have made the database and the table i Just want to create a search option to search for records according to the fields specified in a textbox

He understood perfectly. A dataTable is completely different to a database. A database and tables are the physical data, a dataSet and dataTables (or tableAdapters) are part of the ADO.net framework that store the data in memory to use in applications.
Have a readup of ADO.net, or look through the walkthroughs for VS2005 in my signature.

 

[discGolfer]

put a textbox on your form and use it to build your sql statement.

something like:

ssql="select * from tablename where criteria=" & textbox1.text

dont forget to validate user input.

This is very important to protect your database. You should read up on Sql Injections before implementing this. If you call a stored procedure from your code and pass in parameters rather than hardcoding the select statement, you can prevent a lot of injections.

http://www.imperva.com/application_defense_center/glossary/sql_injection.html

 

[ethicalhacker]

Im using the following code to search using a text typed into a textbox but I need the output to be displayed somewhere (preferably in a datagridview) how do i do that?

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New SqlConnection("Server=.; database=billing;user id=sa; password=.")
        con.Open()
        Dim com As New SqlCommand("Select * from airlines where tick_no=" & TextBox1.Text, con)
        com.ExecuteNonQuery()
        con.Close()
    End Sub

 

[ethicalhacker]

better to create a FillBy query on your tableAdapter and use a textbox or some kind of input as the query parameter.

I did that but I cant use things like form3.textbox1.text inside the query there.
It gives me an error, how do I do the equivalent to "select * from dbo.airlines where tick_no =" & textbox1.text to show the result in a datagridview?

 

[JohnH]

I did that but I cant use things like form3.textbox1.text inside the query there.

Why not?? Did you miss the Creating a Form to Search Data in a Windows Application walkthrough?

 

[ethicalhacker]

thanks a lot!

 

[ethicalhacker]

that was great but what if I have to show results with more than one condition using one textbox for example if I have to use select * from airlines where tick_no = @tick_no or passenger_name="James".

Also one other thing when I search for something using the fillby technique I have to enter exactly the same term or I wont get the result. Lets say I want to search for a passenger_name called "James Pinto" it should be possible that even if I type James the record having "James Pinto" shows up. Will I use select * from airlines where passenger_name like 'James%' . How will I implement it with the passenger_name = @passenger_name clause?
Will like @passenger_name work?

 

[Arg81]

You just type your SQL's correctly.

For example purposes, I want to be able to search my customer database for every customer who has "tan" in the name.

My "FillBy" query on my Customer tableAdapter will be:
SELECT * FROM Customer WHERE CustomerName LIKE @CustomerName

On my form I have a datagrid bound to the Customer tableAdapter and a textbox (txtCustomer) and button (btnSearch).

On the button click the following happens;

Private Sub btnSearch_Click (...... ) Handles btnSearch.Click

If me.txtCustomer.text = Nothing Then
   messagebox.show("You must enter a search string")
Else
   dim strCustomer as string = "%" & me.txtCustomer.text & "%"
   me.CustomerTableAdapter.FillByCustomerSearch(me.dsCustomer.Customer, strCustomer)
End If

^^ if the textbox is empty when the button is pressed, a messagebox pops up.

The textbox text is wrapped in % - this is the SQL wildcard. Because it's %tan% , it means that it will search for any customer that has tan in it, if you want it to start with tan you remove the first % so its just tan%.
Therefore in my fictional example, the datagrid is filled with:

Stanbra
Setana
Tanner

but what if I have to show results with more than one condition using one textbox for example if I have to use select * from airlines where tick_no = @tick_no or passenger_name="James".

Don't quite understand what you mean, as in your example you have hardcoded the passenger_name.

Your SQL query would be
SELECT * FROM Airlines WHERE tick_no = @tick_no OR passenger_name = @passengername

You then need to provide the 2 variables to the FillBy query on the form.

Dim intTicket as integer = "1234"
Dim strPassenger as string = "James"
AirlineTableAdapter.FillByTicketOrPassenger(me.dsAirline.Airline, intTicket, strPassenger)

I made up the names of the tableAdapter, FillBy query and dataSet - you would obviously replace them with your own.

That query will then load any rows that has the ticket number 1234 or the passenger name of James.