Optional parameter query .FILL

[pachjo]

Is there a way to have a parameter as optional on the .FILL method of a tableadapter?

I have a fill method query that has a parameter @Void which I thought I could set as optional as in a stored procedure but have come unstuck trying to achive it.

I'm trying to have it so that if the @Void is false or omitted the returned data will only include rows whose void field is false. If @Void is true the data will return all rows regardless of void being true or false

 

[pachjo]

looking into this I think the approach I should be taking is to amend the commandtext dependant on the setting required for @Void.

However when I look at the properties of the tableadapter in the dataset designer I see the tableadapter has a property called SelectCommand and in there one called CommandText.

But when in code I cannot access these properties? How do I access them through code?

 

[cjard]

Is there a way to have a parameter as optional on the .FILL method of a tableadapter?

No. The methods generated by the tableadapter custom tool have to be compatible with all flavours of .NET, and flavours like C# dont support optional parameters.

I have a fill method query that has a parameter @Void which I thought I could set as optional as in a stored procedure but have come unstuck trying to achive it.

I'm trying to have it so that if the @Void is false or omitted the returned data will only include rows whose void field is false. If @Void is true the data will return all rows regardless of void being true or false

IF @Void IS NULL THEN
  SELECT * FROM table WHERE void = false
ELSE
  SELECT * FROM table
ENDIF

That said though.. this is a boolean value.. its only 2 states.. Whats the hardship of writing in code:

If userComboChoice = "All Rows" Then
  myTA.FillByVoid(myDT, True)
Else
  myTA.FillByVoid(myDT, False)
endif

Or to be really smart:

myTA.FillByVoid(myDT, comboChoice = "All Rows")

I forsee no need for optional here

 

[pachjo]

Mmm not sure I get you or you get me?

What I have done is to have the method called .Fill that populates the table with all records and one called .FillFalse which as the name suggests only populates the table with non void (false) records.

The user form has a checkbox that I query to determine if all records or just non void records should be returned.

As I have to perform this check I decided to call the relevant method.

It works but I am sure your opinion shall tell me otherwise as to the being messy or not

 

[cjard]

If you really want to combine them then get smart with your query:

SELECT * FROM table WHERE (@param = TRUE) OR (voidCol = @param)


When @param is true, WHERE true = TRUE OR voidCol = TRUE, all rows will return because of the true = true
When @param is false, WHERE false = true OR voidCol = false, only rows where voidCol is false will return

And call then like I suggest

 

[pachjo]

Hi cjard, I just revisted this on a new project I am working on and your last post works fandabbydosey

Thnx