.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
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




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification