none
SQL query RRS feed

  • Question

  • I have a datagridview that displays a set of records.  I want to use an SQL query to filter the records (ACCESS) for display.

    The filter needs to be based on a single string field that contains a comma delimited string of ID numbers/characters.

    a record in the field might look like:

    1002354, 9888133, 1118765, ......,

    What I want to do is to scan the field and check each number/character set against a variable to display all records that have a number/character set that matches the variable.

    I have run a number of different queries, but have never tried this and have no idea if it can even be done.  I would think it can be, since I have done this in the past using just ACCESS to search a field for a specific number/character set.


    gwboolean

    Wednesday, August 2, 2017 5:02 AM

Answers

  • Hi gwboolean,

    According to your description, the one field would hold multiple value, like this:productname1="111,112,111,113" or productname1="111,113,114,115" or other? If yes, the code above still work fine, because no matter how many values it has, it's just a string.

     Dim dt As New DataTable
            Dim arrayA As String() = {"111,112,111,113", "111,113,114,115", "113", "114"}
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True"
            Using con As New SqlConnection(str)
                con.Open()
                Dim sql As String = "select * from Test5 where productName in (@productname1, @productname2,@productname3,@productname4)"
                Using cmd As New SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@productname1", arrayA(0))
                    cmd.Parameters.AddWithValue("@productname2", arrayA(1))
                    cmd.Parameters.AddWithValue("@productname3", arrayA(2))
                    cmd.Parameters.AddWithValue("@productname4", arrayA(3))
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gwboolean Tuesday, August 8, 2017 4:32 PM
    Tuesday, August 8, 2017 2:27 AM
    Moderator

All replies

  • Hi gwboolean,

    According to you description, you can use SQL IN operator in SQL line. Please refer to the code below.

    Dim dt As New DataTable
            Dim arrayA As String() = {"111", "112", "113", "114"}
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True"
            Using con As New SqlConnection(str)
                con.Open()
                Dim sql As String = "select * from Test5 where productName in (@productname1, @productname2,@productname3,@productname4)"
                Using cmd As New SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@productname1", arrayA(0))
                    cmd.Parameters.AddWithValue("@productname2", arrayA(1))
                    cmd.Parameters.AddWithValue("@productname3", arrayA(2))
                    cmd.Parameters.AddWithValue("@productname4", arrayA(3))
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using

    And you just query sql database one time to implement multi-conditional queries.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Cor Ligthert Wednesday, August 2, 2017 8:38 AM
    Wednesday, August 2, 2017 8:31 AM
    Moderator
  • Cherry,

    I am probably completely wrong, but it appears to me that your code would put the four values in the array into a field from a single record.  Is that right?

    I have a form (below) that displays a single record of an item/part/assembly.  In that form there is a DGV (Item sources) that shows a list of vendors/sources that supply that item.  The item is data from one table and the vendors are from another table.

    What I am wanting to do is to search through a single field of the Vendor table, that already contains one or more comma delimited values (I am not wedded to comma delimited, they could be space delimited or other form of delimiter). 

    What needs to occur is that there is a variable with a defined value equal to the Item ID number (Validation #).  The query would then search the field of each record of the Vendor table and would display in the DGV each record that contained a value in that field that matches the variable. 

    I might be way off on this, but the way I read your code that is not what is done.  Can you walk me through it and explain where I went wrong with what I am seeing?

    I have done this in the past (long ago) using a database table.  I have searched through a text field for say a particular word.  However, I have never done this from Visual Basic.


    gwboolean



    • Edited by gwboolean Wednesday, August 2, 2017 4:49 PM
    Wednesday, August 2, 2017 4:43 PM
  • Hi gwboolean,

    >>but it appears to me that your code would put the four values in the array into a field from a single record.  Is that right?

    Form my code, it can search all data that the productname1="111" or productname1="112" or productname1="113" or productname1="114".

    You can see the follow picture, it can help you to understand.

    The DataBase Table:

    The filter data:

    >>What I am wanting to do is to search through a single field of the Vendor table, that already contains one or more comma delimited values (I am not wedded to comma delimited, they could be space delimited or other form of delimiter). 

    I do not specifically understand this sentence, you can give an example to help us to understand.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, August 3, 2017 10:17 AM
    Moderator
  • Cherry, sorry to take so long to get back.  OK, I can see what you are setup to do.  That is cool, but what I am/was looking at is a single field holding more than one value.  So instead of a field holding one value per record:

    111

    112

    111

    113

    The field would hold multiple values in a single record, 111, 112, 111, 113

    I am not actually using this any longer, but I still would like to be able to search through a text field that might have multiple values or words.


    gwboolean

    Monday, August 7, 2017 2:52 PM
  • Hi gwboolean,

    According to your description, the one field would hold multiple value, like this:productname1="111,112,111,113" or productname1="111,113,114,115" or other? If yes, the code above still work fine, because no matter how many values it has, it's just a string.

     Dim dt As New DataTable
            Dim arrayA As String() = {"111,112,111,113", "111,113,114,115", "113", "114"}
            Dim str As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=D:\C# and VB Support\Example(VB)\Demo\Data3.mdf;Integrated Security=True"
            Using con As New SqlConnection(str)
                con.Open()
                Dim sql As String = "select * from Test5 where productName in (@productname1, @productname2,@productname3,@productname4)"
                Using cmd As New SqlCommand(sql, con)
                    cmd.Parameters.AddWithValue("@productname1", arrayA(0))
                    cmd.Parameters.AddWithValue("@productname2", arrayA(1))
                    cmd.Parameters.AddWithValue("@productname3", arrayA(2))
                    cmd.Parameters.AddWithValue("@productname4", arrayA(3))
                    Dim adapter As New SqlDataAdapter(cmd)
                    adapter.Fill(dt)
                    DataGridView1.DataSource = dt
                End Using
            End Using

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by gwboolean Tuesday, August 8, 2017 4:32 PM
    Tuesday, August 8, 2017 2:27 AM
    Moderator
  • Thanks Cherry.  That was very helpful.

    gwboolean

    Tuesday, August 8, 2017 4:32 PM