Button_Click .Update command

[Morris Neuman]

Hi,

I have a button (Update) and hve the click event defined as below. The
update event does not give me an error, however the record is not updated
either. Both AccessDataSource1 and SqlDataSource1 are defined as
AccessDataSource and SqlDataSource controls.

I have 2 textboxes (NumToDial and AttID). The textboxes and button are as
defined below.

<asp:TextBox ID="AttID" runat="server"></asp:TextBox>
<asp:TextBox ID="NumToDial" runat="server"></asp:TextBox>
<asp:Button ID="Update" runat="server" onclick="Update_Click"
Text="Update" />

protected void Update_Click(object sender, EventArgs e)
{
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

if (id.Equals("SqlDataSource1"))
{
GridView1.DataSourceID = "SqlDataSource1";
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
GridView1.DataBind();
GridView1.Visible = true;
}
else if (id.Equals("AccessDataSource1"))
{
GridView1.DataSourceID = "AccessDataSource1";
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
GridView1.DataBind();
GridView1.Visible = true;
}
}

I have tried different ways to define the parameters but keep getting syntax
errors when I do. When I do a similar .SelectCommand it works, however the
..Update give no error but the record is not updated either.

Can you please tell me what is wrong with the .UpdateCommand?

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for your post.

The issue is because the Update method on data source control is not
called. UpdateCommand will only be used in Update method. It is documented
at
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasou
rce.update.aspx.

The Update method is automatically called by the GridView, DetailsView, and
FormView controls during postback if the data has been changed. For data
that has been changed in other controls, the Update method can be
explicitly called on postback during the Load event.

In this case, please change the Update_Click method to:
---------------------------------------------
protected void Update_Click(object sender, EventArgs e)
{
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

if (id.Equals("SqlDataSource1"))
{
GridView1.DataSourceID = "SqlDataSource1";
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
// Update is required here
SqlDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;
}
else if (id.Equals("AccessDataSource1"))
{
GridView1.DataSourceID = "AccessDataSource1";
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
// Update is required here
AccessDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;
}
}
---------------------------------------------

In addition, I found that your task to update a the data source can be
simplified by ControlParameter
(http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlpa
rameter.aspx). Here is an example code for you to reference:
---------------------------------------------
<asp:TextBox ID="AttID" runat="server"></asp:TextBox>
<asp:TextBox ID="NumToDial" runat="server"></asp:TextBox>
<asp:Button ID="Update" runat="server" onclick="Update_Click"
Text="Update" />

<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="(SQL database connection string key)"
updatecommand="UPDATE Attendant SET attendant.ExtensionToDial =
@NumToDial WHERE Attendant.AttendantID = @AttID">
<selectparameters>
<asp:controlparameter name="NumToDial" controlid="NumToDial"
propertyname="Text"/>
<asp:controlparameter name="AttID" controlid="AttID"
propertyname="Text"/>
</selectparameters>
</asp:sqldatasource>

<asp:accessdatasource
id="AccessDataSource1"
runat="server"
datafile="(Path of access db file)"
updatecommand="UPDATE Attendant SET attendant.ExtensionToDial =
@NumToDial WHERE Attendant.AttendantID = @AttID">
<selectparameters>
<asp:controlparameter name="NumToDial" controlid="NumToDial"
propertyname="Text"/>
<asp:controlparameter name="AttID" controlid="AttID"
propertyname="Text"/>
</selectparameters>
</asp:accessdatasource>

<asp:gridview id="GridView1"
datasourceid="<%$ AppSettings:MyDataSource %>"
runat="server">
</asp:gridview>

Code behind:
protected void Update_Click(object sender, EventArgs e)
{
if (GridView1.DataSourceId.Equals("SqlDataSource1"))
{
SqlDataSource1.Update();
}
else if (GridView1.DataSourceId.Equals("AccessDataSource1"))
{
AccessDataSource1.Update();
}
GridView1.DataBind();
GridView1.Visible = true;
}
---------------------------------------------

Please let us know if it works. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.
 
Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Thanks I added the .Udate() command and it worked. I have used the
datasource command bound to a gridview.

In the click event, I have concatanated the text values of textboxes in the
update and select statement.

How would I define the parameters in the click event? I tried the following
to add parameters but get error.
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = @ExtensionToDial WHERE AttendantID= @AttID";
SqlDataSource1.UpdateParameters.Add("@ExtensionToDial",
SqlDbType.NVarChar, 32, NumToDial.Text);
SqlDataSource1.UpdateParameters.Add("@AttID",
SqlDbType.NVarChar, 25, AttID.Text);

Can you tell me what the correct syntax would be to use parameters with the
datasource definition in the click event?
--
Thanks
Morris

Hi Morris,

Thanks for your post.

The issue is because the Update method on data source control is not
called. UpdateCommand will only be used in Update method. It is documented
at
http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasou
rce.update.aspx.

The Update method is automatically called by the GridView, DetailsView, and
FormView controls during postback if the data has been changed. For data
that has been changed in other controls, the Update method can be
explicitly called on postback during the Load event.

In this case, please change the Update_Click method to:
---------------------------------------------
protected void Update_Click(object sender, EventArgs e)
{
string id = ConfigurationManager.AppSettings["MyDataSource"];
Control datasourcecontrol = this.FindControl(id);

if (id.Equals("SqlDataSource1"))
{
GridView1.DataSourceID = "SqlDataSource1";
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
// Update is required here
SqlDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;
}
else if (id.Equals("AccessDataSource1"))
{
GridView1.DataSourceID = "AccessDataSource1";
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";
// Update is required here
AccessDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;
}
}
---------------------------------------------

In addition, I found that your task to update a the data source can be
simplified by ControlParameter
(http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.controlpa
rameter.aspx). Here is an example code for you to reference:
---------------------------------------------
<asp:TextBox ID="AttID" runat="server"></asp:TextBox>
<asp:TextBox ID="NumToDial" runat="server"></asp:TextBox>
<asp:Button ID="Update" runat="server" onclick="Update_Click"
Text="Update" />

<asp:sqldatasource
id="SqlDataSource1"
runat="server"
connectionstring="(SQL database connection string key)"
updatecommand="UPDATE Attendant SET attendant.ExtensionToDial =
@NumToDial WHERE Attendant.AttendantID = @AttID">
<selectparameters>
<asp:controlparameter name="NumToDial" controlid="NumToDial"
propertyname="Text"/>
<asp:controlparameter name="AttID" controlid="AttID"
propertyname="Text"/>
</selectparameters>
</asp:sqldatasource>

<asp:accessdatasource
id="AccessDataSource1"
runat="server"
datafile="(Path of access db file)"
updatecommand="UPDATE Attendant SET attendant.ExtensionToDial =
@NumToDial WHERE Attendant.AttendantID = @AttID">
<selectparameters>
<asp:controlparameter name="NumToDial" controlid="NumToDial"
propertyname="Text"/>
<asp:controlparameter name="AttID" controlid="AttID"
propertyname="Text"/>
</selectparameters>
</asp:accessdatasource>

<asp:gridview id="GridView1"
datasourceid="<%$ AppSettings:MyDataSource %>"
runat="server">
</asp:gridview>

Code behind:
protected void Update_Click(object sender, EventArgs e)
{
if (GridView1.DataSourceId.Equals("SqlDataSource1"))
{
SqlDataSource1.Update();
}
else if (GridView1.DataSourceId.Equals("AccessDataSource1"))
{
AccessDataSource1.Update();
}
GridView1.DataBind();
GridView1.Visible = true;
}
---------------------------------------------

Please let us know if it works. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/en-us/subscriptions/aa948868.aspx#notifications.

Note: MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 2 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions. Issues of this
nature are best handled working with a dedicated Microsoft Support Engineer
by contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/en-us/subscriptions/aa948874.aspx
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for your reply.

Please change the code into:
---------------------------------------------------
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = @ExtensionToDial WHERE AttendantID= @AttID";
SqlDataSource1.UpdateParameters.Add("ExtensionToDial",
SqlDbType.NVarChar, 32, NumToDial.Text);
SqlDataSource1.UpdateParameters.Add("AttID",
SqlDbType.NVarChar, 25, AttID.Text);
---------------------------------------------------

There is no need to add "@" prefix when adding parameters.

Please let me know if it works. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Hi,

When I tried the proposed code, I get he following error:
Compilation Error
Description: An error occurred during the compilation of a resource required
to service this request. Please review the following specific error details
and modify your source code appropriately.

Compiler Error Message: CS1501: No overload for method 'Add' takes '4'
arguments

Source Error:



Line 74: // Update is parameterized as follows
Line 75: SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = @ExtensionToDial WHERE AttendantID= @AttID";
Line 76: SqlDataSource1.UpdateParameters.Add("ExtensionToDial",
SqlDbType.NVarChar, 32, NumToDial.Text);
Line 77: SqlDataSource1.UpdateParameters.Add("AttID",
SqlDbType.NVarChar, 25, AttID.Text);
Line 78: SqlDataSource1.Update();
Source File:
c:\Inetpub\wwwroot\CMWebManager\SystemAdminOnly\Copies\Test-with
Butto_Click-n-BrowseFile Examples.aspx Line: 76


Also how would I create these parameters for AccessDataSource? Just change
the @XXX to question mark (?)? Would I also need to replace the
"ExtensionToDial" and "AttID" to "?" each?

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for your reply.

I copied your original code and just deleted "@" prefix.

The correct syntax should be:
---------------------------------------
SqlDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = @ExtensionToDial WHERE AttendantID= @AttID";
SqlDataSource1.UpdateParameters.Add("ExtensionToDial", SqlDbType.NVarChar,
NumToDial.Text);
SqlDataSource1.UpdateParameters.Add("AttID", SqlDbType.NVarChar,
AttID.Text);
---------------------------------------
Please make sure that the SqlDbType parameter is the same as what the
column is in database.

For AccessDataSource, it has a restriction of paramters' order. Any
parameterized SQL queries that you specify in the SelectCommand,
UpdateCommand, InsertCommand, and DeleteCommand properties must match the
order of any Parameter objects that are in the corresponding parameter
collection.

However in this case, the order we specify the parameters is the same as
the order in UpdateCommand sql statement. So you can use the same way above
for AccessDataSource.

Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Hi,

1) I got the _click event update to work for SqlDataSource by using the
TypeCode.String instaed of the SqlDBType.NVarChar even though the db is sql
and the field is nvarchar. Can you tell my why TypeCode worked and not
SqlDBType?

2) I may also be required to update an Access table. I am using the
following in the _click command, however though I don't get any error, the
record does NOT update. I even replaced the "NumToDial" and "AttID" in the
parameters group with "?" but still does not work. Can you tell me why?

Code for the _Click event
GridView1.DataSourceID = "AccessDataSource1";
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = ? WHERE AttendantID= ?";
AccessDataSource1.UpdateParameters.Add("NumToDial",
TypeCode.String, NumToDial.Text);
AccessDataSource1.UpdateParameters.Add("AttID", TypeCode.String,
AttID.Text);
AccessDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;

Error:

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for your post.

For the 1) question, it is because that the update parameters of
SqlDataSource control is System.Web.UI.WebControls.Parameter type, which
can only accept TypeCode and DBType argument. SqlDBType should not be used
here. It is copied from your original sample code by mistake.

For 2), The code looks fine. We tested similar code in our lab and both "?"
and "@" parameters works properly for access database (mdb file). In order
to verfiy if the access database works correctly on your side, please use
Visual Studio to run an Update command as following steps:

1. Double click mdb file in the Solution Explorer and the mdb file will be
opened in Server Explorer. (If it is also opened in Office Access, please
close it)
2. Right click it in Server Explorer and select New Query.
3. In the Add Table window, select Attendant table and click Add button,
then close the window.
4. Right click on the query panel, select to change type to Update.
5. Copy the update statement "UPDATE Attendant SET
Attendant.ExtensionToDial = ? WHERE AttendantID= ?" into the query panel
and replace the "?" into real value.
6. Right click and select Execute SQL to run the update operation.
7. Right click on the table in Server Explorer and select Retrieve Data to
check if the data has been updated correctly.

Another option for you is to send us your mdb file and we will do a
complete test on it in our lab.

Please let us know if there is anything unclear. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Hi,

I did as suggested and tried the query directly via the Solution Explorer
and it worked. I also tried it in Access and it also worked.

In my web page I tried as a concatenated query as below and it works.
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";


However when I try the code below, I don't get and error but it does not work.

AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
ExtensionToDial = ? WHERE (AttendantID= ?)";
AccessDataSource1.UpdateParameters.Add("NumToDial",
TypeCode.String, NumToDial.Text);
AccessDataSource1.UpdateParameters.Add("AttID", TypeCode.String,
AttID.Text);


I have the following namespaces defined. Not sure if it has somthing to do
with that.
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.Odbc" %>

Thanks for the followup and help.

--
Thanks
Morris

Hi Morris,

Thanks for your post.

For the 1) question, it is because that the update parameters of
SqlDataSource control is System.Web.UI.WebControls.Parameter type, which
can only accept TypeCode and DBType argument. SqlDBType should not be used
here. It is copied from your original sample code by mistake.

For 2), The code looks fine. We tested similar code in our lab and both "?"
and "@" parameters works properly for access database (mdb file). In order
to verfiy if the access database works correctly on your side, please use
Visual Studio to run an Update command as following steps:

1. Double click mdb file in the Solution Explorer and the mdb file will be
opened in Server Explorer. (If it is also opened in Office Access, please
close it)
2. Right click it in Server Explorer and select New Query.
3. In the Add Table window, select Attendant table and click Add button,
then close the window.
4. Right click on the query panel, select to change type to Update.
5. Copy the update statement "UPDATE Attendant SET
Attendant.ExtensionToDial = ? WHERE AttendantID= ?" into the query panel
and replace the "?" into real value.
6. Right click and select Execute SQL to run the update operation.
7. Right click on the table in Server Explorer and select Retrieve Data to
check if the data has been updated correctly.

Another option for you is to send us your mdb file and we will do a
complete test on it in our lab.

Please let us know if there is anything unclear. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for your reply.

We have built a similiar website as yours to try to reproduce the issue.
However, the code your provided works correctly to our lab's mdb file
(Access database file). Can you send us your mdb file and the parameter
values (NumToDial.Text and AttID.Text) to reproduce the issue? If it is too
big, please clear the data in table other than "Attendant".

Please understand that reproducing the issue is a very important step for
us to resolve this issue. My email address is
(e-mail address removed), remove 'online.'.

Thanks for your cooperation.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Morris Neuman]

Hi,

I finally got the Access update query to work, however I needed a
UpdateParameters.Clear command as follows.

GridView1.DataSourceID = "AccessDataSource1";
//Can concatenate parameters as following statement or use as
parameters.
//AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
Attendant.ExtensionToDial = '" + NumToDial.Text + "' WHERE
(Attendant.AttendantID)= '" + AttID.Text + "'";

// Update is parameterized as follows - WORKING with .Clear only
AccessDataSource1.UpdateParameters.Clear();
AccessDataSource1.UpdateCommand = "UPDATE Attendant SET
ExtensionToDial = ? WHERE (AttendantID= ?)";
AccessDataSource1.UpdateParameters.Add("NumToDial",
TypeCode.String, NumToDial.Text);
AccessDataSource1.UpdateParameters.Add("AttID", TypeCode.String,
AttID.Text);

AccessDataSource1.Update();
GridView1.DataBind();
GridView1.Visible = true;

Why do I require this clear method? It is not required for a similar update
to the SQL table.

 

[Hongye Sun [MSFT]]

Hi Morris,

Thanks for sharing your solution with us.

Have you ever changed the UpdateParameters before the code was executed?
You can verify it by set a breakpoint just before
AccessDataSource1.UpdateParameters.Clear(); and check if the value of
AccessDataSource1.UpdateParameters.Count is not 0. If it is not, that means
it has been changed before you use it.

If it is in this situation, AccessDataSource1.UpdateParameters.Clear()
works to clear the parameters and keep it to be empty.

Please have a try and let us know if there is anything unclear. Thanks.

Regards,
Hongye Sun ([email protected], remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).
 
This posting is provided "AS IS" with no warranties, and confers no rights.