Trying to update an ORACLE 11G DB with C# Asp.net using Oracle Managed Driver.

178 views Asked by At

I am trying to write the update code to Update Oracle in VS12 using the Oracle Managed driver.

I am using Oracle 11G XE with ODT for VS addon and VS2012.

I have tried many different things and I am at a loss. please help.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;
using Oracle.ManagedDataAccess.Client;


public partial class frmUserInfo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {



        //Get the User ID for the current user. 
        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        OracleCommand userCommand = new OracleCommand("Select cust_id from CIS_CUSTOMER_TABLE where cust_username ='" + Session["username"].ToString() + "'", conn);
        conn.Open();    
        OracleDataReader userReader = userCommand.ExecuteReader();
        string User_ID;
        userReader.Read();
        User_ID = (userReader["cust_id"].ToString());
        conn.Close();

        //populate the Shipping information secction
        OracleCommand custCommand = new OracleCommand("Select * from CIS_CUSTOMER_TABLE where cust_username ='" + Session["username"].ToString() + "'", conn);
        conn.Open();
        OracleDataReader custReader = custCommand.ExecuteReader();
        while (custReader.Read())
        {
            tbShipFName.Text = (custReader["cust_fname"].ToString());
            tbShipLName.Text = (custReader["cust_lname"].ToString());
            tbShipAddress.Text = (custReader["cust_street"].ToString());
            tbShipCity.Text = (custReader["cust_city"].ToString());
            ddShipState.Text = (custReader["cust_st"].ToString());
            tbShipZipCode.Text = (custReader["cust_Zip"].ToString());
            tbShipPhone.Text = (custReader["cust_phone"].ToString());
            tbShipEmail.Text = (custReader["cust_email"].ToString());
            lblUsername.Text = (custReader["cust_username"].ToString());
        }
        conn.Close();

        //Popuate the Billing information section
        OracleCommand billCommand = new OracleCommand("Select * from CIS_BILLING_TABLE where cust_id =" +User_ID+"", conn);
        conn.Open();
        OracleDataReader billReader = billCommand.ExecuteReader();
        while (billReader.Read())
        {
            tbBillFName.Text = (billReader["cust_fname"].ToString());
            tbBillLName.Text = (billReader["cust_lname"].ToString());
            tbBillAddress.Text = (billReader["cust_street"].ToString());
            tbBillCity.Text = (billReader["cust_city"].ToString());
            ddBillState.Text = (billReader["cust_st"].ToString());
            tbBillZipCode.Text = (billReader["cust_Zip"].ToString());
            tbBillCreditCard.Text = (billReader["cc_info"].ToString());
            tbBillExperation.Text = (billReader["cc_exp"].ToString());
        }
        //Close connection for billing.
        conn.Close();

        }


    protected void btnUpdateUserInfo_Click(object sender, EventArgs e)
    {


        OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        OracleCommand updateShip = new OracleCommand("UPDATE cis_customer_table SET cust_fname = :ShipFName , cust_lname= :ShipFName, cust_street= :ShipAddress, cust_city= :ShipCity, cust_st= :ShipState, cust_zip= :ShipZipCode, cust_phone= :ShipPhone, cust_email= :ShipEmail WHERE cust_username = :Username;", conn);

        updateShip.Parameters.Add("ShipFName", OracleDbType.Varchar2).Value=tbShipFName.Text;
        updateShip.Parameters.Add("ShipLName", OracleDbType.Varchar2).Value = tbShipLName.Text;
        updateShip.Parameters.Add("ShipAddress", OracleDbType.Varchar2).Value = tbShipAddress.Text;
        updateShip.Parameters.Add("ShipCity", OracleDbType.Varchar2).Value = tbShipCity.Text;
        updateShip.Parameters.Add("ShipState", OracleDbType.Char).Value = ddShipState.Text;
        updateShip.Parameters.Add("ShipZipCode", OracleDbType.Double).Value = tbShipZipCode.Text;
        updateShip.Parameters.Add("ShipPhone", OracleDbType.Varchar2).Value = tbShipPhone.Text;
        updateShip.Parameters.Add("ShipEmail", OracleDbType.Varchar2).Value = tbShipEmail.Text;
        updateShip.Parameters.Add("Username", OracleDbType.Varchar2).Value = lblUsername.Text;

        conn.Open();
        updateShip.ExecuteNonQuery();
        conn.Close();

    }
}
1

There are 1 answers

5
user2151099 On

You call conn.Open(); after OracleCommand userCommand = new OracleCommand("Select...'", conn); Try open connection then make command.

And what error you get? It look good.

    //Get the User ID for the current user. 
    OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);


    //conn open before next using
    conn.Open();    



    OracleCommand userCommand = new OracleCommand("Select cust_id from CIS_CUSTOMER_TABLE where cust_username ='" + Session["username"].ToString() + "'", conn);
    OracleDataReader userReader = userCommand.ExecuteReader();
    string User_ID;