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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.