Click here to Skip to main content
13,046,281 members (86,687 online)
Rate this:
Please Sign up or sign in to vote.
See more:
Can we use 2 update statements in one SQLDatasource?
Posted 2-Jul-13 23:38pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

Of course you can!

<asp:sqldatasource id="YourSqlDataSource" runat="server" xmlns:asp="#unknown">
    ConnectionString="<%$ ConnectionStrings:YourConnectionString %>" 
    UpdateCommand="UPDATE YourTable SET YourColumn = '0';
    UPDATE Your2ndTable SET Your2ndColumn = '1' WHERE ID = @ID;">
        <asp:parameter name="ID" />
vibsg 3-Jul-13 6:00am
Hi I am using update statement in deletecommand however I am getting error message as "must declare scaler variable @UpdatedBy" however delete is not working , update is perfectly working fine.
Please see the below code.
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:AddConnectionStrings %>" SelectCommand="SELECT ID,Name,Address,UpdatedBy,UpdatedDate FROM [Data]where Status=1 and Id=@Id order by Name" DeleteCommand="UPDATE [Data] SET [Status] = '0',[UpdatedBy]= @UpdatedBy,[UpdatedDate] = @UpdatedDate WHERE [ID] = @ID" InsertCommand="INSERT INTO [Data] ([Name], [Address][UpdatedBy],[UpdatedDate]) VALUES (@Name, @Address,@UpdatedBy,@UpdatedDate)" UpdateCommand="UPDATE [Data] SET [Name]= @Name, [Address] = @Address,[UpdatedBy]= @UpdatedBy,[UpdatedDate] = @UpdatedDate WHERE [ID] = @ID" > <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> <asp:Parameter Name="ID" Type="Int32" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" /> <asp:Parameter Name="ID" Type="String" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address" Type="String" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" /> <asp:Parameter Name="ID" Type="int32" /> <asp:Parameter Name="Name" Type="String" /> <asp:Parameter Name="Address" Type= "String" /> <asp:Parameter Name="UpdatedBy" Type="String" /> <asp:Parameter Name="UpdatedDate" Type="DateTime" />
pjaar89 3-Jul-13 6:13am
Do you execute your update command with the "enable editing" option of your GridView?

We don't have all the information to help you,

Why all your "parameter" tags aren't in for example? Do you really redefine your "UpdatedBy" parameter of your Update command?
vibsg 3-Jul-13 6:21am
If any insert/delete/update occurs in my detailsview then i want to save updatedby and updateddate filed in the table.

protected void DetailsView1_ItemUpdating(object sender, DetailsViewUpdateEventArgs e)

e.NewValues["UpdatedBy"] = Session["UpdatedBy"];
e.NewValues["UpdatedDate"] = DateTime.Now.ToString();

protected void DetailsView1_ItemInserting(object sender, DetailsViewInsertEventArgs e)
e.Values["UpdatedBy"] = Session["UpdatedBy"];
e.Values["UpdatedDate"] = DateTime.Now.ToString();


in case of update and insert above code is working fine.however when I am trying to delete then I am getting message as must declare scalar varibale @updatedby.

the same way I have wrtieen the code for deleting.

protected void DetailsView1_ItemDeleting(object sender, DetailsViewDeleteEventArgs e)

e.Values["UpdatedBy"] = Session["UpdatedBy"];
e.Values["UpdatedDate"] = DateTime.Now.ToString();


Please advice.-
vibsg 3-Jul-13 6:47am
<asp:GridView id="GridView1" runat="server"
OnRowDataBound="GridView1_RowDataBound" DataKeyNames="ID"
GridLines="None" ForeColor="#333333" EmptyDataText="There are no data records to display."
DataSourceID="sdsMapsAdd" CellPadding="4" AutoGenerateColumns="False" AllowSorting="True"
AllowPaging="False" OnRowCommand="GridView1_RowCommand"

<asp:CommandField ShowSelectButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" Visible="false" />
<asp:BoundField DataField="Name" HeaderText="Site Name" ReadOnly="true" SortExpression="Name" />
<asp:BoundField DataField="Address" HeaderText="Address" SortExpression="Address" />
<asp:BoundField DataField="UpdatedBy" Visible="false" />
<asp:BoundField DataField="UpdatedDate" Visible="false" />

<asp:SqlDataSource ID="sdsAdd" runat="server" ConnectionString="<%$ ConnectionStrings:AddConnectionStrings %>"

SelectCommand="SELECT ID,Name,Address,UpdatedBy,UpdatedDate FROM [Data]where Status=1 order by Name"


<asp:DetailsView AutoGenerateRows="False" DataKeyNames="ID" DataSourceID="SqlDataSource1"
ID="DetailsView1" runat="server" Width="554px" OnItemUpdated="DetailsView1_ItemUpdated" OnItemCommand="DetailsView1_ItemCommand" OnItemDeleted="DetailsView1_ItemDeleted" OnItemInserted="DetailsView1_ItemInserted" OnItemUpdating="DetailsView1_ItemUpdating" OnItemInserting="DetailsView1_ItemInserting" OnItemDeleting="DetailsView1_ItemDeleting" >
<asp:TemplateField HeaderText="Name" SortExpression="Name">

<asp:TextBox ID="TextBox50" TextMode="MultiLine" Width="350px" runat="server" Text='<%# Bind(Name") %>'>
<asp:RequiredFieldValidator ID="RequiredFieldValidator50" runat="server" ControlToValidate="TextBox50" Text="*" ErrorMessage="You must provide name" ValidationGroup="InsertValidationControls">
<asp:ValidationSummary ID="ValidationSummary50" runat="server"
ShowMessageBox="True" ShowSummary="False"
ValidationGroup="InsertValidationControls" >

<asp:TextBox ID="TextBox1" runat="server" Width="350px" Text='<%# Bind("Name") %>'>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1" ErrorMessage="You must provide name" Text="*" ValidationGroup="InsertValidationControls">
<asp:ValidationSummary ID="ValidationSummary2" runat="server"
ShowMessageBox="True" ShowSummary="False"
ValidationGroup="InsertValidationControls" >

<asp:Label ID="Label1" runat="server" Text='<%# Bind("Name") %>'>

<asp:TemplateField HeaderText="Address" SortExpression="Address">
<asp:TextBox ID="TextBox1" TextMode="MultiLine" Width="350px" runat="server" Text='<%# Bind("Address") %>'>
<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox1" Text="*" ErrorMessage="You must provide address" ValidationGroup="InsertValidationControls"></asp
pjaar89 3-Jul-13 7:49am
I do not think you actually assigns values ​​to your parameters in your method "DetailsView1_ItemDeleting".

This is a rather special case, why not try something like this?

ID="SqlDataSource3" runat="server"
DeleteCommand="UPDATE [Data] SET [Status] = '0',[UpdatedBy]= @UpdatedBy,[UpdatedDate] = getdate() WHERE [ID] = @ID"><deleteparameters><asp:SessionParameter
Name="UpdatedBy" SessionField="UpdatedBy" />

Your parameters will be defined somewhere.
vibsg 3-Jul-13 8:29am
Het Thank You so much . it worked well :) cheers
pjaar89 3-Jul-13 8:31am
glad, don't forget to mark the question as answered ;-)
vibsg 3-Jul-13 8:33am
ok,sure i will do right now. Thanks again :)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 3 Jul 2013
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100