I am kind of at my wits end chasing this thing around. I would really appreciate any help. Thank you in advance.
This is the code-behind the web form:
using System; using System.Collections; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; namespace cipher_pos_asp.Pages { public partial class ViewPunches : System.Web.UI.Page { private static SQL_Connections sql = new SQL_Connections(); private const string CurrentDay = " Select P.Punch As Punch, (E.FirstName + ' ' + E.LastName) As EmployeeName, PT.Description As PunchType " + " From Punches P " + " Join Employee E On P.EmployeeID = E.EmployeeID " + " Join PunchType PT On P.PunchTypeID = PT.PunchTypeID " + " Where (Punch > DATEADD(d, -1, GetDate())) And " + " (Punch < DATEADD(d, 1, GetDate())) "; private DataTable dtViewPunches = new DataTable(); protected void Page_Load(object sender, EventArgs e) { sql.Clear(); sql.Add(CurrentDay); sql.Open(); var initialColumns = new List<string> {"Punch", "EmployeeName", "PunchType"}; grdViewPunches.DataSource = LoadData(initialColumns); grdViewPunches.DataBind(); /* dsViewPunches.ConnectionString = sql.ConnectionString; dsViewPunches.SelectCommand = CurrentDay; dsViewPunches.Select(DataSourceSelectArguments.Empty); grdViewPunches.DataBind(); */ } private ICollection LoadData (IEnumerable<string> columns) { foreach (var column in columns) { dtViewPunches.Columns.Add(column); } do { foreach (var column in columns) { DataRow dataRow = dtViewPunches.NewRow(); try { dataRow[column] = sql.reader[column]; } catch (Exception ex) { Console.WriteLine(ex); } } } while (sql.reader.Read()); /* dataRow = dtViewPunches.NewRow(); dataRow[0] = sql.reader["Punch"]; dataRow[1] = sql.reader["PunchType"]; dataRow[2] = sql.reader["EmployeeName"]; while (sql.reader.Read()) { dataRow = dtViewPunches.NewRow(); dataRow[0] = sql.reader["Punch"]; dataRow[1] = sql.reader["PunchType"]; dataRow[2] = sql.reader["EmployeeName"]; } */ var dv = new DataView(dtViewPunches); return dv; } } }
This is the relevent parts of my data class:
class SQL_Connections { private SqlConnection connection; private SqlCommand command; private static SqlDataReader dataReader; private SqlCommandBuilder commandBuilder; private const string TCConnectionString = "Data Source = (local); Initial Catalog = Timeclock; User ID = sa; Password = Sasa001"; private string RecordValue; public string ConnectionString { get { return TCConnectionString; } } public string CurrentValue { get { return RecordValue != String.Empty ? RecordValue : String.Empty; } } public SqlDataReader reader { get { return dataReader; } } public SQL_Connections() { connection = new SqlConnection(TCConnectionString); command = new SqlCommand(); } public void Open() { if (command == null) return; if (command.CommandText == String.Empty) return; if (command.Connection == null) command.Connection = connection; try { connection.Close(); connection.Open(); } catch (Exception ex) { Console.WriteLine(ex); } dataReader = command.ExecuteReader(); if (dataReader.Read()) RecordValue = dataReader.GetValue(0).ToString(); } public void Clear() { if (command == null) return; command.CommandText = String.Empty; } public void Close() { if (connection == null) return; if (connection.State != ConnectionState.Closed) { Clear(); connection.Close(); } } public void Execute() { if (command == null) return; if (command.CommandText == String.Empty) return; try { connection = new SqlConnection { ConnectionString = TCConnectionString }; connection.Open(); if (command.Connection == null) command.Connection = connection; } catch (Exception ex) { Console.WriteLine(ex); } command.ExecuteNonQuery(); } public void Add(string sqlString) { if (command == null) { command = new SqlCommand (); } command.CommandText = command.CommandText + sqlString; }