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.
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 ListBox1.Items.Add(Comma) '//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 Else 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... Else ' Otherwise display a message that the record was 'found and reposition the CurrencyManager to that 'record... End If Next End If End Sub
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 is one of my assignment that I have to do. it is a triangle calculator. everything well with it but it just the calculation. when I run it, ...
Good man James helped me learn die roll board game programming in Java,
and I applied it on Ladders and Snakes. Made one in Java. Now I'm learning to do ...
Hi, I have been given a project to finish in school in 3 phases, the thing is I am not that good when it comes to programing. The project was ...