my update functions does not work

protected void btnSave_Click(object sender, EventArgs e)
    {
        if ((Session["OrgID"] != null))
        {
           
            // Insert Participant info
            SqlCommand cmd = new SqlCommand("sp_DataEntry", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@OrgID", Session["OrgID"].ToString()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss1", TaskAss1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed1", PosFeed1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle1", BriefHuddle1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step1", Step1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff1", Handoff1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges1", Challenges1.Text.Trim()));

try
            {
                myConnection.Open();
 
                    //update function
                    cmd.ExecuteNonQuery();
     
                    txtTexbox1.Text = "Record has been saved updated";

            }


            catch (Exception exc)
            {
                txtTexbox1.Text = exc.Message;
            }
            finally
            {
                myConnection.Close();

            }



        }
        else
        {
            Session.Abandon();
            Response.Redirect("../default.aspx", true);
        }

    }
   

and my sql procedure.
ALTER   PROCEDURE [dbo].SP_CCC(
      @OrgID as int,
            @TaskAss1 as nchar(10),
      @PosFeed1 as nchar(10),
      @BriefHuddle1 as nchar(10),
      @Step1 as nchar(10),
      @Handoff1 as nchar(10),
      @Challenges1 as nchar(10)

)      

 AS BEGIN
 declare @DataEntryID int         
 
 set @DataEntryID  = (select DataEntryID from Tbl_DataEntry where OrgID = @OrgID)
IF EXISTS (SELECT * FROM Tbl_DataEntry WHERE DataEntryID = @DataEntryID AND OrgID = @OrgID)
      update Tbl_DataEntry set
 OrgID = @OrgID,

            TaskAss1 = @TaskAss1,
      PosFeed1 = @PosFeed1 ,
      BriefHuddle1 = @BriefHuddle1,
      Step1 = @Step1,
      Handoff1   = @Handoff1,  
      Challenges1   = @Challenges1

      
      where DataEntryID = @DataEntryID
 ELSE
      insert into Tbl_DataEntry
      
(
      OrgID ,
            TaskAss1 ,
      PosFeed1 ,
      BriefHuddle1 ,
      Step1 ,
      Handoff1 ,
      Challenges1
 )
      
      VALUES
      (
      
      @OrgID ,
            @TaskAss1,
      @PosFeed1,
      @BriefHuddle1,
      @Step1,
      @Handoff1,
      @Challenges1,

)



end


It save save and retrive, but does not update.
no error is coming.
shpresaAsked:
Who is Participating?
 
Mark ElySenior Coldfusion DeveloperCommented:
Yes... setting a null value to the incoming sp helps handle situations where you are not sending the value in.
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Remove the try and catch so you can see the error.

Your update exists  within the try (Line 18).. temporarily remove it as well as the catch lines 30 to 38
0
 
ste5anSenior DeveloperCommented:
Come on, your IF is testing for DataEntry AND OrgID, but your UPDATE is changing ALL DataEntry rows.

btw, your stored procedure cannot compile. Cause the INSERT has a comma too much. When ignoring this error, it still is missing the DataEntry value.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Mark ElySenior Coldfusion DeveloperCommented:
Run this and see what error you get..

protected void btnSave_Click(object sender, EventArgs e)
    {
        if ((Session["OrgID"] != null))
        {
           
            // Insert Participant info
            SqlCommand cmd = new SqlCommand("sp_DataEntry", myConnection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add(new SqlParameter("@OrgID", Session["OrgID"].ToString()));

            cmd.Parameters.Add(new SqlParameter("@TaskAss1", TaskAss1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@PosFeed1", PosFeed1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@BriefHuddle1", BriefHuddle1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Step1", Step1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Handoff1", Handoff1.Text.Trim()));
            cmd.Parameters.Add(new SqlParameter("@Challenges1", Challenges1.Text.Trim()));


            {
                myConnection.Open();
  
                    //update function
                    cmd.ExecuteNonQuery();
      
                    txtTexbox1.Text = "Record has been saved updated";

            }


          

        }
        else
        {
            Session.Abandon();
            Response.Redirect("../default.aspx", true);
        }

    }
   

and my sql procedure.
ALTER   PROCEDURE [dbo].SP_CCC(
      @OrgID as int,
            @TaskAss1 as nchar(10),
      @PosFeed1 as nchar(10),
      @BriefHuddle1 as nchar(10),
      @Step1 as nchar(10),
      @Handoff1 as nchar(10),
      @Challenges1 as nchar(10)

)      

 AS BEGIN
 declare @DataEntryID int         
 
 set @DataEntryID  = (select DataEntryID from Tbl_DataEntry where OrgID = @OrgID)
IF EXISTS (SELECT * FROM Tbl_DataEntry WHERE DataEntryID = @DataEntryID AND OrgID = @OrgID)
      update Tbl_DataEntry set 
 OrgID = @OrgID,

            TaskAss1 = @TaskAss1,
      PosFeed1 = @PosFeed1 ,
      BriefHuddle1 = @BriefHuddle1, 
      Step1 = @Step1,
      Handoff1   = @Handoff1,  
      Challenges1   = @Challenges1

      
      where DataEntryID = @DataEntryID
 ELSE
      insert into Tbl_DataEntry
      
(
      OrgID ,
            TaskAss1 ,
      PosFeed1 ,
      BriefHuddle1 ,
      Step1 ,
      Handoff1 ,
      Challenges1 
 )
      
      VALUES
      (
      
      @OrgID ,
            @TaskAss1,
      @PosFeed1,
      @BriefHuddle1,
      @Step1,
      @Handoff1,
      @Challenges1,

)



end

Open in new window

0
 
shpresaAuthor Commented:
To Mark ELy,
thanks, i did and i still don't see error, besides the message "record has been update"

To ste5an, sorry, i was cutting the rest of fields to make the coding shorter and i forgot the coma there, the store procedure works fine and it is doing the insertion but it does not update. Thanks.

I
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Run SQL Profiler and debug it...
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
What if there's more than one dataentryID?

imagine
DataEntryID   OrgID
1                            1
2                            2
3                            1


if @orgID = 1 which one do you choose?

max(dataentryid) top 1 . .. order by?

set @DataEntryID  = (select max(DataEntryID) from Tbl_DataEntry where OrgID = @OrgID)
0
 
shpresaAuthor Commented:
there is only 1 dataentry ID per OrgID. So it should choose dataentry ID where OrgID = 1.
0
 
shpresaAuthor Commented:
I run the store procedure, and it returned a null value when the dataentryID exist.
The error says that Procedure or function 'sp_DataEntry' expects parameter '@PosFeed1', which was not supplied.

DECLARE      @return_value int

EXEC      @return_value = [dbo].[sp_DataEntry]
            @OrgID = 6,
            @TaskAss1 = N'02/2003',
            @Handoff1 = N'04/2000'

SELECT      'Return Value' = @return_value
0
 
Mark ElySenior Coldfusion DeveloperCommented:
@PosFeed1 as nchar(10) = NULL
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Set the default @PossFeed1 to null

somewhere around line 56
0
 
shpresaAuthor Commented:
DECLARE      @return_value int

EXEC      @return_value = [dbo].[sp_DataEntry]
            @OrgID = 6,
            @TaskAss1 = N'01/2001',
            @PosFeed1 = N'01/2001',
            @BriefHuddle1 = N'01/2001',
            @Step1 = NULL,
            @Handoff1 = NULL,
            @Challenges1 = NULL

SELECT      'Return Value' = @return_value

Once, i declared all other values to null, it return a value of 0 The update took place. So the problem must be with my code not on the store procedure

Is it because i need to enter null value on the code itself if no data entered on the text box?
0
 
shpresaAuthor Commented:
Ah, after being so close, now i see another errror

Error: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

have never seen this before.
0
 
Mark ElySenior Coldfusion DeveloperCommented:
It is good that your removed the try /catch... That was preventing you from seeing those errors.  If you absolutely feel it is necessary to add it back once you have debugged your code be aware of it in the future if you ever have to debug it again.  

Also running SQL Profiler on this helps a lot during debugging.
0
 
shpresaAuthor Commented:
Thank you , i did added agian the try and catch and i changed the coding of data fields to do.
            if (!IsEmpty(TaskAss1.Text))
                cmd.Parameters.Add(new SqlParameter("@TaskAss1", TaskAss1));
            else
                cmd.Parameters.Add(new SqlParameter("@TaskAss1", DBNull.Value));

and now requires some sort of mapping. Do you know what is this about?

Exception Details: System.ArgumentException: No mapping exists from object type System.Web.UI.WebControls.TextBox to a known managed provider native type.

Source Error:


Line 315:  
Line 316:                    //update function
Line 317:                    cmd.ExecuteNonQuery();
0
 
shpresaAuthor Commented:
Ok, I fixed the above error by place .Trim() to all fields and my  update function is back to not working again.

It seems like there is something else going wrong with my code.
0
 
Mark ElySenior Coldfusion DeveloperCommented:
Remove the try and catch again.  Keep it off until you discovered all the bugs.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.