How to reference a control inside a formview template from a sqldatasource?

[Ned Balzer]

I'm trying to create a formview bound to a sqldatasource, and use a
stored procedure to insert data from the formview into several tables.
I have some simplified code below, the real code is much longer but
this should illustrate the problem:

<asp:FormView ID="bookFormView" runat="server"
DataSourceID="bookSqlDataSource">
<ItemTemplate>
<asp:textBox id="name"
text='<%# Eval("name") %>'
runat="server" />
<asp:textbox id="city"
text='<%#Eval("city") %>'
</ItemTemplate>
<InsertItemTemplate>
<asp:textBox id="name"
text='<%# Bind("name") %>'
runat="server" />
<asp:textbox id="city"
text='<%#Bind("city") %>'
runat = "server" />
</InsertItemTemplate>
</asp:FormView>

<asp:SqlDataSource ID="bookSqlDataSource" runat="server"
ConnectionString="<%$
ConnectionStrings:lapubsDBConnectionString %>"
ProviderName="<%$
ConnectionStrings:lapubsDBConnectionString.ProviderName %>"
InsertCommandType="StoredProcedure"
InsertCommand="usp_add_person"
SelectCommand="select name, city from view_namecity" >
<InsertParameters>
<asp:ControlParameter Name="Name" ControlID="???"
PropertyName="Text" Type="String" ConvertEmptyStringToNull="TRUE"
Size="20" />
<asp:ControlParameter Name="City" ControlID="???"
PropertyName="Text" Type="String" ConvertEmptyStringToNull="TRUE"
Size="20" />
</InsertParameters>
</asp:SqlDataSource>

The problem I'm having is in what to specify in the ControlID attribute
of the SQLdatasource's insertParameter. It seems that the only object
it will recognize is the Formview (ie. ControlID="bookFormView"). But
that isn't specifically the control that the param should be bound to.
But it doesn't understand if I specify ControlID="name" -- I think
that's because the page doesn't have such a control, the control is
embedded in the insertTemplate of the FormView. I've even tried
something like ControlID="bookFormview.FindControl('name')" but that
doesn't work either. How can I get a handle for the control that's
embedded inside the formview's template?

Or am I approaching this the wrong way? Should I specify the parameter
list inside the insertCommand: insertCommand="usp_add_person @name,
@city"? I can't really do that either because a couple of the
parameters are coming in from session and application variables, not
from the formview at all. I have to use a stored procedure because I
need to insert into multiple tables, and the app design won't allow me
to use triggers either.

Thanks, I hope someone can understand this question and has some
experience with this!

-- Ned

 

[chris]

Ned,

You simply need to use the Bind() method when binding any of your
fields to the FormView and then when you Insert or Update it will
automatically read the new values into your parameters. Use the
regular <asparameter /> to fill the values from the FormView, and
<asp:SessionParameter /> to fill from session variables.

To make things easy on yourself, make sure all of your parameter names
match from your stored proc, to your parameter variables in your
SqlDataSource, to your FormView.

This should allow everything to just work. It is really pretty nice,
once you get used to it.

One more thing you might run into if you use Guids in your database.
There is currently a bug when it comes to passing Guids throught he
<asparameter /> variable. The wizard will default the type="object"
but you will want to just delete that and leave the type out and
everything will work.

Also, you should be able to use the FormView wizard to configure all of
this.

HTH,
Chris

 

[Ned Balzer]

Chris,

Sorry for the late reply and thanks for your help -- this did work. I
was making it more complicated than it was. I assumed a control inside
a formview was a control, so was trying to use a controlParameter
instead of just a Parameter. At the moment I am not using GUIDs but I
may modify the code to use them.

Thanks again.

-- Ned