TableAdapter.fillby criteria problem

[johncassell]

Hi there,

Was hoping someone could help me out with this problem please.

I have a tableadapter which loads information based on my criteria

Me.CustomersTableAdapter.FillBy(Me.RedcarWorkshopDataSet.Customers, rptcustomercodefilter.Text)

This works fine if I enter a value in my 'rptcustomercodefilter' textbox. But if I don't enter a value I would like it to display all information. At the moment it just displays nothing.

How can I achieve this please?

Thanks

John

 

[Arg81]

You mean you want it to display ALL records if the textbox is empty?

Just whack it in a IF-END IF

i.e.

If me.rptCustomerCodeFilter.Text = "" Then
  me.customersTableAdapter.Fill(me.redcarworkshopDataSet.Customers)
Else
  me.CustomersTableAdapter.FillBy(me.redcarworkshopDataSet.Customers, rptCustomerCodeFilter.Text)
End If

If me.redcarworkshopDataSet.Customers.Rows.Count = 0 Then
  Messagebox.Show("Sorry, no records found for that text")
End If

 

[snoopdog]

I am guessing the textbox.text information is then sent to a variable in a stored procedure ? I use a similar fill for all of my tables based on certain criteria in comboboxes to limit the data initially loaded from the tables. When I leave my textboxes or comboboxes empty my tables still load, I am not sure why you are having an empty database load. If you are passing parameters to a stored procedure maybe you could post the stored procedure ?

 

[Arg81]

I am not sure why you are having an empty database load

Because it's a parameterised query and the query string is blank. Nothing matches the "blank" query string so it returns no rows...

that's standard procedure of a parameterised query on a table. Nothing was mentioned about SP's , and that's a different ball game....

 

[cjard]

Hi there,

Was hoping someone could help me out with this problem please.

I have a tableadapter which loads information based on my criteria

Me.CustomersTableAdapter.FillBy(Me.RedcarWorkshopDataSet.Customers, rptcustomercodefilter.Text)

This works fine if I enter a value in my 'rptcustomercodefilter' textbox. But if I don't enter a value I would like it to display all information. At the moment it just displays nothing.

How can I achieve this please?

Modify your SQL:

SELECT * FROM customers WHERE CustomerCode = @CustomerCode OR @CustomerCode = ''


Now, if the param is blank, the second part of the where clause becomes TRUE for all rows


Oh, and, youre SUPPOSED to EDIT the name of FillBy to be FillByXXX

e.g. this sql.. FillByCustomerCode

IF you have 3 FillBys:

FillByCustomerCode
FillByLastName
FillByWhatever