how to insert the value using store procedure in asp.net using C#

Question

0.00/5 (No votes)

See more:

My run mode as follows

Faculty(Tab Container)

When i click the tab container in gridview as follows

For each dropdownlist rating from Poor to excellent

1.Poor,2.Fair,3.Good,4.Very Good,5.Excellent. in teh below dropwonlist when user select the Good means 3 value to be saved in the database. (for drodpownlist1 to dropdownlist10)

Faculty Name Question1 Question2 Question3 Question4 Question5

XXX dropdownlist1 dropdownlist2 dropdownlist3 dropdownlist4 dropdownlist5
YYY dropdownlist6 dropdownlist7 dropdownlist8 dropdownlist9 dropdownlist10

I have on esubmit as follows

I want to save the above gridview records in table.

Faculty table Structure as follows

Facid Autoincrment
Facname varchar(50)
Question1 varchar(50)
Question2 varchar(50)
Question3 varchar(50)
Question4 varchar(50)
Question5 varchar(50)

In table i want to shows the record as follows

Facid Facname Question1 Question2 Question3 Question4 Question5
1 XXX 5 4 3 2 1
2 YYY 5 4 3 2 1

i want to create store procedure in that store procedure i want to insert the record in the Faculty table using store procedure.

Store procedure as follows

SQL

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


 ALTER procedure [dbo].[PreseaFac]
 as 
 begin

  declare @Facid  varchar(50),
          @Facname varchar(50),
          @FacQ1 varchar(50),
          @FacQ2 varchar(50),
          @FacQ3 varchar(50),
          @FacQ4 varchar(50),
          @FacQ5 varchar(50)

  insert into Tb_Faculty_Feedback (Facid ,Facname,FacQ1,FacQ2,FacQ3,FacQ4,FacQ5) 
  values (@Fid,@Facid,@FacQ1,@FacQ2,@FacQ3,@FacQ4,@FacQ5)
  end.

in the above store procedure how to insert the ratings poor means 1 value to saved in the table using store procedure.

I have one submit button in that subit button i call the store procedure.

for that how can i insert the value using store procedure.

Regards,
Jegan B.

Posted 6-Jun-14 3:23am

jegan1991

Updated 6-Jun-14 3:25am

Abhishek Pant

v2

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)

Debabrata_Das · Answer 1 · 2014-06-06T04:38:00

Hello friend, as per my understanding of this problem, you have to do the following changes:

First of all make the stored procedure parametric, means you have to add parameters so that you can pass the values from your code behind:

SQL

ALTER procedure [dbo].[PreseaFac]
	@Facid  varchar(50),
	@Facname varchar(50),
	@FacQ1 varchar(50),
	@FacQ2 varchar(50),
        @FacQ3 varchar(50),
        @FacQ4 varchar(50),
        @FacQ5 varchar(50)
AS
BEGIN
	INSERT INTO Tb_Faculty_Feedback 
	(Facid ,Facname, FacQ1, FacQ2, FacQ3, FacQ4, FacQ5) 
	VALUES (@Fid,@Facid,@FacQ1,@FacQ2,@FacQ3,@FacQ4,@FacQ5)
END

I assume you must have added controls on your aspx page as follows:

XML

<asp:DropDownList ID="DropDownList1" runat="server">
    <asp:ListItem Value="1">Poor</asp:ListItem>
    <asp:ListItem Value="2">Fair</asp:ListItem>
    <asp:ListItem Value="3">Good</asp:ListItem>
    <asp:ListItem Value="4">Very Good</asp:ListItem>
    <asp:ListItem Value="5">Excellent</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="DropDownList2" runat="server">
    <asp:ListItem Value="1">Poor</asp:ListItem>
    <asp:ListItem Value="2">Fair</asp:ListItem>
    <asp:ListItem Value="3">Good</asp:ListItem>
    <asp:ListItem Value="4">Very Good</asp:ListItem>
    <asp:ListItem Value="5">Excellent</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="DropDownList3" runat="server">
    <asp:ListItem Value="1">Poor</asp:ListItem>
    <asp:ListItem Value="2">Fair</asp:ListItem>
    <asp:ListItem Value="3">Good</asp:ListItem>
    <asp:ListItem Value="4">Very Good</asp:ListItem>
    <asp:ListItem Value="5">Excellent</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="DropDownList4" runat="server">
    <asp:ListItem Value="1">Poor</asp:ListItem>
    <asp:ListItem Value="2">Fair</asp:ListItem>
    <asp:ListItem Value="3">Good</asp:ListItem>
    <asp:ListItem Value="4">Very Good</asp:ListItem>
    <asp:ListItem Value="5">Excellent</asp:ListItem>
</asp:DropDownList>
<asp:DropDownList ID="DropDownList5" runat="server">
    <asp:ListItem Value="1">Poor</asp:ListItem>
    <asp:ListItem Value="2">Fair</asp:ListItem>
    <asp:ListItem Value="3">Good</asp:ListItem>
    <asp:ListItem Value="4">Very Good</asp:ListItem>
    <asp:ListItem Value="5">Excellent</asp:ListItem>
</asp:DropDownList>
<asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Button" />

Then you have to write ADO.NET code to call this stored procedure from the Submit button click as follows:

C#

string conStr = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
using (SqlConnection con = new SqlConnection(conStr))
{
    con.Open();

    SqlParameter Facid = new SqlParameter();
    Facid.ParameterName = "@Facid";
    Facid.Value = "1";  // passing a hard coded value

    SqlParameter Facname = new SqlParameter();
    Facname.ParameterName = "@Facname";
    Facname.Value = "DD"; // passing a hard coded value

    SqlParameter FacQ1 = new SqlParameter();
    FacQ1.ParameterName = "@FacQ1";
    FacQ1.Value = DropDownList1.SelectedValue;

    SqlParameter FacQ2 = new SqlParameter();
    FacQ2.ParameterName = "@FacQ2";
    FacQ2.Value = DropDownList2.SelectedValue;

    SqlParameter FacQ3 = new SqlParameter();
    FacQ3.ParameterName = "@FacQ3";
    FacQ3.Value = DropDownList3.SelectedValue;

    SqlParameter FacQ4 = new SqlParameter();
    FacQ4.ParameterName = "@FacQ4";
    FacQ4.Value = DropDownList4.SelectedValue;

    SqlParameter FacQ5 = new SqlParameter();
    FacQ5.ParameterName = "@FacQ5";
    FacQ5.Value = DropDownList5.SelectedValue;

    SqlCommand cmd = new SqlCommand("PreseaFac", con);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add(Facid);
    cmd.Parameters.Add(Facname);
    cmd.Parameters.Add(FacQ1);
    cmd.Parameters.Add(FacQ2);
    cmd.Parameters.Add(FacQ3);
    cmd.Parameters.Add(FacQ4);
    cmd.Parameters.Add(FacQ5);

    cmd.ExecuteNonQuery();
}

Note: I have kept your database changes intact. Please make sure that you implement some exception handling as well.

- DD