Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#2.0
Can we use 2 update statements in one SQLDatasource?
Posted 3-Jul-13 0:38am
vibsg428

1 solution

Rate this: bad
good
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;">
    <updateparameters>
        <asp:parameter name="ID" />
    </updateparameters>
</asp:sqldatasource>
  Permalink  
v2
Comments
vibsg at 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 at 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 at 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 at 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">
pjaar89 at 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"><asp:SessionParameter
Name="UpdatedBy" SessionField="UpdatedBy" />

 
Your parameters will be defined somewhere.
vibsg at 3-Jul-13 8:29am
   
Het Thank You so much . it worked well :) cheers
pjaar89 at 3-Jul-13 8:31am
   
glad, don't forget to mark the question as answered ;-)
vibsg at 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
0 OriginalGriff 329
1 Maciej Los 290
2 Sergey Alexandrovich Kryukov 230
3 Shweta N Mishra 185
4 PIEBALDconsult 149
0 OriginalGriff 7,660
1 Sergey Alexandrovich Kryukov 7,072
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,760


Advertise | Privacy | Mobile
Web02 | 2.8.1411023.1 | Last Updated 3 Jul 2013
Copyright © CodeProject, 1999-2014
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