How can one search for multiple items at once and display the result in a datagrid? for example, i want to search for multiple mobile numbers (in sql server database)at once and display the found numbers and their corresponding names.

6 Years
Discussion Span
Last Post by zx1

Something like:

select FirstName, LastName from MyTable
    where PhoneNumber in ('111-222-3333', '222-333-4444')

The in clause allows you to specify multiple values to compare to and returns whatever matches any one of the values.


i have tried, but it wont work. i have a multiple entry textbox where i enter the phone numbers with a comma separating them, i have a listbox (invisible to the user) where the phone numbers are separated into different strings as independent values and i want my program to search sql server and find all the numbers in the listbox and display the results on a datagrid with the names of the mobile owners. here is my code below. I need help ASAP

Private Sub findButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles findButton.Click

        //'separate numbers in display 
        Dim s As String = findTextBox.Text

        If s.Contains(",") Then

            Dim newstr As String() = s.Split(",".ToCharArray())

            For Each Comma As String In newstr

'//start database connection          
      Dim objDataAdapter As New SqlDataAdapter( _
                "SELECT Name, Mobile FROM PHC_Contacts WHERE Mobile In '" + ListBox1.Items.ToString + "'", objConnection)

                objDataSet = New DataSet()
                'Fill the DataSet object with data...
                objDataAdapter.Fill(objDataSet, "PHC_Contacts")

                'Set the DataView object to the DataSet object...
                objDataView = New DataView(objDataSet.Tables("PHC_Contacts"))

                'Set our CurrencyManager object to the DataView object...
                objCurrencyManager = CType(Me.BindingContext(objDataView), CurrencyManager)

                Dim intPosition As Integer

                'If the search field is not empty then...
                If findTextBox.Text <> "" Then
                    objDataView.Sort = "Mobile"

                    'Find the user_id...
                    intPosition = objDataView.Find(comma)

                    Dim objdatatable As DataTable = objDataSet.Tables("PHC_Contacts")

'//datagrid display 
                    With dashBoard

                        .AutoGenerateColumns = True
                        .DataSource = objDataSet
                        .DataMember = "PHC_Contacts"

                        'Declare and set the alternating rows style...
                        Dim objAlternatingCellStyle As New DataGridViewCellStyle()
                        objAlternatingCellStyle.BackColor = Color.WhiteSmoke
                        dashBoard.AlternatingRowsDefaultCellStyle = objAlternatingCellStyle

                    End With


                    MessageBox.Show("please enter MADEX number", "Mobile Search" _
                    , MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If

                If intPosition = -1 Then
                    'Display a message that the record was not found...

                    ' Otherwise display a message that the record was
                    'found and reposition the CurrencyManager to that

                End If


        End If

    End Sub

hi chibex64

If you look at Momerath's example, there are single quotes around each phone number - '111-222-3333', '222-333-4444'. Your sql statement built by this line

"SELECT Name, Mobile FROM PHC_Contacts WHERE Mobile In '" + ListBox1.Items.ToString + "'",

will enclose the whole list in quotes.

To be honest, I'm not sure how your invisible listbox helps. It might be better to use the comma separated list directly from the text box, something like this:

Dim s As String = "'" & findTextBox.Text.Replace(",", "','") & "'"
Dim sql As String = "SELECT Name, Mobile FROM PHC_Contacts WHERE Mobile In (" & s & ")"
Dim objDataAdapter As New SqlDataAdapter(sql, objConnection)

how i update the previous records which takes the *.-,#,%,$,@,! all these also,i have done but it doesnot take all these values when i am updating fields rather when adding records it takes all these values
plz solve my problem

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.