I have a GridView with radiobuttonlist template column and textbox in footer. I want the radio buttons to update immediately when selected and used this tutorial to achieve the desired functionality http://www.dotnetcurry.com/ShowArticle.aspx?ID=261. It works great.
When new value in textbox of footer is changed, SQL is successfully updated via SqlDataSource Update() in the TextChanged() event, BUT the old value is returned in textbox after postback. As a check, I pass new value to a label on page and that is correctly displayed as well.
I tried putting GridView1.DataBind() in Page_Load() of if(!IsPostBack) but that causes radiobuttonlist items to not stay selected when they are changed and sqldatasource is not updated.
I don't know if this is relevent but because this app is a prototype, I am currently loading a specific record in gridview after page load when user enters the MYID in a textbox and clicks a button. Eventually the grid will load with value supplied via QueryString coming from another page.
Essentially I want the textbox to work like the radiobuttonlist...as soon as the value is changed I want the database to update and display the new value in grid/textbx after postback. Is there something obvious I am missing?
UPDDATE: added radiobuttonlist selectedindexchanged event code UPDATE 2: added sqldatasource UPDATE 3: Solution was to update SQL database directly via custom method, removed the 2nd update query in sqldatasource. TextChanged event code updated and custom method added.
HTML:
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
DataKeyNames="MYID"
DataSourceID="SqlDataSource1"
onrowdatabound="GridView1_RowDataBound"
ShowFooter="True" >
<Columns>
<asp:BoundField DataField="MYID" HeaderText="MYID" ReadOnly="True"
SortExpression="MYID" />
<asp:BoundField DataField="DocID" HeaderText="DocID" ReadOnly="True"
SortExpression="DocID" />
<asp:BoundField DataField="ItemID" HeaderText="ItemID"
InsertVisible="False" ReadOnly="True" SortExpression="ItemID" />
<asp:TemplateField HeaderText="Item" SortExpression="Item">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Item") %>'></asp:Label>
</ItemTemplate>
<FooterTemplate >
<asp:TextBox ID="txtComment1" runat="server"
Text='Section 1 Comments' AutoPostBack="True"
ontextchanged="txtComment1_TextChanged" MaxLength="1000"
TextMode="MultiLine" Width="650px"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText=" -- 1 -- 2 -- 3 -- 4 -- 5 -- " >
<ItemTemplate>
<asp:RadioButtonList AutoPostBack="True" ID="rblRating" runat="server"
Enabled="true" SelectedIndex='<%#Convert.ToInt32(DataBinder.Eval(Container.DataItem , "Rating"))%>'
OnSelectedIndexChanged="rblRating_SelectedIndexChanged" RepeatDirection="Horizontal">
<asp:ListItem Value="0">0</asp:ListItem>
<asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
<asp:ListItem Value="5">5</asp:ListItem>
</asp:RadioButtonList>
</ItemTemplate>
</asp:TemplateField>
<asp:BoundField DataField="Rating" HeaderText="Rating"
SortExpression="Rating" ReadOnly="True" />
</Columns>
</asp:GridView>
<asp:Label ID="lblComments1" runat="server" Text="Label"></asp:Label>
</div>
.CS:
protected void UpdateComment1(int myid, string comment)
{
using (SqlConnection con = new SqlConnection(conStr)))
{
string cmdStr = "UPDATE tblComments SET Comment1 = @Comment1 WHERE MYID = @MYID";
using (SqlCommand cmd = new SqlCommand(cmdStr, con)))
{
cmd.Parameters.AddWithValue("@MYID", myid);
cmd.Parameters.AddWithValue("@Comment1", comment);
try
{
con.Open();
int affectedRows = cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
Response.Write(ex.Message);
}
}
}
}
protected void txtComment1_TextChanged(object sender, EventArgs e)
{
TextBox tbox = (TextBox)sender;
string oldComment1 = ViewState["OldComment1"].ToString(); //value saved from PreRender()
string newComment1 = (GridView1.FooterRow.FindControl("txtComment1") as TextBox).Text;
ViewState["Section1Comments"] = newComment1;
if(oldComment1 != newComment1)
{
//<<TODO>>update history table
}
if (newComment1 != null)
{
//update SQL directly via custom method
UpdateComment1(Convert.ToInt32(MYID), newComment1);
}
GridView1.DataBind();
}
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
DataRowView drv = e.Row.DataItem as DataRowView;
RadioButtonList rbtnl = (RadioButtonList)e.Row.FindControl("rblRating");
if (e.Row.RowType == DataControlRowType.DataRow)
{
if ((e.Row.RowState & DataControlRowState.Normal) > 0) //.Edit, .Normal, .Alternate, .Selected
{
//check for null
if (rbtnl.SelectedItem != null)
{
if (rbtnl.SelectedItem.Text == "0") //if rating isn’t inserted into SQL yet, deselect all 5 radio buttons
{
rbtnl.SelectedItem.Selected = false;
}
rbtnl.SelectedValue = drv[4].ToString();
}
}
//remove extra list item
ListItem blank = rbtnl.Items.FindByValue("0");
if (blank != null)
{
rbtnl.Items.Remove(blank);//always remove list item at index zero
}
}
}
protected void rblRating_SelectedIndexChanged(object sender, EventArgs e)
{
string rate = string.Empty;
RadioButtonList rBtnList = (RadioButtonList)sender;
GridViewRow gvr = (GridViewRow)rBtnList.Parent.Parent;
if (rBtnList.SelectedValue != null)
{
rate = rBtnList.SelectedValue;
SqlDataSource1.UpdateParameters["Rating"].DefaultValue = rate;
SqlDataSource1.UpdateParameters["MYID"].DefaultValue = gvr.Cells[0].Text;
SqlDataSource1.UpdateParameters["ItemID"].DefaultValue = gvr.Cells[2].Text;
}
else
{
}
SqlDataSource1.Update();
GridView1.DataBind();
}
SQL & SqlDataSource:
<asp:SqlDataSource
ID="SqlDataSource1"
runat="server"
ConnectionString="<%$ ConnectionStrings:SomeConnectionString %>"
SelectCommand="SelectSection1"
UpdateCommand="UPDATE tblDetails SET Rating = @Rating WHERE MYID = @myid AND ItemID = @ItemID;
--UPDATE [tblComments] SET [Comment1] = @Comment1 WHERE MYID =@myid; "
SelectCommandType="StoredProcedure" >
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" DefaultValue="0" Name="eprid" PropertyName="Text" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Rating" Type="Int32" />
<asp:Parameter Name="myid" Type="Int32" />
<asp:Parameter Name="ItemID" Type="Int32" />
<asp:Parameter Name="Comment1" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
ALTER PROCEDURE [dbo].[SelectSection1]
@myid int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
--Has Form been submitted to tblComments yet?
declare @docid int
set @docid =(select distinct d.docid
from dbo.tblEmployee e
full outer join dbo.tblDetails d on e.MYID = d.myid
where e.myid = @myid)
IF @docid is null
----if not submitted yet, fill grid with evaluation items only, set rating to NULL
BEGIN
SELECT
@myid As MYID
,0 as DocID
,ItemID
,Item
,0 as Rating
,'' As Comment1
FROM [EPR].[dbo].[tblItems]
where SectionID = 1 and Active = 1
END
-- if submitted (DocID exists), fill grid with evaluations items and rating
ELSE
BEGIN
SELECT
d.eprid
,d.DocID
,i.[ItemID]
,i.[Item]
,d.Rating
,c.Comment1
FROM [EPR].[dbo].[tblItems] i
join tblDetails d on i.ItemID = d.ItemID
join tblComments c on d.MYID = c.MYID
--Competence Section
where i.SectionID = 1 and i.Active = 1 and d.MYID = @myid
END
END
My solution is included in my Updated question. My error seems to have been placing 2 update queries in sqldatasource. I removed one update query and created a custom method to update SQL database directly to get the textbox to update immediately on textchanged event and radiobuttonlist to update immediately when selectedindexchanged. I read that you can have 2 UPDATE queries using sqldatasource if you separate each query with semi colon, but that did not work in my case.