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)…
Tag: ADO.Net
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…
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…
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:…
"There is already an open DataReader associated with this Command which must be closed first" explained
Short one. This sometimes catches people out. When reading a SqlDataReader and then after the read is done, there is an attempt to use the connection that was used for the reading. The exception thrown is (in this case it is thrown when trying to update a table using the same connection that was…
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…
SQL CLR stored procedure called via JDBC using setAutoCommit(false) does not update database.
I have come across this question a few times; “I have a java client that connects to SQL Server via the JDBC driver. I then call a CLR stored procedure that interacts with the database. When I run with setAutoCommit false the data is not committed. However, if I call a ‘normal’ TSQL stored procedure,…
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…
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…
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…