Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Search not responding to query parameters, gridview vb.net

Question: How can I modify the following code to respond to my entries for the three parameters I have?

No matter what I enter in the criteria boxes, the gridview displays all records. It seems line 8 below shoud change to td.FilledBy not fill. But when I change it to FilledBy, I get: 'FilledBy' is not a member of 'Project1.dsMyDatabaseTableAdapter.tblCustomersTableAdapter'

I do not know how to handle it. Please see the attached image of .xsd file.
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        'Execute GetDataBy with three search parameters to populate tableAdapter ta
        ta.GetDataBy(Me.FirstNameTextBox.Text.Trim, Me.LastNameTextBox.Text.Trim, Me.CityTextBox.Text.Trim)

        MessageBox.Show(Me.FirstNameTextBox.Text).ToString()
        'fill data table per ta
        ta.Fill(dt)

        'assign dt as data source to the gridview
        Me.TblCustomersDataGridView.DataSource = dt

    End Sub

Here is the query:
SELECT        CustomerID, FirstName, LastName, Address, City, State, ZipCode, Phone, Email
FROM            tblCustomers
WHERE        (FirstName LIKE @FirstName + '%') AND (LastName LIKE @LastName + '%') AND (City LIKE @City + '%')

Open in new window

xsdFile.png
0
Mike Eghtebas
Asked:
Mike Eghtebas
  • 6
  • 5
2 Solutions
 
sammySeltzerCommented:
Hi,

How are you tying the parameters back to control on your markup?

For instance, you need something like this:

 cmd.Parameters.AddWithValue("@FirstName ", FirstNameTextBox.Text)
 cmd.Parameters.AddWithValue("@LastName ", LastNameTextBox.Text)
etc
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Sammy,

The query (FillBy, GetDataBy(@FirstName, @LastName,@City):
SELECT        CustomerID, FirstName, LastName, Address, City, State, ZipCode, Phone, Email
FROM            tblCustomers
WHERE        (FirstName LIKE @FirstName + '%') AND (LastName LIKE @LastName + '%') AND (City LIKE @City + '%')

Open in new window

has 3 parameters as you can see on the attached figure:TableAdapter queryAnd then there is this code to to pass the values in the text boxes to the query:

ta.GetDataBy(Me.FirstNameTextBox.Text.Trim, Me.LastNameTextBox.Text.Trim, Me.CityTextBox.Text.Trim)

Mike
0
 
sammySeltzerCommented:
I am not used to seeing it done that way.

However, if you can post entire code, including stored proc,  I will see what I can do to help you resolve it.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Sammy,

This is ADO.NET coding (Typed DataSet)? Do you have experience using ADO.NET? Please see: http://msdn.microsoft.com/en-us/library/ms171905.aspx

This is a good starting point just in case someone needs it: http://msdn.microsoft.com/en-us/library/8bw9ksd6.aspx

Mike
0
 
sammySeltzerCommented:
Well, click on my name and you will get all the answers you would need about my abilities.

Although, I don't visit here as much as I would like to due to heavy workload.

There are a million ways of using ADO.NET and in your case, you are using with gridview.

So, your approach is just one way of doing it.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Lets put it this way.

I have a table adapter in which I have a query built into it (both table adapter and query are giving above).

I want to use this query (in the table adapter) to fill the data table defined as:

Public Class frmCustomerSearch

    'Declare class-level dataset objects for the search form
    Dim dt As New dsWSCGSoftware.tblCustomersDataTable
    Dim ta As New dsWSCGSoftwareTableAdapters.tblCustomersTableAdapter
Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        'Execute GetDataBy with three search parameters to populate tableAdapter ta
        ta.GetDataBy(Me.FirstNameTextBox.Text.Trim, Me.LastNameTextBox.Text.Trim, Me.CityTextBox.Text.Trim)

        MessageBox.Show(Me.FirstNameTextBox.Text).ToString()
        'fill data table per ta
        ta.Fill(dt)

        'assign dt as data source to the gridview
        Me.TblCustomersDataGridView.DataSource = dt

    End Sub
End  Class

Open in new window


The above code doesn't work, how can I correct it (so that the result of this query will show in my gridview)?

Mike
0
 
sammySeltzerCommented:
As stated earlier my friend, you will need to use the full blown parametized query.

For instance,

Private sub selectRec(....)

          sqlStatement = "SELECT CustomerID, FirstName, LastName, Address, City, State, ZipCode, Phone, Email
FROM tblCustomers
WHERE  (FirstName LIKE @FirstName + '%') AND (LastName LIKE @LastName + '%') AND (City LIKE @City + '%')

         connection.Open()

            Dim cmd As New SqlCommand(sqlStatement, connection)
            cmd.Parameters.AddWithValue("@FirstName ", FirstNameTextBox.Text)
            cmd.Parameters.AddWithValue("@LastName", LastNameTextBox.Text)
            cmd.Parameters.AddWithValue("@City", CityTextBox.Text)
             cmd.CommandType = CommandType.Text
rest of stuff....
            

Open in new window


Then you can use the button click event like you are using it and it will work.

My code is not tested but the concept is clear and solid and you should be able to follow it.

Otherwise, please post back for any additional questions.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Sammy,

The idea here is to use a TableAdapter query (http://msdn.microsoft.com/en-us/library/ms171905.aspx). As the saying goes, there are many ways to skin an apple. The intention here is to use table adapter query.

I think if I build this query in SQL and then include it with the table adapter, it might do the job.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
Hi Sammy,

I guess I figured out my error:

In the table adapter, I have FillBy and GetDataBy quries. This is the parameter query I have made and saved as these default names. Because I only wanted to fill a data table not get data, I needed only FillBy option (to fill data table based on query "FillBy"). Fill by takes "data table" variable "dt" as first parameter:
    Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click

        'Execute GetDataBy with three search parameters to populate tableAdapter ta
        ta.CustomerSearchResult(dt, Me.FirstNameTextBox.Text.Trim, Me.LastNameTextBox.Text.Trim, Me.CityTextBox.Text.Trim)

        'assign dt as data source to the gridview
        Me.TblCustomersDataGridView.DataSource = dt

    End Sub

Open in new window


This one works.

I appreciate the input.

Mike
0
 
sammySeltzerCommented:
Glad you got things worked out Mike.
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
I needed only FillBy option (to fill data table based on query "FillBy"). Fill by takes "data table" variable "dt" as first parameter.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now