Solved

Search not responding to query parameters, gridview vb.net

Posted on 2014-09-29
11
221 Views
Last Modified: 2014-10-05
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
Comment
Question by:Mike Eghtebas
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40350876
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40351195
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40351357
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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40351384
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40351441
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40351451
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
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 500 total points
ID: 40351465
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 40351471
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
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 0 total points
ID: 40352329
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 40353709
Glad you got things worked out Mike.
0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40361907
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question