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

  This exception still seems to catch people out when updating, for example, DataSets:   System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 of the expected 1 records.   This is simply because DataSet (or ADO.Net rather) uses Optimistic Concurrency by default. This means that when updating, the whole row (rather than the changed column only)…

9

Parent/Child DataRelation. The Basics.

  In short, a parent – child relation is one where (in database terms) two tables have a one-to-many relationship. One example would be Author and Books, one Author may have written many books, but a Book can only have one Author. This is of course an extreme simplification.   In ADO.Net you can easily…

1

How to see the SQL executing when updating a DataSet via DataAdapter.Update()

A fairly common question: How can I see the SQL that will be executed when updating a DataSet using a DataAdapter?   The first thing most people try is to get hold of is the Insert/Update commands for the DataAdapter. This is typically done like so (using the trusted Northwind) with CommandBuilders:               using…

0

DataTable.GetChanges(DataRowState.Modified) returns NULL

  The scenario; you have a DataGridView on a Windows Form, you then manually, in code, create a DataTable and set this to be the DataSource of the DataGrid. You then edit some of the data in the grid and then you need to get hold of a subset of data containing the Modified rows:…

0

IsolationLevel is carried over to connections in connectionpool

When using SqlConnection and Transactions you my get a somewhat unexpected behavior. Namely that when you pick a connection from the connection pool, the connection you get will have inherited the IsolationLevel from what was set on the connection when it went into the pool.   When doing research on this, I found that this…

1

System.Data.SqlClient.SqlException: Cannot open database "database name" requested by the login. The login failed.

A short one today, this is about one of the slightly confusing error messages when it comes to connecting to SQL Server, namely:   System.Data.SqlClient.SqlException: Cannot open database “database name” requested by the login. The login failed. Login failed for user ‘the login’.    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at…

0

How use Convert in a DataTable select?

How to use Convert() when doing DataTable.Select ?   Sometimes you may need or want to select out a number of rows from a DataTable using the LIKE operator, this is no problem on a column with a string type. But what if you wish to do it on a column with, for example, an…

1

A simple example on how to get Return and Out parameter values using Ado.Net

Another short one.   How to capture OUT and RETURN parameter values from a stored procedure in .Net.   Well, I’ll let the example speak for itself.   First create a stored procedure, this just take an in parameter and an out parameter, it declares a local variable that will be used as the return…

2