Solved

VB.net Concurrency Violation Error

Posted on 2010-11-26
12
613 Views
Last Modified: 2012-05-10
Greetings;
      I have a VB.net 2010 application that is running against a SQL Server 2008 database.  I have over 4000 records in this database that have been created using this application.  However, a handful of the records will not update after some unknown event happens to them.  I get the error "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records."  However for testing, I am working in a local copy of the database, no one else is logged in, and I only get this message on certain records.  I can edit the record in the back end on the database without error, even changing the same values that I am attempting to edit through the application.  This is all generated code, nothing fancy in the SQL or the Update Logic.  I even removed all table triggers from the table I'm updating just to make sure it wasn't an error embedded in some other business logic.  I've tried setting the transaction isolation levels to every available level without any change in this behavior.  

      Attached you will find the generated update statement, data captured for the rows before and after values, and a set of the code used to do the work.  I'm only changing a value in one text column for the purpose of the test.

      Please advise any ideas on this issue.

Thanks;

Billy

0
Comment
Question by:pcadpcad
12 Comments
 
LVL 32

Expert Comment

by:Erick37
Comment Utility
Nothing is attached.
0
 
LVL 10

Expert Comment

by:Mez4343
Comment Utility
Dont see any attached code.

If your using the DataAdaptor wizard to generate the code, it will not handle everytihng for you.

I would suggest you add some Concurrency Violation error handling code. This might help you understand were in your app you get the problems and second, prepare your app if you do have a need to support multiple users.

Here is an article to get started http://articles.techrepublic.com.com/5100-10878_11-1050110.html
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Do you have any default values in the DB? Also, any int type columns which can be null?
0
 

Author Comment

by:pcadpcad
Comment Utility
Sorry about that, I saw them in the Preview :-)  
Let's try attaching the file again....

Generated SQL:  Ugly-SQL.txt
Sample Code:  Sample-Code.txt
Row Data:RAW-Row-Data.txt

Thanks;

Billy
0
 

Author Comment

by:pcadpcad
Comment Utility
Greetings;

@Erick37 I have attached the files now, sorry about that.

@Mez4343 Right, I have some code in place to catch the errors, and review the source, that's how I got the values and the generated SQL.  However, there is no Concurrency Violation, hence my confusion.  I tried Repeatable Read Isolation on a copy of the database that I'm the only user of and still got the error, but it's only for that certain record.  Other records update fine.

@CodeCruiser The only column that is not nullable is the PK.  The PK is not being changed during the update and is visible in the values in the Row Data File confirming that.  Here is the SQL DDL for the table just in case your interested.

CREATE TABLE [Main](
      [ID_KEY] [int] NOT NULL,
      [ID_TICKET] [int] NULL,
      [DT_RCVD] [datetime] NULL,
      [DT_COMPETE] [datetime] NULL,
      [ID_ADAPTER] [smallint] NULL,
      [ID_BAG] [smallint] NULL,
      [ID_REWORK] [smallint] NULL,
      [ID_QB_INV] [int] NULL,
      [ID_COMPLETE] [int] NULL,
      [ID_BILLED] [int] NULL,
      [TX_REVD_LOC] [char](6) NULL,
      [TX_COMP_LOC] [char](6) NULL,
      [ID_EMERGENCY] [smallint] NULL,
      [ID_VEND] [int] NULL,
      [TX_STATE] [char](2) NULL,
      [TX_ZIP] [char](5) NULL,
      [TX_PHONE1] [char](15) NULL,
      [TX_PHONE2] [char](15) NULL,
      [TX_PHONE3] [char](15) NULL,
      [ID_RECVD_BY] [int] NULL,
      [ID_SERVD_BY] [int] NULL,
      [ID_WARRANTY] [smallint] NULL,
      [ID_ESTIMATE] [smallint] NULL,
      [ID_APPROVED] [smallint] NULL,
      [DT_APPROVED] [datetime] NULL,
      [ID_TYPE] [smallint] NULL,
      [TX_RESOLUTION] [varchar](8000) NULL,
      [TX_INTERNAL] [varchar](8000) NULL,
      [TX_PROBLEM] [varchar](8000) NULL,
      [TX_MODEL] [varchar](50) NULL,
      [TX_PCAD_SERIAL] [varchar](50) NULL,
      [TX_COMPANY] [varchar](50) NULL,
      [TX_CUSTOMER] [varchar](50) NULL,
      [TX_ADDRESS1] [varchar](50) NULL,
      [TX_ADDRESS2] [varchar](50) NULL,
      [TX_CITY] [varchar](50) NULL,
      [TX_PASSWORD] [varchar](20) NULL,
      [TX_EMAIL] [varchar](100) NULL,
 CONSTRAINT [PK__Main__7C8480AE] PRIMARY KEY CLUSTERED
(
      [ID_KEY] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UQ__Main__7D78A4E7] UNIQUE NONCLUSTERED
(
      [ID_KEY] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks;

Billy
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
Comment Utility
You have this in your first text file

WHERE (([ID_KEY] = ?) AND ((? = 1 AND [ID_TICKET] IS NULL) OR ([ID_TICKET] = ?)) AND ((? = 1 AND [DT_RCVD] IS NULL) OR ([DT_RCVD] = ?)) AND ((? = 1 AND [DT_COMPETE] IS NULL) OR ([DT_COMPETE] = ?)) AND ((? = 1 AND [ID_ADAPTER] IS NULL) OR ([ID_ADAPTER] = ?)) AND ((? = 1 AND [ID_BAG] IS NULL) OR ([ID_BAG] = ?)) AND ((? = 1 AND [ID_REWORK] IS NULL) OR ([ID_REWORK] = ?)) AND ((? = 1 AND [ID_QB_INV] IS NULL) OR ([ID_QB_INV] = ?)) AND ((? = 1 AND [ID_COMPLETE] IS NULL) OR ([ID_COMPLETE] = ?)) AND ((? = 1 AND [ID_BILLED] IS NULL) OR ([ID_BILLED] = ?)) AND ((? = 1 AND [TX_REVD_LOC] IS NULL) OR ([TX_REVD_LOC] = ?)) AND ((? = 1 AND [TX_COMP_LOC] IS NULL) OR ([TX_COMP_LOC] = ?)) AND ((? = 1 AND [ID_EMERGENCY] IS NULL) OR ([ID_EMERGENCY] = ?)) AND ((? = 1 AND [ID_VEND] IS NULL) OR ([ID_VEND] = ?)) AND ((? = 1 AND [TX_STATE] IS NULL) OR ([TX_STATE] = ?)) AND ((? = 1 AND [TX_ZIP] IS NULL) OR ([TX_ZIP] = ?)) AND ((? = 1 AND [TX_PHONE1] IS NULL) OR ([TX_PHONE1] = ?)) AND ((? = 1 AND [TX_PHONE2] IS NULL) OR ([TX_PHONE2] = ?)) AND ((? = 1 AND [TX_PHONE3] IS NULL) OR ([TX_PHONE3] = ?)) AND ((? = 1 AND [ID_RECVD_BY] IS NULL) OR ([ID_RECVD_BY] = ?)) AND ((? = 1 AND [ID_SERVD_BY] IS NULL) OR ([ID_SERVD_BY] = ?)) AND ((? = 1 AND [ID_WARRANTY] IS NULL) OR ([ID_WARRANTY] = ?)) AND ((? = 1 AND [ID_ESTIMATE] IS NULL) OR ([ID_ESTIMATE] = ?)) AND ((? = 1 AND [ID_APPROVED] IS NULL) OR ([ID_APPROVED] = ?)) AND ((? = 1 AND [DT_APPROVED] IS NULL) OR ([DT_APPROVED] = ?)) AND ((? = 1 AND [ID_TYPE] IS NULL) OR ([ID_TYPE] = ?)) AND ((? = 1 AND [TX_RESOLUTION] IS NULL) OR ([TX_RESOLUTION] = ?)) AND ((? = 1 AND [TX_INTERNAL] IS NULL) OR ([TX_INTERNAL] = ?)) AND ((? = 1 AND [TX_PROBLEM] IS NULL) OR ([TX_PROBLEM] = ?)) AND ((? = 1 AND [TX_MODEL] IS NULL) OR ([TX_MODEL] = ?)) AND ((? = 1 AND [TX_PCAD_SERIAL] IS NULL) OR ([TX_PCAD_SERIAL] = ?)) AND ((? = 1 AND [TX_COMPANY] IS NULL) OR ([TX_COMPANY] = ?)) AND ((? = 1 AND [TX_CUSTOMER] IS NULL) OR ([TX_CUSTOMER] = ?)) AND ((? = 1 AND [TX_ADDRESS1] IS NULL) OR ([TX_ADDRESS1] = ?)) AND ((? = 1 AND [TX_ADDRESS2] IS NULL) OR ([TX_ADDRESS2] = ?)) AND ((? = 1 AND [TX_CITY] IS NULL) OR ([TX_CITY] = ?)) AND ((? = 1 AND [TX_PASSWORD] IS NULL) OR ([TX_PASSWORD] = ?)) AND ((? = 1 AND [TX_EMAIL] IS NULL) OR ([TX_EMAIL] = ?)))


Now everytime you try to update, tableadapter tries to match old values to make sure the record has not been changed.

Here is an example
You select a record where name is "Code" and ID is 3. You change the name to "Cruiser" and this is the generated update query

Update tablename Set Name="Cruiser" Where ID = 3 AND Name="Code"

Now this makes sense but we get into trouble where we have int fields which can be null. The trouble is that Integer can not be null in .NET so the default value in .NET is 0 for int as compaired to DBNull in the DB. So we get a query like this

Update tablename Set Name = "Cruiser" Where ID = 3 AND Name="Code" and Age=0

but Age is Null in DB not 0 hence the concurrency violation.


Try to modify the update command manually and remove checks on all fields except the ID field.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:pcadpcad
Comment Utility
Good Morning;
That makes sense.  What I don’t understand is the SQL for that tableadapter is “Select * from Main where id_key  = ?”  I’m not specifying any of the other fields that it’s checking on, I suppose that’s the .Net Framework trying to look out for me.
 
I tried this for the update command.  I didn’t want to edit the XML directly because it keeps getting replaced anytime I open the dataset editor.

Dim UpdateSQL As New OleDb.OleDbCommand()
            ''Dim UpdateSQLParameter As New OleDb.OleDbParameter
            UpdateSQL.CommandText = "UPDATE [PCAD].[Main] SET [ID_TICKET] = ?, [DT_RCVD] = ?, [DT_COMPETE] = ?, [ID_ADAPTER] = ?, [ID_BAG] = ?, [ID_REWORK] = ?, [ID_QB_INV] = ?, [ID_COMPLETE] = ?, [ID_BILLED] = ?, [TX_REVD_LOC] = ?, [TX_COMP_LOC] = ?, [ID_EMERGENCY] = ?, [ID_VEND] = ?, [TX_STATE] = ?, [TX_ZIP] = ?, [TX_PHONE1] = ?, [TX_PHONE2] = ?, [TX_PHONE3] = ?, [ID_RECVD_BY] = ?, [ID_SERVD_BY] = ?, [ID_WARRANTY] = ?, [ID_ESTIMATE] = ?, [ID_APPROVED] = ?, [DT_APPROVED] = ?, [ID_TYPE] = ?, [TX_RESOLUTION] = ?, [TX_INTERNAL] = ?, [TX_PROBLEM] = ?, [TX_MODEL] = ?, [TX_PCAD_SERIAL] = ?, [TX_COMPANY] = ?, [TX_CUSTOMER] = ?, [TX_ADDRESS1] = ?, [TX_ADDRESS2] = ?, [TX_CITY] = ?, [TX_PASSWORD] = ?, [TX_EMAIL] = ? WHERE [ID_KEY] = ?"
            UpdateSQL.Connection = MainTableAdapterEdit.Connection
            Me.MainTableAdapterEdit.ExposedDataAdapter.UpdateCommand = UpdateSQL

Open in new window



The result of that change was that the parameters are not getting passed to the update.  Error: “No value given for one or more required parameters.”  I guess this means that the custom update command is not getting the parameters passed to it by the update request and I will have to write a little routine to fill that in.  Can you confirm that, or am I ignorant of the correct way to implement the custom update?

On another note, the record right before this one has a null in id_estimate (int field).  I can open the record, edit the information and save it without changing the update function.  I’m attaching that in a file.  Capture-Results.txt  
=====================================================
Update Capture Begin                         
No Error
Gen. SQL: UPDATE [PCAD].[Main] SET [ID_KEY] = ?, [ID_TICKET] = ?, [DT_RCVD] = ?, [DT_COMPETE] = ?, [ID_ADAPTER] = ?, [ID_BAG] = ?, [ID_REWORK] = ?, [ID_QB_INV] = ?, [ID_COMPLETE] = ?, [ID_BILLED] = ?, [TX_REVD_LOC] = ?, [TX_COMP_LOC] = ?, [ID_EMERGENCY] = ?, [ID_VEND] = ?, [TX_STATE] = ?, [TX_ZIP] = ?, [TX_PHONE1] = ?, [TX_PHONE2] = ?, [TX_PHONE3] = ?, [ID_RECVD_BY] = ?, [ID_SERVD_BY] = ?, [ID_WARRANTY] = ?, [ID_ESTIMATE] = ?, [ID_APPROVED] = ?, [DT_APPROVED] = ?, [ID_TYPE] = ?, [TX_RESOLUTION] = ?, [TX_INTERNAL] = ?, [TX_PROBLEM] = ?, [TX_MODEL] = ?, [TX_PCAD_SERIAL] = ?, [TX_COMPANY] = ?, [TX_CUSTOMER] = ?, [TX_ADDRESS1] = ?, [TX_ADDRESS2] = ?, [TX_CITY] = ?, [TX_PASSWORD] = ?, [TX_EMAIL] = ? WHERE (([ID_KEY] = ?) AND ((? = 1 AND [ID_TICKET] IS NULL) OR ([ID_TICKET] = ?)) AND ((? = 1 AND [DT_RCVD] IS NULL) OR ([DT_RCVD] = ?)) AND ((? = 1 AND [DT_COMPETE] IS NULL) OR ([DT_COMPETE] = ?)) AND ((? = 1 AND [ID_ADAPTER] IS NULL) OR ([ID_ADAPTER] = ?)) AND ((? = 1 AND [ID_BAG] IS NULL) OR ([ID_BAG] = ?)) AND ((? = 1 AND [ID_REWORK] IS NULL) OR ([ID_REWORK] = ?)) AND ((? = 1 AND [ID_QB_INV] IS NULL) OR ([ID_QB_INV] = ?)) AND ((? = 1 AND [ID_COMPLETE] IS NULL) OR ([ID_COMPLETE] = ?)) AND ((? = 1 AND [ID_BILLED] IS NULL) OR ([ID_BILLED] = ?)) AND ((? = 1 AND [TX_REVD_LOC] IS NULL) OR ([TX_REVD_LOC] = ?)) AND ((? = 1 AND [TX_COMP_LOC] IS NULL) OR ([TX_COMP_LOC] = ?)) AND ((? = 1 AND [ID_EMERGENCY] IS NULL) OR ([ID_EMERGENCY] = ?)) AND ((? = 1 AND [ID_VEND] IS NULL) OR ([ID_VEND] = ?)) AND ((? = 1 AND [TX_STATE] IS NULL) OR ([TX_STATE] = ?)) AND ((? = 1 AND [TX_ZIP] IS NULL) OR ([TX_ZIP] = ?)) AND ((? = 1 AND [TX_PHONE1] IS NULL) OR ([TX_PHONE1] = ?)) AND ((? = 1 AND [TX_PHONE2] IS NULL) OR ([TX_PHONE2] = ?)) AND ((? = 1 AND [TX_PHONE3] IS NULL) OR ([TX_PHONE3] = ?)) AND ((? = 1 AND [ID_RECVD_BY] IS NULL) OR ([ID_RECVD_BY] = ?)) AND ((? = 1 AND [ID_SERVD_BY] IS NULL) OR ([ID_SERVD_BY] = ?)) AND ((? = 1 AND [ID_WARRANTY] IS NULL) OR ([ID_WARRANTY] = ?)) AND ((? = 1 AND [ID_ESTIMATE] IS NULL) OR ([ID_ESTIMATE] = ?)) AND ((? = 1 AND [ID_APPROVED] IS NULL) OR ([ID_APPROVED] = ?)) AND ((? = 1 AND [DT_APPROVED] IS NULL) OR ([DT_APPROVED] = ?)) AND ((? = 1 AND [ID_TYPE] IS NULL) OR ([ID_TYPE] = ?)) AND ((? = 1 AND [TX_RESOLUTION] IS NULL) OR ([TX_RESOLUTION] = ?)) AND ((? = 1 AND [TX_INTERNAL] IS NULL) OR ([TX_INTERNAL] = ?)) AND ((? = 1 AND [TX_PROBLEM] IS NULL) OR ([TX_PROBLEM] = ?)) AND ((? = 1 AND [TX_MODEL] IS NULL) OR ([TX_MODEL] = ?)) AND ((? = 1 AND [TX_PCAD_SERIAL] IS NULL) OR ([TX_PCAD_SERIAL] = ?)) AND ((? = 1 AND [TX_COMPANY] IS NULL) OR ([TX_COMPANY] = ?)) AND ((? = 1 AND [TX_CUSTOMER] IS NULL) OR ([TX_CUSTOMER] = ?)) AND ((? = 1 AND [TX_ADDRESS1] IS NULL) OR ([TX_ADDRESS1] = ?)) AND ((? = 1 AND [TX_ADDRESS2] IS NULL) OR ([TX_ADDRESS2] = ?)) AND ((? = 1 AND [TX_CITY] IS NULL) OR ([TX_CITY] = ?)) AND ((? = 1 AND [TX_PASSWORD] IS NULL) OR ([TX_PASSWORD] = ?)) AND ((? = 1 AND [TX_EMAIL] IS NULL) OR ([TX_EMAIL] = ?)))
OLD: 4115,8119,11/23/2010 12:00:00 AM,,0,0,0,,0,,E6    ,,0,9,GA,30016,404-788-9659   ,               ,               ,10,,,,,,2,,Told her she should have her laptop back at the latest by tuesday. - BN - Thanks   ORIGINAL TICKET 7847  d,Jr worked on this before. It was getting a black screen and no video. It was working when he worked on hit but now it just stays black. Im think a video cable may have came loose,dv4 series,,                                                  ,Natasha Bullard                                   ,235 Hampton Ct                                    ,                                                  ,Covington                                         ,,,
NEW: 4115,8119,11/23/2010 12:00:00 AM,,0,0,0,,0,,E6    ,,0,9,GA,30016,404-788-9659   ,               ,               ,10,,,,,,2,,Told her she should have her laptop back at the latest by tuesday. - BN - Thanks   ORIGINAL TICKET 7847    This is a test update,Jr worked on this before. It was getting a black screen and no video. It was working when he worked on hit but now it just stays black. Im think a video cable may have came loose,dv4 series,,                                                  ,Natasha Bullard                                   ,235 Hampton Ct                                    ,                                                  ,Covington                                         ,,,
NUL: IsNull_ID_TICKET:0,IsNull_DT_RCVD:0,IsNull_DT_COMPETE:1,IsNull_ID_ADAPTER:0,IsNull_ID_BAG:0,IsNull_ID_REWORK:0,IsNull_ID_QB_INV:1,IsNull_ID_COMPLETE:0,IsNull_ID_BILLED:1,IsNull_TX_REVD_LOC:0,IsNull_TX_COMP_LOC:1,IsNull_ID_EMERGENCY:0,IsNull_ID_VEND:0,IsNull_TX_STATE:0,IsNull_TX_ZIP:0,IsNull_TX_PHONE1:0,IsNull_TX_PHONE2:0,IsNull_TX_PHONE3:0,IsNull_ID_RECVD_BY:0,IsNull_ID_SERVD_BY:1,IsNull_ID_WARRANTY:1,IsNull_ID_ESTIMATE:1,IsNull_ID_APPROVED:1,IsNull_DT_APPROVED:1,IsNull_ID_TYPE:0,IsNull_TX_RESOLUTION:1,IsNull_TX_INTERNAL:0,IsNull_TX_PROBLEM:0,IsNull_TX_MODEL:0,IsNull_TX_PCAD_SERIAL:1,IsNull_TX_COMPANY:0,IsNull_TX_CUSTOMER:0,IsNull_TX_ADDRESS1:0,IsNull_TX_ADDRESS2:0,IsNull_TX_CITY:0,IsNull_TX_PASSWORD:1,IsNull_TX_EMAIL:1,
Columns that are null: (DT_COMPETE,ID_QB_INV,ID_BILLED,TX_COMP_LOC,ID_SERVD_BY,ID_WARRANTY,ID_ESTIMATE,ID_APPROVED,DT_APPROVED,TX_RESOLUTION,TX_PCAD_SERIAL,TX_PASSWORD,TX_EMAIL,)
Update Capture End                           
=====================================================
 
=====================================================
Update Capture Begin                         
Error   : Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.
Gen. SQL: UPDATE [PCAD].[Main] SET [ID_KEY] = ?, [ID_TICKET] = ?, [DT_RCVD] = ?, [DT_COMPETE] = ?, [ID_ADAPTER] = ?, [ID_BAG] = ?, [ID_REWORK] = ?, [ID_QB_INV] = ?, [ID_COMPLETE] = ?, [ID_BILLED] = ?, [TX_REVD_LOC] = ?, [TX_COMP_LOC] = ?, [ID_EMERGENCY] = ?, [ID_VEND] = ?, [TX_STATE] = ?, [TX_ZIP] = ?, [TX_PHONE1] = ?, [TX_PHONE2] = ?, [TX_PHONE3] = ?, [ID_RECVD_BY] = ?, [ID_SERVD_BY] = ?, [ID_WARRANTY] = ?, [ID_ESTIMATE] = ?, [ID_APPROVED] = ?, [DT_APPROVED] = ?, [ID_TYPE] = ?, [TX_RESOLUTION] = ?, [TX_INTERNAL] = ?, [TX_PROBLEM] = ?, [TX_MODEL] = ?, [TX_PCAD_SERIAL] = ?, [TX_COMPANY] = ?, [TX_CUSTOMER] = ?, [TX_ADDRESS1] = ?, [TX_ADDRESS2] = ?, [TX_CITY] = ?, [TX_PASSWORD] = ?, [TX_EMAIL] = ? WHERE (([ID_KEY] = ?) AND ((? = 1 AND [ID_TICKET] IS NULL) OR ([ID_TICKET] = ?)) AND ((? = 1 AND [DT_RCVD] IS NULL) OR ([DT_RCVD] = ?)) AND ((? = 1 AND [DT_COMPETE] IS NULL) OR ([DT_COMPETE] = ?)) AND ((? = 1 AND [ID_ADAPTER] IS NULL) OR ([ID_ADAPTER] = ?)) AND ((? = 1 AND [ID_BAG] IS NULL) OR ([ID_BAG] = ?)) AND ((? = 1 AND [ID_REWORK] IS NULL) OR ([ID_REWORK] = ?)) AND ((? = 1 AND [ID_QB_INV] IS NULL) OR ([ID_QB_INV] = ?)) AND ((? = 1 AND [ID_COMPLETE] IS NULL) OR ([ID_COMPLETE] = ?)) AND ((? = 1 AND [ID_BILLED] IS NULL) OR ([ID_BILLED] = ?)) AND ((? = 1 AND [TX_REVD_LOC] IS NULL) OR ([TX_REVD_LOC] = ?)) AND ((? = 1 AND [TX_COMP_LOC] IS NULL) OR ([TX_COMP_LOC] = ?)) AND ((? = 1 AND [ID_EMERGENCY] IS NULL) OR ([ID_EMERGENCY] = ?)) AND ((? = 1 AND [ID_VEND] IS NULL) OR ([ID_VEND] = ?)) AND ((? = 1 AND [TX_STATE] IS NULL) OR ([TX_STATE] = ?)) AND ((? = 1 AND [TX_ZIP] IS NULL) OR ([TX_ZIP] = ?)) AND ((? = 1 AND [TX_PHONE1] IS NULL) OR ([TX_PHONE1] = ?)) AND ((? = 1 AND [TX_PHONE2] IS NULL) OR ([TX_PHONE2] = ?)) AND ((? = 1 AND [TX_PHONE3] IS NULL) OR ([TX_PHONE3] = ?)) AND ((? = 1 AND [ID_RECVD_BY] IS NULL) OR ([ID_RECVD_BY] = ?)) AND ((? = 1 AND [ID_SERVD_BY] IS NULL) OR ([ID_SERVD_BY] = ?)) AND ((? = 1 AND [ID_WARRANTY] IS NULL) OR ([ID_WARRANTY] = ?)) AND ((? = 1 AND [ID_ESTIMATE] IS NULL) OR ([ID_ESTIMATE] = ?)) AND ((? = 1 AND [ID_APPROVED] IS NULL) OR ([ID_APPROVED] = ?)) AND ((? = 1 AND [DT_APPROVED] IS NULL) OR ([DT_APPROVED] = ?)) AND ((? = 1 AND [ID_TYPE] IS NULL) OR ([ID_TYPE] = ?)) AND ((? = 1 AND [TX_RESOLUTION] IS NULL) OR ([TX_RESOLUTION] = ?)) AND ((? = 1 AND [TX_INTERNAL] IS NULL) OR ([TX_INTERNAL] = ?)) AND ((? = 1 AND [TX_PROBLEM] IS NULL) OR ([TX_PROBLEM] = ?)) AND ((? = 1 AND [TX_MODEL] IS NULL) OR ([TX_MODEL] = ?)) AND ((? = 1 AND [TX_PCAD_SERIAL] IS NULL) OR ([TX_PCAD_SERIAL] = ?)) AND ((? = 1 AND [TX_COMPANY] IS NULL) OR ([TX_COMPANY] = ?)) AND ((? = 1 AND [TX_CUSTOMER] IS NULL) OR ([TX_CUSTOMER] = ?)) AND ((? = 1 AND [TX_ADDRESS1] IS NULL) OR ([TX_ADDRESS1] = ?)) AND ((? = 1 AND [TX_ADDRESS2] IS NULL) OR ([TX_ADDRESS2] = ?)) AND ((? = 1 AND [TX_CITY] IS NULL) OR ([TX_CITY] = ?)) AND ((? = 1 AND [TX_PASSWORD] IS NULL) OR ([TX_PASSWORD] = ?)) AND ((? = 1 AND [TX_EMAIL] IS NULL) OR ([TX_EMAIL] = ?)))
OLD: 4116,8121,11/23/2010 12:00:00 AM,11/24/2010 12:00:00 AM,1,0,1,70983,0,1,,GENE  ,0,1,GA,30016,706-819-9244-C ,678-658-6850-H ,               ,16,3,1,,,,2,removed a couple spyware objects removed a couple trojan viruses everything working good,left laptop run?ning one last AVG scan to ensure infection is gone, but should be good to go,Still has virus.,,,                                                  ,Alfred Baldwin                                    ,105 Havenwood Ln.                                 ,                                                  ,Covington                                         ,,,
NEW: 4116,8121,11/23/2010 12:00:00 AM,11/24/2010 12:00:00 AM,1,0,1,70983,0,1,,GENE  ,0,1,GA,30016,706-819-9244-C ,678-658-6850-H ,               ,16,3,1,,,,2,removed a couple spyware objects removed a couple trojan viruses everything working good,left laptop run?ning one last AVG scan to ensure infection is gone, but should be good to go  This is a test update,Still has virus.,,,                                                  ,Alfred Baldwin                                    ,105 Havenwood Ln.                                 ,                                                  ,Covington                                         ,,,
NUL: IsNull_ID_TICKET:0,IsNull_DT_RCVD:0,IsNull_DT_COMPETE:0,IsNull_ID_ADAPTER:0,IsNull_ID_BAG:0,IsNull_ID_REWORK:0,IsNull_ID_QB_INV:0,IsNull_ID_COMPLETE:0,IsNull_ID_BILLED:0,IsNull_TX_REVD_LOC:1,IsNull_TX_COMP_LOC:0,IsNull_ID_EMERGENCY:0,IsNull_ID_VEND:0,IsNull_TX_STATE:0,IsNull_TX_ZIP:0,IsNull_TX_PHONE1:0,IsNull_TX_PHONE2:0,IsNull_TX_PHONE3:0,IsNull_ID_RECVD_BY:0,IsNull_ID_SERVD_BY:0,IsNull_ID_WARRANTY:0,IsNull_ID_ESTIMATE:1,IsNull_ID_APPROVED:1,IsNull_DT_APPROVED:1,IsNull_ID_TYPE:0,IsNull_TX_RESOLUTION:0,IsNull_TX_INTERNAL:0,IsNull_TX_PROBLEM:0,IsNull_TX_MODEL:1,IsNull_TX_PCAD_SERIAL:1,IsNull_TX_COMPANY:0,IsNull_TX_CUSTOMER:0,IsNull_TX_ADDRESS1:0,IsNull_TX_ADDRESS2:0,IsNull_TX_CITY:0,IsNull_TX_PASSWORD:1,IsNull_TX_EMAIL:1,
Columns that are null: (TX_REVD_LOC,ID_ESTIMATE,ID_APPROVED,DT_APPROVED,TX_MODEL,TX_PCAD_SERIAL,TX_PASSWORD,TX_EMAIL,)
Update Capture End                           
=====================================================

Open in new window


This is the code I’m using to generate that file.
Private Sub adapter_RowUpdated(ByVal sender As Object, ByVal e As System.Data.OleDb.OleDbRowUpdatedEventArgs)
        'This Handler is only active during debugging sessions
        'It's used for troubleshooting failed Updates
        'The handler is only added during debugger.isattached = true

        Try
            Debug.WriteLine("=====================================================")
            Debug.WriteLine("Update Capture Begin                         ")
            If (e.Errors IsNot Nothing) Then
                Debug.WriteLine("Error   : " & e.Errors.Message.ToString)
            Else
                Debug.WriteLine("No Error")
            End If

            Debug.WriteLine("Gen. SQL: " & e.Command.CommandText)

            Dim OldRow As String = ""
            Dim NewRow As String = ""
            Dim NullRow As String = ""
            Dim WhatIsNull As String = ""

            For Each par As OleDb.OleDbParameter In e.Command.Parameters
                'Debug.WriteLine(par.ParameterName & ":" & par.Value.ToString)
                If Mid(par.ParameterName, 1, 9) = "Original_" Then
                    OldRow = OldRow & par.Value.ToString & ","
                ElseIf Mid(par.ParameterName, 1, 7) = "IsNull_" Then
                    NullRow = NullRow & par.ParameterName & ":" & par.Value.ToString & ","
                    If par.Value = 1 Then WhatIsNull = WhatIsNull & par.ParameterName.ToString.Replace("IsNull_", "") & ","
                Else
                    NewRow = NewRow & par.Value.ToString & ","
                End If
            Next

            Debug.WriteLine("OLD: " & OldRow.Replace(vbNewLine, " "))
            Debug.WriteLine("NEW: " & NewRow.Replace(vbNewLine, " "))
            Debug.WriteLine("NUL: " & NullRow)
            Debug.WriteLine("Columns that are null: (" & WhatIsNull & ")")
            Debug.WriteLine("Update Failure Capture End                           ")
            Debug.WriteLine("=====================================================")

        Catch
            'Failure of a Failure Capture.  Sigh.
            MsgBox(Err.Description)
        End Try

    End Sub

Open in new window

 


Maybe we're getting close?  I think your right about the nulls.  I'm going to try filling in the nulls with zeros in that row to test while this is posted for review.  Thanks for all the help.

Billy
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
> I suppose that’s the .Net Framework trying to look out for me.
Yes. The tableadapter has builtin concurrency check mechanism.

I think you also need to define the parameters for the update command.
0
 

Author Comment

by:pcadpcad
Comment Utility
Good Evening CodeCruiser;

I agree that you are correct about the nulls from the updates, but I don't know an easy way to implement the parameters for the update.  Is there an easy way to do that, or perhaps even lock the row in the database and turn off the built in concurrency check for that transaction in progress?

Thanks;

Billy
0
 

Author Closing Comment

by:pcadpcad
Comment Utility
Any difficulty in following the solution was due to the complexity of the problem, and not CodeCrusier's explanation.
0
 

Author Comment

by:pcadpcad
Comment Utility
As a final follow up; I added logic to handle Concurrency and never could get the error to go away.  It turns out that a bad character was in one of the text fields.  Even if I removed every null from the record on the server side, Visual Studio would not update it.  I couldn't fix it in Visual Studio no matter what I did because that character kept me from saving anything back to the database.  I had to remove the character on the database side (it showed as a `?` in the data) and the problem went away.  I'm writing a "Repair" stored procedure for my application to repair these in the future.  I'll try to write some sanity checking on what my people are pasting into the system.

Thanks;

Billy
0
 
LVL 83

Expert Comment

by:CodeCruiser
Comment Utility
Glad the problem has been sorted. Also make sure you strip out the ' character (single quotation) as this also causes problems if its in the fields (SQL uses ' to enclose varchar values).
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Help with SQL Query 23 39
Extract XML Data from using TSQL 5 28
Round up to 100% in .NET 10 42
Creating Alerts in sql sever 2 13
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

744 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

18 Experts available now in Live!

Get 1:1 Help Now