1. We have moved from vBulletin to XenForo and you are viewing the site in the middle of the move. Though the functional aspect of everything is working fine, we are still working on other changes including the new design on Xenforo.
    Dismiss Notice

insert into table problem in Oracle dataBase Through CommandText.Text

Discussion in 'ASP.NET' started by shyam_oec, Nov 1, 2009.

  1. shyam_oec

    shyam_oec New Member

    Dear sir,
    i am new to ASP.NET programming ,through c#.
    In my web project,i am inserting into SqlServer Database through Command.Text method.It is working fine.
    Below i am giving the code for SqlServer database table.

    Code:
    ...
    ....
    using System.Data.SqlClient;
    
    
    public partial class _Default : System.Web.UI.Page
    {
        SqlConnection sqlcon = new SqlConnection("Data Source=.\\sqlexpress;Initial Catalog=DB2;Integrated Security=True;Pooling=False");
        protected void Page_Load(object sender, EventArgs e)
        {
            GridControl();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            String sqlins = "insert into emp_t values(@emp_name)";  
            SqlCommand sqlcmd = new SqlCommand();
            sqlcmd.Connection = sqlcon;
            sqlcmd.CommandText = sqlins;
            sqlcmd.CommandType = CommandType.Text;
            sqlcmd.Parameters.Add("@emp_name", TextBox1.Text.ToString());
            sqlcon.Open();
            sqlcmd.ExecuteNonQuery();
            sqlcon.Close();
            Response.Write("record saved");
    
            GridControl();
        }
        public void GridControl()
        {
            String sqlsel = "select *from emp_t";
            SqlDataAdapter sda = new SqlDataAdapter(sqlsel, sqlcon);
            DataTable dt = new DataTable();
            sda.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();
    
        }
    
    }
    
    this is working fine....BUT when i tried to do so in Oracle DataBase table it gave error.Exception is returned by Oracle database.Below is the cose for Oracle DataBase table

    Code:
    ...
    .....
    using System.Data.OracleClient;
    
    
    
    public partial class _Default : System.Web.UI.Page
    {
        OracleConnection oracon = new OracleConnection("Data Source=EARTH;Persist Security Info=True;User ID=rinku;Password=rinku;Unicode=True");
       
        protected void Page_Load(object sender, EventArgs e)
        {
            GridControl();
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            String orains = "insert into T6 values(@name)";
    
            OracleCommand oracmd = new OracleCommand();
            oracmd.Connection = oracon;
            oracmd.CommandText = orains;
            oracmd.CommandType = CommandType.Text;
            oracmd.Parameters.Add("@name", TextBox1.Text.ToString());
            oracon.Open();
            oracmd.ExecuteNonQuery();
            oracon.Close();
            Response.Write("Record saved");
            GridControl();
            
        }
        public void GridControl()
        {
             String orasel="select *from T6";
             OracleDataAdapter oda = new OracleDataAdapter(orasel, oracon);
             DataTable dt = new DataTable();
             oda.Fill(dt);
             GridView1.DataSource = dt;
             GridView1.DataBind();
    
        }
    
    
    }
    
    .
    Build is successful but,
    It gave the following runtime error:-
    Error at : oracmd.ExecuteNonQuery();
    OracleException was unhandled by the user code
    ORA-01036: illegal variable name/number

    plz help me ...
     
  2. nimesh

    nimesh New Member

    Use Exception Handling to trace the error.
    Find the function in which you are getting the error.

    From the code I see only 1 problem; syntax error in below line.
    String orasel="select *from T6";

    Correct code:
    String orasel="select * from T6";
     
  3. shyam_oec

    shyam_oec New Member

    main problem is in this statement:

    oracmd.ExecuteNonQuery();

    .
    Because oracle is returnong some error at run time,when i am inserting value through CommandText.Text. And there is no problem in simple insert.
    Even when i am using SQL Server Database it is working fine,but it returns error for oracle DataBase..
    Exception handling will not solve my problem..plz any one help
     
  4. nimesh

    nimesh New Member

    Try this:

    replace @ with colon
    OR modifying to this:
    OR try inserting the value in string directly, instead of giving by parameter.
    String orains = "insert into T6 values('" & TextBox1.Text.ToString() & "')";
     
    Last edited: Nov 1, 2009
  5. shyam_oec

    shyam_oec New Member

    oh! thanks a lot...it is working fine.
    So main problem was with oracle data provider...is'nt it..
     
  6. nimesh

    nimesh New Member

    what did you modify that solved your problem?
     
  7. shyam_oec

    shyam_oec New Member

    As you said, i replaced all the '@' symbols with ':' symbols and my problem is solved.
     
  8. nimesh

    nimesh New Member

    Good. Thanks for confirmation.
     

Share This Page