Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records

Posted on 2010-11-18
5
Medium Priority
?
699 Views
Last Modified: 2012-05-10
Hi,
Please help...
I bind my datagridview with data table...
My select command contains join so I have created Update and Inset command by my self...
Content of Main_table:
TC                 TC_Desc        jan
11111111        Moj Tc          256
11111112        Moj tc 2        15

Content of Main_table_2
TC                  TC_Desc
11111111        Moj Tc    
11111112        Moj TC 2  
11111113        Moj TC 3  
11111114        Moj tC 4  

For both tables TC is primary key...

Content of datagridview after form_load as follows:

      11111111        Moj Tc          256
      11111112        Moj TC 2        15
      11111113        Moj TC 3        0
      11111114        Moj tC 4        0
                  
When I change for instance value for 11111111 from 256 to 200 and call update method it works perfeclly against database on sqlServer....
But when I change value for 111111113 from 0 to 15 (for e.g) and call updat method  then I get error message

Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.

I beleive that this is because I dont have 111111113 in my main_table....
Can someone help me to solve the problem in order to be able to insert this new row in my Main_table .....
My vb code is below...
Thanks...
Imports System.Data.SqlClient

Public Class Form1
    Private con As New SqlConnection()

    Private WithEvents cmdSelect As New SqlCommand()
    Private WithEvents cmdUpdate As New SqlCommand()
    Private WithEvents cmdInsert As New SqlCommand()
    Private WithEvents da As New SqlDataAdapter()
    Private WithEvents table As New DataTable()

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        con.ConnectionString = My.Settings.mycon

        da.SelectCommand = cmdSelect
        da.UpdateCommand = cmdUpdate
        da.InsertCommand = cmdInsert

        da.SelectCommand.CommandText = "Select b.TC, b.TC_Desc, isnull(a.Jan,0.00) as Jan from Main_table a RIGHT JOIN Main_table_2 b ON a.TC= b.TC "
        da.SelectCommand.Connection = con

        da.UpdateCommand.CommandText = "UPDATE Main_table SET TC = @TC, TC_Desc = @TC_Desc, Jan=  @Jan WHERE TC = @TC"
        da.UpdateCommand.Connection = con

        da.InsertCommand.CommandText = "INSERT INTO Main_table (TC, TC_Desc, Jan) VALUES (@TC, @TC_Desc, @Jan)"
        da.InsertCommand.Connection = con


        '------------------------------------------------------------
        ' Adding parameters ... 
        '------------------------------------------------------------
        da.UpdateCommand.Parameters.Add("@TC", SqlDbType.VarChar, 101, "TC")
        da.UpdateCommand.Parameters.Add("@TC_Desc", SqlDbType.VarChar, 101, "TC_Desc")
        da.UpdateCommand.Parameters.Add("@Jan", SqlDbType.Float, 20, "Jan")

        da.InsertCommand.Parameters.Add("@TC", SqlDbType.VarChar, 101, "TC")
        da.InsertCommand.Parameters.Add("@TC_Desc", SqlDbType.VarChar, 101, "TC_Desc")
        da.InsertCommand.Parameters.Add("@Jan", SqlDbType.Float, 20, "Jan")



        Try

            ' Create a command builder to generate SQL update, insert, and
            ' delete commands based on selectCommand. These are used to
            ' update the database.
            Dim commandBuilder As New SqlCommandBuilder(Me.da)

            ' Populate a new data table and bind it to the BindingSource.

            table.Locale = System.Globalization.CultureInfo.InvariantCulture
            Me.da.Fill(table)
            Me.BindingSource1.DataSource = table


            Me.DataGridView1.DataSource = Me.BindingSource1.DataSource


            ' Resize the DataGridView columns to fit the newly loaded content.
            Me.DataGridView1.AutoResizeColumns( _
                DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader)
        Catch ex As SqlException
            MessageBox.Show("Error")
        End Try

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try

            da.Update(table)

        Catch ex As SqlException

            MsgBox(ex.Message)
            MsgBox(ex.ToString)

        End Try
    End Sub
End Class

Open in new window

0
Comment
Question by:alsam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 34168540
The 0 in third column, is it 0 in DB or is it Null?
0
 
LVL 1

Author Comment

by:alsam
ID: 34170853
Hi,
actually the line
11111113   Moj_TC 3  0  
does not exist in Main_table where I want to insert this row...
In datagridview it is created from
Select b.TC, b.TC_Desc, isnull(a.Jan,0.00) as Jan from Main_table a RIGHT JOIN Main_table_2 b ON a.TC= b.TC  
where i collect all records from Main_table_2 with right join(no matter if exesit in Main_table or not).... And When I do make change on this line in data grid view then I want to insert the same into table Main_table by calling Update method....
Literarly, current content of Main_table where I want to insert this row in SQL Server database is

TC                          TC_Desc         Jan
11111111              Moj Tc 1            256
11111112              Moj Tc 2              15

After I change Jan value for 11111113   Moj_TC 3  0  (for e.g from 0 to 200) in Datagridview and call Update method then table (Main_table) in SQL Server should look like:
TC                          TC_Desc         Jan
11111111              Moj Tc 1            256
11111112              Moj Tc 2              15
11111113              Moj Tc 3            200

I hope I was not confused....




0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 1500 total points
ID: 34171367
I think the Adapter tries to execute the Update command instead of Insert command.
0
 
LVL 1

Author Comment

by:alsam
ID: 34171506
Do you maybe have some suggestion how to make this working....
0
 
LVL 1

Author Closing Comment

by:alsam
ID: 34186507
Ok,
I have decided to use upsert on SQL Server side to prepare tables for end users in order to make this working....
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question