C# Datepicker 值未保存在 MySql 数据库中

Question

我正在使用 C# Winforms 创建一个主/详细的凭证系统，使用 MySql 作为后端。创建 Voucher 类以设置以下内容:

1.创建新优惠券2. 保存优惠券3、查询凭证

目前保存凭证有问题，我已经设置了插入/更新/删除命令，为了使这篇文章简短，我发布了插入命令，以便应用程序的总体思路变得清晰:

类变量

    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;

以下是 NewVoucher 程序，它将设置 VoucherForm 以打开空白记录:

 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);
    }

以下是 SaveVoucher 过程:

 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;

        }

    }

使用参数化的StoredProc将数据保存到MySql数据库中，对于插入记录，使用以下StoredProc:

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 ;

问题:vChqDt 是 MySql DateTime 字段，我在 C# Winform 中使用 DateTimePicker 供用户选择日期。 svChqDt 是我在 SaveVoucher 过程中使用的参数，所有字段都正确保存，除了 vChqDt....它只获取 NULL。我在这个论坛中搜索了解决方案，发现使用 ToString 将日期格式更改为 yyyy-mm-dd 即可解决问题。但问题是我应该把它放在哪里？

编辑:(原因=添加表单代码)

   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);
    }

Answer 1

好的，我已经成功了......只需更改此:

txtChqDt.DataBindings.Add(new Binding("Text", bs, "vChqDt"));

至

txtChqDt.DataBindings.Add(new Binding("Value", bs, "vChqDt", true));

谢谢

艾哈迈德