Using Variables in a fill query in a TableAdapter

Question

Hi, 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]

Answer 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.

Answer 2

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.

Answer 3

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.