Click here to Skip to main content
15,171,260 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Can we use 2 update statements in one SQLDatasource?
Posted

1 solution

Of course you can!

ASP.NET
<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;">
    <updateparameters>
        <asp:parameter name="ID" />
    </updateparameters>
</asp:sqldatasource>
   
v2
Comments
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"
>



<columns>
<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" >
<fields>
<asp:TemplateField HeaderText="Name" SortExpression="Name">


<edititemtemplate>
<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" >

<insertitemtemplate>
<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" >


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




<asp:TemplateField HeaderText="Address" SortExpression="Address">
<edititemtemplate>
<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">
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?

<asp:SqlDataSource
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)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900