FIX: The data source SqlDataSource does not support update operations unless you specify UpdateCommand

Today, to do something, update the database, the database has been updated successfully, but SqlDataSource to support, so I found all possible reasons, finally solved. is to update the database must remember to update sqldatasource! although not a big mistake, but changed, the page will be hundred no error, look at the comfort of it! Here is a simple example of updating it:

Sqldatasource1.updatecommand = "UPDATE PlayList SET playtime=" +time2+ ", playborder=" +border+ "";

Let's take a look at the reasons below:
SqlDataSource controls

The SqlDataSource control is a data source control that represents a connection to a relational data store, such as SQL Server or Oracle or any data source that can be accessed through an OLE DB or an ODBC bridge.

We use two main attributes to establish a data store connection: ConnectionString and ProviderName. The previous property represents a connection string that contains enough information to open a session with the underlying engine. The latter attribute specifies the namespace of the ADO-managed provider used by this operation. The ProviderName property defaults to System.Data.SqlClient, which means that the default data store is SQL Server. For example, to target an OLE DB provider, use the SYSTEM.DATA.OLEDB string.

The control can use either a data adapter or a command object to retrieve data. Based on our selection, the data obtained will be encapsulated in a DataSet object or in a data reader. The following code snippet illustrates the minimum code required to activate a SQL data source control that is bound to a SQL Server database:

<asp:sqldatasource runat= "Server" id= "Mysqlsource"

Providername= ' <%$ ConnectionStrings:LocalNWind.ProviderName%> '

Connectionstring= ' <%$ connectionstrings:localnwind%> '

Selectcommand= "SELECT * FROM Employees"/>

<asp:datagrid runat= "Server" id= "grid" datasourceid= "Mysqlsource"/>

1. Programming Interface for SqlDataSource controls
Table 9.10 Lists the attribute groups that provide the data operations that are supported by the associated View class.



Table 9.10 Properties of the configuration data operation
Attribute Group
Describe

DeleteCommand, DeleteParameters,

Deletecommandtype
Gets or sets the SQL statements, related parameters, and types (text or stored procedures) that are used to delete rows of data in the underlying data store.

FilterExpression, FilterParameters
Gets or sets the string (and related parameters) used to create a filter on top of the data obtained using the Select command. Only works if the control is managing data through a dataset.

InsertCommand, InsertParameters,

Insertcommandtype
Gets or sets the SQL statements, related parameters, and types (text or stored procedures) that are used to insert new rows in the underlying data store.

SelectCommand, SelectParameters,

Selectcommandtype
Gets or sets the SQL statements, related parameters, and types (text or stored procedures) that are used to fetch data from the underlying data store.

SortParameterName
Gets or sets the name of an input parameter that the stored procedure of a command will use to store data. (the command in this case must be a stored procedure.) If this parameter is missing, an exception is raised.

UpdateCommand, UpdateParameters,

Updatecommandtype
Gets or sets the SQL statements, related parameters, and types (text or stored procedures) that are used to update rows of data in the underlying data store.


Each command property is a string that contains the SQL text that will be used. The command can optionally contain the parameters listed in the associated parameter set. The managed provider and its underlying relational engine determine the exact syntax to use for SQL and the syntax for embedding parameters. For example, if the data source control points to SQL Server, the command parameter name must be prefixed with @symbol. If the target data source is an OLE DB provider, the parameter is unnamed, identified with a placeholder symbol, and positioned by location. The following code snippet shows a more complex data source control with the parameterized Delete and update command enabled:

<asp:sqldatasource runat= "Server" id= "Mysqlsource"

Connectionstring= ' <%$ connectionstrings:localnwind%> '

Selectcommand= "SELECT * FROM Employees"

Updatecommand= "UPDATE Employees SET [email protected]"

Deletecommand= "DELETE from Employees WHERE [email protected]"

Filterexpression= "EmployeeID > 3" >

<!--parameters Go

</asp:SqlDataSource>

The FilterExpression attribute applies the same syntax as the RowFilter property of the DataView class, and the latter is similar to the syntax used by the SQL WHERE clause. If the filterexpression needs to be parameterized, the parameters can be indicated by the FilterParameters collection. Filtering is enabled only if DataSourceMode is set to a dataset.

Note the difference between the filter expression and the parameters on the Select command. The parameters on the command affect the result set returned by the data store, and the filter expression restricts the display of the result set returned by the Select command.

Table 9.11 describes the other operation properties of the SqlDataSource class in detail. The list does not include cache-related properties, which are described later.



Table 9.11 Other properties of the SqlDataSource class
Property
Describe

Cancelselectonnullparameter
Indicates whether the data retrieval operation is revoked if one parameter equals null. The default value is true.

ConflictDetection
Determines how the control should handle data conflicts during a single delete or update operation. By default, changes that occur at the same time are overwritten.

ConnectionString
The connection string to connect to the database.

DataSourceMode
Indicates how data should be returned: through a dataset or through a data reader.

OldValuesParameterFormatString
Gets or sets a format string that is applied to the name of any parameter passed to the delete or Update method.

ProviderName
Indicates the namespace of the ADO-managed provider that will be used.


Interestingly, many of these properties map the same properties defined on the actual view class, as shown in Figure 9.10 earlier.

The SqlDataSource object has several methods and events, and in most cases all of the data source components have these methods and events. Methods include Delete, Insert, select, and update, and they are implemented as a unique wrapper for the corresponding method of the underlying data source view class. These events are present in pairs: deleting/deleted, inserting/inserted, selecting/selected, and updating/updated, and are fired before and after the above methods. The start of the filtering operation is signaled by the filtering event.

As mentioned earlier, the control that is unique to the ASP. NET 2.0 is the only control that truly leverages the capabilities of the data source control. Thus, the following two chapters specifically describe the GridView, DetailsView, and FormView controls, where you will see a large number of sample code that shows how to use the SqlDataSource Control for selection, update, paging, and sorting. In this chapter, we'll spend more time discussing other features of the control, which are particularly well suited for use in real-world applications.

2. Declarative parameters
Each command property has its own set of parameters--parametercollection An instance of the collection class. ASP. NET 2.0 supports a number of parameter types, as shown in table 9.12.

Table 9.12 parameter types in ASP. NET 2.0
Parameters
Describe

ControlParameter
Gets the parameter value from any public property of a server control.

CookieParameter
Sets the parameter value based on the content of the specified HTTP cookie.

FormParameter
Gets the parameter value from the specified input field in the HTTP request form.

Parameter
Gets the parameter values that are assigned by the code.

Profileparameter
Gets the parameter value from the specified property in the profile object that is created based on the personalization mechanism of the application.

QueryStringParameter
Gets the parameter value from the specified variable in the request query string.

SessionParameter
Sets the parameter value based on the contents of the specified session-state slot.


Each parameter class has a Name property and its role and implementation-specific set of properties. To understand the declarative parameters in the data source control, let's look at the following code:

<asp:sqldatasource runat= "Server" id= "MySource"

Connectionstring= ' <%$ connectionstrings:localnwind%> '

Selectcommand= "SELECT * FROM Employees WHERE EmployeeID > @MinID" >

<SelectParameters>

<asp:controlparameter name= "MiniD" controlid= "EmpID"

Propertyname= "Text"/>

</SelectParameters>

</asp:SqlDataSource>

The query contains a placeholder named @minid. The data source control automatically populates the placeholder with the information returned by the ControlParameter object. The value of the control parameter is determined by the given property on the given control. The PropertyName property specifies the name of the property, and the ID of the control is in the ControlID property. In order for the preceding code to work, the page developer must ensure that the page contains a control with the given ID and attributes; otherwise, an exception is thrown. In this example, the value of the Text property on the Empid control is used as the value of the matching parameter.

The binding between the formal parameter (the placeholder in the command text) and the actual value depends on how the underlying managed provider handles and identifies the parameter. If the provider type supports well-known parameters (like SQL Server and Oracle), this binding involves matching the name of the placeholder with the name of the parameter. Otherwise, this match is location-based. Therefore, the 1th placeholder is bound to the 1th parameter, and so on. This is what happens when you use OLE DB to access data.

3. Conflict Detection
The SqlDataSource control can selectively perform database intrusion operations (delete and update) in one of two ways. The data source control is associated with a data-bound control, so it is not farfetched to read the data at the same time, perhaps to modify it on the client and then update it. How should the update/delete method behave when multiple users have read/write access to the database and if the records they are attempting to manipulate are also modified?

The SqlDataSource control uses the ConflictDetection property to determine what to do when the update and delete operations are performed. The property is declared as a ConflictOptions enumeration type, and its default value is OverwriteChanges, which means that any intrusion operation will change regardless of whether the value in that row has changed since the last time it was read. Another value is compareallvalues, which simply ensures that the SqlDataSource control will take the raw data from the database read and pass it to the delete or update method of the underlying view class.

It is important to note that unless you write a delete or UPDATE statement, that is, if the data in the row does not match the data that was originally read, the command fails, otherwise changing the value of ConflictDetection does not have any significant effect. To do this, the command should be defined as follows:

UPDATE employees SET [email protected]

WHERE [email protected] and [email protected]_firstname

In other words, we must explicitly add an extra clause to the command to check whether the current value of the field being modified still matches the value that was originally read. In this way, intermediate changes entered by multiple users at the same time cause the WHERE clause to fail, causing the command to fail. We are responsible for adjusting the command text ourselves, and it is not enough to set ConflictDetection to CompareAllValues.

How do I format the name of a parameter that represents an old value? The SqlDataSource control uses the OldValuesParameterFormatString property to format these parameter names. The default value is original_{0}.

If you use the CompareAllValues option, you can handle deleted or updated events on the data source control to check how many rows are affected. If the operation does not affect any records, a concurrency violation may occur (concurrency violation):

void OnUpdated (object sender, SqlDataSourceStatusEventArgs e)

{

if (e.affectedrows = = 0) {

...

}

}

4. Caching behavior
Data binding between a data-bound control and its data source component is done automatically, and occurs every time a page postback is caused by a data-bound control. Suppose a page has a grid, a data source control, and a button. If you open the grid in edit mode, the Select command runs, and if you click the button (outside the bounds of the data-bound control), the UI of the grid is rebuilt according to the view state and no SELECT statements are run.

To save a query on each postback, you can require the data source control to cache the result set within a given period. In the time the data is cached, the Select method retrieves the data from the cache (not the underlying database). When the cache expires, the Select method retrieves data from the underlying data and the new data is stored back into the cache. The caching behavior of the SqlDataSource is controlled by the attributes in table 9.13.

Table 9.13 Cache properties on SqlDataSource
Property
Describe

CacheDuration
Indicates how long (in seconds) the data should remain in the cache.

CacheExpirationPolicy
Indicates whether the cache age is absolute or adjustable. If absolute, invalidates the data in the cache after the specified number of seconds. If it is adjustable, it invalidates data that has not been used within the specified period.

Cachekeydependency
Indicates the name of the user-defined cache key that is linked to all cache entries created by the data source control. By terminating the key, you can clearly understand the cache of the control.

EnableCaching
Enable or disable cache support.

SqlCacheDependency
Gets or sets a semicolon-delimited string that indicates which databases and tables are used for SQL Server cache dependency.


Each of the different combinations of SelectCommand, ConnectionString, and SelectParameters creates a cache entry. If multiple SqlDataSource controls just load the same data in the same database, they can share the same cache entry. The Cachekeydependency property allows you to control which cache items are managed by the data source control. If the property is set to a non-null string, the SqlDataSource control is forced to establish a dependency between the user-defined key and all cache entries created by the control. At this point, to clear the cache of the control, simply assign a new value to the dependent key:

cache["ClearAll"] = Anyinitializationvalue;

sqldatasource1.cachekeydependency = "ClearAll";

...

cache["ClearAll"] = Anyothervalue;

The SqlDataSource control can cache data only when it is working in DataSet mode. If DataSourceMode is set to DataReader and the cache is enabled, an exception is obtained.

Finally, the SqlCacheDependency property links the data cached by the SqlDataSource control to the contents of the specified database table (usually the same table as the table that provides the cached data):

<asp:sqldatasource id= "SqlDataSource1" runat= "Server"

cacheduration= "1200"

connectionstring= "<%$ connectionstrings:localnwind%>"

Enablecaching= "true"

Selectcommand= "SELECT * FROM Employees"

sqlcachedependency= "Northwind:employees" >

</asp:SqlDataSource>

FIX: The data source SqlDataSource does not support update operations unless you specify UpdateCommand