Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2010-11-18
5
Medium Priority
?
719 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
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline

927 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