how would i filter results on my bindingnavigator when using a stored procedure call with linq?

tonyonlinux

I have a stored procedure that takes and gives me all the results based on authorFirst and authorLast works fine when i execute it in sql server

USE [Books]
GO
/****** Object: StoredProcedure [dbo].[usp_SelectBasedonAuthor] Script Date: 02/08/2010 17:52:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* Name: usp_SelectBasedonAuthor
Description: Get authorfirst and last from user and do a select to get all the rows
meeting that criteria
Author: Tony Stegall
Modification Log: Change

Description Date Author
Created Procedure 2/8/2010 Tony Stegall

*/
ALTER PROCEDURE [dbo].[usp_SelectBasedonAuthor]
--Variables
@AuthorFirst varchar(50),
@AuthorLast varchar(50)

AS

SELECT Book.*, Authors.*
FROM Book INNER JOIN
(Authors INNER JOIN BookAuthors ON Authors.ID = BookAuthors.AuthorID)
ON Book.ID = BookAuthors.BookID
WHERE AuthorFirst = @AuthorFirst AND AuthorLast = @AuthorLast;

in c# I have the following but obviously it is not filtering correctly and I'm not understanding why

private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
{
try
{
using (var db = new mombooksDataContext(Properties.Settings.Default.BooksConnectionString2))
{

                String\[\] splitstring = null;
                string treeviewtext = treeView1.SelectedNode.Text;
                splitstring = treeviewtext.Split(',');
                var filter = db.usp\_SelectBasedonAuthor(authorFirstTextBox.Text, authorLastTextBox.Text);

                bindingNavigator1.BindingSource.DataSource = filter.ToList();

            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

        
    }

My objective is to click on the author name in the treeview and simply call the stored procedure based on that. and pass the results back so i can navigate the results using the bindingnavigator

tonyonlinux · Description Date Author Created Procedure 2/8/2010 Tony Stegall

I even tried doing the following but nothing seems to help...

try
{

            using (var db = new mombooksDataContext(Properties.Settings.Default.BooksConnectionString2))
            {
                
                
                                                              
                                      
                String\[\] splitstring = null;
                string treeviewtext = treeView1.SelectedNode.Text;
                splitstring = treeviewtext.Split(',');
                var query = db.Books.SelectMany(o => o.BookAuthors.Where(a => a.Author.AuthorFirst == splitstring\[1\].ToString()));

                bookBindingSource.DataSource = query.ToList();


              
                
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString());
        }

do i have to format the returned data somehow in order for the datasoure to understand it ?

tonyonlinux

Okay well I got it work sorta but i'm having an issue with the where clause. If i hardcode the value it works fine but when I take and pass it the splitstring[1].tostring() which according to the debugger contains "tony" then i get no results..

String[] splitstring = null;

                string treeviewtext = treeView1.SelectedNode.Text.Trim();

                splitstring = treeviewtext.Split(',');

                var query = from b in db.Books

                            join ba in db.BookAuthors on b.ID equals ba.BookID

                            join a in db.Authors on ba.AuthorID equals a.ID

                            join bn in db.BookNumbers on ba.BookID equals bn.BookID

                            where a.AuthorFirst ==splitstring\[1\].tostring()

```
                            select b;
```

at line 9 if i make that where.a.AuthorFirst == "tony" all is fine however if i leave it like it is then the query ends up being null. if i set splitstring[1] = "tony" in here specifically then it works. does spaces matter or am I overlooking something simple here ?