I wanted to know how the parameters values are passed to SqlDataSource object through the Data web server control GridView. From the Microsoft website I got to know that the values are passed using DataKeyNames therefore the parameters name must match with
that of DataKeyNames. But when I tried to print their value while updating data through the GridView control (the values was printed from the updating
function method of SqlDataSource's Object). But It prints nothing for p.DefaultValue or p.ToString(); I am just getting blank in front of the parameter on the output screen.
How to retrieve those values? Or may be the values doesn't get actually passed. Please help me where I am missing.
How can I check those values just before updating the data in database?
Second Question I am unable to delete from DataGridView Control. Everytime I click on delete button a postback occurs but nothing happens.
Here is the code.
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:DCSADatabaseConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Emp]"
UpdateCommand="UPDATE [Emp] SET [ename] = @ename, [job] = @job, [mgr] = @mgr, [hiredate] = @hiredate, [sal] = @sal, [comm] = @comm, [deptno] = @deptno WHERE [empno] = @original_empno AND [ename] = @original_ename AND [job] = @original_job AND (([mgr] = @original_mgr) OR ([mgr] IS NULL AND @original_mgr IS NULL)) AND (([hiredate] = @original_hiredate) OR ([hiredate] IS NULL AND @original_hiredate IS NULL)) AND (([sal] = @original_sal) OR ([sal] IS NULL AND @original_sal IS NULL)) AND (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"
DeleteCommand="DELETE FROM Emp WHERE (empno = @empno)"
OnUpdated="SqlDataSource1_Updated"
OnUpdating="SqlDataSource1_Updating">
<DeleteParameters>
<asp:Parameter Name="empno" Type="Int32"/>
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ename" Type="String" />
<asp:Parameter Name="job" Type="String" />
<asp:Parameter Name="mgr" Type="Int32" />
<asp:Parameter DbType="Date" Name="hiredate" />
<asp:Parameter Name="sal" Type="Int32" />
<asp:Parameter Name="comm" Type="Int32" />
<asp:Parameter Name="deptno" Type="Int32" />
<asp:Parameter Name="original_empno" Type="Int32" />
<asp:Parameter Name="original_ename" Type="String" />
<asp:Parameter Name="original_job" Type="String" />
<asp:Parameter Name="original_mgr" Type="Int32" />
<asp:Parameter DbType="Date" Name="original_hiredate" />
<asp:Parameter Name="original_sal" Type="Int32" />
<asp:Parameter Name="original_comm" Type="Int32" />
<asp:Parameter Name="original_deptno" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="Status" runat="server" Text="Status"></asp:Label>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1" OnPreRender="GridView1_PreRender" OnRowDataBound="GridView1_RowDataBound" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" OnRowDeleting="GridView1_RowDeleting">
<Columns>
<asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
<asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
<asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
<asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
<asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True" />
</Columns>
</asp:GridView>
</div>
</form>
</body>
Also while updating nothing gets changed.
Please help I will be very much thankful to you. I have wasted lot of time understanding how the data flows from gridview control to sqldatasource's object for updating deleting and inserting but I am getting lot of confusion.
As far as I know,OldValuesParameterFormatString applied to the names of any oldValues parameters passed to the Delete() or Update() methods.So, int the DeleteCommand,you must set empno is equal to original_empno.
At the same time,when you update the gridview, if you couldn't bind all columns to gridview,these columns will be NULL.Then it will not update successfully.
Besides,I think you could simplify your code.In UpdateParameters,you may couldn't write original_. The format string is applied to each parameter name in the oldValues collection.
More details ,you could refer to below code:
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Emp]"
UpdateCommand="UPDATE [Emp]
SET [ename] = @ename,
[job] = @job, [mgr] = @mgr,
[hiredate] = @hiredate, [sal] = @sal,
[comm] = @comm, [deptno] = @deptno
WHERE
[empno] = @original_empno AND [ename] = @original_ename
AND [job] = @original_job AND (([mgr] = @original_mgr)
OR ([mgr] IS NULL AND @original_mgr IS NULL))
AND (([hiredate] = @original_hiredate)
OR ([hiredate] IS NULL AND @original_hiredate IS NULL))
AND (([sal] = @original_sal) OR
([sal] IS NULL AND @original_sal IS NULL)) AND
(([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"
DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno">
<DeleteParameters>
<asp:Parameter Name="empno" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="empno" Type="Int32" />
<asp:Parameter Name="ename" Type="String" />
<asp:Parameter Name="job" Type="String" />
<asp:Parameter Name="mgr" Type="Int32" />
<asp:Parameter DbType="Date" Name="hiredate" />
<asp:Parameter Name="sal" Type="Int32" />
<asp:Parameter Name="comm" Type="Int32" />
<asp:Parameter Name="deptno" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
<asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
<asp:BoundField DataField="job" HeaderText="job" SortExpression="job" Visible="false" />
<asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
<asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />
<asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />
<asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
<asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />
</Columns>
</asp:GridView>
</div>
Result:
Best regards,
Yijing Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
How can I check those values just before updating the data in database?
I suggest you could use e.Command.Parameters to get the updated value in the updating method.
More details ,you could refer to below code:
ASPX:
<div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues"
ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>"
OldValuesParameterFormatString="original_{0}"
SelectCommand="SELECT * FROM [Emp]"
UpdateCommand="UPDATE [Emp]
SET [ename] = @ename,
[job] = @job, [mgr] = @mgr,
[hiredate] = @hiredate, [sal] = @sal,
[comm] = @comm, [deptno] = @deptno
WHERE
[empno] = @original_empno AND [ename] = @original_ename
AND [job] = @original_job AND (([mgr] = @original_mgr)
OR ([mgr] IS NULL AND @original_mgr IS NULL))
AND (([hiredate] = @original_hiredate)
OR ([hiredate] IS NULL AND @original_hiredate IS NULL))
AND (([sal] = @original_sal) OR
([sal] IS NULL AND @original_sal IS NULL)) AND
(([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))"
DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno" OnUpdating="SqlDataSource1_Updating">
<DeleteParameters>
<asp:Parameter Name="empno" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="empno" Type="Int32" />
<asp:Parameter Name="ename" Type="String" />
<asp:Parameter Name="job" Type="String" />
<asp:Parameter Name="mgr" Type="Int32" />
<asp:Parameter DbType="Date" Name="hiredate" />
<asp:Parameter Name="sal" Type="Int32" />
<asp:Parameter Name="comm" Type="Int32" />
<asp:Parameter Name="deptno" Type="Int32" />
</UpdateParameters>
</asp:SqlDataSource>
<asp:Label ID="Status" runat="server" Text="Status"></asp:Label>
<br />
<br />
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1" >
<Columns>
<asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" />
<asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" />
<asp:BoundField DataField="job" HeaderText="job" SortExpression="job" />
<asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" />
<asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" />
<asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" />
<asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" />
<asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" />
<asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" />
</Columns>
</asp:GridView>
</div>
Code-Behind:
protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e)
{
Status.Text = "";
DbCommand command = e.Command;
for (int i = 0; i < SqlDataSource1.UpdateParameters.Count; i++)
{
Status.Text += command.Parameters[i].Value+ "<br/>";
}
}
Result:
Best regards,
Yijing Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
2 Points
1 Post
Unable to Delete from a GridView
Dec 02, 2019 10:32 AM|Vishal Rana|LINK
I wanted to know how the parameters values are passed to SqlDataSource object through the Data web server control GridView. From the Microsoft website I got to know that the values are passed using DataKeyNames therefore the parameters name must match with that of DataKeyNames. But when I tried to print their value while updating data through the GridView control (the values was printed from the updating function method of SqlDataSource's Object). But It prints nothing for p.DefaultValue or p.ToString(); I am just getting blank in front of the parameter on the output screen.
How to retrieve those values? Or may be the values doesn't get actually passed. Please help me where I am missing.
How can I check those values just before updating the data in database?
Here is the Code.
protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e) { Status.Text = ""; ParameterCollection coll = SqlDataSource1.UpdateParameters; foreach (Parameter p in coll) { Status.Text += p.Name + ": " + p.DefaultValue + "<br/>"; } }Second Question I am unable to delete from DataGridView Control. Everytime I click on delete button a postback occurs but nothing happens.
Here is the code.
<body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:DCSADatabaseConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Emp]" UpdateCommand="UPDATE [Emp] SET [ename] = @ename, [job] = @job, [mgr] = @mgr, [hiredate] = @hiredate, [sal] = @sal, [comm] = @comm, [deptno] = @deptno WHERE [empno] = @original_empno AND [ename] = @original_ename AND [job] = @original_job AND (([mgr] = @original_mgr) OR ([mgr] IS NULL AND @original_mgr IS NULL)) AND (([hiredate] = @original_hiredate) OR ([hiredate] IS NULL AND @original_hiredate IS NULL)) AND (([sal] = @original_sal) OR ([sal] IS NULL AND @original_sal IS NULL)) AND (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))" DeleteCommand="DELETE FROM Emp WHERE (empno = @empno)" OnUpdated="SqlDataSource1_Updated" OnUpdating="SqlDataSource1_Updating"> <DeleteParameters> <asp:Parameter Name="empno" Type="Int32"/> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="ename" Type="String" /> <asp:Parameter Name="job" Type="String" /> <asp:Parameter Name="mgr" Type="Int32" /> <asp:Parameter DbType="Date" Name="hiredate" /> <asp:Parameter Name="sal" Type="Int32" /> <asp:Parameter Name="comm" Type="Int32" /> <asp:Parameter Name="deptno" Type="Int32" /> <asp:Parameter Name="original_empno" Type="Int32" /> <asp:Parameter Name="original_ename" Type="String" /> <asp:Parameter Name="original_job" Type="String" /> <asp:Parameter Name="original_mgr" Type="Int32" /> <asp:Parameter DbType="Date" Name="original_hiredate" /> <asp:Parameter Name="original_sal" Type="Int32" /> <asp:Parameter Name="original_comm" Type="Int32" /> <asp:Parameter Name="original_deptno" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <asp:Label ID="Status" runat="server" Text="Status"></asp:Label> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"DataKeyNames="empno" DataSourceID="SqlDataSource1"
OnPreRender="GridView1_PreRender" OnRowDataBound="GridView1_RowDataBound"
OnSelectedIndexChanged="GridView1_SelectedIndexChanged"
OnRowDeleting="GridView1_RowDeleting"> <Columns> <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" /> <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" /> <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" /> <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" /> <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" /> <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" /> </Columns> </asp:GridView> </div> </form> </body>
Also while updating nothing gets changed.
Please help I will be very much thankful to you. I have wasted lot of time understanding how the data flows from gridview control to sqldatasource's object for updating deleting and inserting but I am getting lot of confusion.
Contributor
3730 Points
1409 Posts
Re: Unable to Delete from a GridView
Dec 03, 2019 10:05 AM|yij sun|LINK
Hi Vishal Rana,
As far as I know,OldValuesParameterFormatString applied to the names of any oldValues parameters passed to the Delete() or Update() methods.So, int the DeleteCommand,you must set empno is equal to original_empno.
At the same time,when you update the gridview, if you couldn't bind all columns to gridview,these columns will be NULL.Then it will not update successfully.
Besides,I think you could simplify your code.In UpdateParameters,you may couldn't write original_. The format string is applied to each parameter name in the oldValues collection.
More details ,you could refer to below code:
<div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Emp]" UpdateCommand="UPDATE [Emp] SET [ename] = @ename, [job] = @job, [mgr] = @mgr, [hiredate] = @hiredate, [sal] = @sal, [comm] = @comm, [deptno] = @deptno WHERE [empno] = @original_empno AND [ename] = @original_ename AND [job] = @original_job AND (([mgr] = @original_mgr) OR ([mgr] IS NULL AND @original_mgr IS NULL)) AND (([hiredate] = @original_hiredate) OR ([hiredate] IS NULL AND @original_hiredate IS NULL)) AND (([sal] = @original_sal) OR ([sal] IS NULL AND @original_sal IS NULL)) AND (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))" DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno"> <DeleteParameters> <asp:Parameter Name="empno" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="empno" Type="Int32" /> <asp:Parameter Name="ename" Type="String" /> <asp:Parameter Name="job" Type="String" /> <asp:Parameter Name="mgr" Type="Int32" /> <asp:Parameter DbType="Date" Name="hiredate" /> <asp:Parameter Name="sal" Type="Int32" /> <asp:Parameter Name="comm" Type="Int32" /> <asp:Parameter Name="deptno" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" /> <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" /> <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" Visible="false" /> <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" /> <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" /> <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" /> <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" /> <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" /> <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" /> </Columns> </asp:GridView> </div>Result:
Best regards,
Yijing Sun
Contributor
3730 Points
1409 Posts
Re: Unable to Delete from a GridView
Dec 04, 2019 09:59 AM|yij sun|LINK
Hi Vishal Rana,
I suggest you could use e.Command.Parameters to get the updated value in the updating method.
More details ,you could refer to below code:
ASPX:
<div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:aspnet-TestApplicationWithDatabase-20190820030542ConnectionString %>" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT * FROM [Emp]" UpdateCommand="UPDATE [Emp] SET [ename] = @ename, [job] = @job, [mgr] = @mgr, [hiredate] = @hiredate, [sal] = @sal, [comm] = @comm, [deptno] = @deptno WHERE [empno] = @original_empno AND [ename] = @original_ename AND [job] = @original_job AND (([mgr] = @original_mgr) OR ([mgr] IS NULL AND @original_mgr IS NULL)) AND (([hiredate] = @original_hiredate) OR ([hiredate] IS NULL AND @original_hiredate IS NULL)) AND (([sal] = @original_sal) OR ([sal] IS NULL AND @original_sal IS NULL)) AND (([comm] = @original_comm) OR ([comm] IS NULL AND @original_comm IS NULL)) AND (([deptno] = @original_deptno) OR ([deptno] IS NULL AND @original_deptno IS NULL))" DeleteCommand="DELETE FROM [Emp] WHERE empno = @original_empno" OnUpdating="SqlDataSource1_Updating"> <DeleteParameters> <asp:Parameter Name="empno" Type="Int32" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="empno" Type="Int32" /> <asp:Parameter Name="ename" Type="String" /> <asp:Parameter Name="job" Type="String" /> <asp:Parameter Name="mgr" Type="Int32" /> <asp:Parameter DbType="Date" Name="hiredate" /> <asp:Parameter Name="sal" Type="Int32" /> <asp:Parameter Name="comm" Type="Int32" /> <asp:Parameter Name="deptno" Type="Int32" /> </UpdateParameters> </asp:SqlDataSource> <asp:Label ID="Status" runat="server" Text="Status"></asp:Label> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="empno" DataSourceID="SqlDataSource1" > <Columns> <asp:BoundField DataField="empno" HeaderText="Employee No" SortExpression="empno" /> <asp:BoundField DataField="ename" HeaderText="ename" SortExpression="ename" /> <asp:BoundField DataField="job" HeaderText="job" SortExpression="job" /> <asp:BoundField DataField="mgr" HeaderText="mgr" SortExpression="mgr" InsertVisible="False" /> <asp:BoundField DataField="hiredate" HeaderText="hiredate" SortExpression="hiredate" InsertVisible="False" /> <asp:BoundField DataField="sal" HeaderText="sal" SortExpression="sal" /> <asp:BoundField DataField="comm" HeaderText="comm" SortExpression="comm" /> <asp:BoundField DataField="deptno" HeaderText="deptno" SortExpression="deptno" /> <asp:CommandField ShowEditButton="True" ShowDeleteButton="True" ButtonType="Link" /> </Columns> </asp:GridView> </div>Code-Behind:
protected void SqlDataSource1_Updating(object sender, SqlDataSourceCommandEventArgs e) { Status.Text = ""; DbCommand command = e.Command; for (int i = 0; i < SqlDataSource1.UpdateParameters.Count; i++) { Status.Text += command.Parameters[i].Value+ "<br/>"; } }Result:
Best regards,
Yijing Sun