Solved

Search not responding to query parameters, gridview vb.net

Posted on 2014-09-29
11
204 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
  • 6
  • 5
11 Comments
 
LVL 28

Expert Comment

by:sammySeltzer
Comment Utility
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 33

Author Comment

by:Mike Eghtebas
Comment Utility
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 28

Expert Comment

by:sammySeltzer
Comment Utility
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
 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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 28

Expert Comment

by:sammySeltzer
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 33

Author Comment

by:Mike Eghtebas
Comment Utility
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 28

Assisted Solution

by:sammySeltzer
sammySeltzer earned 500 total points
Comment Utility
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 33

Author Comment

by:Mike Eghtebas
Comment Utility
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 33

Accepted Solution

by:
Mike Eghtebas earned 0 total points
Comment Utility
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 28

Expert Comment

by:sammySeltzer
Comment Utility
Glad you got things worked out Mike.
0
 
LVL 33

Author Closing Comment

by:Mike Eghtebas
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

744 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now