Conditional queries

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi

I have created a simple data entry form by dragging a table onto a form and
letting vs2005 generate all bindings etc. All
works fine. Now I need to be able to view records selectively ie sometimes I
need to view/edit only 'Current' accounts and other times I need to
view/edit only 'Prospect' accounts. How do I implement this selective
behaviour from this point?

Thanks

Regards
 
I have created a simple data entry form by dragging a table onto a form
and letting vs2005 generate all bindings etc. All
works fine. Now I need to be able to view records selectively ie sometimes
I need to view/edit only 'Current' accounts and other times I need to
view/edit only 'Prospect' accounts. How do I implement this selective
behaviour from this point?
Click to expand...

Firstly, please don't crosspost - it just pisses people off...

But I'll answer your post anyway... :-)

Let's say you have an AccountStatus table to hold details of your account
statuses e.g.

AccountStatusID (int) AccountStatus (varchar)
--------------------------------------------------
1 Prospect
2 Current
3 Closed

Let's also say you have an Accounts table to hold details of your accounts
e.g.

AccountID (int) AccountStatusID (int) Account name (varchar) <other
fields>
--------------------------------------------------------------------------------------
1 1 Microsoft
<other values>
2 1 Oracle
<other values>
3 2 Symantec
<other fields>
4 3 Borland
<other fields>

Create a stored procedure called AccountsSelect as follows:

CREATE PROCEDURE AccountsSelect
@AccountStatusID int = NULL
AS

SELECT * FROM Accounts
WHERE AccountStatusID = COALESCE(@AccountStatusID, AccountStatusID)


Now, when you need to view only the Current accounts, change your binding to
"AccountsSelect @AccountID=2".
And, if you want to view all accounts, don't pass the parameter e.g.
"AccountsSelect".
 
I have not played with automatic data binding in VS Whidbey (sorry). I'm
going to guess.

It is quite likely that the bindings included a data adapter. In the data
adapter, you will see a command object assigned for the QueryCommand. That
command object contains the SQL for returning all rows (something like
"Select au_id, au_lname, au_fname from Authors"). If you want to return
fewer rows, you can either modify this statement with a Where clause or you
can create another command object that uses a SQL statement that has the
Where clause.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
 
Back
Top