DataAdapter.Upadate error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

am binding my DataGrid using a StoredProc which uses an OuterJoin query as
follows:

CREATE PROCEDURE spGetClientExpenses
@Client_ID int
AS
SELECT Expense_Details.Exp_Detail_ID,Expense_Details.Exp_Cat_ID,
CASE WHEN Client_Expenses.Client_ID IS NULL THEN @Client_ID ELSE
Client_Expenses.Client_ID END AS Client_ID,Expense_Details.Description,
CASE WHEN Client_Expenses.CashExpenditure IS NULL THEN 0 ELSE
Client_Expenses.CashExpenditure END AS CashExpenditure,
CASE WHEN Client_Expenses.CreditExpenditure IS NULL THEN 0 ELSE
Client_Expenses.CreditExpenditure END AS CreditExpenditure,
CASE WHEN Client_Expenses.Frequency IS NULL THEN 12 ELSE
Client_Expenses.Frequency END AS Frequency
FROM Expense_Details
LEFT OUTER JOIN Client_Expenses
ON Expense_Details.Exp_Detail_ID = Client_Expenses.Exp_Detail_ID
AND Client_ID = @Client_ID
ORDER BY Description
GO

now when i try and use DataAdapter.Update method after user has made changes
to the DataGrid i get Concurrency exception. Does this mean i can't use
Update method with OuterJoin queries? Please help
 
Hi Job,

Do you have an UpdateCommand command actually defined in your adapter?
You should provide a valid one if there is none or the existing one is not
good.
(the same goes for Insert and Delete if you need them)
 
Hi Miha

Ya i do have an Update Command defined in the adapter. Below are select,
insert and update command.

da.SelectCommand = New SqlCommand
With da.SelectCommand
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "spGetClientExpenses"
.Parameters.Clear()
.Parameters.Add("@Client_ID", 1001)
End With


'Set Insert Command for expenseDA.
da.InsertCommand = New SqlCommand
With da.InsertCommand
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "spInsertClientExpenses"
.Parameters.Clear()
.Parameters.Add("@Client_ID", SqlDbType.Int, 4, "Client_ID")
.Parameters.Add("@Exp_Detail_ID", SqlDbType.Int, 4,
"Exp_Detail_ID")
.Parameters.Add("@CashExpenditure", SqlDbType.Money, 8,
"CashExpenditure")
.Parameters.Add("@CreditExpenditure", SqlDbType.Money, 8,
"CreditExpenditure")
.Parameters.Add("@Frequency", SqlDbType.Int, 4, "Frequency")
End With

'Set Update Command for expenseDA.
da.UpdateCommand = New SqlCommand
With da.UpdateCommand
.Connection = conn
.CommandType = CommandType.StoredProcedure
.CommandText = "spUpdateClientExpenses"
.Parameters.Clear()
.Parameters.Add("@Client_ID", SqlDbType.Int, 4, "Client_ID")
.Parameters.Add("@Exp_Detail_ID", SqlDbType.Int, 4,
"Exp_Detail_ID")
.Parameters.Add("@CashExpenditure", SqlDbType.Money, 8,
"CashExpenditure")
.Parameters.Add("@CreditExpenditure", SqlDbType.Money, 8,
"CreditExpenditure")
.Parameters.Add("@Frequency", SqlDbType.Int, 4, "Frequency")
End With

The error I am getting is “Concurrency Violation: the UpdateCommand affected
0 records.â€

I can’t figure out why the Adapter.Update method is issuing UpdateCommand,
although there are no records in Client_Expenses table. Below are my insert
and update stored proc. I want to insert and update Client_Expenses table,
whereas the Select command is using outer join. Is there something to with
the outer join?

CREATE PROCEDURE spInsertClientExpenses
@Client_ID int,
@Exp_Detail_ID int,
@CashExpenditure money,
@CreditExpenditure money,
@Frequency int
AS
INSERT Client_Expenses
VALUES
(@Client_ID,@Exp_Detail_ID,@CashExpenditure,@CreditExpenditure,@Frequency)
GO


CREATE PROCEDURE spUpdateClientExpenses
--Parameters for Client_Expenses Table.
@Client_ID int,
@Exp_Detail_ID int,
@CashExpenditure money,
@CreditExpenditure money,
@Frequency int
AS
--UPDATE Client_Expenses.
UPDATE Client_Expenses
SET CashExpenditure = @CashExpenditure,
CreditExpenditure = @CreditExpenditure,
Frequency = @Frequency
WHERE Client_ID = @Client_ID
AND Exp_Detail_ID = @Exp_Detail_ID
 
Hi Job,

At first glance it seems ok.
However, from the select of yours I see the following:
SELECT Expense_Details.Exp_Detail_ID,Expense_Details.Exp_Cat_ID,
CASE WHEN Client_Expenses.Client_ID IS NULL THEN @Client_ID ELSE
Client_Expenses.Client_ID END AS Client_ID,

This practically inserts a "fake" client_id when client_id is actually null.
And your update might use this "fake" id to update and because the id
doesn't exist it doesn't update any row.
Thus it might fail.
Can this be the case?
BTW, it doesn't matter the structure of the select statament (joins, etc.)
as long as it returns correct data.
 
Back
Top