Select row in datagrid and populate another datagrid

Hi,

I am populating a customer datagrid with a web service and SQL server. I want to be able to select a customer in the datagrid, click a button and populate an invoice datagrid with invoices related to selected customer. I have done this in asp.net/C# before but feel a bit lost when it comes to WPF.

I now have these two web methods to get customers and invoices

    <WebMethod()> _
    Function GetCustomerInvoices As InvoicesDataTable

        Using taCustomerInvoices As New InvoicesTableAdapter
            Using dtCustomerInvoices As New InvoicesDataTable

                taCustomerInvoices.Fill(dtCustomerInvoices)

                'Return the list of invoices
                Return dtCustomerInvoices
            End Using
        End Using
    End Function


    <WebMethod()> _
    Function GetCustomerList As CustomerDataTable

        Using taCustomers As New CustomerTableAdapter
            Using dtCustomers As New CustomerDataTable

                taCustomers.Fill(dtCustomers)

                'Return the list of customers
                Return dtCustomers
            End Using
        End Using
    End Function

As it is now to populate the datagrids:

Private CustomerServiceClientInstance As New CustomerServiceReference.CustomerServiceSoapClient    
Private Sub CustomerInvoicesDataTable()
        InvoicesDataTableInstance = CustomerClientInstance.GetCustomerInvoices
        Me.dgInvoices.DataContext = InvoicesDataTableInstance
    End Sub

    Private Sub CustomerDataTable()
        CustomerDataTableInstance = CustomerClientInstance.GetCustomerList
        Me.dgCustomer.DataContext = CustomerDataTableInstance
    End Sub

    Private Sub btnAllCustomers_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnAllCustomers.Click
        Call CustomerDataTable()
    End Sub

    Private Sub btnGetCustomerInvoices_Click(ByVal sender As Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnGetCustomerInvoices.Click
        Call CustomerInvoicesDataTable()
    End Sub

And the two datagrids:

        <my:DataGrid IsReadOnly="True" SelectionMode="Single" AutoGenerateColumns="False" x:Name="dgInvoices" ItemsSource="{Binding}">
            <my:DataGrid.Columns>
                <my:DataGridTextColumn Width="SizeToCells" Header="ID" Binding="{Binding Path=InvoiceID}"/>
            <my:DataGridTextColumn Width="SizeToCells" Header="Customer ID" Binding="{Binding Path=CustomerID}"/>
                <my:DataGridTextColumn Width="SizeToCells" Header="Reference" Binding="{Binding Path=InvoiceReference}"/>
                <my:DataGridTextColumn Width="SizeToCells" Header="Amount" Binding="{Binding Path=InvoiceAmount}"/>
                <my:DataGridTextColumn Width="SizeToCells" Header="Date" Binding="{Binding Path=InvoiceDate}"/>
                <my:DataGridTextColumn Width="SizeToCells" Header="Due Date" Binding="{Binding Path=InvoiceDueDate}"/>
                <my:DataGridTextColumn Width="SizeToCells" Header="Description" Binding="{Binding Path=InvoiceDescription}"/>
            </my:DataGrid.Columns>
        </my:DataGrid>

<my:DataGrid SelectedValuePath="CustomerID" IsReadOnly="True" SelectionMode="Single" AutoGenerateColumns="False" x:Name="dgCustomer" ItemsSource="{Binding}">
                <my:DataGrid.Columns>
                <my:DataGridTextColumn Width="SizeToCells" Header="ID" Binding="{Binding Path=CustomerID}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Name" Binding="{Binding Path=CustomerName}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Street" Binding="{Binding Path=CustomerStreet}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="City/Suburb" Binding="{Binding Path=CustomerCity}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="State" Binding="{Binding Path=CustomerState}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Country" Binding="{Binding Path=CustomerCountry}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Email" Binding="{Binding Path=CustomerEmail}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Phone" Binding="{Binding Path=CustomerPhone}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Fax" Binding="{Binding Path=CustomerFax}"/>
                </my:DataGrid.Columns>
            </my:DataGrid>

So what is best practice to enable a user to select a customer and then populate the invoice datagrid with related invoices?

Any help is appreciated.
msglAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tovvenkiCommented:
Hi,
In the btngetCustomerInvoice you are just calling the method  CustomerInvoicesDataTable(), but you are not passing the selected customer details.
How are you passing the details of the selected customer to the GetCustomerInvoices web method.

Thanks and regards,
Venki
0
msglAuthor Commented:
Hi, sorry for the late reply (have been very busy this weekend).

Yes, now I just populate the dgCustomerInvoices with all invoices in the database.
I have a stored procedure in the database that accepts customerid and returns invoices related to that customer.

I'm not sure about best practice though when it comes to execute this stored procedure.
I saw some other examples online (http://bytes.com/topic/asp-net/answers/869839-web-service-call-sql-stored-procedure) but do I really need to have the database connection etc in the web service? I have a CustomerDataset.xsd already with the customer and invoice tables.

When I click the btnGetCustomerInvoices I want to pass along the selected customerID in dgCustomer to a stored procedure in the database via a web service ,return a dataset with invoices related to selected customer.

Please ask further questions is I'm still a bit unclear about the topic.
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

CodeCruiserCommented:
What part of it you need help with? Getting the selected id from grid?
0
msglAuthor Commented:
Hi CodeCruiser,

I guess when I made my first post that what I want to do would be a bit easier than it appears to be (for me). I have read the above links but still feel stuck.

Yes, I need help with getting the selected id from the grid. According to the first article above I first need to populate the customer datagrid with a CustomerDataProvider class and then bind it with an objectdataprovider in the XAML, is that correct? The difference is that I'm using a web service method to return a dataset (code in first post) and how should I edit the CustomerDataProvider class?

    Public Class CustomerDataProvider
       Private adapter As CustomersTableAdapter
       
        Private dataset As NorthwindDataSet
       
        Public Sub New()
            dataset = New NorthwindDataSet()
            adapter = New CustomersTableAdapter()
            adapter.Fill(dataset.Customers)
       End Sub
     
       Public Function GetCustomers() As DataView
           Return dataset.Customers.DefaultView
       End Function
  End Class

0
msglAuthor Commented:
After reading http://www.codeproject.com/KB/WPF/WPFDataGridExamples.aspx over and over I might have gotten an information overload.

Starting again, I added SelectionChanged="dgCustomer_SelectionChanged" to the dgCustomer

<my:DataGrid SelectedValuePath="CustomerID" AutoGenerateColumns="False" x:Name="dgCustomer" ItemsSource="{Binding}" SelectionChanged="dgCustomer_SelectionChanged">
                <my:DataGrid.Columns>
                <my:DataGridTextColumn Width="SizeToCells" Header="ID" Binding="{Binding Path=CustomerID}"/>
                    <my:DataGridTextColumn Width="SizeToCells" Header="Name" Binding="{Binding Path=CustomerName}"/>
                </my:DataGrid.Columns>
            </my:DataGrid>


In the dgCustomer_SelectionChanged event


      '<summary>
      'Obtains all the orders for the given customer.
      '</summary>

       Public Function GetOrdersByCustomer(ByVal CustomerID As Guid) As DataView
        If CustomerID = Nothing OrElse CustomerID = Guid.Empty Then
            Return Nothing
        End If

        Dim view As DataView = CustomerServiceReference.CustomerDataSet.OrderDataTable <- is a type and cannot be used as an expression
        view.RowFilter = String.Format("CUS_ID='{0}'", CUS_ID)
        Return view
    End Function

How do I edit this if I'm using a web service and not an objectdataprovider? Also, conversion from C# to VB.NET is not my strongest skill

Private Sub dgCustomer_SelectionChanged(ByVal sender As System.Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles dgCustomer.SelectionChanged
 Dim grid As DataGrid =  sender as DataGrid
 
    ' pass the customer ID to our Orders datasource via the ObjectDataProvider
    Dim orderProvider As ObjectDataProvider =  Me.FindResource("OrderDataTable") as ObjectDataProvider
    orderProvider.MethodParameters(0) = grid.SelectedValue
End Sub

0
msglAuthor Commented:
Trying a different approach...
I have a MyDataSet.xsd with two tables, ORDERS and CUSTOMERS

ORDERS
OrderID (guid)
CustomerID (Guid)
OrderAmount

with a OrdersTableAdapter with a stored procedure called GetOrdersByCustomer(@CustomerID) which returns OrderID, CustomerID and OrderAmount

The customers table
CUSTOMERS
CustomerID (guid)
CustomerName

' In the GetCustomerOrders button event I want something like

    Private Sub btnGetCustomerOrders_Click(ByVal sender As System.Object, ByVal e As System.Windows.RoutedEventArgs) Handles btnGetCustomerOrders.Click
        Dim CustomerID As Guid
        CustomerID = dgCustomer.SelectedValue

      ' CustomerID is then the parameter that GetOrdersByCustomer takes and returns the orders
      

    End Sub

In the webmethod I want the function to execute the GetOrdersByCustomer procedure in the dataset and return the result or can this be done in the client code?


' Current function to return all orders
      <WebMethod()> _
    Function GetCustomerOrders As OrdersDataTable

        Using taCustomerOrders As New OrdersTableAdapter
            Using dtCustomerOrders As New OrdersDataTable

                taCustomerOrders.Fill(dtCustomerOrders)

                'Return the list of invoices
                Return dtCustomerOrders
            End Using
        End Using
    End Function

Since I don't get many replies I guess I don't explain my problem well enough so please ask more questions.
is this a better way to go?
0
CodeCruiserCommented:
If you already have the dataset on the client and just want to filter it then no need to involve webmethods. Use the rowfilter property to filter the results.
0
msglAuthor Commented:
Hi CodeCruiser,

So I should do like in my fourth post? I don't know how to convert it to vb.net properly.
The fucntion:
/// <summary>
/// Obtains all the orders for the given customer.
/// </summary>
public DataView GetOrdersByCustomer(string customerId)
{
    if (customerId == null || customerId == string.Empty)
    {
        return null;
    }

    DataView view = NorthWindDataProvider.NorthwindDataSet.Orders.DefaultView;
    view.RowFilter = string.Format("CustomerID='{0}'", customerId);
    return view;
}

should be something like:

    Public Function GetOrdersByCustomer(ByVal CustomerID As Guid) As DataView
        If CustomerID = Nothing OrElse CustomerID = Guid.Empty Then
            Return Nothing
        End If

        Dim view As DataView = CustomerReference.CustomerDataSet.OrderDataTable
        view.RowFilter = String.Format("CustomerID='{0}'", CustomerID)
        Return view
    End Function

I get the error that OrderDataTable is a type and cannot be used as an expression.
And then the CustomerGrid_SelectionChanged event:

private void CustomerGrid_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    DataGrid grid = sender as DataGrid;

    // pass the customer ID to our Orders datasource via the ObjectDataProvider
    ObjectDataProvider orderProvider = this.FindResource("Orders") as ObjectDataProvider;
    orderProvider.MethodParameters[0] = grid.SelectedValue;
}

Thanks for your time.
0
CodeCruiserCommented:
Declare the view as datatable and then return the view.defaultview
0
CodeCruiserCommented:
Also view.rowfilter

changes to

View.defaultview.rowfilter
0
msglAuthor Commented:
So something like

    Private Function GetOrdersByCustomer(ByVal CustomerID As Guid) As DataView
        If CustomerID = Nothing OrElse CustomerID = Guid.Empty Then
            Return Nothing
        End If

        Dim view As DataTable
        view = CustomerServiceReference.CustomerDataSet.OrderDataTable
        view.DefaultView.RowFilter = String.Format("CustomerID='{0}'", CustomerID)
        Return view.DefaultView
    End Function

?

On view =  CustomerServiceReference.CustomerDataSet.OrderDataTable I get OrderDataTable is a type in companyclient.CustomerServiceReference.CustomerDataSet and cannot be used as an expression.

Thanks again.
0
msglAuthor Commented:
Ok, final attempt, should be close now

I added a call to the stored procedure in the webmethod

    <WebMethod()> _
    Function GetCustomerOrders(ByVal CustomerID As Guid) As OrdersDataTable
        Using taCustomerOrders As New OrdersTableAdapter
            Using dtCustomerOrders As New OrdersDataTable
               
            'Call the stored procedure in the dataset
                taCustomerOrders.GetOrdersByCustomer(CustomerID)

                'Return the list of Orders
                Return dtCustomerOrders
            End Using
        End Using
    End Function

And then the sub where I get the selected ID (tested with a msgbox to see if it works and it does) and pass it to the webmethod

    Private Sub CustomerOrdersDataTable()
        Dim CustomerID As Guid
        CustomerID = dgCustomer.SelectedValue

        OrdersDataTableInstance = CustomerServiceClientInstance.GetCustomerOrders(CustomerID)
        Me.dgOrders.DataContext = OrdersDataTableInstance
    End Sub

To the customer datagrid I have on selectionchanged
    Private Sub dgCustomer_SelectionChanged(ByVal sender As Object, ByVal e As System.Windows.Controls.SelectionChangedEventArgs) Handles dgCustomer.SelectionChanged
        'Fill the invoice datagrid with selected customer's Orders
        Call CustomerOrdersDataTable()
    End Sub

I have checked that selected customer actually have related Orders in the database but still no Orders are loaded into the invoice datagrid, any ideas?

The invoice datagrid looks like:

        <my:DataGrid IsReadOnly="True" AutoGenerateColumns="False" x:Name="dgOrders" ItemsSource="{Binding}">
            <my:DataGrid.Columns>
                <my:DataGridTextColumn Header="ID" Binding="{Binding Path=InvoiceID}" Visibility="Hidden" />
                <my:DataGridTextColumn Header="Reference" Binding="{Binding Path=InvoiceReference}"/>
                <my:DataGridTextColumn Header="Amount" Binding="{Binding Path=InvoiceAmount}"/>
                <my:DataGridTextColumn Header="Date" Binding="{Binding Path=InvoiceDate}"/>
            </my:DataGrid.Columns>
        </my:DataGrid>
0
msglAuthor Commented:
Typo
I meant order datagrid not invoice datagrid.
0
CodeCruiserCommented:
You are setting the datacontext of grid instead of itemsource property! Add a msgbox in method to see if any rows are returned in the datatable.
0
msglAuthor Commented:
Hi CodeCruiser,

I'm not sure about what you mean, should I change Me.dgOrders.DataContext to Me.dgOrders.Itemssource?


And no, no rows are returned. I put a MsgBox(OrdersDataTableInstance.Rows.Count) and the value is 0. If I put a msgbox(CustomerID.ToString) after CustomerID = dgCustomers.SelectedValue I get the selected Guid in a textbox but it is not passed to the function in the web service, the stored procedure doesn't get any parameter and returns nothing.
0
CodeCruiserCommented:
You need to sort the web method then.
0
CodeCruiserCommented:
You are calling the getcustomers method on the tableadapter inside the method but you are not assigning the return value to the datatable. I would have pasted the correct code but i am replying from my iphone(sitting in microsoft techdays conference!)
0
msglAuthor Commented:
Dim CustomerID as Guid
CustomerID = dgCustomer.SelectedValue
If I put a msgbox(CustomerID.ToString) here I get a msgbox with selected customerid

but when I put a breakpoint on the next row
OrdersDataTableInstance = CustomerServiceClientInstance.GetCustomerOrders(CustomerID)
and step through the application I get that CustomerID is empty and I can't get my head around it
0
msglAuthor Commented:
Ok I'm with you now with the fault in the webmethod
        Using taCustomerOrders As New OrdersTableAdapter
            Using dtCustomerOrders As New OrdersDataTable
               
            'Call the stored procedure in the dataset
                taCustomerOrders.GetOrdersByCustomer(CustomerID)

                'Return the list of Orders
                Return dtCustomerOrders

Should be something like taCustomerOrders.GetOrdersByCustomer(dtCustomerOrders, CustomerID) ?
0
CodeCruiserCommented:
I think it should be

dtCustomerOrders= taCustomerOrders.GetOrdersByCustomer(CustomerID)
0
msglAuthor Commented:
Ok thank you. I had a look at the example on http://msdn.microsoft.com/en-us/library/kda44dwy%28v=VS.80%29.aspx

Anyway, no parameter is passed to the web service method. As above:

Dim CustomerID as Guid
CustomerID = dgCustomer.SelectedValue
If I put a msgbox(CustomerID.ToString) here I get a msgbox with selected customerid

but when I put a breakpoint on the next row
OrdersDataTableInstance = CustomerServiceClientInstance.GetCustomerOrders(CustomerID)
and step through the application I can see that CustomerID is empty
0
CodeCruiserCommented:
You need to get the types right. A guid is not same as string and vice versa.
0
msglAuthor Commented:
Well the msgbox is just so I can check if I get the customer id or not with selectedvalue.
I declare the CustomerID variable as Guid and I set it as the selected customer id in the datagrid which is a guid but why can't I pass it to the web service method?

What's wrong with
Dim CustomerID as Guid
CustomerID = dgCustomer.SelectedValue
OrdersDataTableInstance = CustomerServiceClientInstance.GetCustomerOrders(CustomerID)

?
0
CodeCruiserCommented:
Do you use guid as the type of parameter to the webmethod?
0
msglAuthor Commented:
yeah well I declare the CustomerID as a guid

then
CustomerID = dgCustomer.SelectedValue
OrdersDataTableInstance = CustomerServiceClientInstance.GetCustomerOrders(CustomerID)

then in the webmethod
Function GetCustomerOrders(ByVal CustomerID As Guid) As OrdersDataTable

Or did I miss anyhting?
0
msglAuthor Commented:
I managed to solve the problem now but I don't know how solid the work around is.
In the app.config I have <system.servicemodel><bindings><basicHttpBinding><binding name="CustomerServiceSoap" and here I had to change maxBufferSize and maxReceivedMessageSize to 2000000 (just a number I picked) instead of the default 65536.

Is this the way to do it or should I do it in another way?

Thanks.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CodeCruiserCommented:
If increasing the message size has solved the problem then it looks like your method was returning a lot of rows. This is fine as long as its working but try to reduce the amount of data you transmit down the line to client.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.