Solved

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

Posted on 2010-11-18
5
696 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 500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.NET Inline If statement 4 58
Asp.Net Session Question 2 49
vb.net make textbox not visible after 1 minute elapsed 6 27
VB .net 2010 Byte array 2 24
Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

752 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