Just started in both C# and .net so please go gentle
I have a bit field in a db and need to substitue for the 0 or 1 value
Basically if it's 0 I need to output 15 min and if it's 1 I need to out put 30 min
I have no idea where to begin and the tutorials were just confusing me more.
Thanks in advance for your help.
if(myVal == 0)
myVal = 15;
Zath
Alright I guess this is my fault for not explaning better what I was looking for
I think I am on the right track here as digging around I found some more out but again I hit a road block
I should store the result of the query in a datatable and then parse through the table row by row
How do I get the total value of the number of returned rows.
Basically a php convert here so this is what I am looking to do in php
While ($row = mysql_fetch_array($result)) {
// code stuffs to do here each and every time like
if ($row[4] == 0) {
$row[4] = 15 min;
} else {
$row[4] = 30 min;
}
Simply for display purposes though
How to approach this depends on the context in which you will be using this. Are you going to use a list control like a DataGrid, DataList or a Repeater? What does your Sql Statement look like, and how do you store the returned result?
If you are using a DataReader, e.g. an SqlDataReader, and the Bit field is located at index '0' in the Sql, the code could look like this (not tested):
string minutes = null;
con.Open();
rdr = cmd.ExecuteReader();
while(rdr.Read())
{
if((bool)rdr["MyBitField"])
minutes = "30 min";
else
minutes = "15 min";
}
rdr.Close();
con.Close();
Okay then how would I then make the minutes variable appear where the normal rdr["MyBitField"] would have?
Read my message. I cannot answer this until you explain where and how you want to display these values.
Sorry about that I thought I was understanding something that I obviously wasn't sorry![Embarrassed [:$]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_v65KanMuaAGT72SeqCqoxBF7Yb-qLb0Nha6ygZPO-NDuD2McHRjpk-TUx_7narlYdZvw0EUo8IKZCym9RDoIig1WcyG3LoE3cJDeF1zrYEnCeDmstAtw=s0-d)
This is what I have so far and I would like to display in a datagrid
string break_type =null;
SqlConnection mySqlConnection =newSqlConnection("server = '**********'; database = '*******'; uid = '*********'; pwd = '*********'");
SqlDataAdapter myCommand =newSqlDataAdapter("SELECT break_time, type FROM schedule", mySqlConnection);
DataTable break_table =newDataTable();
DataRow break_row;
break_table.Columns.Add(newDataColumn("IntegerValue",typeof(Int32)));
break_table.Columns.Add(newDataColumn("StringValue",typeof(string)));
break_table.Columns.Add(newDataColumn("BooleanValue",typeof(bool)));
for (int i = 1; i <= 4; i++) {
break_row = break_table.NewRow();
break_row[0] = break_row[0];
break_row[1] = break_row[1];
if (break_row[2] == 0) {
break_type ="15 min";
}else {
break_type = ToString() +"30 min";
}
break_table.Rows.Add(break_row);
}
ScheduleGrid.DataSource =newDataView(break_table);
ScheduleGrid.DataBind();
Try this:
void BindGrid()
{
string conn = "server=someserver;database=somedb;uid=sa;pwd=secret";
string sql = "SELECT break_time, type FROM schedule";
SqlConnection con = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr;
con.Open();
rdr = cmd.ExecuteReader();
ScheduleGrid.DataSource = rdr;
ScheduleGrid.DataBind();
rdr.Close();
con.Close();
}
To check the Bit value and display the correct Min value, use the ItemDataBound Event of the DataGrid:
void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
ListItemType item = e.Item.ItemType;
switch(item)
{
case ListItemType.AlternatingItem :
case ListItemType.Item :
// Use a TemplateColumn in your DataGrid to hold the Minute value.
// Add a Label Control (ID = lblMin) to yr TemplateColumn
Label lbl = (Label)e.Item.FindControl("lblMin");
string min = "15 minutes";
if((bool)(DataBinder.Eval(e.Item, "DataItem.break_time")))
min = "30 minutes";
lbl.Text = min;
break;
}
}
Thanks for the help
I feel more confused now then before as I thought I was close but looking at your solution it doesn't even resemble what I had and since I truly don't understand what your code is doing or how it is doing it I will keep on looking.
I am not trying to discount your help at all just rather unless I can understand something I don't like it to be in the applications I am working on and I can't understand you above code.
To help further, I have actually made a working demo solution for you. You need to change the SQL details. I would recommend you get yourself a book on how to develop Asp.Net Applications. "Asp.Net Unleashed by Stephen Walther (SAMS)" is excellent, and will get you up standard fast.
Code follows:
<%@. Page Language="C#" Trace="False" %>
<%@. Import NameSpace="System" %>
<%@. Import NameSpace="System.Web" %>
<%@. Import NameSpace="System.Web.UI" %>
<%@. Import NameSpace="System.Web.UI.WebControls" %>
<%@. Import NameSpace="System.Data" %>
<%@. Import NameSpace="System.Data.SqlClient" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<script runat="server">
void Page_Load(object s, EventArgs e)
{
if(!IsPostBack)
{
BindGrid();
}
}
void BindGrid()
{
string conn = "server=(local);trusted_connection=true;database=breakdemo";
string sql = "SELECT break_time, break_type FROM breaktbl";
SqlConnection con = new SqlConnection(conn);
SqlCommand cmd = new SqlCommand(sql, con);
cmd.CommandType = CommandType.Text;
SqlDataReader rdr;
con.Open();
rdr = cmd.ExecuteReader();
ScheduleGrid.DataSource = rdr;
ScheduleGrid.DataBind();
rdr.Close();
con.Close();
}
//To check the Bit value and display the correct Min value, use the ItemDataBound Event of the DataGrid:
void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
ListItemType item = e.Item.ItemType;
switch(item)
{
case ListItemType.AlternatingItem :
case ListItemType.Item :
// Use a TemplateColumn in your DataGrid to hold the Minute value.
// Add a Label Control (ID = lblMin) to yr TemplateColumn
Label lbl = (Label)e.Item.FindControl("lblMin");
string min = "15 minutes";
if((bool)(DataBinder.Eval(e.Item, "DataItem.break_time")))
min = "30 minutes";
lbl.Text = min;
break;
}
}
</script>
<html>
<head>
<title>Untitled</title>
</head>
<body>
<form runat="server" ID="form1">
<asp:DataGrid Id="ScheduleGrid"
RunAt="server"
HeaderStyle-BackColor="#808080"
HeaderStyle-ForeColor="#FFFFFF"
ItemStyle-BackColor="#FFFFFF"
AlternatingItemStyle-BackColor="#E1E3E8"
PagerStyle-Visible="true"
OnItemDataBound="DataGrid1_ItemDataBound"
AutoGenerateColumns="False">
<columns>
<asp:boundcolumn datafield="Break_Type" headertext="Reason for Break" readonly="true"></asp:boundcolumn>
<asp:templatecolumn headertext="Break time">
<itemstyle horizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:label id="lblMin" runat="server"></asp:label>
</itemtemplate>
</asp:templatecolumn>
</columns>
</asp:DataGrid>
</form>
</body>
</html>
Thank you very much - it was actually the output portion that I was struggling with.
could you elaborate a bit more on this statement?
case ListItemType.AlternatingItem :
I changed the code according (sql statements)
and generated a Specified Cast not valid error on this line
if((bool)(DataBinder.Eval(e.Item, "DataItem.break_time")))
Thank you so much for your help through this - I don't know why I am just struggling mightely here
Every other line (DataGridItem) displaying a record is an "AlternatingItem". Practical, as you can e.g. use different bg colors on Item/Alternating ListItemTypes to make your grid more readable.
As for the "Cast not valid" error, I'm puzzled. If "break_time" is an SQL Bit datatype as you say, this should work. I have reproduced your scenario, so I know that my code works.
Thank you very much
I needed to bool check to go from type, not break_time didn't even double check that sorry
Once again thank you very much for your help and seeing this through for me. I fele you went above and beyond for me.
I am sure I will be back with other issues and if I am helped out half as mucha gain in the future I have no fear.
alright so I have taken your code and added things that I need to add however it seems like an awful long wat to go for what I need and maybe it is just me doing things the long way or this is just going to be the norm but can someone take a look and let me know if I am on the right track as far as how to do this
<%@.PageLanguage="C#"Trace="False"Debug="true"%>
<%@.ImportNameSpace="System" %>
<%@.ImportNameSpace="System.Web" %>
<%@.ImportNameSpace="System.Web.UI" %>
<%@.ImportNameSpace="System.Web.UI.WebControls" %>
<%@.ImportNameSpace="System.Data" %>
<%@.ImportNameSpace="System.Data.SqlClient" %>
<!DOCTYPEHTMLPUBLIC"-//W3C//DTD HTML 4.01 Transitional//EN">
<scriptrunat="server">
void Page_Load(object s,EventArgs e)
{
// To be able to use template like variables in the page
Page.DataBind();
if(!IsPostBack)
{
BindGrid();
}
}
// To store the value in a template like variable
string PageTitle{
get {
return"Break Schedule";
}
}
void BindGrid()
{
string conn ="server=******;database=raintranet;uid=sa;pwd=*****";
string sql ="SELECT s.break_time, s.type, b.day_id as break_day, u.first_name, u.last_name FROM schedule as s, break_data as b LEFT JOIN users as u ON b.users_id = u.users_id WHERE b.schedule_id = s.schedule_id ORDER BY break_day, s.break_time";
SqlConnection con =newSqlConnection(conn);
SqlCommand cmd =newSqlCommand(sql, con);
cmd.CommandType =CommandType.Text;
SqlDataReader rdr;
con.Open();
rdr = cmd.ExecuteReader();
ScheduleGrid.DataSource = rdr;
ScheduleGrid.DataBind();
rdr.Close();
con.Close();
}
//To check the Bit value and display the correct Min value, use the ItemDataBound Event of the DataGrid:
void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
ListItemType item = e.Item.ItemType;
switch(item)
{
caseListItemType.AlternatingItem :
caseListItemType.Item :
// Use a TemplateColumn in your DataGrid to hold the Minute value.
// Add a Label Control (ID = lblMin) to yr TemplateColumn
Label lbl = (Label)e.Item.FindControl("lblMin");
string min ="15 min";
if ((bool)(DataBinder.Eval(e.Item,"DataItem.type")))
{
min ="30 min";
}
lbl.Text = min;
// Do the same for the day
Label daylbl = (Label)e.Item.FindControl("lblDay");
string day ="Sunday";
string dayVal;
dayVal =DataBinder.Eval(e.Item,"DataItem.break_day").ToString();
if (String.Compare(dayVal,"2") == 0)
{
day ="Monday";
}
elseif (String.Compare(dayVal,"3") == 0)
{
day ="Tuesday";
}
elseif (String.Compare(dayVal,"4") == 0)
{
day ="Wednesday";
}
elseif (String.Compare(dayVal,"5") == 0)
{
day ="Thursday";
}
elseif (String.Compare(dayVal,"6") == 0)
{
day ="Friday";
}
elseif (String.Compare(dayVal,"7") == 0)
{
day ="Saturday";
}
//------ Make the name string valid ------
Label name_lbl = (Label)e.Item.FindControl("lblName");
string Full_name;
Full_name =DataBinder.Eval(e.Item,"DataItem.first_name").ToString();
Full_name = Full_name +" " +DataBinder.Eval(e.Item,"DataItem.last_name").ToString();
name_lbl.Text = Full_name;
daylbl.Text = day;
break;
}
}
</script>
<html>
<head>
<title><%# PageTitle %></title>
</head>
<body>
<formrunat="server"ID="form1">
<asp:DataGridId="ScheduleGrid"
RunAt="server"
HeaderStyle-BackColor="#808080"
HeaderStyle-ForeColor="#FFFFFF"
ItemStyle-BackColor="#FFFFFF"
AlternatingItemStyle-BackColor="#E1E3E8"
PagerStyle-Visible="true"
OnItemDataBound="DataGrid1_ItemDataBound"
AutoGenerateColumns="False"
cellpadding="2">
<columns>
<asp:boundcolumndatafield="break_time"headertext="Time"readonly="true"></asp:boundcolumn>
<asp:templatecolumnheadertext="Type">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblMin"runat="server"></asp:label>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumnheadertext="Day">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblDay"runat="server"></asp:label>
</itemtemplate>
</asp:templatecolumn>
<asp:templatecolumnheadertext="Day">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblName"runat="server"></asp:label>
</itemtemplate>
</asp:templatecolumn>
</columns>
</asp:DataGrid>
</form>
</body>
</html>
That will output a table with the following data
Break | Type | Day | Who
6:00 15 min Sat Rick
6:15 30 min Sun Tara
So on and so forth
First off - the code looks good ( though all those {} and ; always look funny to me
![Smile [:)]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sNhcAz3YH9-VDU04ZQoCwAARvda1rgY9D0-MksyrQEbTYwNF3uMBJ_o9hPv69A0ULa4WFkKTZnUDRDv3Iq9HQj3MetEwUBfRbSD6m4SqlMafjO_so1-g=s0-d)
I would just like to add that there are other ( maybe easier ) ways todo this. As is the case with programming - there are many ways toperform the same action.
I would do something like this for the full name field :
<itemtemplate>
<asp:labelid="lblName"runat="server" text='<%#DataBinder.Eval(Container.DataItem, "First_Name") + " " +DataBinder.Eval(Container.DataItem, "Last_Name")%>' />
</itemtemplate>
For the Type
<asp:templatecolumnheadertext="Type">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblMin"runat="server" text='<%# IIF(DataBinder.Eval(Container.DataItem, "type"),"30 min","15 min") %> ' />
</itemtemplate>
</asp:templatecolumn>
then for the day I would either use a helper function like this :
<asp:templatecolumnheadertext="Day">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblDay"runat="server"text='<%# GetDay(DataBinder.Eval(Container.DataItem, "break_day"))%>' />
</itemtemplate>
then add a function like
Function GetDay(MyDay) as string
Select Case MyDay
case 1
return "Sun"
Case 2
return "Mon"
.....
End Function
Sorry not C# - i think it's switch
Then you don't need the OnItemDataBound event
If one of the columns is the actual date (instead of a number representing a day) then you could do something like
<asp:templatecolumnheadertext="Day">
<itemstylehorizontalalign="Center"></itemstyle>
<itemtemplate>
<asp:labelid="lblDay"runat="server"text='<%#DataBinder.Eval(Container.DataItem, "ActualDateField")).DayOfWeek %>' />
</itemtemplate>
HTH
PS - just realized that C# doesn't have IIF - maybe there is an equivalent - maybe "?"
cond-expr ? expr1 : expr2
<asp:labelid="lblMin"runat="server" text='<%# (DataBinder.Eval(Container.DataItem, "type")==1) ? "30 min":"15 min"; %> ' />
learn_asp_fast: