Query builder for a database search

Windows Form app, Sql Client

Page 1 of 1

3 Replies - 5587 Views - Last Post: 21 December 2010 - 10:41 AM Rate Topic: -----

#1 nanowoman1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 02-September 10

Query builder for a database search

Posted 18 December 2010 - 06:14 PM

ok so i have this win form app that performs a database search of customer information. I have a datagridview, a binding navigator and a fill/addquery toolstrip with a toolstrip button and a textbox.

Here is my query, i want to find all customer names like what i enter in the textbox
SELECT     ACCOUNT_NUMBER, COMPANY_NAME, COMPANY_ADDRESS, BILLING_ADDRESS, CITY, STATE, ZIP, COUNTRY, PHONE FROM         tblCustomerInfo
WHERE     (COMPANY_NAME LIKE @COMPANY_NAME + N'%')



i am using sql client with a datagrid and i already bind the data to the grid as a dataset automatically so i did not manually code the connection, cmd, dataset ect

here is the find button code that recieves the customer info according to the sql query
    Private Sub FillByToolStripButton_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles FillByToolStripButton.Click
        Try
            Me.TblCustomerInformationTableAdapter.FillBy(Me.CoCustomerAccountInformationDataSet.tblCustomerInformation, COMPANY_NAMEToolStripTextBox.Text)
                    Catch ex As System.Exception
            System.Windows.Forms.MessageBox.Show(ex.Message)
        End Try

    End Sub



COMPANY_NAMEToolStripTextBox.Text is where the the Customer Name will be entered... I originally called it txtName.Text but still even when i changed and did everything through the query builder I get nothing when i run the query.

Please any info on what i am doing wrong???

Is This A Good Question/Topic? 0
  • +

Replies To: Query builder for a database search

#2 simalih  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 19-December 10

Re: Query builder for a database search

Posted 19 December 2010 - 04:33 AM

i don't understand with dataset.
but if i want to search something to database i'll code it with query to database.

example : i want to search account_number from tblCustomerInfo. and the result will show all account_number that first char is match with account_number

this is my query
select * from tblCustomerInfo where account_number like 'myText%'

i think it's not difficult to show something from database if you understand how to use query in sql.
in your case, try this query
SELECT ACCOUNT_NUMBER, COMPANY_NAME, COMPANY_ADDRESS, BILLING_ADDRESS, CITY, STATE, ZIP, COUNTRY, PHONE FROM tblCustomerInfo
WHERE (COMPANY_NAME LIKE 'COMPANY_NAMEToolStripTextBox.Text')
i assumed that company_name stored your customer name.

sorry for my english :(
Was This Post Helpful? 0
  • +
  • -

#3 polens  Icon User is offline

  • D.I.C Head

Reputation: 13
  • View blog
  • Posts: 155
  • Joined: 09-November 09

Re: Query builder for a database search

Posted 19 December 2010 - 09:36 AM

Here's a code i made but it is the textchange event of the text box hope it helps..
The problem here is it is comparing all the letters in the database..
You may want to improve it if you want..
Dim objConnection As New SqlConnection(Module1.mysqconnection)
        Dim objDt As New DataTable
        dgSchool.DataSource = Nothing
        Try
            objConnection.Open()

            Dim sSQL As String = "SELECT * FROM tblschool Where schoolName + ' ' Like '%" & txtSearch.Text & "%'" '
            Dim objCommand As SqlCommand = New SqlCommand(sSQL, objConnection)
            Dim objAdapter = New SqlDataAdapter(objCommand)
            Dim objAdap As SqlDataAdapter = New SqlDataAdapter(sSQL, objConnection)
            objAdapter.Fill(objDt)
            dgSchool.DataSource = objDt
            dgSchool.Columns(0).Visible = False

            If objDt.Rows.Count = 0 Then
                MessageBox.Show("No Record Found.", "Member Information", MessageBoxButtons.OK)

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            objConnection.Close()

            objConnection.Dispose()
        End Try

Was This Post Helpful? 0
  • +
  • -

#4 nanowoman1  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 20
  • Joined: 02-September 10

Re: Query builder for a database search

Posted 21 December 2010 - 10:41 AM

View Postpolens, on 19 December 2010 - 08:36 AM, said:

Here's a code i made but it is the textchange event of the text box hope it helps..
The problem here is it is comparing all the letters in the database..
You may want to improve it if you want..
Dim objConnection As New SqlConnection(Module1.mysqconnection)
        Dim objDt As New DataTable
        dgSchool.DataSource = Nothing
        Try
            objConnection.Open()

            Dim sSQL As String = "SELECT * FROM tblschool Where schoolName + ' ' Like '%" & txtSearch.Text & "%'" '
            Dim objCommand As SqlCommand = New SqlCommand(sSQL, objConnection)
            Dim objAdapter = New SqlDataAdapter(objCommand)
            Dim objAdap As SqlDataAdapter = New SqlDataAdapter(sSQL, objConnection)
            objAdapter.Fill(objDt)
            dgSchool.DataSource = objDt
            dgSchool.Columns(0).Visible = False

            If objDt.Rows.Count = 0 Then
                MessageBox.Show("No Record Found.", "Member Information", MessageBoxButtons.OK)

            End If

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            objConnection.Close()

            objConnection.Dispose()
        End Try

OMG!!! It worked... Thank you so very MUCH!!!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1