Need To Create Search Bar For Database

Need To Create Search Bar For Database

• InHowes

  Hi, happy to read it helped you. If you leave the parameter to null and the fields of the DB accept null values, you will get the rows back that have a null value for the fields include the LIKE part of your query.
  
  For the no result you message, just add the following to the example:
  
  If Me.DsProd.Prod.Count = 0 Then
  MessageBox.Show("No rows returned for your query", "Data not found", MessageBoxButtons.OK, MessageBoxIcon.Information)
  End If
  
  

• Kestutis

  One additional comment on the drag-and-drop approach - you can use the standard Fill() command on your TableAdapter to get all the employee records. Then perform your search by appling a filter string to the BindingSource.Filter property. The filter is written like a where clause in a SQL statement.

  This method has the advantage of allowing the user to perform multiple searches without multiple trips to the server. It also give the user more flexibility as all employee records are available to work with and can be filtered and sorted quickly. The BindingSource will also handle sorting for you. And for even more control you can use a DataView object and filter by rowstate (added, deleted, modified, unchanged, etc) in addition to the other functions

  
  
  

• Chris Honcoop

  First of all... correct me if i am wrong and please provide more info as to how the user will be searching... and what database your using .. the code below is for sql server !
  
  i am assuming with the employee number...
  
  Write this on the top
  
  Imports System.Data.SqlClient
  
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  Dim con As SqlConnection
  Dim command As SqlCommand
  Dim dr As SqlDataReader
  
  con = New SqlConnection("server=ASD\SA;database=databasename;user id=username;password=yourpassword")
  con.Open()
  command = New SqlCommand("Select First_Name, Last_Name from Employees where Employee_Number like " & txtEmployeeNumber.Text, con)
  dr = command.ExecuteReader
  While dr.Read
  txtEmployeeFirstName.Text = dr(0).ToString()
  txtEmployeeLastName.Text = dr(1).ToString()
  End While
  con.Close()
  End Sub
  
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  
  i hop you know a little bit of SQL and ADO.net.
  
  

• mabrouk

  I understand the code, but i'd like to know the function to work with access, i guess my question isbecause i dont know the language yet.. instead of Sql Connection... wich funciont should i call

  con = New SqlConnection("server=ASD\SA;database=databasename;user id=username;password=yourpassword")
  

  
  
  

• Igzz

  Wow finally someone has the same questions like I do. All samples I've seen are with the navigator bar.

   Is there a sample code where it uses the data connection wizard I have an app that does similar thing, but I don't want to open and close the connection everytime I do a search as this app is used at all time. Open the connection on load, closes the connection when the app is closed.

  
  

• Amit Chopra_MSFT

  I think you are using the SQL server Mobile Edition 2005... i don't know much about it...
  
  As far as the code is concerned i would do my best to explain you everything i have done which i believe is the easiest way...
  
  I don't use drag-drop ...i mean if everything is meant to be done this way even kids would be able to do it..
  
  So... why don't you start by making a form..
  make
  3 Labels--First Name,Last Name, Employee Number
  3 Textboxes--for each one them
  1 Button- To search
  
  On the Button Click event..
  
  Write the above code..now if your getting errors then i think your installation is corrupt..
  
  But here is the connection string-
  
  con = New SqlConnection("server=ASD\SA;database=databasename;user id=username;password=yourpassword")
  
  Where you must provide the server name-- which is the name of your SQL server instance...
  
  Where you must provide the database name-- which is the name of the database where your Employee table is located
  
  Where you must provide the username-- which is the name is the user
  where you must provide the password-- password of the user
  
  

  command = New SqlCommand("Select First_Name, Last_Name from Employees where Employee_Number like " & txtEmployeeNumber.Text, con)

  Here txtEmployeeNumber is the name of the Textbox and it contains the Employee Number that you are searching...

  I Think this is the easiest way... i like to do the old fashion way .. even though i am only 18
  

  
  

  
  
  

• Karim Hemani

  Hi,

  Actually it is not that difficult to create a search feature. I assume you have created a dataset in the example called DsProd, the underlying DB-table is called Prod. Just add a select query to the dataset that looks like this:

  Name:FillBySearch

  SELECT * FROM Prod WHERE (ProdID LIKE @SEARCH) OR (ProdName LIKE @SEARCH)

  Now with the drag and drop features of VB2005 for creating simple databound UI from Datasets, you create your UI interface. A toolstrip will be added automatically with the navigation buttons. Add a textbox and a button. Double click the button and fill in code like hereunder. Now you have a search feature.

  Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click

  Me.ProdTableAdapter.FillBySearch(Me.DsProd.Prod, Me.TsSearch.Text)

  End Sub

  FillBySearch has two arguments 1 which datatable to fill Prod table in the DsProd dataset and 2 @SEARCH a text string since the LIKE was only used on varchar declared fields in the query. If you to search on field of different types you'll have to device some extra code. However if you only search on a numeric field don't forget to use CInt,Cdouble,... to convert your textbox.text data to the correct numeric format. Intellisense will tell what format is expected. This is one those nice features of strongly typed datasets. Since I assumed you used the drag and drop feature to create the UI from the dataset, the above code should be sufficient. If you choose to program everything you'll have to add and manage the connection, bindingsource,....

  This example is also somewhere on MSDN, since it was there that I found it a few months ago when I needed the feature. Sorry, but can't tell you exactly where it can be found.

  
  

• J-Pixel

  I appreciate the time that you are spending on this.

  To answer your questions:

  1) The user will perform the search by putting a number in the EmployeeNumber textbox and then press the Button to find the matching Employee First Name and Employee Last Name if there is a record that matches this number.

  2) I am using a database that is saved to my desktop (because I am having problems with https://forums.learnvisualstudio.net/Topic1392-9-1.aspx as well). It is located at C:\Documents and Settings\Administrator\Desktop\EmployeeInformationDatabase1. This, I assume is SQL server that came with Microsoft Visual Studio 2005 Professional Edition.

  Under the server explorer data connection my database is EmployeeInformationDatabase1 and the dataset is named EmployeeInformationDatabase1Dataset

  I have sqiglly lines under several of the words which I have higlighted below.

  Public Class Form1

  Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

  End Sub

  Imports System.Data.SqlClient

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

  Button1.Click()

  Dim con As SqlConnection

  Dim command As SqlCommand

  Dim dr As SqlDataReader

  con = New SqlConnection("server=ASD\SA;database=databasename;user id=username;password=yourpassword")

  con.Open()

  command = New SqlCommand("Select First_Name, Last_Name from Employees where Employee_Number like " & txtEmployeeNumber.Text, con)

  dr = command.ExecuteReader

  While dr.Read

  txtEmployeeFirstName.Text = dr(0).ToString()

  txtEmployeeLastName.Text = dr(1).ToString()

  End While

  con.Close()

  End Sub

  End Class

  I am having a problem understanding the sql connection and sql command part. I am accustomed to dragging and dropping all connections and datasets from the data sources tab. However, I tried to type in the connection string and I didn't get any results. Maybe you can elaborate with the additional information that I have provided.

  I have spent several hours trying to figure this out since your reply. I don't know where to start on the amount of errors that I have received trying different things.

  Is there not an easy way to manipulate the automatic data table adapters, bindings, navigators, and so on and so forth

  
  

• ReneeC

  Krigo,

  Wow. That worked with such ease. I can't thank you enough for your time. If you had a paypal account I would send you a donation. I am still amazed that I can't find that anywhere on the forums. I think that we will see this thread get used a lot.

  An additional question I had though:

  Can I leave the parameter of the search to null on each of the searches.

  What should I do to display a message box if the search doesn't return a result.

  Again, thank you so much for the help!

  
  

• QWERTYtech

  Yorker,

  I wanted to thank you for your time as well.

  I was able to remove all of the errors from the code but was never able to establish a connection to my server through the sqlconnection line. I would like to know how to do this if you have any suggestions.

  Under my computer>c:>Program Files I have two SQL Server Programs. One is SQL Server 2005 and the other is SQL Server 2005 Mobile Edition. The mobile edition only has a folder in it with samples of the northwind. The SQL Server 2005 has several different types of folders under it and I would assume that this is the SQL server that comes with Visual Studio 2005 Professional Edition Program.

  My question is this: Since the server is on this machine how would I write out the sql connection line. Remember that the EmployeeInformationdatabase1.mdf is on my desktop.

  I would also appreciate any help you could give me on configuring the server user id and user password. I have looked at the forums but dont' seem to be having any luck.

  Hope I haven't strayed too far from the original question for this forum

  
  
• Need To Create Search Bar For Database