dcsimg
Results 1 to 21 of 21

Thread: Search the Records in Multiple Fields and Multiple Tables based on selection of table

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Search the Records in Multiple Fields and Multiple Tables based on selection of table

    The below code, i used to search the field but it didn't worked out.

    Code:
      Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
            ' bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%'"
            Dim strsearch As String
            Dim Task As String
          
            strsearch = Me.txtsearch.Text
            Task = "SELECT * FROM [" & ComboBox1.Text & " ] WHERE ((Agilenumber Like ""*" & strsearch & "*"") OR (Description Like ""*" & strsearch & "*"") OR (CellName Like ""*" & strsearch & "*""))"
            da.SelectCommand = New OleDbCommand(Task, cnn)
        End Sub

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,410

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    1) why aren't you using parameters like you were shown in your other thread?
    2) I'm going to be pedantic and say, actually it IS working, it's doing exactly what you told it to do. If it wasn't working, you'd get an error, but you don't. The problem is that you're simply setting the select command, but you never execute it. DataAdaptors aren't a magic box. They still have to be told. You gave it a new command. Great. but you still have to tell it to execute that new command. Otherwise it'll just sit there.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3
    #28 for the Yanks coming GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,334

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    I know the monitor is tilled to the left try tilling it to the right.


    I didn't work means nothing what didn't work... The connection failed? The data did not exists? Was there are error returned? Did you check in the database itself that the data your looking for is there? What database system? The * works in some but others require a % for the wild card
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  4. #4

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    I am not getting any error at the same time, i cannot able to find what i want? When i click the search button, it doesn't filter out what i want.

  5. #5

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Code:
      Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
    
    
            If ComboBox1.Text = "Connector" Then
    
                Dim query As String = "SELECT ID, Agilenumber, Description, Rohs, Manufacturer, ManufacturerPartnumber, PartNumber, CellName, Symbol FROM Connector"
                query &= " WHERE ID LIKE '%' + @SearchTerm + '%'"
                query &= " OR Agilenumber LIKE '%' + @SearchTerm + '%'"
                query &= " OR Description LIKE '%' + @SearchTerm + '%'"
                query &= " OR Rohs LIKE '%' + @SearchTerm + '%'"
                query &= " OR Manufacturer LIKE '%' + @SearchTerm + '%'"
                query &= " OR ManufacturerPartnumber LIKE '%' + @SearchTerm + '%'"
                query &= " OR PartNumber LIKE '%' + @SearchTerm + '%'"
                query &= " OR CellName LIKE '%' + @SearchTerm + '%'"
                query &= " ORSymbol LIKE '%' + @SearchTerm + '%'"
                query &= " OR @SearchTerm = ''"
    
                Dim cmd As OleDbCommand = New OleDbCommand(query, cnn)
                cmd.Parameters.AddWithValue("@SearchTerm", txtsearch.Text.Trim())
                Dim da As OleDbDataAdapter = New OleDbDataAdapter(cmd)
                Dim dt As DataTable = New DataTable()
                da.Fill(dt)
    
            End If
        End Sub

    i am getting below error,

    Syntax error (missing operator) in query expression 'ID LIKE '%' + @SearchTerm + '%' OR Agilenumber LIKE '%' + @SearchTerm + '%' OR Description LIKE '%' + @SearchTerm + '%' OR Rohs LIKE '%' + @SearchTerm + '%' OR Manufacturer LIKE '%' + @SearchTerm + '%' OR ManufacturerPartnumber LIKE '%' + @SearchTerm + '%'.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,752

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Is this an Access database? If so then you cannot reuse parameters. If it was SQL Server or various other databases, you could just add one parameter and use that multiple times in your SQL code but, for Access, you have to add one parameter for each usage in the SQL. That means that, even if it's the same value every time, if you want to use a parameter 10 times then you have to add 10 parameters.

    As for the syntax issue, just open your eyes. LOOK at your code. You have actually formatted the code that builds the SQL fairly well, even if it's not the way I'd do it, and that makes the issue blindingly obvious if you simply look at it.

    Also, while I'm not exactly sure how any particular database might optimise things, logic dictates that you test the input value for being empty first. If it's empty, all the other comparisons are pointless and definitely won't be performed whereas, if that one is last, the rest might be done first and be a waste of time. If you had to make 9 comparisons only if a value wasn't empty, wouldn't you check whether the value was empty first? I certainly hope so. Why wouldn't you do that here then?

  7. #7

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    I am using Access database, With the binding source filter also i am getting below error.

    Conversion from string "Agilenumber LIKE'K05LF60013%'" to type 'Long' is not valid.

    Code:
    bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%'" OR "Description LIKE'" & txtsearch.Text & "%'" Or "Rohs LIKE'" & txtsearch.Text & "%'"
    My aim is to search the multiple fields with the single textbox and search button.

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,091

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Make sure the parts you want to be text are delimited appropriately. For example, this part:
    Code:
    bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%'" OR "Description LIKE'" &
    ...was presumably meant to be:
    Code:
    bs.Filter = "Agilenumber LIKE'" & txtsearch.Text & "%' OR Description LIKE'" &
    (ie: the OR being part of the text)


    There are various ways to build strings, and the one you are using is prone to errors like this. Try using String.Format instead, eg:
    Code:
    bs.Filter = String.Format("Agilenumber LIKE'{0}%' OR Description LIKE'{0}%' Or Rohs LIKE'{0}%'", txtsearch.Text)

  9. #9

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Code:
     Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
    
            If ComboBox1.Text = "Capacitor" Then
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Type] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [Tolerance] LIKE'{0}%' OR [Temp_Coefficient] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
    
            ElseIf ComboBox1.Text = "Connector" Then
                txtsearch.Clear()
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%'  OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Crystal" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Frequency] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Diode_Led" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "IC" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%'  OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Inductor" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Amps] LIKE'{0}%' OR [Resistance] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Resistor" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Type] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Wattage] LIKE'{0}%' OR [Tolerance] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
            End If
        End Sub

    When i am switching between one table to another table it show error, Cannot find column [Type].

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,752

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by Ashwin975 View Post
    Cannot find column [Type]
    What part of that is confusing you?

  11. #11

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by jmcilhinney View Post
    What part of that is confusing you?

    bs.bindingsource = dt the error message as In this part.

    For example: I am searching in the capacitor table, once that’s is done I am switching to connector table, the again to capacitor.

  12. #12
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,752

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    So what part of the error message is confusing you? It says that it can't find a Type column. Why would that be a surprise if the table has no Type column?

  13. #13
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,991

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by Ashwin975 View Post
    Code:
     Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
    
            If ComboBox1.Text = "Capacitor" Then
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Type] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [Tolerance] LIKE'{0}%' OR [Temp_Coefficient] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
    
            ElseIf ComboBox1.Text = "Connector" Then
                txtsearch.Clear()
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%'  OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Crystal" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Frequency] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Diode_Led" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Voltage] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "IC" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%'  OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Inductor" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Amps] LIKE'{0}%' OR [Resistance] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
    
            ElseIf ComboBox1.Text = "Resistor" Then
    
                bs.Filter = String.Format("[Agilenumber] LIKE'{0}%' OR [Description] LIKE'{0}%' Or Rohs LIKE'{0}%' OR [Manufacturer] LIKE'{0}%' OR [ManufacturerPartnumber] LIKE'{0}%' OR [Type] LIKE'{0}%' OR [Packtype] LIKE'{0}%' OR [Value] LIKE'{0}%' OR [Wattage] LIKE'{0}%' OR [Tolerance] LIKE'{0}%' OR [PartNumber] LIKE'{0}%' OR [CellName] LIKE'{0}%' OR Symbol LIKE'{0}%'", txtsearch.Text)
            End If
        End Sub

    When i am switching between one table to another table it show error, Cannot find column [Type].
    you should think again if you want to do the Filter like that.

    you can read the Columns also from the Database, just like the Tables

    here what I mean, place 2 Comboboxes; a Button and a Textbox(multiline)
    then...
    Code:
    Imports System.Data.OleDb
    
    
    Public Class Form3
    
        Public Function ColumnsFromMDBTable(ByVal sMDBFile As String, _
                     ByVal sTable As String) As ArrayList
    
            Dim oColumns As New ArrayList
            Try
                Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & sMDBFile
                Using oConn As New OleDbConnection(sConn)
                    oConn.Open()
                    Dim oTable As DataTable = oConn.GetSchema("Columns")
                    For Each oRow As DataRow In oTable.Rows
                        If oRow("TABLE_NAME") = sTable Then
                            oColumns.Add(oRow("COLUMN_NAME"))
                        End If
                    Next
                End Using
            Catch ex As Exception
                MsgBox("error with Access Table!" & vbCrLf & _
                  ex.Message, MsgBoxStyle.Exclamation)
            End Try
            Return oColumns
        End Function
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sSql As String
            sSql = "Select * From " & ComboBox1.Text
            sSql &= " Where " & ComboBox2.Text
            sSql &= " Like '" & txtSearch.Text & "%'"
    
            TextBox1.Text = sSql
    
        End Sub
    
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim connection As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection()
            connection.ConnectionString = ("Provider=Microsoft.jet.oledb.4.0;data source=E:\Northwind.mdb")
            connection.Open()
    
            Me.ComboBox1.DisplayMember = "TABLE_NAME"
            Dim restrictions() As String = New String(3) {}
            restrictions(3) = "Table"
    
            Me.ComboBox1.DataSource = connection.GetSchema("Tables", restrictions)
    
            connection.Close()
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Dim sMDBFile As String = "E:\Northwind.mdb"
            Dim oColumns As ArrayList = ColumnsFromMDBTable(sMDBFile, ComboBox1.Text)
            ComboBox2.Items.Clear()
            ComboBox2.Items.AddRange(oColumns.ToArray)
            ComboBox2.SelectedIndex = 0
    
        End Sub
    End Class
    this will fill all the Columns from the Table, you might not want that.
    but you can/should fill a second combo with the Columns you want to query with (create a DataTable for combo 2)

    here a Image, and see the sSql, without all those OR's

    Name:  ashwin.jpg
Views: 56
Size:  19.5 KB

    HTH
    Last edited by ChrisE; Aug 1st, 2019 at 08:36 AM.
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  14. #14

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by ChrisE View Post
    you should think again if you want to do the Filter like that.

    you can read the Columns also from the Database, just like the Tables

    here what I mean, place 2 Comboboxes; a Button and a Textbox(multiline)
    then...
    Code:
    Imports System.Data.OleDb
    
    
    Public Class Form3
    
        Public Function ColumnsFromMDBTable(ByVal sMDBFile As String, _
                     ByVal sTable As String) As ArrayList
    
            Dim oColumns As New ArrayList
            Try
                Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & sMDBFile
                Using oConn As New OleDbConnection(sConn)
                    oConn.Open()
                    Dim oTable As DataTable = oConn.GetSchema("Columns")
                    For Each oRow As DataRow In oTable.Rows
                        If oRow("TABLE_NAME") = sTable Then
                            oColumns.Add(oRow("COLUMN_NAME"))
                        End If
                    Next
                End Using
            Catch ex As Exception
                MsgBox("error with Access Table!" & vbCrLf & _
                  ex.Message, MsgBoxStyle.Exclamation)
            End Try
            Return oColumns
        End Function
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim sSql As String
            sSql = "Select * From " & ComboBox1.Text
            sSql &= " Where " & ComboBox2.Text
            sSql &= " Like '" & txtSearch.Text & "%'"
    
            TextBox1.Text = sSql
    
        End Sub
    
        Private Sub Form3_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            Dim connection As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection()
            connection.ConnectionString = ("Provider=Microsoft.jet.oledb.4.0;data source=E:\Northwind.mdb")
            connection.Open()
    
            Me.ComboBox1.DisplayMember = "TABLE_NAME"
            Dim restrictions() As String = New String(3) {}
            restrictions(3) = "Table"
    
            Me.ComboBox1.DataSource = connection.GetSchema("Tables", restrictions)
    
            connection.Close()
        End Sub
    
        Private Sub ComboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ComboBox1.SelectedIndexChanged
            Dim sMDBFile As String = "E:\Northwind.mdb"
            Dim oColumns As ArrayList = ColumnsFromMDBTable(sMDBFile, ComboBox1.Text)
            ComboBox2.Items.Clear()
            ComboBox2.Items.AddRange(oColumns.ToArray)
            ComboBox2.SelectedIndex = 0
    
        End Sub
    End Class
    this will fill all the Columns from the Table, you might not want that.
    but you can/should fill a second combo with the Columns you want to query with (create a DataTable for combo 2)

    here a Image, and see the sSql, without all those OR's

    Name:  ashwin.jpg
Views: 56
Size:  19.5 KB

    HTH

    This Works, But it Didn't filter out the data in Datagridview, When i click on the search button it shows like "Select * From & [Capacitor ] Where Manufacturer Like 'AVX%'", But the filtering is not happening. Any help is appreciated.

  15. #15
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,991

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by Ashwin975 View Post
    This Works, But it Didn't filter out the data in Datagridview, When i click on the search button it shows like "Select * From & [Capacitor ] Where Manufacturer Like 'AVX%'", But the filtering is not happening. Any help is appreciated.
    well does the Sql look correct ?
    Code:
    "Select * From  & [Capacitor ] Where Manufacturer Like 'AVX%'"
    here a good read about Sql ..https://www.w3schools.com/sql/
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  16. #16

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    I need to view the search results in the datagridview. Its not working in that way.

  17. #17
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    102,752

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Why post if you're not going to answer the question posed? Read post #15. It contains a question. Did you even consider that question? Does that SQL actually look right to you? If not, fix it. If so, you don't understand SQL syntax well enough and should do some reading on the subject, which is why post #15 contains a link to some relevant information.

  18. #18

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Code:
       Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
            If Not cnn.State = ConnectionState.Open Then
                'open connection
                cnn.Open()
            End If
    
            Dim sSql As String
            sSql = "Select * From [" & ComboBox1.Text & " ]"
            sSql &= " Where " & ComboBox2.Text
            sSql &= " Like '" & txtsearch.Text & "%'"
            'txtsearch.Text = sSql
            ' dt = New DataTable
            da.SelectCommand = New OleDbCommand(sSql, cnn)
            cmdbldr = New OleDbCommandBuilder(da)
            
            'da.Fill(dt)
            ' bs.DataSource = dt
    
            Dim dt As New DataTable
            'fill data into datatable
            da.Fill(dt) --> Syntax error (missing operator) in query expression 'Cell Name Like '0402%''.
            'offer data to be placed in datagridview
            Me.DataGridView1.DataSource = dt
            cnn.Close()
        End Sub
    Hi All the above code works fine for search for the column which is having no space in column name. But it throws an error , "Cell Name" is the column name having the space. But i need the column name with the same, how can i resolve that issue. For that column alone search is not working.

  19. #19
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,091

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Most of your own posts in this thread contain the answer, post #9 contains lots of examples (even post #18 has one, you just need to repeat the same thing on the line after).

  20. #20
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,991

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Quote Originally Posted by Ashwin975 View Post
    [CODE]

    Hi All the above code works fine for search for the column which is having no space in column name. But it throws an error , "Cell Name" is the column name having the space. But i need the column name with the same, how can i resolve that issue. For that column alone search is not working.
    I can only point out again ..https://www.w3schools.com/sql/sql_ref_as.asp

    scroll down and see the example(look what happend to..Contact Person), also it is worth investing a week or more reading and testing those examples.
    create a Test Database, and use that for all the examples you find on that site
    then you have a Template Database with all possible Sql's(Insert, Delete, Update, Select) you can look into

    take the advice or choose to ignore
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  21. #21

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    39

    Re: Search the Records in Multiple Fields and Multiple Tables based on selection of t

    Thank You Chris and SI the geek. The issue got resolved and here's my code, for the future reference and will be helpful for someone.

    Code:
    Private Sub Search_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Search.Click
            If Not cnn.State = ConnectionState.Open Then
                'open connection
                cnn.Open()
            End If
    
            Dim sSql As String
            sSql = "Select * From [" & ComboBox1.Text & " ]"
            sSql &= " Where " & " [" & ComboBox2.Text & " ]"
            sSql &= " Like '" & txtsearch.Text & "%'"
            'txtsearch.Text = sSql
            ' dt = New DataTabl
            da.SelectCommand = New OleDbCommand(sSql, cnn)
            cmdbldr = New OleDbCommandBuilder(da)
            
            'da.Fill(dt)
            ' bs.DataSource = dt
    
            Dim dt As New DataTable
            'fill data into datatable
            da.Fill(dt)
            'offer data to be placed in datagridview
            Me.DataGridView1.DataSource = dt
            cnn.Close()
        End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width