vendredi 31 juillet 2015

How to transfer Session text to Microsoft Access Database

I am using a program from class to make an application that takes a user's input from text boxes and drop-down lists, stores the session, and redirects the user to a verification page that shows that the info was saved or NOT saved.

I put it all together and, for the life of me, can't wrap my head around why I can't get this program to store the info into the database. Here is some of the code:

A copy of the application can be downloaded here:Program Files

// Code that saves the session information and transfers it to the frmPersonnelVerified.aspx
Session["txtInvoice"] = txtInvoice.Text;
Session["DropDownList1"] = DropDownList1.Text;
Session["DropDownList3"] = DropDownList3.Text;
Session["txtPrice"] = txtPrice.Text;
Session["txtYear"] = txtYear.Text;
Session["DropDownList4"] = DropDownList4.Text;
Session["txtModel"] = txtModel.Text;
Session["txtVin"] = txtVin.Text;
Session["txtLastName"] = txtLastName.Text;
Session["DropDownList2"] = DropDownList2.Text;
Session["DropDownList5"] = DropDownList5.Text;
Session["txtSystemNum"] = txtSystemNum.Text;
Session["DropDownList6"] = DropDownList6.Text;
Session["DropDownList8"] = DropDownList8.Text;
Session["txtPayout"] = txtPayout.Text;

Response.Redirect("frmPersonnelVerified.aspx");

From the redirect page:

// Output displayed from Invoice form
txtVerifiedInfo.Text = "ALSCO Invoice: " + (string)Session["txtInvoice"];
txtVerifiedInfo.Text += "\nType: " + (string)Session["DropDownList1"];
txtVerifiedInfo.Text += "\nVendor: " + (string)Session["DropDownList3"];
txtVerifiedInfo.Text += "\nPrice: " + (string)Session["txtPrice"];
txtVerifiedInfo.Text += "\nYear: " + (string)Session["txtYear"];
txtVerifiedInfo.Text += "\nMake: " + (string)Session["DropDownList4"];
txtVerifiedInfo.Text += "\nModel: " + (string)Session["txtModel"];
txtVerifiedInfo.Text += "\nVIN: " + (string)Session["txtVin"];
txtVerifiedInfo.Text += "\nDebtor Last Name: " + (string)Session["txtLastName"];
txtVerifiedInfo.Text += "\nPayment Status: " + (string)Session["DropDownList2"];
txtVerifiedInfo.Text += "\nSystem: " + (string)Session["DropDownList5"];
txtVerifiedInfo.Text += "\nSystem Invoice #: " + (string)Session["txtSystemNum"];
txtVerifiedInfo.Text += "\nSales Rep: " + (string)Session["DropDownList6"];
txtVerifiedInfo.Text += "\nRepo Agent: " + (string)Session["DropDownList8"];
txtVerifiedInfo.Text += "\nEmployee Pay Out: " + (string)Session["txtPayout"];

// Verifies the information from the DB and strings a save line if all is good
if (clsDataLayer.SavePersonnel(Server.MapPath("PayrollSystem_DB.mdb"),
     Session["txtInvoice"].ToString(),
     Session["DropDownList1"].ToString(),
     Session["DropDownList3"].ToString(),
     Session["txtPrice"].ToString(),
     Session["txtYear"].ToString(),
     Session["DropDownList4"].ToString(),
     Session["txtModel"].ToString(),
     Session["txtVin"].ToString(),
     Session["txtLastName"].ToString(),
     Session["DropDownList2"].ToString(),
     Session["DropDownList5"].ToString(),
     Session["txtSystemNum"].ToString(),
     Session["DropDownList6"].ToString(),
     Session["DropDownList8"].ToString(),
     Session["txtPayout"].ToString()))
{
     txtVerifiedInfo.Text = txtVerifiedInfo.Text +
         "\n\nThe information was successfully saved!";
}
else
{
     txtVerifiedInfo.Text = txtVerifiedInfo.Text +
         "\n\nThe information was NOT saved.";
}

Heres the DB code:

// This function saves the  data
    public static bool SavePersonnel(string Database, string AlscoInvoice, string Type,
                                     string Vendor, string Price, 
                                     string Year, string Make, string Model, string VIN, string Debtor, string Payment, 
                                     string System, string SysInvoice, string SalesRep, string RepoAgent, string Payout)
    {

        bool recordSaved;


        OleDbTransaction myTransaction = null;

        try
        {

            OleDbConnection conn = new OleDbConnection(clsDataLayer.GetDataConnection());
            conn.Open();
            OleDbCommand command = conn.CreateCommand();
            string strSQL;


            myTransaction = conn.BeginTransaction();
            command.Transaction = myTransaction;

            // Inserts into table
            strSQL = "Insert into tblPersonnel " +
                     "(ALSCO Invoice, Job Type) values ('" +
                     AlscoInvoice + "', '" + Type + "')";


            command.CommandType = CommandType.Text;
            command.CommandText = strSQL;


            command.ExecuteNonQuery();

            // Updates DB
            strSQL = "Update tblPersonnel " +
                     "Vendor Name=" + Vendor + ", " +
                     "Price='" + Price + "', " +
                     "Vehicle Year='" + Year + "', " +
                     "Vehicle Make='" + Make + "', " +
                     "Vehicle Model='" + Model + "', " +
                     "VIN Number='" + VIN + "', " +
                     "Debtor Last Name='" + Debtor + "', " +
                     "Payment Status='" +Payment + "', " +
                     "System='" + System + "', " +
                     "System Invoice='" + SysInvoice + "', " +
                     "Sales Rep='" + SalesRep + "', " +
                     "Repo Agent='" + RepoAgent + "', " +
                     "Employee Payout='" + Payout + "', " +

                     "Where ID=(Select Max(ID) From tblPersonnel)";


            command.CommandType = CommandType.Text;
            command.CommandText = strSQL;


            command.ExecuteNonQuery();

            myTransaction.Commit();

            conn.Close();
            recordSaved = true;
        }
        catch (Exception )
        {

            myTransaction.Rollback();
            recordSaved = false;

        }

        return recordSaved;

Aucun commentaire:

Enregistrer un commentaire