Go4Expert

Go4Expert (http://www.go4expert.com/)
-   ASP.NET (http://www.go4expert.com/forums/asp-net/)
-   -   insert into table problem in Oracle dataBase Through CommandText.Text (http://www.go4expert.com/forums/insert-table-oracle-database-t19939/)

shyam_oec 1Nov2009 07:27

insert into table problem in Oracle dataBase Through CommandText.Text
 
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 ...

nimesh 1Nov2009 15:56

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
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";

shyam_oec 1Nov2009 16:10

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
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

nimesh 2Nov2009 00:23

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
Try this:

replace @ with colon
Quote:

http://msdn.microsoft.com/en-us/libr...mmandtext.aspx
When using named parameters in an SQL statement called by an OracleCommand of CommandType.Text, you must precede the parameter name with a colon ( : ). However, in a stored procedure, or when referring to a named parameter elsewhere in your code (for example, when adding OracleParameter objects to the Parameters property), do not precede the named parameter with a colon ( : ). The .NET Framework Data Provider for Oracle supplies the colon automatically.
OR modifying to this:
Quote:

http://msdn.microsoft.com/en-us/libr...parameter.aspx
oracmd.Parameters.Add("@name", OleDbType.Char, 3).Value = TextBox1.Text.ToString();
OR try inserting the value in string directly, instead of giving by parameter.
String orains = "insert into T6 values('" & TextBox1.Text.ToString() & "')";

shyam_oec 2Nov2009 05:50

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
oh! thanks a lot...it is working fine.
So main problem was with oracle data provider...is'nt it..

nimesh 2Nov2009 11:58

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
what did you modify that solved your problem?

shyam_oec 2Nov2009 21:13

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
As you said, i replaced all the '@' symbols with ':' symbols and my problem is solved.

nimesh 3Nov2009 01:59

Re: insert into table problem in Oracle dataBase Through CommandText.Text
 
Good. Thanks for confirmation.


All times are GMT +5.5. The time now is 19:37.