Solved

Search not responding to query parameters, gridview vb.net

Posted on 2014-09-29
11
218 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
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 28

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 28

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 28

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 28

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

821 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