MBR IT/.NET 247 : DataAdapter.Upadate error on microsoft.public.dotnet.general

DataAdapter.Upadate error

Messages

Related Types

This message was discovered on microsoft.public.dotnet.general.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Job Lot (VIP)

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

Reply to this message...

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:
[Original message clipped]

Reply to this message...

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" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Job Lot (VIP)

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

Reply to this message...

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" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Job Lot (VIP)

Even I thought of the same reason, but just wanted to confirm it. As you
have picked the same thing I can bank on it. Actually, there arenâ€™t any
records in Client_Expenses table initially. But in my client application I
want my DataGrid to show all available expense details, so that client can
enter their expenses. That is the reason I used an Outer Join query. Is there
anyway to solve this problem? Could you also please tell me how I can make
DataAdapter.Update method to save or insert only newly entered or changed
rows.

Thanks

"Miha Markic [MVP C#]" wrote:

[Original message clipped]

Reply to this message...

	System.Data.CommandType
	System.Data.Common.DataAdapter
	System.Data.SqlClient.SqlCommand
	System.Data.SqlDbType
	System.Web.UI.WebControls.DataGrid
	System.Windows.Forms.DataGrid

MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT

	Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
	Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com