Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Sunday, October 12, 2014 9:37 AM
I tried working with my program, it always says that error as well as I tried to fix it but to no avail somehow I can't find the culprit of the said error also is the syntax SELECT Max(Compensation1) AS MaxCompensation from mydb.ssscorrect? If not, may I know the correct syntax for it? Any suggestion is welcome, thank you.
What I want to do here is to calculate the SSS/ government contribution to be paid by the employee and display it to a textbox called txtSSC
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;
namespace Finals
{
public partial class Payslip : Form
{
public Payslip()
{
InitializeComponent();
}
static string connection = "datasource=127.0.0.1;port=3306;username=root;password=admin";
MySqlConnection conn = new MySqlConnection(connection);
DataTable dbDataset;
private void button1_Click(object sender, EventArgs e)
{
Main mr = new Main();
mr.Show();
this.Hide();
}
private void btncal_Click(object sender, EventArgs e)
{
compute(decimal.Parse(txtTS.Text));
}
private void compute(decimal salaries)
{
decimal curMaxCompensation = new decimal();
decimal curMinCompensation = new decimal();
decimal curPrevSalary = new decimal();
decimal curPrevSSSCont = new decimal();
curPrevSalary = 0;
curPrevSSSCont = 0;
string strSQL = "";
strSQL = "SELECT ID,Salary From mydb.Employee";
MySqlDataReader rdr = (MySqlDataReader)(GetData(strSQL, conn));
//MySqlCommand command = new MySqlCommand(strSQL, conn);
//conn.Open();
//MySqlDataReader rdr=command.ExecuteReader();
try{
conn.Open();
rdr.Read();
if (rdr.HasRows)
{
conn.Open();
curPrevSalary = System.Convert.ToDecimal(rdr["Salary"]);
}
salaries = salaries + curPrevSalary;
//originals...dpat may getfieldvalue kasi un
curMaxCompensation = System.Convert.ToDecimal(GetFieldValue("SELECT Max(Compensation1) AS MaxCompensation from mydb.sss "));
curMinCompensation = System.Convert.ToDecimal(GetFieldValue("SELECT Min(Compensation1) AS MaxCompensation from mydb.sss "));
//curMaxCompensation = System.Convert.ToDecimal("SELECT Max(Compensation1) AS MaxCompensation from mydb.sss ");
// curMinCompensation = System.Convert.ToDecimal("SELECT Min(Compensation1) AS MaxCompensation from mydb.sss ");
//error nya ay ung no overloadmethod takes one arguments
if (salaries <= curMinCompensation)
{
txtSSC.Text = System.Convert.ToString(0);
}
else if (salaries > curMaxCompensation)
{
// string GetShare =System.Convert.ToString("SELECT Max(EmployeeShare1) AS MaxEEShare from mydb.sss ");
// command=new MySqlCommand(GetShare,conn);
// command.ExecuteReader();
/// rdr.Read();
// var GetShare= "Select MAX(EmployeeShare1) AS MaxEEShare from mydb.sss";
//rdr.Read();
//txtSSC.Text = (System.Convert.ToDecimal(System.Convert.ToDecimal(GetShare["MaxEEShare"]-curPrevSSSCont));
object GetShare=(GetData("Select MAX(EmployeeShare1) AS MaxEEShare from mydb.sss",conn));
}
else
{
MySqlDataReader GetShare = (MySqlDataReader)(GetData("SELECT EmployeeShare1 from mydb.sss WHERE Compensation1 <= ," + System.Convert.ToString(salaries) + " AND Compensation2 >= " + System.Convert.ToString(salaries), conn));
GetShare.Read();
txtSSC.Text = (System.Convert.ToString(System.Convert.ToInt64(GetShare["EmployeeShare1"]) - curPrevSSSCont ));
}
}
catch(Exception ex){
MessageBox.Show(ex.Message);
}
}
private object GetData(string sSQL, MySqlConnection conn)
{
MySqlCommand sqlCmd = new MySqlCommand(sSQL, conn);
MySqlDataReader myData ;
error... connection must be valid and open >>>>>myData = sqlCmd.ExecuteReader();
return myData;
}
private object GetFieldValue(string srcSQL="", string strField="")
{
object returnValue = null;
try{
//original format wla string conn at mysql conn
//original error sa part na toh ay The name 'conn' does not exist in the current context
// string connection="datasource=127.0.0.1;port=3306;username=root;password=admin";
//MySqlConnection conn =new MySqlConnection(connection);
MySqlCommand cmd = new MySqlCommand(srcSQL, conn);
//create data reader
MySqlDataReader rdr = cmd.ExecuteReader();
//loop through result set
rdr.Read();
if (rdr.HasRows)
{
returnValue = rdr[strField].ToString();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return returnValue;
}
private void Phil(decimal salaries)
{
MySqlConnection con = new MySqlConnection();
string strSQL = "SELECT ID,Salary From mydb.Employee";
MySqlCommand command = new MySqlCommand(strSQL, con);
MySqlDataReader we;
decimal MinSalaryRange1 = new decimal();
decimal MaxSalaryRange1 = new decimal();
decimal EmployeeShare = new decimal();
try
{
con.Open();
we = command.ExecuteReader();
if (we.HasRows)
{
MinSalaryRange1 = System.Convert.ToDecimal(we["PhilHealth Contribution"]);
}
MinSalaryRange1 = MaxSalaryRange1;
MySqlDataReader GetShare = (MySqlDataReader)(GetData("SELECT MAX(SELECT MAX(EmployeeShare) As MaxEmployeeShare mydb.philhealth", con));
if (salaries <= MinSalaryRange1)
{
txtPHC.Text = System.Convert.ToString(EmployeeShare);
}
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
private void EMPLMTN_Load(object sender, EventArgs e)
{
string connection = "datasource=127.0.0.1;port=3306;username=root;password=admin";
MySqlConnection conn = new MySqlConnection(connection);
MySqlCommand cmd = new MySqlCommand("SELECT ID,FirstName,LastName,MiddleName,Address,Salary FROM mydb.Employee", conn);
conn.Open();
dbDataset = new DataTable();
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
da.Fill(dbDataset);
dataGridView1.DataSource = dbDataset;
da.Update(dbDataset);
}
private void txtSea_TextChanged(object sender, EventArgs e)
{
DataView davi = new DataView(dbDataset);
davi.RowFilter = string.Format("FirstName LIKE '%{0}'", txtSea.Text);
dataGridView1.DataSource = davi;
}
private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs data)
{
if (data.RowIndex >= 0) {
DataGridViewRow rows = this.dataGridView1.Rows[data.RowIndex];
txtFN.Text = rows.Cells["FirstName"].Value.ToString();
txtLN.Text = rows.Cells["LastName"].Value.ToString();
txtMN.Text = rows.Cells["MiddleName"].Value.ToString();
txtTS.Text = rows.Cells["Salary"].Value.ToString();
}
}
private void btnPrint_Click(object sender, EventArgs e)
{
var Date = "Date: " + DateTime.Now.Month + "/" + DateTime.Now.Day + "/" +
DateTime.Now.Year;
var Time= "Time: "+ DateTime.Now.Hour + ":" + DateTime.Now.Minute+":" + DateTime.Now.Second;
File.WriteAllText(@"C:\Users\pc\Desktop\demo.doc", Date + Environment.NewLine + Time + "\r\n\r\n" + "PaySlip Summary for:" + "\r\n\r\n" + "Employee Name: " + txtFN.Text + Environment.NewLine + "Last Name: " + txtLN.Text + Environment.NewLine + "Middle Name: " + txtMN.Text + Environment.NewLine + "Total SSS Contribution: " + txtSSC.Text + Environment.NewLine + "Total PhilHealth Contribution: " + txtPHC.Text + Environment.NewLine + "Total PagIbig Contributions: " + txtPIC.Text + Environment.NewLine + "Total Witholding Tax:" + txtBIRC.Text + Environment.NewLine + "Total Salary: " + txtTS.Text + Environment.NewLine + "Total Deductions: " + txtTD.Text + Environment.NewLine + "" + Environment.NewLine + "Total Receivable: " + txtTR.Text);
}
}
}
All replies (2)
Sunday, October 12, 2014 1:54 PM ✅Answered | 1 vote
Hi
GetFieldValue requires 2 parameters srcSQL, strField and ur passing just srcSQL
strField is required for u to get ur fieldname which is i think should be MaxCompensation else use rdr[0] instead of rdr[strField]
Regards
SethHop
Sunday, October 12, 2014 5:02 PM ✅Answered | 1 vote
Your error "connection must be valid and open" should be obvious. Your GetData() method needs to open your connection first. I suggest checking to see if the connection is already open, and open it if not. Something like this:
private object GetData(string sSQL, MySqlConnection conn)
{
MySqlCommand sqlCmd = new MySqlCommand(sSQL, conn);
MySqlDataReader myData ;
if (conn.State != ConnectionState.Open)
conn.Open();
myData = sqlCmd.ExecuteReader();
return myData;
}
Remember to take the conn.Open() out of your code in the other places you've written it.
~~Bonnie DeWitt [C# MVP]