I am using C# Winforms to create a Voucher system which is Master/Detail, using MySql as backend. Create Voucher class to setup followings:
1.Create New Voucher 2. Save Voucher 3. Query Voucher
Currently having problem with Save Voucher, I've setup Insert/Update/Delete commands, to make this post short I am posting Insert commands so that general idea of application will be clear:
Class Variables
ClsMgt da = new ClsMgt();
MySqlDataAdapter sqlDataMaster = new MySqlDataAdapter();
private DataSet oDs = null;
MySqlCommand selectcommand = null;
MySqlCommand insertcommand = null;
MySqlCommand updatecommand = null;
MySqlCommand deletecommand = null;
private DataTable dt = null;
private DataTable dtDet = null;
private String sSelProcName = null;
private String sInsProcName = null;
private String sDelProcName = null;
private String sUpdProcName = null;
private int voucType;
public MySqlConnection oCn = new MySqlConnection();
MySqlTransaction oTrn = null;
Following is the NewVoucher procedure which will setup VoucherForm to open with blank record:
public DataSet NewVoucher()
{
DataSet vDs = new DataSet();
oCn = da.GetConnection();
if (oCn == null)
{
oCn.Open();
}
try
{
DataTable dt = new DataTable();
//===============================================================================
//--- Set up the Select Command
//===============================================================================
String sqlSelect = "Select vID, vTypeID, vNo, accCodeDR, accCodeCR, vDate, vChqNo, vChqDt, vPayName, vRemarks, vAmount from vMaster";
sqlDataMaster = new MySqlDataAdapter(sqlSelect, oCn);
sqlDataMaster.FillSchema(dt, SchemaType.Source);
vDs.Tables.Add(dt);
VoucherDetails vdet = new VoucherDetails();
DataTable dtDet = new DataTable();
dtDet = vdet.NewVoucherDet();
vDs.Tables.Add(dtDet);
vDs.Tables[0].Columns["vID"].AutoIncrement = true;
vDs.Tables[0].Columns["vID"].AutoIncrementSeed = -1;
vDs.Tables[0].Columns["vID"].AutoIncrementStep = -1;
vDs.Tables[1].Columns["vID"].AutoIncrement = true;
vDs.Tables[1].Columns["vID"].AutoIncrementSeed = -1;
vDs.Tables[1].Columns["vID"].AutoIncrementStep = -1;
vDs.EnforceConstraints = false;
vDs.Relations.Add("VouchersToVoucherDetails", vDs.Tables[0].Columns["vID"], vDs.Tables[1].Columns["vID"]);
}
catch (MySqlException e)
{
MessageBox.Show(e.ToString());
}
return (vDs);
}
Following is the SaveVoucher procedure:
public void SaveVoucher(DataSet oDs)
{
oCn = da.GetConnection();
oTrn = oCn.BeginTransaction();
sqlDataMaster = new MySqlDataAdapter();
try
{
if (oCn == null)
{
oCn.Open();
}
//===============================================================================
//--- Set up the INSERT Command
//===============================================================================
sInsProcName = "prInsert_Voucher";
insertcommand = new MySqlCommand(sInsProcName, oCn, oTrn);
insertcommand.CommandType = CommandType.StoredProcedure;
insertcommand.Parameters.Add(new MySqlParameter("nNewID", MySqlDbType.Int32, 0, "vID"));
insertcommand.Parameters["nNewID"].Direction = ParameterDirection.Output;
insertcommand.UpdatedRowSource = UpdateRowSource.OutputParameters;
insertcommand.Parameters.Add("svTypeID", MySqlDbType.Int32,0, "vTypeID");
insertcommand.Parameters.Add("svNo", MySqlDbType.Int32, 0, "vNo");
insertcommand.Parameters.Add("svaccCodeDR", MySqlDbType.VarChar, 12, "accCodeDR");
insertcommand.Parameters.Add("svaccCodeCR", MySqlDbType.VarChar, 12, "accCodeCR");
insertcommand.Parameters.Add("svDate", MySqlDbType.DateTime);
insertcommand.Parameters["svDate"].SourceColumn = "vDate";
insertcommand.Parameters.Add("svChqNo", MySqlDbType.Int32, 0, "vChqNo");
insertcommand.Parameters.Add("svChqDt", MySqlDbType.DateTime);
insertcommand.Parameters["svChqDt"].SourceColumn = "vChqDt";
insertcommand.Parameters.Add("svPayName", MySqlDbType.VarChar, 100, "vPayName");
insertcommand.Parameters.Add("svRemarks", MySqlDbType.VarChar, 70, "vRemarks");
insertcommand.Parameters.Add("svAmount", MySqlDbType.Double, 0, "vAmount");
sqlDataMaster.InsertCommand = insertcommand;
//===============================================================================
//--- Set up the UPDATE Command
//===============================================================================
sUpdProcName = "prUpdate_Voucher";
updatecommand = new MySqlCommand(sUpdProcName, oCn, oTrn);
updatecommand.CommandType = CommandType.StoredProcedure;
updatecommand.Parameters.Add("nNewID", MySqlDbType.Int32, 4, "vID");
updatecommand.Parameters.Add("svTypeID", MySqlDbType.Int32, 0, "CustomerID");
updatecommand.Parameters.Add("svNo", MySqlDbType.Int32, 0, "vNo");
updatecommand.Parameters.Add("svaccCodeDR", MySqlDbType.VarChar, 12, "accCodeDR");
updatecommand.Parameters.Add("svaccCodeCR", MySqlDbType.VarChar, 12, "accCodeCR");
updatecommand.Parameters.Add("svDate", MySqlDbType.DateTime);
updatecommand.Parameters["svDate"].SourceColumn = "vDate";
updatecommand.Parameters.Add("svChqNo", MySqlDbType.Int32, 0, "vChqNo");
updatecommand.Parameters.Add("svChqDt", MySqlDbType.Date);
updatecommand.Parameters["svChqDt"].SourceColumn = "vChqDt";
updatecommand.Parameters.Add("svPayName", MySqlDbType.VarChar, 100, "vPayName");
updatecommand.Parameters.Add("svRemarks", MySqlDbType.VarChar, 70, "vRemarks");
updatecommand.Parameters.Add("svAmount", MySqlDbType.Double, 0, "vAmount");
sqlDataMaster.UpdateCommand = updatecommand;
//===============================================================================
//--- Set up the DELETE Command
//===============================================================================
sDelProcName = "prDelete_Voucher";
deletecommand = new MySqlCommand(sDelProcName, oCn, oTrn);
deletecommand.CommandType = CommandType.StoredProcedure;
deletecommand.Parameters.Add("nNewID", MySqlDbType.Int32, 4, "vID");
sqlDataMaster.DeleteCommand = deletecommand;
sqlDataMaster.Update(oDs.Tables[0]);
VoucherDetails vDet = new VoucherDetails();
vDet.SaveVoucher(oTrn, oDs.Tables[1]);
oTrn.Commit();
MessageBox.Show("Records saved.", "Saving Records");
}
catch (MySqlException e)
{
//===============================================================================
//--- Rollback the transaction
//===============================================================================
oTrn.Rollback();
//MessageBox.Show(e.ToString());
MessageBox.Show(e.Message +" Error code: "+ e.Number);
oDs.Tables[0].Rows[0]["vNo"] = DBNull.Value;
}
}
Using paramterized StoredProc to save data in MySql db, for Insert records following StoredProc is used:
DELIMITER $$
USE `generalledger`$$
DROP PROCEDURE IF EXISTS `prInsert_Voucher`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `prInsert_Voucher`(
IN svTypeID INT,
IN svNo INT,
IN svaccCodeDR VARCHAR(12),
IN svaccCodeCR VARCHAR(12),
IN svDate DATETIME,
IN svChqNo INT,
IN svChqDt DATETIME,
IN svPayName VARCHAR(100),
IN svRemarks VARCHAR(70),
IN svAmount DOUBLE,
OUT nNewID INT)
BEGIN
INSERT INTO vMaster (vTypeID, vNo, accCodeDR,accCodeCR,vDate, vChqNo, vChqDt, vPayName, vRemarks, vAmount)
VALUES (svTypeID, svNo, svaccCodeDR, svaccCodeCR, svDate, svChqNo, svChqDt, svPayName, svRemarks, svAmount);
SET nNewID = LAST_INSERT_ID();
END$$
DELIMITER ;
The problem: The vChqDt is the MySql DateTime field, and I am using DateTimePicker in C# Winform for user to select date. svChqDt is the parameter I used in SaveVoucher procedure, all fields are saved properly EXCEPT vChqDt....which is only getting NULL. I've search this forum for solution, and find that using ToString to change format of date to yyyy-mm-dd will do the trick. But the problem is where should I put it??
EDIT: (Reason= Adding Form Code)
public frmVoucher()
{
InitializeComponent();
DoNewVoucher();
}
private void Form1_Load(object sender, EventArgs e)
{
txtVDt.Text = DateTime.Today.ToString();
}
public void DoNewVoucher()
{
vr.VoucherType = 1;
ds = vr.NewVoucher();
//Create New binding source for Order
bs = new BindingSource();
//Create New binding source for Order Details
bsDet = new BindingSource();
//Set Order binding source to Dataset ds
bs.DataSource = ds;
//Set Order Data Member to Dataset ds table "Orders"
bs.DataMember = "vMaster";
//Set Order Details binding source to Order's Binding Source
bsDet.DataSource = bs;
//Set Order Data Member to Dataset's Relationship for data integrity
bsDet.DataMember = "VouchersToVoucherDetails";
BindControls();
bs.AddNew();
txtVDt.Text = DateTime.Today.ToString();
// txtChqDt.Text = DateTime.Today.ToString();
}
public void BindControls()
{
txtvTypeID.Text = null;
txtVType.Text = null;
txtVNum.Text = null;
txtVDesc.Text = null;
txtVDt.Text = null;
if (vr.VoucherType == 1)
{
txtChqNo.Text = null;
txtChqDt.Text = null;
txtDrAcc.Text = null;
txtDrAccDesc.Text = null;
txtCrAcc.Text = null;
txtCrAccDesc.Text = null;
txtPayName.Text = null;
txtAmount.Text = null;
}
txtRemarks.Text = null;
txtTransID.DataBindings.Clear();
txtvTypeID.DataBindings.Clear();
txtVType.DataBindings.Clear();
txtVNum.DataBindings.Clear();
txtVDt.DataBindings.Clear();
if (vr.VoucherType == 1)
{
txtChqNo.DataBindings.Clear();
txtChqDt.DataBindings.Clear();
txtDrAcc.DataBindings.Clear();
txtCrAcc.DataBindings.Clear();
txtPayName.DataBindings.Clear();
txtAmount.DataBindings.Clear();
}
txtRemarks.DataBindings.Clear();
txtTransID.DataBindings.Add(new Binding("Text", bs, "vID"));
txtvTypeID.DataBindings.Add(new Binding("Text", bs, "vTypeID"));
txtVNum.DataBindings.Add(new Binding("Text", bs, "vNo"));
txtVDt.DataBindings.Add(new Binding("Text", bs, "vDate"));
if (vr.VoucherType == 1)
{
txtChqNo.DataBindings.Add(new Binding("Text", bs, "vChqNo"));
//string s = txtChqDt.Value.ToString("yyyy-MM-dd HH:mm");
//DateTime dt = DateTime.ParseExact(txtChqDt.Text, "yyyy-MM-dd HH:mm", CultureInfo.InvariantCulture);
txtChqDt.DataBindings.Add(new Binding("Text", bs, "vChqDt"));
txtDrAcc.DataBindings.Add(new Binding("Text", bs, "accCodeDR"));
txtCrAcc.DataBindings.Add(new Binding("Text", bs, "accCodeCR"));
txtPayName.DataBindings.Add(new Binding("Text", bs, "vPayName"));
txtAmount.DataBindings.Add(new Binding("Text", bs, "vAmount"));
}
txtRemarks.DataBindings.Add(new Binding("Text", bs, "vRemarks"));
txtVDt.Text = DateTime.Today.ToString();
if (vr.VoucherType == 1)
{
txtChqDt.Text = DateTime.Today.ToString();
}
if (txtvTypeID.Text == null || txtvTypeID.Text == string.Empty)
{
//txtVType.Text = dm.GetData("Select vType from vType Where vTypeID = '" + txtvTypeID.Text + "'").Rows[0]["vType"].ToString();
txtVType.Text = null;
}
else
{
txtVType.Text = dm.GetData("Select vType from vType Where vTypeID = '" + txtvTypeID.Text + "'").Rows[0]["vType"].ToString();
}
dgVDet.AutoGenerateColumns = false;
dgVDet.DataSource = bsDet;
dgVDet.Columns["vDetID"].DataPropertyName = "vDetID";
dgVDet.Columns["vID"].DataPropertyName = "vID";
dgVDet.Columns["accCode"].DataPropertyName = "accCode";
dgVDet.Columns["accName"].DataPropertyName = "accName";
dgVDet.Columns["accDebit"].DataPropertyName = "accDebit";
dgVDet.Columns["accCredit"].DataPropertyName = "accCredit";
dgVDet.Columns["accRemarks"].DataPropertyName = "accRemarks";
//CalculateTotals();
}
private void btnSave_Click(object sender, EventArgs e)
{
int rowCount = 0;
rowCount = dgVDet.Rows.Count - 1;
this.Validate();
bs.EndEdit();
double dCr = 0;
double dDr = 0;
Double.TryParse(txtTotDR.Text.ToString(), out dDr);
Double.TryParse(txtTotCR.Text.ToString(), out dCr);
if (!ds.HasChanges())
{
MessageBox.Show("No changes to save.", "Saving Records");
return;
}
if (dCr != dDr)
{
MessageBox.Show("Total of Debit and Credit are not equal.", "Saving Records");
return;
}
try
{
if (txtvTypeID.Text == null || txtvTypeID.Text == string.Empty)
{
return;
}
else
{
if (txtVNum.Text == null || txtVNum.Text == string.Empty)
{
int temp = int.Parse(this.txtvTypeID.Text.ToString());
ds.Tables[0].Rows[0]["vNo"] = vr.GetVoucherNum(temp);
}
}
vr.SaveVoucher(ds);
}
catch (MySqlException err)
{
// MessageBox.Show(err.Message.ToString() +" "+ err.ErrorCode );
MessageBox.Show(err.ErrorCode.ToString());
}
CellSum(5);
CellSum(6);
}
Ok guys I got it working..just need to change this:
txtChqDt.DataBindings.Add(new Binding("Text", bs, "vChqDt"));
to
txtChqDt.DataBindings.Add(new Binding("Value", bs, "vChqDt", true));
Thanks
Ahmed