Solved

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

Posted on 2010-11-18
5
690 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
  • 3
  • 2
5 Comments
 
LVL 83

Expert Comment

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

Author Comment

by:alsam
Comment Utility
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 500 total points
Comment Utility
I think the Adapter tries to execute the Update command instead of Insert command.
0
 
LVL 1

Author Comment

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

Author Closing Comment

by:alsam
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
This video discusses moving either the default database or any database to a new volume.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now