Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Search not responding to query parameters, gridview vb.net

Posted on 2014-09-29
11
Medium Priority
?
226 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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 2000 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

Stack Overflow Podcast - Developer Story

Welcome to the Stack Overflow podcast recorded Thursday July 20 at Stack Overflow Headquearters in NYC. Your hosts today are podcast regulars Jay Hanlon, David Fullerton, and Ilana Yitzhaki, plus the quite irregular Matt Sherman (Stack Overflow Engineering Manager extraordinaire)

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)…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

670 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