We help IT Professionals succeed at work.

C# Web service insert into Oracle table

dodgerfan
dodgerfan asked
on
734 Views
Last Modified: 2017-05-22
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";
}
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
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);

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Ryan ChongSoftware Tead Lead / Business Analyst / System Analyst / Data Engineer / Applications Consultant
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
>>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

CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2015

Commented:
Agree with slightwv:  You cannot use SqlCommand and SqlConnection for Oracle--those classes are specfic to SQL Server.

Author

Commented:
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.

Author

Commented:
Thanks for the help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.