Tutorial :Using Variables in a fill query in a TableAdapter



Question:

I’m using C# and Winforms. I’m trying to make a report that gives a list of people that need to renew their memberships if they expire within a user supplied date range. I've got a connection to an Access file, and I'm trying to set up a fill query for a TableAdapter, and I can’t figure out how to put a variable in my "WHERE" clause. The below SQL works to do what I need but obviously I don’t want to hard code the dates. I would like to substitute the dates with the variables “fromDate” and “toDate”. How can I do this? Thanks.

SELECT   [Last Name], [First Name], [Renewal Date]  FROM     Members  WHERE    ([Renewal Date] >= #1/1/2000#) AND ([Renewal Date] <= #1/1/2012#)  ORDER BY [Renewal Date]  


Solution:1

In access here you can find how to define in a query that a value will be used as a paramater.

Here you can find samples of how to set parameter values in .net.


Solution:2

I did a similar thing this morning in a Typed Dataset. I wanted the tableadapter of the typed dataset to get only certain rows. If you open the Typed Dataset in the designer, right-click on it, and choose Configure... you can adjust the Select query. Add a "Where" part, and include your parameter like this:

select * from shoes where shoecolor = @ColorParameter

In code when you are using the tableadapter to fill the dataset, you'll be able to add the value to use for the parameter there.


Solution:3

I don't use TableAdapters, so I could be wrong in this, but you could try doing the following:

DateTime startDate = ...somedate;  DateTime endDate = ...somedate;  myTableAdapter.SelectCommand.Text = GetExpiringMembershipsSql(startDate, EndDate);    ...    public static void GetExpiringMembershipsSql(DateTime startDate, DateTime endDate)  {      string template = @"SELECT   [Last Name], [First Name], [Renewal Date]  FROM     Members  WHERE    ([Renewal Date] >= #{0}#) AND ([Renewal Date] <= #{1}#)  ORDER BY [Renewal Date]";        return string.Format(template, startDate.ToShortDateString(), endDate.ToShortDateString());  }  

This is a rather dirty approach and utilizing parameters would be better, but I'm not very familiar with how access handles parameters.


Solution:4

In a DataSet's TableAdapter , for a Access database connexion , you use the below query if you need a filter parameter .

select var1,var2 from Table where var1 = ?

Cheers !


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »