Gridview, Data Source Control, and Update Parameters

[seaport]

I've been play Gridview (asp.net 2.0) for a few days and would like to confirm my findings.

1. Binding a gridview to a data source control (sqldatasource, in my case) is easy and uses very little code.

2. To update a data source, there must be mappings between the parameters of sql statement (or a store procedure) and information on the aspx page. I am surprised by how asp.net 2.0 handles the mappings using "UpdateParameters" collection. A parameter can be a base Parameter class, or some other parameter (like ControlParameter, CookieParameter). Unlike a ControlParameter, which provides a one-to-one mapping between a sql parameter and a control on the page, a base parameter (the tag is "asparameter") does not using any mapping.

So, implicitly, an update parameter is supposed to map to a bound field, which has a field name the same as the sql update parameter name, in a gridview row that is in the edit mode.

I did a test by specifying the update sql stored procedure with a parameter that is different from a bound field name. In other word, the bound field is "PersonName" but the parameter for the sql update procedure is "@PersonNameK".

When I click the "Update" button in the gridview, I got the error - "Procedure or function prPersons_Update has too many arguments specified. "

I stick to the rule that all operations on a sql server should be done only through stored procedures. So if I want to taking the advantage of binding a gridview to a sqldatasource, I must make the stored procedure of "select" to be consistent with the stored procedure of "update" - by mapping the output fields in the "select" procedure with parameters in the "Update" procedure.

So, what I mean is that this easy approach in asp.net requires some extra caution in sql programming.

Hopefully I made myself clear.

Any feedback is appreciated.

Seaport

 

[Kalisto]

Yes, but if you are selecting from a table, or updating the same table then your column names are the same anyway, so I dont see what the issue is?

If you need to make your gridview Column names more descriptive than those in your database you could always have

Select ID, Name, PersonNameK as PersonName....

Using the .net DataConnectionObjects imposes quite a strict structure on you behind the scenes to make it all appear to work so easily. If you need more flexibility, you'd be better off not using the control and writing your own code to connect, update etc.

K

 

[jbenson001]

This is why I stay away from the datasource controls. While they do help with a simple page, and not coding is required, there are problems when more complicated things need to be done. Use stored proecedures and fill a data set and bind that to your grid. And as Kalisto said, write your own Update and Insert stored procedures as well. You can always use the Microsoft Data Access Application Block

 

[seaport]

Thanks for your replies.

What I learned about using the data source control is that a set of stored procedures must be specifically created for it. So, if the cost of creating and maintaining that sql code goes beyond of writing vb code in aspx, I'd better not using data source control.

Seaport