Update Commands in SQLDataSource.....

Question

0.00/5 (No votes)

See more:

C#2.0

C#

Can we use 2 update statements in one SQLDatasource?

Posted 2-Jul-13 23:38pm

vibsg

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

pjaar89 · Accepted Answer · 2013-07-02T23:54:00

Solution 1

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>

Posted 2-Jul-13 23:54pm

pjaar89

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