Using stored procedures with sqldatasource control Part 11

Link for csharp, asp.net, ado.net, dotnet basics and sql server video tutorial playlists
/ kudvenkat
Link for text version of this video
csharp-video-tu...
Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our KZbin channel. Hope you can help.
/ @aarvikitchen5572
In this video we will discuss about using a stored procedure with sqldatasource control, to retrieve and display data in a gridview control. First let's create required tables and populate them with sample data.
Create Table tblDepartment
(
DepartmentId int primary key identity,
Name nvarchar(50)
)
Insert into tblDepartment values('IT')
Insert into tblDepartment values('HR')
Insert into tblDepartment values('Payroll')
Insert into tblDepartment values('Administration')
Create Table tblEmployee
(
EmployeeId int Primary key identity,
Name nvarchar(50),
DeptId int Foreign key references tblDepartment(DepartmentId)
)
Insert into tblEmployee values('Mark',1)
Insert into tblEmployee values('John',1)
Insert into tblEmployee values('Mary',3)
Insert into tblEmployee values('Mike',4)
Insert into tblEmployee values('Pam',3)
Insert into tblEmployee values('David',1)
Insert into tblEmployee values('Tom',2)
Insert into tblEmployee values('Jason',4)
Insert into tblEmployee values('Vicky',3)
Insert into tblEmployee values('Todd',1)
Insert into tblEmployee values('Ben',2)
Stored procedure to retrieve department Ids and Names
Create Procedure spGetDepartments
as
Begin
Select DepartmentId, Name from tblDepartment
End
Stored procedure to retrieve employees by department
Create Procedure spGetEmployeesByDepartmentId
@DepartmentId int
as
Begin
Select EmployeeId, tblEmployee.Name as EmployeeName, tblDepartment.Name as DepartmentName
from tblEmployee
join tblDepartment
on tblDepartment.DepartmentId = tblEmployee.DeptId
where tblDepartment.DepartmentId = @DepartmentId
End
Create an asp.net web application. Drag and drop 2 sqldatasource controls, a dropdownlist and a gridview control on WebForm1.aspx.
Configure "SqlDataSource1" control to retrieve departments data using stored procedure "spGetDepartments"
1. Right click on "SqlDataSource1" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetDepartments" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On the subsequent screen, you can test the query, if you wish to.
8. Finally click Finish.
We are now done configuring "SqlDataSource1" control.
Let's now configure "DropDownList1" to use "SqlDataSource1" control.
1. Right click on "DropDownList1" control and select "Show Smart Tag"
2. Now click on "Choose Data Source" link
3. Select "SqlDataSource1" from "Select a Data Source" dropdownlist
4. Select "Name" from "Select a data field to display in the dropdownlist"
5. Select "DepartmentId" from "Select a data field for the value of the dropdownlist" and click OK
At this point, if you run the application, all the departments should be displayed in the dropdownlist control
Now let us configure "SqlDataSource2" control, to retrieve employees by department.
1. Right click on "SqlDataSource2" control and select "Show Smart Tag"
2. Now click on "Configure Data Source" link
3. Select connection string, from the dropdownlist on "Choose your data connection" screen. You need to have a connection string specified in web.config file.
4. Click Next
5. Select "Specify a custom SQL statement or stored procedure" radio button on "Configure the Select statement" screen and click "Next"
6. Select stored procedure "spGetEmployeesByDepartmentId" from the dropdownlist on "Define custom statements or stored procedures" screen and click "Next"
7. On "Define Parameters" screen, select "Control" from "Parameter Source" dropdownlist
8. Select "DropDownList1" from "ControlID" dropdownlist and click "Next"
9. On "Test Query" screen click "Finish"
Now let us associate "SqlDataSource2" control with "GridView1" control
1. Right click on "GridView1" control and select "Show Smart Tag"
2. Select "SqlDataSource2" from "Choose Data Source" dropdownlist
Finally set AutoPostBack property of DropDownList1 control to true, so that the webform will automatically postback to the server whenever the selection in the dropdownlist changes.
Run the application and notice that, as the selection in the dropdownlist changes, the data in gridview control also changes.

[@eldershima]

I usually don't like listening to Indian voices in tutorials, but I find your English great and your explanations very thorough. Thanks for changing the stereotype.

[@TheZahid2008]

Sir, your teaching techniques are very simple and understandable. Thanks for your effort.

[@Csharp-video-tutorialsBlogspot]

Thank you very much for taking time to give feedback. We will discuss about the exact same scenario in our upcoming videos. Please stay tuned. Please subscribe to my channel if you want to receive an email when I upload new videos. Good Luck

[@Csharp-video-tutorialsBlogspot]

Hi Riya, there are several ways we can do this. You can modify the stored procedure to return "Select" and "-1" along with the rest of the rows or you can add this item to the dropdownlist dynamically in the code behind file. Hope this helps. Good Luck.

[@nle196654]

hi Kudvenkat you are my great teacher. i will introduce to my friends

[@davidespada01]

thanks kudvenkat.. very nice

[@albahrainking]

Thanks Mr Venkat Great tutorial

[@Csharp-video-tutorialsBlogspot]

Hi Nithya, can you please check, if the email is going into spam folder. If that's the case, open the email and click on not spam. Hope this helps. Good Luck

[@karangoyalkkp]

thank you sir... great videos..

[@sarkarpiu1]

Sir, I want to ask you a question about the dropdown list. In this video when you are running the project by default department IT is shown in the drop down list. I want to make it in such a way so that when we run the project "Select" option will appear in the dropdown list and obviously no data will appear in the gridview. Then when the user selects any department , data will be shown accordingly.

[@baishaliaich7151]

hello kudvenkat....i have been watching your videos for gridview tutorial and beyond any doubt they are extremely thorough with strong examples....thank you a lot for that...however i have a question regarding part 11 of gridview series where you have used stored procedure in sql server and connected that with sqlDataSource. my question is how can i achieve same with oracle stored procedure....searched the net but could not find any solution. i am unable to run and test example of part 11 now. any help will be greatly appreciated... thank you again for the awesome explanationns....

[@ww1flyingace263]

A slightly quicker way to set the dropdownlist to AutoPostBack is to click the smart tag on the control and check "Enable AutoPostBack"

[@ajarcilla9933]

Hello Kudvenkat ^_^ I can't find the Define Parameters, is it because I'm using a binding source? I am using VB 2012 and it seems like it is also possible to do things like this. I just can't find where the Define parameters wherein I can set my combobox as control. Thank you for the video ^_^ Keep on posting.

[@1990Hadeel]

Great video , but i have question if i want to create a questions and answers(text box) form the questions i am will retrieve it from QuestionTable in my database and the answers i will insert the values in the answerTable(after the user fill the answer ) what is the best approach to implement it(similar to dynamic survey) ? i have used repeater control but it did not work. :(

[@ashishsah6967]

How to use a stored Procedure with a cursor in it? The GridView fetches only first record, as is returned by cursor. How to read all records in Gridview?

[@opsoftware]

One thing you didn't explained is how to insert in gridview, if gridview is connected with foreign key table? Hope I will get the reply asap.

[@damonlee11]

I am doing the same thing with my project but for some reason the details view will not display. I'm using text box to search instead of drop down list. Help please

[@anuragmalik3636]

great

[@GauravSharma-ct8vn]

sir we can do this widout backend code. plzz show me the same thing using code

[@Nitya878]

Thanks sir.good tutorial..Even if i have subscribed to your channel i am not receiving any mail..