locked
Passing Parameters RRS feed

  • Question

  • User-39953368 posted

    Hi,

    first time using Oracle DB, always used SQL Server. I am able to connect to the database but I am not sure if the query I am using is correct. I found out that I had to use ':' instead of '@' for parameters but am probably missing some other stuff becuase my queries don't work. 

    Can someone give me an example or two of how to pass parameters. I am using C#. Do I need double quotes for strings? etc.

    This is a sample of what I have which does not update anything!

            using Oracle.DataAccess.Client;
            OracleConnection oConn = new OracleConnection(m_sOracleConnection);
            OracleCommand oCmd = null;
            OracleTransaction OTxn = null;
            string sSql = string.Empty;
    
            sSql = "update ORIGINATIONS SET SITE_NAME=:FacilityName, ORIGIN_GUID=:OriginGUID, ADDRESS_3=:Address, ADDRESS_5=:CityStateZip, " +
                "ADDRESS_6=:Area, ZIP_CODE=:Zip, HOST_IP_ADDRESS=:ATLASIP, " +
                "LAST_UPDATE=to_date('" + DateTime.Now + "','MM-DD-YYYY HH:MI:SS AM') where FACILITY_ID = :FacilityID";
            try
            {
                oConn.Open();
                oCmd = new OracleCommand(oSql, oConn);
                oCmd.Parameters.Clear();
                OracleParameter FacilityID = oCmd.Parameters.Add("FacilityID", OracleDbType.Varchar2);
                OracleParameter FacilityName = oCmd.Parameters.Add("FacilityName", OracleDbType.Varchar2);
                OracleParameter OriginGUID = oCmd.Parameters.Add("OriginGUID", OracleDbType.Varchar2);
                OracleParameter Address = oCmd.Parameters.Add("Address", OracleDbType.Varchar2);
                OracleParameter CityStateZip = oCmd.Parameters.Add("CityStateZip", OracleDbType.Varchar2);
                OracleParameter Zip = oCmd.Parameters.Add("Zip", OracleDbType.Varchar2);
                OracleParameter Area = oCmd.Parameters.Add("Area", OracleDbType.Varchar2);
                OracleParameter ATLASIP = oCmd.Parameters.Add("ATLASIP", OracleDbType.Varchar2);
    
                FacilityID.Value = oFacility.FacilityID;
                FacilityName.Value = oFacility.FacilityName;
                Address.Value = oFacility.Address;
                CityStateZip.Value = sCityStateZip;
                Area.Value = oFacility.Area;
                Zip.Value = oFacility.Zip;
                ATLASIP.Value = oFacility.ATLASHostIPAddress;
                OriginGUID.Value = oFacility.ARTEMIS_GUID;
    
                OTxn = oConn.BeginTransaction();
                oCmd.ExecuteNonQuery();
                OTxn.Commit();
            }


     

    Thursday, December 2, 2010 11:17 AM

All replies

  • User-2110079211 posted

    check out this for more help http://www.oracle.com/technology/sample_code/tech/windows/odpnet/odpbasic.html 

    Thursday, December 2, 2010 5:00 PM
  • User665793701 posted

    Hello NoBullMan

    Please recheck this line

        "LAST_UPDATE=to_date('" + DateTime.Now + "','MM-DD-YYYY HH:MI:SS AM') where FACILITY_ID = :FacilityID";

    instead

        "LAST_UPDATE=to_date('" + DateTime.Now + "','MM-DD-YYYY HH:MI:SS AM') where FACILITY_ID =Vlaue;

    like

    "LAST_UPDATE=to_date('" + DateTime.Now + "','MM-DD-YYYY HH:MI:SS AM') where FACILITY_ID =10;

    Friday, December 3, 2010 12:10 PM
  • User-39953368 posted

    This is what I ended up doing, so for an update I used "update blah set yada='" + yada_value + "'".

     I just appended the actual values instead of using parameters. But being new to Oracle, I just want to know how to specify parameters in a SQL query destined for an Oracle database. 

    Friday, December 3, 2010 3:41 PM
  • User665793701 posted

    In your code everything looks ok but in where clause you can’t use parameter.


    Saturday, December 4, 2010 12:42 AM
  • User-1181492241 posted

    Hi,

    Can you change the order of parameters like this and try.

        OracleParameter FacilityName = oCmd.Parameters.Add("FacilityName", OracleDbType.Varchar2);  
        OracleParameter OriginGUID = oCmd.Parameters.Add("OriginGUID", OracleDbType.Varchar2);  
        OracleParameter Address = oCmd.Parameters.Add("Address", OracleDbType.Varchar2);  
        OracleParameter CityStateZip = oCmd.Parameters.Add("CityStateZip", OracleDbType.Varchar2);  
        OracleParameter Area = oCmd.Parameters.Add("Area", OracleDbType.Varchar2);  
        OracleParameter Zip = oCmd.Parameters.Add("Zip", OracleDbType.Varchar2);  
        OracleParameter ATLASIP = oCmd.Parameters.Add("ATLASIP", OracleDbType.Varchar2);  
        OracleParameter FacilityID = oCmd.Parameters.Add("FacilityID", OracleDbType.Varchar2);  

     I just changed the order of parameters as you have used in the query.

    Thanks.

    Saturday, December 4, 2010 1:05 AM
  • User665793701 posted

    Hello Mr. N Rajesh


    Order is not a problem here.

    I have gives a sample update code. Hope it will help you.

         CultureInfo ci = new CultureInfo("en-GB");
                OracleCommand cmd = new OracleCommand("", conn);
                DataRow raw = tDs.Transaction.Rows[0];
    
                cmd.CommandText = @"UPDATE TRANSACTION SET OPERATION_NO=:OPERATION_NO,OPERATION_TYPE_ID=:OPERATION_TYPE_ID,
                                    DESCRIPTION=:DESCRIPTION,PERFORMER_ID=:PERFORMER_ID,OPERATION_DATE=:OPERATION_DATE,
                                    CREATED_BY=:CREATED_BY,CREATED_TIME=:CREATED_TIME,COMPANY_ID=:COMPANY_ID
                                    WHERE IDN=" + raw["IDN"];
    
                //prepare the parameters
                //OracleParameter IDN = new OracleParameter("IDN", OracleDbType.Int32, ParameterDirection.Input);
                OracleParameter OPERATION_NO = new OracleParameter("OPERATION_NO", OracleDbType.Varchar2, ParameterDirection.Input);
                OracleParameter OPERATION_TYPE_ID = new OracleParameter("OPERATION_TYPE_ID", OracleDbType.Decimal, ParameterDirection.Input);
                OracleParameter DESCRIPTION = new OracleParameter("DESCRIPTION", OracleDbType.Varchar2, ParameterDirection.Input);
                OracleParameter PERFORMER_ID = new OracleParameter("PERFORMER_ID", OracleDbType.Decimal, ParameterDirection.Input);
                OracleParameter OPERATION_DATE = new OracleParameter("OPERATION_DATE", OracleDbType.Date, ParameterDirection.Input);
                OracleParameter CREATED_BY = new OracleParameter("CREATED_BY", OracleDbType.Varchar2, ParameterDirection.Input);
                OracleParameter CREATED_TIME = new OracleParameter("CREATED_TIME", OracleDbType.Date, ParameterDirection.Input);
                OracleParameter COMPANY_ID = new OracleParameter("COMPANY_ID", OracleDbType.Varchar2, ParameterDirection.Input);
    
    
                if (DBNull.Value != raw["OPERATION_NO"] )
                {
                    OPERATION_NO.Value =raw["OPERATION_NO"].ToString();
                }
                if (DBNull.Value != raw["OPERATION_TYPE_ID"] && !raw["OPERATION_TYPE_ID"].Equals(""))
                {
                    OPERATION_TYPE_ID.Value = Convert.ToDecimal(raw["OPERATION_TYPE_ID"]);
                }
                if (DBNull.Value != raw["DESCRIPTION"] && !raw["DESCRIPTION"].Equals(""))
                {
                    DESCRIPTION.Value = raw["DESCRIPTION"].ToString();
                }
                if (DBNull.Value != raw["PERFORMER_ID"] && !raw["PERFORMER_ID"].Equals(""))
                {
                    PERFORMER_ID.Value =Convert.ToDecimal(raw["PERFORMER_ID"]);
                }
                if (DBNull.Value != raw["OPERATION_DATE"] && !raw["OPERATION_DATE"].Equals(""))
                {
                    OPERATION_DATE.Value = Convert.ToDateTime(raw["OPERATION_DATE"].ToString(), ci);
                }
                if (DBNull.Value != raw["CREATED_BY"])
                {
                    CREATED_BY.Value = raw["CREATED_BY"].ToString();
                }
                if (DBNull.Value != raw["CREATED_TIME"] && !raw["CREATED_TIME"].Equals(""))
                {
                    CREATED_TIME.Value = Convert.ToDateTime(raw["CREATED_TIME"].ToString(), ci);
                }
                if (DBNull.Value != raw["COMPANY_ID"] && !raw["COMPANY_ID"].Equals(""))
                {
                    COMPANY_ID.Value =Convert.ToDecimal(raw["COMPANY_ID"]);
                }
    
                cmd.Parameters.Add(OPERATION_NO);
                cmd.Parameters.Add(OPERATION_TYPE_ID);
                cmd.Parameters.Add(DESCRIPTION);
                cmd.Parameters.Add(PERFORMER_ID);
                cmd.Parameters.Add(OPERATION_DATE);
                cmd.Parameters.Add(CREATED_BY);
                cmd.Parameters.Add(CREATED_TIME);
                cmd.Parameters.Add(COMPANY_ID);
    
                cmd.ExecuteNonQuery();
                cmd.Dispose();



    Regards,

    Saturday, December 4, 2010 2:03 AM
  • User1577467205 posted

     You have to include the colon in the paramter name when you add the parameter as well as in the SQL statement.

     

    So,

    OracleParameter FacilityID = oCmd.Parameters.Add("FacilityID", OracleDbType.Varchar2);

    Should Be

    OracleParameter FacilityID = oCmd.Parameters.Add(":FacilityID", OracleDbType.Varchar2);

    Thursday, December 9, 2010 11:11 AM