locked
Problem Error Handling in Update Statement? RRS feed

  • Question

  • Here is my stored procedure on updating records :

    </

    ALTER 
    PROCEDURE
    [dbo].[sp_UpdatetoShipped] 
    ( 
    @Date datetime, 
    @SerialNumber 
    varchar
    (50), 
    @User 
    varchar
    (50), 
    @WorkWeek 
    varchar
    (50)  
    ) 
    AS 
    BEGIN 
     
    UPDATE dbo
    .FG_FILLIN SET Status='SHIPPED',DateModified=@Date,ModifiedBy=@User,WorkWeek=@WorkWeek where (Status='KITTED')and SerialNumber=@SerialNumber 
     
    END 
    />

    Then this is my DAL:

    </

    public int UpdatetoShipped(FillinEntity fin)  
    { 
    SqlConnection conn = new SqlConnection(connStr);  
    conn
    .Open(); 
    SqlCommand cmd = new SqlCommand("sp_UpdatetoShipped", conn);  
    cmd
    .CommandType =CommandType.StoredProcedure;  
    try 
    { 
    cmd
    .Parameters.Add("@SerialNumber", SqlDbType.VarChar,50).Value = fin.SerialNumber;  
    cmd
    .Parameters.Add("@WorkWeek", SqlDbType.VarChar, 50).Value = fin.WorkWeek;  
    cmd
    .Parameters.Add("@Date", SqlDbType.DateTime).Value = DateTime.Now.ToString();  
    cmd
    .Parameters.AddWithValue("@User", fin.ModifiedBy);  
    return cmd.ExecuteNonQuery();  
    } 
    catch 
    { 
    throw;  
    } 
    finally 
    { 
    cmd
    .Dispose(); 
    conn
    .Close(); 
    conn
    .Dispose(); 
    } 
    } 
    />

    My BLL:

    </

    public int UpdatetoShipped(FillinEntity fin)  
    { 
    DAL pDAL
    = new DAL();  
    try 
    { 
    return pDAL.UpdatetoShipped(fin);  
    } 
    catch 
    { 
    throw;  
    } 
    finally 
    { 
    pDAL
    = null;  
    } 
    } 
    />

    And MY UI:

    </

    string filepath2 = txtPath2.Text;  
    Stream stream2 = new FileStream(filepath2, FileMode.Open, FileAccess.Read, FileShare.Read);  
    ExcelMapper<FillinEntity> exceltoshipped = new ExcelMapper<FillinEntity>();  
    IExcelParser excelParser2 = new ExcelReaderExcelParser(stream2);  
    IExcelRowMapper<FillinEntity> mapper2 = new ShippedRowMapper();  
    IEnumerable<FillinEntity> fillin2 = exceltoshipped.ListAll(excelParser2, mapper2); 
    int intResult = 0;  
    BAL pBAL
    = new BAL(); 
    try 
    { 
    foreach (FillinEntity fin in fillin2)  
    { 
    fin
    .ModifiedBy = loggedUser; 
    intResult
    = pBAL.UpdatetoShipped(fin); 
    } 
    if (intResult > 0)  
    MessageBox.Show("Record Updated Successfully.");  
    else 
    MessageBox.Show("Record couldn't Updated Check Serial");  
    } 
    catch (Exception ee)  
    { 
    MessageBox.Show(ee.Message.ToString());  
    } 
    finally 
    { 
    pBAL
    =null;  
    } 

    />

    My problem is it always says updated succussfully. But if i updated it again as duplicate update i want to show serial is already updated.

    • Edited by Crimsonland Friday, August 13, 2010 12:40 AM NO</>
    Thursday, August 12, 2010 7:59 AM

Answers

  • There are two options to consider.  One would be to get the values for that serial number before updating it to see if they are the same.  The other would be to go to the where clause of the update and put, "and (DateModified<>@Date or ModifiedBy<>@User or WorkWeek<>@WorkWeek) "

    That way it will only update the line if one of the values differs.  Then you can look at the number of rows modified.  If it is 0 then you know that the row either doesn't exist, or it has the same values, if it is 1 then it was different and was successfully changed, and if it was greater than one then you changed multiple rows, and in this context that probably shouldn't happen.  So as long as you know for sure that the row already exists before call this prcedure then if the rows modified is 0 you know that it was the same.

    • Marked as answer by Mike Dos Zhang Wednesday, August 18, 2010 11:40 AM
    Friday, August 13, 2010 4:09 PM

All replies

  • If you use the </> tool in the toolbar to post your code, it will be much more legible.

    Regarding your question, you want to track which rows are updated and not allow them to be updated again?


    www.insteptech.com ; msmvps.com/blogs/deborahk
    We are volunteers and ask only that if we are able to help you, that you mark our reply as your answer. THANKS!
    Thursday, August 12, 2010 3:39 PM
  • Thanks Sir. I just edited it already.
    Friday, August 13, 2010 1:35 AM
  • There are two options to consider.  One would be to get the values for that serial number before updating it to see if they are the same.  The other would be to go to the where clause of the update and put, "and (DateModified<>@Date or ModifiedBy<>@User or WorkWeek<>@WorkWeek) "

    That way it will only update the line if one of the values differs.  Then you can look at the number of rows modified.  If it is 0 then you know that the row either doesn't exist, or it has the same values, if it is 1 then it was different and was successfully changed, and if it was greater than one then you changed multiple rows, and in this context that probably shouldn't happen.  So as long as you know for sure that the row already exists before call this prcedure then if the rows modified is 0 you know that it was the same.

    • Marked as answer by Mike Dos Zhang Wednesday, August 18, 2010 11:40 AM
    Friday, August 13, 2010 4:09 PM