asked on

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.

Mark Ely

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

ste5an

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.

Mark Ely

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

shpresa

ASKER

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

Mark Ely

Run SQL Profiler and debug it...

Kyle Abrahams, PMP

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)

shpresa

ASKER

there is only 1 dataentry ID per OrgID. So it should choose dataentry ID where OrgID = 1.

shpresa

ASKER

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

Mark Ely

@PosFeed1 as nchar(10) = NULL

Mark Ely

Set the default @PossFeed1 to null

somewhere around line 56

shpresa

ASKER

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?

ASKER CERTIFIED SOLUTION

Mark Ely

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

shpresa

ASKER

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.

Mark Ely

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.

shpresa

ASKER

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();

shpresa

ASKER

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.

Mark Ely

Remove the try and catch again. Keep it off until you discovered all the bugs.