TableAdapters with Dynamic SQL Select

[shalan99]

Hi everyone,

[note: I have also posted this in the Window Forms section]
I have a VB WindowsForms app using SQLServer as my db, and strongly-typed datasets. On my Main Form I have a DataGRidView, that is populated via the TableAdapter with the Fill method.

What I have also is different 5 T-SQL select statements (FillBy...), and another windows form where users can choose only 1 of 5 Filter options at a time (radioButtons) to dynamically change the content of the Grid.

What I instead want to do is somehow have One generic T-SQL command to handle if the user wants to display data in the grid by a combination of different filter options...so essentially changing the radioButtons to checkboxes. I have read an article on codeProject (http://www.codeproject.com/vb/net/TableAdapter.asp) that confused me a bit, but in any case, if I were passing in strings as parameters it would be easy to constuct a dynamic SQL statement for that, but I also have 1 boolean, 2 dates and 2 strings as possible parameter values .

Please could some kind soul help me out?

Much thanx!
Shalan99

 

[cjard]

create this SQL in your tableadapter:

SELECT
  *
FROM
  person
WHERE
  (name = @name OR @name IS NULL) AND
  (age = @age OR @age IS NULL) AND
  (address = @address OR @address IS NULL) AND
  [I]blah...[/I]

TA gets method FillByVaried(datatable, name, age, address, blah..)


Now if you want search by name and age:
myTA.FillByVaried(dataTable, "Fred", 23, DBNull.Value, blah..)

Now if you want search by address only:
myTA.FillByVaried(dataTable, DBNull.Value, DBNull.Value, "101 East Street", blah..)


I use oracle, where empty string '' and NULL are the same. I can databind some controls up so that if the user leaves them blank, they are sent as "" empty string, which DB interprets as null. TO do a search, i clear the form, add one row, let the user type, and then call:

myTA.FillByVaried(dataTable, dt.Rows(0).Name, dt.Rows(0).Age, dt.Rows(0).Address, blah..)


For string values, maybe your DB draws a distinction between '' and NULL, then youll have to fiddle the query:

WHERE
(name = @name OR @name = '')


Whatever it takes to make that test resolve to TRUE if the user provided NO VALUE.

Hopefully this makes sense

 

[shalan99]

Hi cjard and thanx,

I actually just logged on now to rply to my own post here that I have partially solved my problem.

What I did this morning is:

1. For the boolean column I am writing: "...where (BoolCol = @BoolVal1 OR BoolCol = BoolVal2)..."
   If the user ticks the checkbox to 'filter' by boolean values then I'll either pass in 2 False vals or 2 True vals depending on their choice. If that option wasn't checked then I pass in both True & False
2. For the Date filter, if not chosen, I will pass in the min date (01/01/1753) and the max date (31/12/9999....i think, but that does seem to work), otherwise if it is chosen, then I simply pass in the users' dates.
3. For the string column, if not chosen as a filter option, then I pass in a % for wildcard, otherwise, I pass in the user's string
4. HERE IS WHERE I AM CURRENTLY STUCK.....I lastly have an integer column. If the user does select to filter by an integer value, I can simply pass that thru as a parameter. But what do I pass in for that parameter if the user does not select to filter by an Integer option?

I hope Im making sense with all of the above. When I get home, I will try and whip up a quick screenshot of what I want to accomplish.

Thanx for all your help again, cjard...really appreciate it!

 

[cjard]

Well if youre happy with the idea of passing booleans in, you can write this:

SELECT * FROM table
WHERE
  (@boolTicked = false OR boolCol = @boolVal) AND
  (@dateTicked = false OR dateCol = @dateVal) AND
  (@stringTricked = false OR stringCol = @stringVal) AND
  (@intTicked = false OR intCol = @intVal)

Now your query will look like on the table adapter:

FillByXXX(datatable, chkBool.Checked, BOOL_VALUE, chkDate.Checked, DATE_VALUE, chkString.Checked, STRING_VAL, chkInt.Checked, INT_VAL)

It's a bit messier than mine (i prefer using nulls to indicate wildcard/ignore that search term) but now, only the options you ticked will affect the search result

 

[cjard]

But what do I pass in for that parameter if the user does not select to filter by an Integer option?

Erm. Just like the dates, pass in the min and max values?

Tbh, i dont really like the approach of passing these in as huge ranges because it's extra processing compared to providing a TRUE or FALSE, but if you can get dates working, then ints are no different!

 

[shalan99]

Hi cjard, and thanx.

I am not passing all boolean values as parameters. As promised, I have just constructd a quick representation of what I want to do (attached pic).

I already hav most of the SQL Select sorted out, except for the Integer part. Just like how I am passing in a "%" for a string wildcard in the event that "By Document Name" is not checked (so that DocumentName doesn't affect my select), I need to somehow ascertain what wildcard character or method I can use for the IntegerColumn (DeptID).

Thus far:

SELECT DocumentID, DocumentName, Active, DateSubmitted, DeptID
FROM Documents
WHERE (DocumentName = @DocName)
AND (Active = @Active1 OR Active = @Active2)
AND (DateSubmitted BETWEEN @Date1 AND @Date2)
AND (DeptID = @DeptID)

the last AND... is where I need the wildcard method for the integer, but obviously an IntegerColumn doesn;t accept anything besides....an integer!

What can I do or how can I construct my select differently to allow "all" DeptID's through if the Department Checkbox is not ticked on the form?

I haven't tried using ....DeptID IN @DeptID, but I doubt that will work.

if u need more clarity on what I am trying to accomplish then please let me know. Im sorry also for the image labelling....just trying to be elementary as possible for the sake of exposition.

rgdz

Shalan99

 

[shalan99]

In addition, I know that passing in the DocumentName as a string that users have to type in like how I did is silly, but I just whipped this up quickly for demonstration

 

[shalan99]

also, as per your last comment...i agree about the xtra processing with the dates issue. But if u could tell me also, how I can pass in the min & max dates. I just tried passing "Min(DateSubmitted)" and it gave me an error.

 

[cjard]

Sometimes, i really wonder if people actually read my posts at all..

Right.

WHAT DO YOU DO WHEN YOUR USER DOESNT TICK A DATE?

Dim fromDate, toDate As Date
If chkDate.Checked Then
  fromDate = dtpFromDate.Value
  toDate = dtpToDate.Value
Else
  fromDate = Date.MinValue
  toDate = Date.MaxValue
EndIf

myTa.FIllByX(datatable, blah, fromDate, toDate, blah)

Its the same for integer!!! Dates ARE integers to a database (well, actually they are doubles but..)
Maybe if you used the SQL like date >= @fromDate AND date < @toDate instead of BETWEEN, you'd go "ah.. of course.. i can do the same with integer!"

 

[shalan99]

OMG! sorry, u were right...I didnt read your post properly. It works now when I say ...AND (DeptID BETWEEN @DeptID1 AND DeptID2)....

Sorry about that cjard, but thank u so much for all your help!!! I really do appreciate it
....and sorry for the blond moment there!