Link to home
Start Free TrialLog in
Avatar of dodgerfan
dodgerfanFlag for United States of America

asked on

C# Web service insert into Oracle table

I have a web service called form an asp.net page. It originally inserted records into a SQL Server table, but that now needs to be Oracle. It works for SQL Server, but in trying to get it to work with Oracle it will not insert. The current error is missing expression error. The code I had for SQL Server is below. How would I update it to use Oracle? I found a hot to for the SQL Server insert, but I can;t find the right one for Oracle/

Web Method:
[WebMethod]
public void db_insert(string p ,int q ,string s)
{
        SqlConnection con = new SqlConnection("data source=test;Integrated Security=Yes;Database=test");
        con.Open();
        SqlCommand cmd = new SqlCommand("insert into content values(@a,@b,@c)", con);
        cmd.Parameters.AddWithValue("a", p);
        cmd.Parameters.AddWithValue("b", q);
        cmd.Parameters.AddWithValue("c", s);
        int i = cmd.ExecuteNonQuery();
 }

The button on the webpage to insert into the table:

protected void btnInsert_Click(object sender, EventArgs e)
{
        myservice1 myform = new myservice1();
        myform.db_insert((txtname.Text).ToString(), Convert.ToInt32(textage.Text), (txtloc.Text).ToString());
        Label2.Text = "Data inserted successfully";
}
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

I'm not familiar with SqlConnection and SqlCommand using Oracle.

We use ODP.Net and if you aren't, you should really look into converting.  The new managed Client doesn't need any Oracle Client install.

That said, Bind variables in Oracle use a ":".

Try this:
SqlCommand cmd = new SqlCommand("insert into content values(:a,:b,:c)", con);
Avatar of dodgerfan

ASKER

That code is the SQL version that works for SQL Server. I was hoping to find out what I need to change to make it work with Oracle. Thus far my changes have not worked. I am using ODP.Net, too.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
quick try what if replace:

cmd.Parameters.AddWithValue("a", p);
        cmd.Parameters.AddWithValue("b", q);
        cmd.Parameters.AddWithValue("c", s);

Open in new window


to:

cmd.Parameters.AddWithValue("@a", p);
        cmd.Parameters.AddWithValue("@b", q);
        cmd.Parameters.AddWithValue("@c", s);

Open in new window


?

but in general, SqlConnection will only work for MS SQL.

as mentioned by slightwv, you probably need to use an Oracle Client library instead.

Or you can try use OdbcConnection with System.Data.Odbc library instead.
>>Or you can try use OdbcConnection with System.Data.Odbc library instead.

You will lose A LOT of capability using ODBC.


Here is a canned example I wrote a long time ago (it still uses a datagrid) that inserts and displays a CLOB.  It should give you a good place to start.

The table definition is commented in the code.

<%@ import namespace = "system.data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>


<html>
<title>CLOB test</title>
<body>
<script language="VB" runat="server">


'
'drop table tab1;
'create table tab1 (
'	col1 number,
'	col2 clob
');
'


sub callProc(sender as object,e as eventargs)
		Dim con As New OracleConnection() 

		con = New OracleConnection("User Id=scott;Password=tiger;Data Source=ORCL;")

		Dim cmd as OracleCommand = new OracleCommand()
		cmd.Connection	= con
		cmd.commandText	= " insert into tab1 values(:id, :clobVal) "

		Dim param1 as OracleParameter = cmd.Parameters.Add("id", OracleDbType.varchar2, _
			50, someId.text, ParameterDirection.Input)

		Dim param2 as OracleParameter = cmd.Parameters.Add("clobVal", OracleDbType.clob)

		param2.value=someText.text

		try
			con.open()
			cmd.ExecuteNonQuery()


			cmd.commandText	= " select * from tab1 "
			genericDataGrid.DataSource = cmd.executereader()
			genericDataGrid.DataBind() 


		catch ex as exception
			ErrMsg.text = ex.tostring()

		finally
			cmd.Dispose()
			con.Close()
			con.Dispose()
		end try


end sub
</script>

<form id="myForm" runat="server">

	<asp:label id="ErrMsg" runat="server"/>
	<br/>

	ID: <asp:textbox id="someId" textmode="SingleLine" columns=5 maxlength=5 runat="server"/>
	<br/>

	Clob:<br/>
	<asp:textbox id="someText" textmode="MultiLine" width="400" rows="5" runat="server"/>
	<br/>

	<asp:Button id="SubmitButton" height="22" width="60" OnClick="callProc" runat="server" Text="Go"/>



	<asp:datagrid id="genericDataGrid" runat="server" />

</form>

</body>
</html>

Open in new window

Agree with slightwv:  You cannot use SqlCommand and SqlConnection for Oracle--those classes are specfic to SQL Server.
Thanks for the help. I am unable to work on this right now, but I will be giving it a another shot tomorrow. Thanks for the suggestions. I'll update with what I manage to get working or questions.
Thanks for the help.