Stored Procedure Not Executing ASP.NET C#

Question

I really don't know what to do. Could you please help me?

What I'm trying to do is that when a person watns to modify his data shown at screen taken from the DataBase when they finish modifing they click in a button and that's where the stored procedure comes in a checks if the data was modified it updates but if it isn't modified...well it doesn't modify the DataBase BUT when i run the proyect it just doesn't work.

The funny thing is that the Stored Procedure works at SQLServer but at C# won't.

Here's my code:

Stored Procedure:

    Create Procedure SP_PDF
    (@nombre varchar(50), @tel int, @dir varchar(50), @rfc char(13))
    as
    declare @cont int
    set @cont = 0
    while(@cont < 4)
    Begin
        if((select nombre from cat_cliente where rfc = @rfc) != @nombre)
            Begin
                update cat_cliente set nombre = @nombre 
            End
        else
            Begin
                set @cont += 1
            End 
        if((select telefono from cat_cliente where rfc = @rfc) != @tel)
            Begin
                update cat_cliente set telefono = @tel
            End
        else
            Begin
                set @cont += 1
            End
        if((select direccion from cat_cliente where rfc = @rfc) != @dir)
            Begin
                update cat_cliente set direccion = @dir
            End
        else
            Begin
                set @cont += 1
            End
    End

C#:

        String query = "exec SP_PDF @nombre, @tel, @dir, @rfc";
        SqlCommand cmd = new SqlCommand(query, cnx);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@nombre", SqlDbType.VarChar).Value = Nombre.Text;
        cmd.Parameters.AddWithValue("@tel", SqlDbType.Int).Value = Telefono.Text;
        cmd.Parameters.AddWithValue("@dir", SqlDbType.VarChar).Value = Direccion.Text;
        cmd.Parameters.AddWithValue("@rfc", SqlDbType.Char).Value = RFC.Text;

        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet set = new DataSet();

        try
        {
            cnx.Open();
            cmd.ExecuteNonQuery();
            adp.Fill(set);
            Response.Write("<script>window.alert('¡Funciono!');</script>");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnx.Close();
        }

it just doesn't work!!! try to debug and find any exceptions. — Bharadwaj
– Bharadwaj, Commented May 26, 2014 at 5:58
If you put a break point in your catch, does it get hit? If so, what is the exception? — Abe Miessler
– Abe Miessler, Commented May 26, 2014 at 6:01
since you are already passing parameters with Parameters.AddWithValue adding the same in string query is not necessary — Mohamed
– Mohamed, Commented May 26, 2014 at 6:02
"Could not find stored procedure 'exec SP_PDF'." That's the exception that throws. — RalphVB
– RalphVB, Commented May 26, 2014 at 6:05

Deepak Joshi · Accepted Answer · 2014-05-26 06:45:52Z

2

Try This Alter your SP as below

ALTER Procedure SP_PDF
(@nombre varchar(50), @tel int, @dir varchar(50), @rfc char(13))
as
declare @cont int
set @cont = 0


declare @sp_nombre varchar(50) 
declare @sp_tel int
declare @sp_dir varchar(50)
declare @sp_rfc char(13)

select @sp_nombre=nombre,@sp_tel=telefono,@sp_dir=direccion from cat_cliente where rfc = @rfc

while(@cont < 4)
Begin
    if( @sp_nombre <> @nombre)
        Begin
            update cat_cliente set nombre = @nombre 
        End
    else
        Begin
            set @cont += 1
        End 
    if(@sp_tel <> @tel)
        Begin
            update cat_cliente set telefono = @tel
        End
    else
        Begin
            set @cont += 1
        End
    if(@sp_dir <> @dir)
        Begin
            update cat_cliente set direccion = @dir
        End
    else
        Begin
            set @cont += 1
        End
End

Write C# code as below

  String query = "SP_PDF";
        SqlCommand cmd = new SqlCommand(query, cnx);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@nombre", SqlDbType.VarChar).Value = Nombre.Text;
        cmd.Parameters.AddWithValue("@tel", SqlDbType.Int).Value = Telefono.Text;
        cmd.Parameters.AddWithValue("@dir", SqlDbType.VarChar).Value = Direccion.Text;
        cmd.Parameters.AddWithValue("@rfc", SqlDbType.Char).Value = RFC.Text;
        try
        {
            cnx.Open();
            cmd.ExecuteNonQuery();
            Response.Write("<script>window.alert('¡Funciono!');</script>");
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            cnx.Close();
        }

edited May 26, 2014 at 6:45

answered May 26, 2014 at 6:01

Deepak Joshi

1,0668 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

RalphVB Over a year ago

Well...now it runs BUT it doesn´t update the information if I modify it. I think it is my Stored Procedure but I can't error, it runs at Sql Server and that is what doesn't makes sense to me.

RalphVB Over a year ago

It's up there at the description of my question.

Deepak Joshi Over a year ago

I have updated the above code please try the code it will work for you

Aniket Over a year ago

If you modify store procedure then again execute

Deepak Joshi Over a year ago

I have modified SP and C# code please use above code it will works for you i think.First execute sp in SQL server and let me know. Thanks

Mohamed · Accepted Answer · 2014-05-26 05:57:55Z

1

Modify like the following

    SqlCommand cmd = new SqlCommand();
    cmd.Connection = cnx;
    cmd.Text = "SP_PDF";
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@nombre", SqlDbType.VarChar).Value = Nombre.Text;
    cmd.Parameters.AddWithValue("@tel", SqlDbType.Int).Value = Telefono.Text;
    cmd.Parameters.AddWithValue("@dir", SqlDbType.VarChar).Value = Direccion.Text;
    cmd.Parameters.AddWithValue("@rfc", SqlDbType.Char).Value = RFC.Text;

    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataSet set = new DataSet();

    try
    {
        cmd.Connection = cnx;
        cnx.Open();
        cmd.ExecuteNonQuery();
        adp.Fill(set);
        Response.Write("<script>window.alert('¡Funciono!');</script>");
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cnx.Close();
    }

answered May 26, 2014 at 5:57

Mohamed

5303 silver badges14 bronze badges

2 Comments

Krunal Patil Over a year ago

help the OP to understand what changes you have made and how?

RalphVB Over a year ago

"Could not find stored procedure 'exec SP_PDF'." That's the exception that is still throwing.

Aniket · Accepted Answer · 2014-05-26 06:23:21Z

1

If you modify store procedure then again execute

ALTER Procedure SP_PDF
(@nombre varchar(50), @tel int, @dir varchar(50), @rfc char(13))
as
declare @cont int
set @cont = 0
while(@cont < 4)
Begin
    if((select nombre from cat_cliente where rfc = @rfc) != @nombre)
        Begin
            update cat_cliente set nombre = @nombre 
        End
    else
        Begin
            set @cont += 1
        End 
    if((select telefono from cat_cliente where rfc = @rfc) != @tel)
        Begin
            update cat_cliente set telefono = @tel
        End
    else
        Begin
            set @cont += 1
        End
    if((select direccion from cat_cliente where rfc = @rfc) != @dir)
        Begin
            update cat_cliente set direccion = @dir
        End
    else
        Begin
            set @cont += 1
        End
End

and then execute the code again.

answered May 26, 2014 at 6:23

Aniket

18110 bronze badges

1 Comment

RalphVB Over a year ago

I think that my C# code it's executing the SP because it never receives the command to execute it.

Alisworld · Accepted Answer · 2015-11-24 09:40:59Z

As you mention that it's funny thing is that SP working in sql server and not in c#. B'coz there is some mistake in your code.

String query = "SP_PDF";
    SqlCommand cmd = new SqlCommand(query, cnx);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@nombre", SqlDbType.VarChar).Value = Nombre.Text;
    cmd.Parameters.AddWithValue("@tel", SqlDbType.Int).Value = Telefono.Text;
    cmd.Parameters.AddWithValue("@dir", SqlDbType.VarChar).Value = Direccion.Text;
    cmd.Parameters.AddWithValue("@rfc", SqlDbType.Char).Value = RFC.Text;

    SqlDataAdapter adp = new SqlDataAdapter(cmd);
    DataSet set = new DataSet();

    try
    {
        cnx.Open();
        cmd.ExecuteNonQuery();
        adp.Fill(set);
        Response.Write("<script>window.alert('¡Funciono!');</script>");
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        cnx.Close();
    }

OR

Go to this site :- http://www.aspsnippets.com/Articles/Calling-Update-SQL-Server-Stored-Procedures-using-ADO.Net.aspx

Collectives™ on Stack Overflow

Stored Procedure Not Executing ASP.NET C#

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related