sqlDataSource parameter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Keith G Hicks - 03 Apr 2008 07:18 GMT

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

Reply to this Message

Patrice - 03 Apr 2008 12:43 GMT

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:
[quoted text clipped - 3 lines]
> 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

Reply to this Message

Keith G Hicks - 03 Apr 2008 17:23 GMT

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>
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="City" Type="String" />
<asp:Parameter Name="Region" Type="String" />
<asp:Parameter Name="PostalCode" Type="String" />
<asp:Parameter 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...
>
[quoted text clipped - 35 lines]
> >
> > Keith

Reply to this Message

sqlDataSource parameter

Free Magazines