asked on

Fill query in Visual Basic gives error

I am trying to create a search box to fill my datagridview according to what the user enters in the search textbox. The way I am trying to do this is by using parameters. Trying to do this gives an error from VB.

My code

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Value As String = Me.ToolStripTextBox1.Text
Me.ExtServicesTableAdapter.search(Me.Database2DataSet.ExtServices, Value)

End Sub

The Error

Too many arguments to 'Public Overridable Overloads Function search(dataTable As Database2DataSet.ExtServicesDataTable) As Integer'.

I have followed the way of using parameters as it is posted on the MSDN center. I do not understand the mistake.

My search query in the dataset is

SELECT ServiceID, ServiceName, CustomerID, Technicians, StartDate, EndDate, TimeScheduled, TimeTaken, Description, Solution, Parts, Completed, Location
FROM ExtServices

Kind new in this so will appreciate greatly any help

cauos

you missed to add the WHERE part of your SQL query; when you call that method the adapter expecting a query need a one parameter; but actually there is no parameters

kapetaniou

ASKER

My problem was what to state at the where clause? Since I am using this parameter what should go in the query as a where clause. I looked everywhere and tried different things but no results.
Can u provide an example

cauos

i create one function that take connection string ,sql query and empty dataset as parameters
when user fire click event of the button will take the string in the text and call the other event

 Public Function SelectRows(ByVal dataSet As DataSet, ByVal connectionString As String, ByVal queryString As String) As DataSet
        Using connection As New SqlConnection(connectionString)
            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(queryString, connection)
            adapter.Fill(dataSet)
            Return dataSet
        End Using
    End Function
 
    Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString
        Dim sql As String = "select * from users where userid=" & TextBox1.Text
        Dim dataset As New DataSet()
        SelectRows(dataset, myConnectionString, sql)
        GridView1.DataSource = dataset
        GridView1.DataBind()
    End Sub

Open in new window

kapetaniou

ASKER

Thanks cauos for the fast replies but you have started moving out of bounds. In my original post I use a far more easily function to call an already created query. I create the query in the dataset. What I am asking is how to alter the query to accept the user entered criteria and make VB stop complaining for this code

Dim Value As String = Me.ToolStripTextBox1.Text
Me.ExtServicesTableAdapter.search(Me.Database2DataSet.ExtServices, Value)

Is the way you suggest the only way? Can I not use it like I have started in the beginning

Nasir Razzaq

The problem with the function you are trying to use is that it only selects rows and does not search rows. It only accepts dataset but you are trying to pass it dataset as well as search criteria hence the error. One way is to modify the method signature and add another parameter. Then you could use that parameter in the select query.

ptakja

If I recall, I think to use the Search method of a DataSet you have to provide a string that would look like a WHERE clause...without the WHERE. I didn't know you could use a Search method on an Adapter
Anyway, it's possible it works the same as a DataSet.Search method

For example:
TableAdapter.Search("CustomerID = " & Textbox1.Text)

kapetaniou

ASKER

Thanks all for the replies
CodeCruiser I am sort of new to vb and I do not how to go around doing what you have suggested. To modify the method signature and modified it how?
ptakja I have tried your approach but vb gives an error.
Anymore suggestion guys. I am kind of stuck on making this search work. I do not know if it matters but the search query I am using has as the 'GenerateMethods' the 'Fill' option. It fills the table. Thanks for your efforts

Nasir Razzaq

I am not sure if its relevant but one way of searching to use the RowFilter in views. For example
dim dTable as New DataTable
'fill dTable
dTable.DefaultView.RowFilter = "Name like '%" & txtsearch.text & "%'"
messagebox.show(dTable.DefaultView.Count & " Matching rows found.")

kapetaniou

ASKER

Ok. I have tried using this code to filter my tables. ( 2 tables services and Extservices )

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
Me.ExtServicesBindingSource.Filter = "Technicians LIKE '%" & ToolStripTextBox1.Text & "%'"
Me.ExtServicesBindingSource.Filter = "Description LIKE '%" & ToolStripTextBox1.Text & "%'"
Me.ServicesBindingSource.Filter = "Technicians LIKE '%" & ToolStripTextBox1.Text & "%'"
Me.ServicesBindingSource.Filter = "Description LIKE '%" & ToolStripTextBox1.Text & "%'"
End Sub

The table provides me with some results but not all of them. For example I have 'george' as technician name. When I search with keyword "g" I only get 1 row with george and in the database it has more thatn 10. Also most results are not showing.

Nasir Razzaq

Bare in mind that Filtering would only apply to the data which is already loaded in datatables. It would not search in the database.

kapetaniou

ASKER

Found the problem. Do not the solution.
The fact was that the system only accepts 1 filter per tablebindingsource. So I have deleted the 'description like' filters and I get the results. The problem is that I want my search to universal and search/ match results from all the columns in the tables?

kapetaniou

ASKER

Codecruiser what do you mean by that? So what data would not be presented?

ASKER CERTIFIED SOLUTION

Nasir Razzaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial