this demonstrates what my way accomplish. thanks for no help in here i wasted my time for nothing cuz it takes forever to update 4 users 5 day attendance.
need to start over and find a better solution.
i hope this code covers someone elses problem in future...
Conclusion: different querries for every repeter row and updating them seperately is a bad idea. Creating yearly 30 column tables seems more efficient right now.
When i first tried it my database was online but website was on my localhost. After publishing the site code works faster. I may put this as an article after i finished everything. Still opening and closing 90 connections to database in one mouse click seems unefficient to me. is this the way to do really ?
protected void Repeater2_ItemDataBound(object sender, RepeaterItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item ||
e.Item.ItemType == ListItemType.AlternatingItem)
{
HiddenField hfUserid = (e.Item.FindControl("hfUser") as HiddenField);
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sql354211ConnectionString"].ConnectionString);
MySqlCommand command = new MySqlCommand("select idattendance,present from attendance where iduser=@userid and idclasslesson=@classlessonid", conn);
command.Parameters.AddWithValue("userid", hfUserid.Value.ToString());
command.Parameters.AddWithValue("classlessonid", 6);
try
{
conn.Open();
MySqlDataAdapter dat = new MySqlDataAdapter(command);
DataTable ds = new DataTable();
dat.Fill(ds);
DataView dv = new DataView(ds);
Repeater r = ((Repeater)e.Item.FindControl("repeatPresent"));
r.DataSource = dv;
r.DataBind();
}
catch { Response.Write("<h3>Something Bad Happened During database connection. Refresh the page</h3>"); }
finally { conn.Close(); }
}
}
protected void btnUpdate_Click(object sender, EventArgs e)
{
foreach (RepeaterItem masteritem in Repeater2.Items)
{
Repeater r = ((Repeater)masteritem.FindControl("repeatPresent"));
foreach (RepeaterItem item in r.Items)
{
string pr = ((TextBox)item.Controls[3]).Text;
string id = ((HiddenField)item.Controls[1]).Value;
if (item.ItemType == ListItemType.Item || item.ItemType == ListItemType.AlternatingItem)
{
MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["sql354211ConnectionString"].ConnectionString);
MySqlCommand command = new MySqlCommand("update attendance set present="+pr+" where idattendance="+id , conn);
try
{
conn.Open();
command.ExecuteNonQuery();
}
catch { }
finally { conn.Close(); }
}
}
}
}
<table>
<asp:Repeater ID="Repeater2" runat="server" OnItemDataBound="Repeater2_ItemDataBound" DataSourceID="SqlDataSource1">
<HeaderTemplate>
</HeaderTemplate>
<FooterTemplate>
</table>
</FooterTemplate>
<ItemTemplate>
<tr>
<th style="width: 150px">
<asp:Label ID="lblUser" runat="server" Text='<%#Eval("iduser").ToString()+Eval("name").ToString() %>'></asp:Label><asp:HiddenField ID="hfUser" runat="server" Value='<%#Eval("iduser") %>' />
</th>
<asp:Repeater ID="repeatPresent" runat="server">
<ItemTemplate>
<th style="width: 30px">
<asp:HiddenField ID="hfAttendid" runat="server" Value='<%#Eval("idattendance") %>' />
<asp:TextBox Width="20px" Height="20px" Font-Size="X-Small" ID="txtPresent" runat="server" Text='<%# Eval("present") %>'></asp:TextBox></th>
</ItemTemplate>
</asp:Repeater>
</tr>
</ItemTemplate>
</asp:Repeater>
<asp:LinkButton ID="btnUpdate" runat="server" class="btn btn-primary" CausesValidation="True" Text="KAYDET" OnClick="btnUpdate_Click" />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:sql354211ConnectionString %>" ProviderName="<%$ ConnectionStrings:sql354211ConnectionString.ProviderName %>" SelectCommand="SELECT DISTINCT attendance.iduser,name, idclasslesson FROM attendance inner join user on user.iduser=attendance.iduser WHERE (idclasslesson = 6)"></asp:SqlDataSource>
CREATE TABLE `attendance` (
`idattendance` int(11) NOT NULL AUTO_INCREMENT,
`iduser` int(11) DEFAULT NULL,
`idclasslesson` int(11) DEFAULT NULL,
`attenddate` date DEFAULT NULL,
`present` int(11) DEFAULT '0',
`excuse` varchar(45) COLLATE utf8_turkish_ci DEFAULT NULL,
PRIMARY KEY (`idattendance`)
) ENGINE=InnoDB AUTO_INCREMENT=50 DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci;