dodgerfan
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.AddWithValu e("a", p);
cmd.Parameters.AddWithValu e("b", q);
cmd.Parameters.AddWithValu e("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).ToSt ring(), Convert.ToInt32(textage.Te xt), (txtloc.Text).ToString());
Label2.Text = "Data inserted successfully";
}
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.AddWithValu
cmd.Parameters.AddWithValu
cmd.Parameters.AddWithValu
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.
Label2.Text = "Data inserted successfully";
}
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
quick try what if replace:
to:
?
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.
cmd.Parameters.AddWithValue("a", p);
cmd.Parameters.AddWithValue("b", q);
cmd.Parameters.AddWithValue("c", s);
to:
cmd.Parameters.AddWithValue("@a", p);
cmd.Parameters.AddWithValue("@b", q);
cmd.Parameters.AddWithValue("@c", s);
?
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.
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>
Agree with slightwv: You cannot use SqlCommand and SqlConnection for Oracle--those classes are specfic to SQL Server.
ASKER
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.
ASKER
Thanks for the help.
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);