sqlDataSource parameter

[Keith G Hicks]

asp.net 2.0

When I use the wizard in VWD 2005 to set up a sqlDataSource it creates code
like this for the UpdateCommand:

UpdateCommand="UPDATE [Customers] SET [CustomerName] = @CustomerName, .....
WHERE ....

When I bind text boxes to the values in the SelectCommand, how does the
UpdateCommand know that the @CustomerName parameter goes with the correct
text box. Is it that the Bind("CustomerName") has the same field name as the
name of the "@" parameter? I don't see anythign else in the code that's
generated that would do that.

Ok, so having asked that here's my main question. I need to pass another
value into the update command that's NOT bound to a text box. For example:

UpdateCommand="UPDATE [Customers] SET [CustomerName] = @CustomerName,
[UpdatedBy] = ???????? ..... WHERE ....

I need to post the currently logged in user (My.User.Name) to the
"UpdatedBy" field in the database. How do I do this?

Lastly, when the wizard generates the UpdateCommand and DeleteCommand, why
does it put ALL the fields in the WHERE clause? That makes no sense to me. I
would normally only put the PK in the WHERE clause in an Update or Delete
statement.

Thanks (still learning),

Keith

 

[Patrice]

Inline...

When I bind text boxes to the values in the SelectCommand, how does the
UpdateCommand know that the @CustomerName parameter goes with the correct
text box. Is it that the Bind("CustomerName") has the same field name as
the
name of the "@" parameter? I don't see anythign else in the code that's
generated that would do that.

http://msdn2.microsoft.com/en-us/library/xt50s8kz(VS.80).aspx

Don't you see this section in your ASPX markup ? Don't you have defined this
?

Ok, so having asked that here's my main question. I need to pass another
value into the update command that's NOT bound to a text box. For example:

UpdateCommand="UPDATE [Customers] SET [CustomerName] = @CustomerName,
[UpdatedBy] = ???????? ..... WHERE ....
I need to post the currently logged in user (My.User.Name) to the
"UpdatedBy" field in the database. How do I do this?

Choose something from the article above and use this as a source ?

Lastly, when the wizard generates the UpdateCommand and DeleteCommand, why
does it put ALL the fields in the WHERE clause? That makes no sense to me.
I
would normally only put the PK in the WHERE clause in an Update or Delete
statement.

This is to handle optimistic concurrency. It uses the original values (in
addtion to the pk) so if someone changed this row, the clause where won't
match and so it means that someone changed this row while you were editing
it... When usign SQL Server a timestamp (rowversion) column (that is updated
automatically each time the row is updated) can be more convenient...

 

[Keith G Hicks]

Thanks Patrice. That helped a lot. One thing I'm still not clear on. If the
Update, Select, ... Commands have more than one parameter, is it the ORDER
of them that counts? In one of the samples on the link you sent me below
they have multiple parameters. In the InsertCommand they are

@LastName, @FirstName, @Address, @City, @Region, @PostalCode

In the parameters definition they have:

<InsertParameters>
<asparameter Name="LastName" Type="String" />
<asparameter Name="FirstName" Type="String" />
<asparameter Name="Address" Type="String" />
<asparameter Name="City" Type="String" />
<asparameter Name="Region" Type="String" />
<asparameter Name="PostalCode" Type="String" />
<asparameter Name="EmpID" Direction="Output" Type="Int32"
DefaultValue="0" />
</InsertParameters>

They are in order but they also have the same names. I'm guessing that it's
the order that's important and not the names but I'm not entirely sure.

Thanks,

Keith

Inline...

When I bind text boxes to the values in the SelectCommand, how does the
UpdateCommand know that the @CustomerName parameter goes with the correct
text box. Is it that the Bind("CustomerName") has the same field name as
the
name of the "@" parameter? I don't see anythign else in the code that's
generated that would do that.

http://msdn2.microsoft.com/en-us/library/xt50s8kz(VS.80).aspx

Don't you see this section in your ASPX markup ? Don't you have defined this

?


Ok, so having asked that here's my main question. I need to pass another
value into the update command that's NOT bound to a text box. For example:

UpdateCommand="UPDATE [Customers] SET [CustomerName] = @CustomerName,
[UpdatedBy] = ???????? ..... WHERE ....
I need to post the currently logged in user (My.User.Name) to the
"UpdatedBy" field in the database. How do I do this?

Choose something from the article above and use this as a source ?

Lastly, when the wizard generates the UpdateCommand and DeleteCommand, why
does it put ALL the fields in the WHERE clause? That makes no sense to me.
I
would normally only put the PK in the WHERE clause in an Update or Delete
statement.

This is to handle optimistic concurrency. It uses the original values (in
addtion to the pk) so if someone changed this row, the clause where won't
match and so it means that someone changed this row while you were editing
it... When usign SQL Server a timestamp (rowversion) column (that is updated
automatically each time the row is updated) can be more convenient...

Thanks (still learning),

Keith