Stored procedure for CRUD , proc name is not found when I call in code behind

Question

0.00/5 (No votes)

See more:

, +

Hi I am new to sql ,I want to bind my table in a single SP and make a call in project.

What I have tried:

SQL

Create PROCEDURE [gvempdetails]

     @Action VARCHAR (10),
     @EMPLOYEEID INT ,
     @EMPLOYEENAME VARCHAR(100)= NULL ,
     @DEPARTMENT VARCHAR(100)= NULL ,
     @AGE VARCHAR(30)= NULL,
     @SALARY INT = NULL
     
AS
BEGIN
      SET NOCOUNT ON 
     IF @Action = 'SELECT'
      BEGIN
      SELECT EmpId,EmpName,Dep,Age,Sal
      FROM gvdetails17
       END
       
     IF @Action='INSERT'
       BEGIN
       INSERT INTO gvdetails17(EmpId,EmpName,Dep,Age,Sal) VALUES(@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY)
        END       
            
     IF @Action ='UPDATE'
      BEGIN
      UPDATE gvdetails17 SET EmpName=@EMPLOYEENAME,Dep=@DEPARTMENT,Age=@AGE,Sal=@SALARY WHERE EmpId=@EMPLOYEEID
      END
      
      
     IF @Action='DELETE'
      BEGIN
      DELETE FROM gvdetails17 where EmpId=@EMPLOYEEID
      END
      SET NOCOUNT OFF
      END
<pre lang="c#"><pre>SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlconnection"].ConnectionString);
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                bind();
                BindAge();
                BindSalary();
            }
        }
        protected void bind()
        {
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from [gvempdetails]", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();
            if (ds.Tables[0].Rows.Count > 0)
            {
                gvDetails.DataSource = ds;
                gvDetails.DataBind();
            }
            else
            {
                ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
                gvDetails.DataSource = ds;
                gvDetails.DataBind();
                int columncount = gvDetails.Rows[0].Cells.Count;
                gvDetails.Rows[0].Cells.Clear();
                gvDetails.Rows[0].Cells.Add(new TableCell());
                gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
                gvDetails.Rows[0].Cells[0].Text = "Enter the details";
            }
        }

        protected void ddlAge_SelectedIndexChanged(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();


            cn.Open();
            if (ddlAge.SelectedValue != "")
            {
                string[] ages = ddlAge.SelectedValue.Split('-');
                string from = ages[0];
                string to = ages[1];

                SqlCommand cmd = new SqlCommand("select * from [gvempdetails] WHERE @AGE between  @from and @to", cn);
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }

            else
            {
                SqlCommand cmd = new SqlCommand("select * from [gvempdetails]", cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            cn.Close();
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
        }

        protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
        {
            if (e.CommandName.Equals("Add"))
            {
                TextBox txtEmpId = (TextBox)gvDetails.FooterRow.FindControl("txtempid");
                TextBox txtEmpName = (TextBox)gvDetails.FooterRow.FindControl("txtempname1");
                TextBox txtEmpDep = (TextBox)gvDetails.FooterRow.FindControl("txtdep1");
                TextBox txtAge = (TextBox)gvDetails.FooterRow.FindControl("txtage1");
                TextBox txtSal = (TextBox)gvDetails.FooterRow.FindControl("txtsal1");

                cn.Open();
                SqlCommand cmd =
                new SqlCommand(
                "Insert into [gvempdetails](@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY) values('" + txtEmpId.Text + "','" + txtEmpName.Text + "','" +
                txtEmpDep.Text + "','" + txtAge.Text + "','" + txtSal.Text + "')", cn);
                int result = cmd.ExecuteNonQuery();
                cn.Close();
                if (result == 1)
                {
                    bind();
                    
                }
                else
                {
                    
                }
            }
        }

        protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
        {
            int EmpId = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["EmpId"].ToString());
            string username = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
            cn.Open();
            SqlCommand cmd = new SqlCommand("Delete from [gvempdetails] where @EMPLOYEEID=" + EmpId, cn);
            int result = cmd.ExecuteNonQuery();
            cn.Close();
            if (result == 1)
            {
                bind();

                //lblresult.Text = username + " details deleted successfully";
            }
        }
        protected void gvDetails_RowUpdating(object sender, GridViewUpdateEventArgs e)
        {
            int EmpId = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Value.ToString());
            string username = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
            TextBox txtDep = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtdep");
            TextBox txtAge = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtage");
            TextBox txtSal = (TextBox)gvDetails.Rows[e.RowIndex].FindControl("txtsal");

            cn.Open();
            SqlCommand cmd = new SqlCommand("Update [gvempdetails] set @DEPARTMENT='" + txtDep.Text + "',@AGE='" + txtAge.Text + "',@SALARY='" + txtSal.Text + "' where @EMPLOYEEID=" + EmpId, cn);
            cmd.ExecuteNonQuery();
            cn.Close();

            // lblresult.Text = username + " Details Updated successfully";
            gvDetails.EditIndex = -1;
            bind();
        }
        protected void gvDetails_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
        {
            gvDetails.EditIndex = -1;
            bind();
        }
        protected void gvDetails_RowEditing(object sender, GridViewEditEventArgs e)
        {
            gvDetails.EditIndex = e.NewEditIndex;
            bind();
        }
        protected void gvDetails_OnRowDataBound(object sender, GridViewRowEventArgs e)
        {


        }
        protected void BindAge()
        {
            DataTable dt = new DataTable();
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from Age", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            da.Fill(dt);
            cn.Close();
            ddlAge.DataSource = dt;
            ddlAge.DataTextField = "Age";
            ddlAge.DataValueField = "Age";
            ddlAge.DataBind();
            ddlAge.Items.Insert(0, new ListItem("--Select--"));

        }
        protected void BindSalary()
        {
            DataTable dt = new DataTable();
            cn.Open();
            SqlCommand cmd = new SqlCommand("Select * from Salary1", cn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            da.Fill(dt);
            cn.Close();
            ddlAddSalary1.DataSource = dt;
            ddlAddSalary1.DataTextField = "Salary";
            ddlAddSalary1.DataValueField = "Salary";
            ddlAddSalary1.DataBind();
            ddlAddSalary1.Items.Insert(0, new ListItem("--Select--"));

        }

        protected void ddlAddSalary1_SelectedIndexChanged1(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            cn.Open();
            if (ddlAddSalary1.SelectedValue != "")
            {
                string[] sal = ddlAddSalary1.SelectedValue.Split('-');
                string from = sal[0];
                string to = sal[1];

                SqlCommand cmd = new SqlCommand("Select * from [gvempdetails] WHERE @SALARY between  @from and @to", cn);
                cmd.Parameters.AddWithValue("@from", from);
                cmd.Parameters.AddWithValue("@to", to);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }

            else
            {
                SqlCommand cmd = new SqlCommand("select * from [gvempdetails]", cn);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
            }
            cn.Close();
            gvDetails.DataSource = dt;
            gvDetails.DataBind();
        }
        protected void OnSelectedIndexChanged(object sender, EventArgs e)
        {
            //txtEmpId.Text = gvDetails.SelectedRow.Cells[0].Text;
            //lblempname.Text = gvDetails.SelectedRow.Cells[1].Text;
            //lbldep.Text = gvDetails.SelectedRow.Cells[2].Text;
            //lblage.Text = gvDetails.SelectedRow.Cells[3].Text;
            //lblsal.Text = gvDetails.SelectedRow.Cells[4].Text;

            
        }
        protected void OnPaging(object sender, GridViewPageEventArgs e)
        {
            gvDetails.PageIndex = e.NewPageIndex;
            this.bind();

        }
    }

}

Posted 21-Feb-17 18:03pm

Member 12605293

Updated 21-Feb-17 22:49pm

Er. Puneet Goel

v4

Add a Solution

Comments

Er. Puneet Goel 22-Feb-17 0:20am

What is the problem exactly? You wants to know how to call SP in project?

Member 12605293 22-Feb-17 0:29am

Hi Puneet,
Thanks for your quick reply,I want to know how to wrap all these table inside a single SP

Er. Puneet Goel 22-Feb-17 1:13am

ok I will help you out, can you tell which 3 sql query you want to convert into proc and use it ? In you code there are sevral queries with DML operation.

Member 12605293 22-Feb-17 1:58am

Hi Puneet
This is what I tried
Create PROCEDURE [gvempdetails]

@Action VARCHAR (10),
@EMPLOYEEID INT ,
@EMPLOYEENAME VARCHAR(100)= NULL ,
@DEPARTMENT VARCHAR(100)= NULL ,
@AGE VARCHAR(30)= NULL,
@SALARY INT = NULL

AS
BEGIN
SET NOCOUNT ON
IF @Action = 'SELECT'
BEGIN
SELECT EmpId,EmpName,Dep,Age,Sal
FROM gvdetails17
END

IF @Action='INSERT'
BEGIN
INSERT INTO gvdetails17(EmpId,EmpName,Dep,Age,Sal) VALUES(@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY)
END

IF @Action ='UPDATE'
BEGIN
UPDATE gvdetails17 SET EmpName=@EMPLOYEENAME,Dep=@DEPARTMENT,Age=@AGE,Sal=@SALARY WHERE EmpId=@EMPLOYEEID
END

IF @Action='DELETE'
BEGIN
DELETE FROM gvdetails17 where EmpId=@EMPLOYEEID
END
SET NOCOUNT OFF
END

Er. Puneet Goel 22-Feb-17 3:37am

Does the sp works fine when you execute it ?

Er. Puneet Goel 22-Feb-17 3:38am

Problem is at "SqlCommand cmd = new SqlCommand("Select * from [gvempdetails]", cn);"
You do not call Procedure this way.

Member 12605293 22-Feb-17 4:06am

ok I rectified it,But now it Procedure or Function expects a paramenter which iis not supplied.I knw I have used a paramenter (@Action) for Insert, Update,Delete.But I dont knw how to pass that input parameter

Member 12605293 22-Feb-17 4:07am

protected void bind()
{
cn.Open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
cn.Close();
if (ds.Tables[0].Rows.Count > 0)
{
gvDetails.DataSource = ds;
gvDetails.DataBind();
}
else
{
ds.Tables[0].Rows.Add(ds.Tables[0].NewRow());
gvDetails.DataSource = ds;
gvDetails.DataBind();
int columncount = gvDetails.Rows[0].Cells.Count;
gvDetails.Rows[0].Cells.Clear();
gvDetails.Rows[0].Cells.Add(new TableCell());
gvDetails.Rows[0].Cells[0].ColumnSpan = columncount;
gvDetails.Rows[0].Cells[0].Text = "Enter the details";
}
}

protected void ddlAge_SelectedIndexChanged(object sender, EventArgs e)
{
DataTable dt = new DataTable();

cn.Open();
if (ddlAge.SelectedValue != "")
{
string[] ages = ddlAge.SelectedValue.Split('-');
string from = ages[0];
string to = ages[1];

SqlCommand cmd = new SqlCommand("select * from [gvempdetails] WHERE @AGE between @from and @to", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@from", from);
cmd.Parameters.AddWithValue("@to", to);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}

else
{
SqlCommand cmd = new SqlCommand("select * from [gvempdetails]", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
}
cn.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}

protected void gvDetails_RowCommand(object sender, GridViewCommandEventArgs e)
{
if (e.CommandName.Equals("Add"))
{
TextBox txtEmpId = (TextBox)gvDetails.FooterRow.FindControl("txtempid");
TextBox txtEmpName = (TextBox)gvDetails.FooterRow.FindControl("txtempname1");
TextBox txtEmpDep = (TextBox)gvDetails.FooterRow.FindControl("txtdep1");
TextBox txtAge = (TextBox)gvDetails.FooterRow.FindControl("txtage1");
TextBox txtSal = (TextBox)gvDetails.FooterRow.FindControl("txtsal1");

cn.Open();
SqlCommand cmd =
new SqlCommand(
"Insert into [gvempdetails](@EMPLOYEEID,@EMPLOYEENAME,@DEPARTMENT,@AGE,@SALARY) values('" + txtEmpId.Text + "','" + txtEmpName.Text + "','" +

txtEmpDep.Text + "','" + txtAge.Text + "','" + txtSal.Text + "')", cn);
cmd.CommandType = CommandType.StoredProcedure;
int result = cmd.ExecuteNonQuery();
cn.Close();
if (result == 1)
{
bind();

}
else
{

}
}
}

protected void gvDetails_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
int EmpId = Convert.ToInt32(gvDetails.DataKeys[e.RowIndex].Values["EmpId"].ToString());
string username = gvDetails.DataKeys[e.RowIndex].Values["EmpName"].ToString();
cn.Open();
SqlCommand cmd = new SqlCommand("Delete from [gvempdetails] where @EMPLOYEEID=" + EmpId, cn);
int result = cmd.ExecuteNonQuery();
cn.Close();
if (result == 1)

Er. Puneet Goel 22-Feb-17 4:28am

look at it ?? http://net-informations.com/csprj/data-providers/cs-procedure-parameter.htm

Karthik_Mahalingam 22-Feb-17 1:05am

post only the relevant code..

Member 12605293 22-Feb-17 2:24am

Hi Karthik
I have updated the question.

2 solutions

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)

ejazwaquif · Answer 1 · 2017-02-21T22:49:00

Hi,

Your stored procedure seems to be good but need certain chenges as per your code behide.

Let's take one by one:

1. Bind() function :-

Here you have to change this code

cn.Open();
            //SqlCommand cmd = new SqlCommand("Select * from [gvempdetails]", cn);
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            //DataSet ds = new DataSet();
            //da.Fill(ds);
            //cn.Close();

to - 
            cn.Open();
            SqlCommand cmd = new SqlCommand("gvempdetails", cn);
            cmd.CommandType = CommandType.StoredProcedure;
	    cmd.Parameters.AddWithValue("@Action","SELECT");
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
            cn.Close();

2. ddlAge_SelectedIndexChanged function
As it seems here, you want to filter the employee with Age between your range i.e. from and to
As you want to use these paramters in Stored Procedure(SP) , you have to take them as input parametrs in SP
Like :

@Action VARCHAR (10),
@EMPLOYEEID INT ,
@EMPLOYEENAME VARCHAR(100)= NULL ,
@DEPARTMENT VARCHAR(100)= NULL ,
@AGE VARCHAR(30)= NULL,
@SALARY INT = NULL,
@FrOM INT = NULL,
@TO INT = NULL

and need to add one more Action for this as

IF @Action = 'SELECTAGE'
BEGIN
SELECT EmpId,EmpName,Dep,Age,Sal where age >= @FROM and age <= @TO
FROM gvdetails17
 END

Now the codebehid code,

cn.open();
SqlCommand cmd = new SqlCommand("gvempdetails", cn);
        cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ACTION", "SELECTAGE");
        cmd.Parameters.AddWithValue("@FROM", from);
        cmd.Parameters.AddWithValue("@TO", to);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);

I hope you will understand by above example how to use Stored Procedure from code

just for information,
While creating SqlCommand object we need to pass the commandText and connection.
So in case of SP you have to pass only the name of SP as commandText in our case "gvempdetails"
and rest of the thing you have to pass as parameter like @ACTION,@FROM in our case.
in your stored procedrue you may have observed that some parameters are initialize with NULL like @SALARY INT = NULL
Here the NULL is use to make the parameter optional otherwise you have to pass this parameter on all call
On last suggestion - @Action VARCHAR (10) here, in action type of parametrs you should take atleast 30 char coz there ia a chance
you to take Action name as long string like 'SELECTAGEBYRANGE'

Hope it will help you out!

Thanks,
Ejaz Waquif

angappans · Answer 2 · 2017-02-21T19:06:00

Hi.,

For Sql you create store procedure with input/output parameters. for that input parameters you have declare type variable. depend on that type you will write you queries.

for example

create procedure sp_test(@type varchar(100), @testname varchar(100)=null, @testaddress varchar(100))
as
begin
if(@type='I')
begin
insert into testtable (testname,testaddress) values(@testname,@testaddress)
end
if(@type='SA')
begin
select testaddress from testtable
end
end

from above procedure you will must pass type, then you will return the query result.

In front end (Asp.net) instead of query you will pass store procedure name.

I hope you will get it..

Thanks

Stored procedure for CRUD , proc name is not found when I call in code behind

2 solutions

Solution 3

Solution 2

Add your solution here

Preview 0