ObjectDataSource trouble

Question

I have been given a small project by the company I have applied for. They want a small application using asp.net GridView, FormView and an ObjectDataSource with a DataSet (xsd) file. I have been doing 3-tier applications but in a different way, I have my DataAccess layer as a c sharp file with methods accepting params and returning datatables etc. My business layer is another class with static methods returning business objects and collections making use of the DAL class. Now this ObjectDataSource is sth i did not really like, its generating some code that i can't even see where?

I could make the application work upto some point(90%). The other 10% is what my question about. I need to make a search by name functionality. There are two ObjectDataSources 1 and 2. ObjectDatasource1 just gets every record from the table on the first load. When search button cliked I set the datasource of gridview to the second ObjectDataSource which has a method called GetDataByNameSearch that is supposed to accept a parameter (all defined by wizzzardz) and parameter source is control (TextBox.Text). While my FormView works fine where it gets its parameter from QueryString, this search returns nothing. Search statement is as follows:

SELECT     Birthday, CreatedAt, ID, Name, Surname
FROM         Users
WHERE     (Name LIKE '%@name%') OR
                      (Surname LIKE '%@name%')

Any idea about how these ObjectDataSources are supposed to be used, and make life easier(!)

Answer 1

Without code samples its hard to tell, but I did notice that your use of SQL parameters is a bit unusual.

You have:

SELECT Birthday, CreatedAt, ID, Name, Surname 
FROM Users 
WHERE (Name LIKE '%@name%') OR (Surname LIKE '%@name%')

I'm not sure if SQL '@' parameters will work when there are speechmarks around them. I think the above example will just result in the literal string '%@name%' being used in the query, which is why you might be getting no results.

SQL Parameters are usually used like this:

SELECT Birthday, CreatedAt, ID, Name, Surname 
FROM Users 
WHERE (Name LIKE @name) OR (Surname LIKE @name)

... but of course then you will lose the '%' wildcards. You might be able to add them directly into the parameter strings before they are passed to the query. If that is not possible, maybe try this version:

SELECT Birthday, CreatedAt, ID, Name, Surname 
FROM Users 
WHERE (Name LIKE '%' + @name + '%') OR (Surname LIKE '%' + @name + '%')

Answer 2

Better to use objectdatasouce.filter property and filter the objectdatasource that is bind to your controls.