Good day Everyone..
Table:Preventive
ID Site Category Facility Activity Recurrence DuePattern Start_Record
1 Langkawi NULL FIRE ALARM Sample of Breakglass Test Monthly 26 NULL
Table: PreventiveRecord
ID Date_Done Date_Due SME Status History_ID Remark
Null Null null null null null null
History_ID same with ID
Output must be in PreventiveRecord like as per below:
ID Date_Done Date_Due SME Status History_ID Remark
Null Null 26/1/2011 null null 1 null
Null Null 27/2/2011 null null 1 null
and so on...
26th -due pattern
monthly basis..
my problem :
I am tring to get data from a sqlserver database and auto generate the next due date.. but i dont noe the correct way to auto generate the due date in the Date_Due column.. please advice.. i searched google but couldn't find any example..
:-(
i've submit by tomorrow :-(...
------------------------------------------------------------------------------
private void Button2_Click(object sender, EventArgs e)
{
try
{
SqlConn.Open();
SqlComm.Connection = SqlConn;
SqlComm.CommandText = "SELECT * FROM preventiveRecord * WHERE [History_ID =''] AND [YEAR (Date_Due)] ='2011' ORDER BY Date_Due";
SqlDataReader reader = SqlComm.ExecuteReader();
while (reader.Read())
{
string id;
string date;
string due;
string sme;
string history;
string remarks;
id = reader["ID"].ToString();
date = reader["Date_Done"] .ToString();
due = reader["Date_Due"].ToString();
sme = reader["SME"].ToString();
history = reader["History_ID"].ToString();
remarks = reader["Remarks"].ToString();
}
SqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
//Console.WriteLine(ex.ToString());
}
}
}
}
-----------------------------------------------------------------------------
Here is d full coding :
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace PreventiveUpdater
{
public partial class Form1 : Form
{
SqlConnection SqlConn = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=system;Data Source=LAPTOP-SUJITA\\SQLEXPRESS");
SqlCommand SqlComm = new SqlCommand();
public Form1()
{
InitializeComponent();
}
private void Form1_Load(object sender, EventArgs e)
{
}
private void button1_Click(object sender, EventArgs e)
{
try
{
SqlConn.Open();
SqlComm.Connection = SqlConn;
SqlComm.CommandText = "SELECT * FROM preventive";
SqlDataReader reader = SqlComm.ExecuteReader();
while (reader.Read())
{
string id;
id = reader["ID"].ToString();
}
//dataGridView1.DataSource = reader;
//dataGridView1
//SqlDataAdapter dataAdapter = new SqlDataAdapter(SqlComm);
//SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);
//BindingSource dbBindSource = new BindingSource();
//// Populate a new data table and bind it to the BindingSource.
//DataTable table = new DataTable();
//table.Locale = System.Globalization.CultureInfo.InvariantCulture;
//dataAdapter.Fill(table);
//dbBindSource.DataSource = table;
//// Resize the DataGridView columns to fit the newly loaded content.
//dataGridView1.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
//// you can make it grid readonly.
//dataGridView1.ReadOnly = true;
//// finally bind the data to the grid
//dataGridView1.DataSource = dbBindSource;
SqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
//Console.WriteLine(ex.ToString());
}
}
private void Button2_Click(object sender, EventArgs e)
{
try
{
SqlConn.Open();
SqlComm.Connection = SqlConn;
SqlComm.CommandText = "SELECT * FROM preventiveRecord * WHERE [History_ID =''] AND [YEAR (Date_Due)] ='2011' ORDER BY Date_Due";
SqlDataReader reader = SqlComm.ExecuteReader();
while (reader.Read())
{
string id;
string date;
string due;
string sme;
string history;
string remarks;
id = reader["ID"].ToString();
date = reader["Date_Done"] .ToString();
due = reader["Date_Due"].ToString();
sme = reader["SME"].ToString();
history = reader["History_ID"].ToString();
remarks = reader["Remarks"].ToString();
}
SqlConn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
//Console.WriteLine(ex.ToString());
}
}
}
}