Lastname

39   public DataSet GetLoad(string sortfield)
40   {
41
42    System.Data.OleDb.OleDbConnection conn = GetConnection();
43    DataSet ds = new DataSet();
44
45    try
46    {
47
48     // Dim sql As String = "SELECT ID data1 as [Firstname],data2 as [Lastname],data3 as [20],data4 as [EG],data5 as [1st-10%],data6 as [50pt],data7 as [EG1],data8 as [2nd-10%],data9 as [50pts],data10 as [EG2],data11 as [20%],data12 as [100pt],data13 as [EG3],data14 as [30%],data15 as [100],data16 as [EG4],data17 as [2nd-30%],data18 as [PrelimGrade],data19 as [Remarks]FROM Table1 order by " + sortfield
49     string sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
50
51     System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
52     try
53     {
54      da.Fill(ds, "grade1");
55     }
56     finally
57     {
58      da.Dispose();
59     }
60     return ds;
61    }
62    finally
63    {
64     conn.Close();
65     conn.Dispose();
66    }
67   }

68   public void RefreshDGV()
69   {
70
71    System.Data.OleDb.OleDbConnection conn = GetConnection();
72    string sql = default(string);
73    sql = "SELECT STUDENTID,data1 AS Firstname, data2 AS Lastname," + "data3 AS 20, data4 AS EG, data5 AS [1st-10%], data6 AS 50pt," + "data7 AS EG1, data8 AS [2nd-10%], data9 AS 50pts, data10 AS EG2," + "data11 AS [20%], data12 AS 100pt, data13 AS EG3, data14 AS [30%]," + "data15 AS 100, data16 AS EG4, data17 AS [2nd-30%], data18 AS PrelimGrade," + "data19 AS Remarks FROM Table1 ORDER BY ID";
74
75    System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter(sql, conn);
76    DataTable dt = new DataTable("grade1");
77    da.Fill(dt);
78    Form1.Default.DataGridView1.DataSource = dt;
79   }

22         private void frmListReturned_Load(object sender, EventArgs e)
23         {
24             sql = "SELECT concat(`FIRSTNAME`, `LASTNAME`) as 'FULLNAME',`STOCKRETURNNUMBER` as 'TRANSACTION#',`NAME` as 'ITEMNAME', `RETURNDATE` FROM `tblstock_return` r, `tblitems` i,`tblperson` p WHERE i.`ITEMID`=r.`ITEMID` and r.`OWNER_CUS_ID` =p.`SUPLIERCUSTOMERID` and p.TYPE not in ('Suplier')" +
25                  " AND STOCKRETURNNUMBER LIKE '%" + txtsearch.Text + "%'";
26             config.Load_DTG(sql, dtglist);
27         }

21         private void frmListStockout_Load(object sender, EventArgs e)
22         {
23             sql = "SELECT `TRANSACTIONNUMBER` as ID,concat(`FIRSTNAME`, `LASTNAME`) as 'CUSTOMER',`NAME` as 'ITEMNAME', `DESCRIPTION`, `PRICE`, `TRANSACTIONDATE` as DATE, o.`QTY`, `TOTALPRICE` FROM `tblperson` p,`tblstock_in_out` o ,`tblitems` i WHERE REMARKS='StockOut' and i.`ITEMID`=o.`ITEMID` and p.`SUPLIERCUSTOMERID`=o.`SUPLIERCUSTOMERID` and p.TYPE = 'Customer'" +
24               " AND (NAME like '%" + txtsearch.Text + "%' OR TRANSACTIONNUMBER like '%" + txtsearch.Text + "%')";
25             config.Load_DTG(sql, dtglist);
26         }

85         private void btnStockOut_Click(object sender, EventArgs e)
86         {
87             switch (cbooption.Text)
88             {
89                 case "Daily Report":
90                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
91                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
92                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND DATE(`TRANSACTIONDATE`)=CURDATE()";
93                     reports(sql, "soldList");
94                     break;
95
96                 case "Weekly Report":
97                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
98                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
99                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND WEEKDAY(`TRANSACTIONDATE`) >=0 AND WEEKDAY(`TRANSACTIONDATE`) <=4";
100                     reports(sql, "soldList");
101                     break;
102                 case "Monthly Report":
103                     sql = "SELECT `TRANSACTIONNUMBER` , concat(`FIRSTNAME`,' ', `LASTNAME`) as 'Customer', `NAME` AS 'Item', io.`QTY` , `TOTALPRICE` , `TRANSACTIONDATE` " +
104                         " FROM `tblitems` i, `tblstock_in_out` io,`tblperson` p" +
105                         " WHERE i.`ITEMID` = io.`ITEMID` AND io.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND REMARKS ='StockOut' AND MONTH(`TRANSACTIONDATE`)=MONTH(CURDATE())";
106                     reports(sql, "soldList");
107                     break;
108
109             }
110         }

190         private void txttransactionid_TextChanged(object sender, EventArgs e)
191         {
192
193             sql = "SELECT p.SUPLIERCUSTOMERID as ID, `FIRSTNAME`, `LASTNAME` ,`ADDRESS` FROM `tbltransaction` t, `tblperson` p WHERE t.`SUPLIERCUSTOMERID`=p.`SUPLIERCUSTOMERID` AND `TRANSACTIONNUMBER`='" + txttransactionid.Text + "'";
194             config.singleResult(sql);
195             if(config.dt.Rows.Count > 0)
196             {
197                 custormerid = config.dt.Rows[0].Field("ID");
198                 txtreturn_name.Text = config.dt.Rows[0].Field("FIRSTNAME").ToString() + " " + config.dt.Rows[0].Field("LASTNAME").ToString();
199                 txtreturn_address.Text = config.dt.Rows[0].Field("ADDRESS").ToString();
200
201
202
203
204                 sql = "SELECT i.`ITEMID`, `NAME`, `DESCRIPTION`, `PRICE`,`TRANSACTIONDATE`, o.`QTY`, `TOTALPRICE`,`STOCKOUTID` FROM `tblitems` i , `tblstock_in_out` o WHERE i.`ITEMID`=o.`ITEMID` AND `TRANSACTIONNUMBER`='" + txttransactionid.Text + "'";
205                 config.Load_DTG(sql,dtgCus_itemlist);
206                 dtgCus_itemlist.Columns[7].Visible = false;
207             }
208             else
209             {
210                 txtreturn_name.Clear();
211                 txtreturn_address.Clear();
212                 dtgCus_itemlist.Columns.Clear();
213             }
214
215
216         }

26         private void txt_cusid_TextChanged(object sender, EventArgs e)
27         {
28             sql = "SELECT * FROM `tblperson` WHERE `SUPLIERCUSTOMERID`='" + txt_cusid.Text + "'";
29             config.singleResult(sql);
30             if(config.dt.Rows.Count > 0)
31             {
32                 txtCus_fname.Text = config.dt.Rows[0].Field("FIRSTNAME");
33                 txtCus_lname.Text = config.dt.Rows[0].Field("LASTNAME");
34             }else
35             {
36                 txtCus_fname.Clear();
37                 txtCus_lname.Clear();
38             }
39         }