How do I filter data as per the selected drop down using stored procedure?

Question

0.00/5 (No votes)

See more:

I have database tables : customer, customertranscations and transactions.
I want to create a dropdown list with the customer name and then on selection of a specific customer i want to view the purchase details of the customer that they have purchased within the last 31 days. I have completed the query part but i am having hard time figuring out how to do this.
I want to display it in datagridview but not use sqldatasource and use the visual studio GUI to do it all. I want to try using stored procedure.

What I have tried:

SELECT c.customer_id, c.name, t.transactions_date, t.payment_amount 
from customer c INNER JOIN customertranscations ct ON c.customer_id = ct.customer_id 
INNER JOIN transactions t on ct.transactions_id= t.transactions_id WHERE 
t.transactions_date >=DATEADD(DAY, -2, getdate() - 31 ) AND c.customer_id = 'cus1'

Posted 3-May-18 23:44pm

Subit Timalsina

Updated 4-May-18 1:43am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

W∴ Balboos, *GHB* · Answer 1 · 2018-05-04T01:44:00

Solution 1

Creating a stored procedure can be, at the level you need it, learned rather quickly from Create a Stored Procedure | Microsoft Docs[^]

The basic format is:

CREATE PROCEDURE SP_NAME_YOU_LIKE (
  @Declare_Input datatype, 
  @moreInput     datatype,
  etc.
)
AS
--  Your SQL Query of any sort.  Use the @values for controlling filters, 
such as
SELECT * FROM Some_Table WHERE colVal=@Declare_Input

Posted 4-May-18 1:44am

W∴ Balboos, GHB

Comments

Subit Timalsina 4-May-18 10:08am

Actually i meant i can do the stored procedure but i want to know how it can be done from code behind. i am really confused on that

W∴ Balboos, GHB 4-May-18 11:48am

C# has a namespace for access to the databases
- build the query that calls your stored procedure and use it.
- when the results come back - do what you want with them.

You "WANT" a lot - well, then start looking up how to do it! You have amazing resources available to you with the internet. I taught myself to do these things from real books - limited resources - and a lot of resolve. You need to make the effort - which is usually hard work.

Also, note that "c.customer_id = 'cus1' " in your query, above, is going to really limit your results!