Dynamically add update parameter to formview

I have a formview with name, email, and password. I bind all fields to sql except the password which is blank.

In my sqldatasource, I define parameters for name, email and id:

UpdateCommand

="UPDATE UserProfile SET Name = @.Name,Email = @.Email WHERE (ID = @.ID)">
<UpdateParameters>
<asp:ParameterName="Name"/>
<asp:ParameterName="Email"/>
<asp:ParameterName="ID"/>
</UpdateParameters>

In code I want to add a password parameter if there is value in the password field otherwise I don't want the password field updated. If I add define a password parameter like above then if a user left the password field blank then their new is blank. That's way I think adding it dynamically is the way. But I am having problems with the code to add the parameter in sqldatasource_updating event.

Protected

Sub SqlProfile_Updating(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.Updating
Dim passwordAs TextBox = FormView1.FindControl

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not password.Text.ToString &"" =""ThenSqlProfile.UpdateParameters.Add(New Parameter("@.Password", TypeCode.String, password.Text.ToString))End IfEnd Sub

ThanksYou're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

|||

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement. That way you just pass what ever you have in your form (blank password or populated password) and let the SQL statement figure it out for you. If not, then you have do add a new parameter to the updateparameters AND modify your UpdateCommand to have the additional line.

need help with the SQL?

|||

ecbruck:

You're close:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Thene.Command.Parameters.Add(password.Text)End IfEnd Sub

if he does it that way, he will need to modify his command as well... adding "Password = @.Something"

|||

pixelsyndicate:

I think you should add the parameter manually, and check for a null / blank parameter in the sql statement.

I agree. I would personally let me Stored Procedure handle the case when the Password parameter was passed in as null.

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

|||

Thanks for the help.

This is what I have so far but still doesn't work.

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then SqlProfile.UpdateParameters.Add("password", password.Text) SqlProfile.UpdateCommand ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

 It updates the name field with no errors but the password doesn't get updated.

|||You need to be modifying the members of the SqlDataSourceCommandEventArgs class rather than the SqlDataSource class as I did in my previous example.|||

When I did your example:

Protected Sub SqlProfile_Updating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceCommandEventArgs) Handles SqlProfile.Updating Dim password As TextBox = FormView1.FindControl("tb_password1") If Not String.IsNullOrEmpty(password.Text) Then e.Command.Parameters.Add(password.Text) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)" End If l_errormessage.Text = password.Text.ToString l_errormessage.Text += e.Command.CommandText.ToStringEnd Sub

I get this error:

The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details:System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not String objects.

Source Error:

Line 31: Dim password As TextBox = FormView1.FindControl("tb_password1")Line 32: If Not String.IsNullOrEmpty(password.Text) ThenLine 33: e.Command.Parameters.Add(password.Text)Line 34: e.Command.CommandText = "UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"Line 35: End If

|||

Thanks for all the help. This finally work with this code:

Protected Sub SqlProfile_Updating(ByVal senderAs Object,ByVal eAs System.Web.UI.WebControls.SqlDataSourceCommandEventArgs)Handles SqlProfile.UpdatingDim passwordAs TextBox = FormView1.FindControl("tb_password1")If Not String.IsNullOrEmpty(password.Text)Then Dim pAs SqlParameter =New SqlParameter("@.Password", SqlDbType.NVarChar) p.Value = password.Text e.Command.Parameters.Add(p) e.Command.CommandText ="UPDATE UserProfile SET FirstName = @.FirstName,Password=@.Password WHERE (UserName = @.UserName)"End If End Sub