DataAdapter.Upadate error

[Job Lot]

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

[Sijin Joseph]

A concurrency exception will occur when no rows are affected by the SQL
statement.

Sijin Joseph
http://www.indiangeek.net
http://weblogs.asp.net/sjoseph

Job Lot wrote:

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

[Miha Markic [MVP C#]]

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)

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:CA**********************************@microsof t.com...

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

[Job Lot]

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,@Credi tExpenditure,@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

[Miha Markic [MVP C#]]

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.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com
www.rthand.com

"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...

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,@Credi tExpenditure,@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