insert into oracle table

[itbnorris]

I'm new to asp.net and I'm trying to get the basics down. I want to insert a record to my oracle table.

I can connect fine and bind to datagrids etc... but when I try the following insert code, it blows up.

---------------------------
Dim conFIS As OleDbConnection
Dim cmdInsert As OleDbCommand
'connection string to Oracle FIS database
Dim strCon As String = "PROVIDER=MSDAORA; DATA SOURCE=mydb;User ID=myuid; PASSWORD=mypw" 'these are not valid btw
'SQL Insert string
Dim strInsert As String = "Insert into csc.mismgr_calls (caller, subject, comments) values (@caller, @subject, @comments)"
'create the connection to oracle
conFIS = New OleDbConnection(strCon)
cmdInsert = New OleDbCommand(strInsert, conFIS)
cmdInsert.Parameters.AddWithValue("@caller", txtCaller.Text)
cmdInsert.Parameters.AddWithValue("@subject", txtSubject.text)
cmdInsert.Parameters.AddWithValue("@comments", txtComments.Text)
conFIS.Open()
cmdInsert.ExecuteNonQuery()
conFIS.Close()

---------------------------------------------

It seems to blow up with an oracle error:

One or more errors occurred during processing of command. ORA-00936: missing expression
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: One or more errors occurred during processing of command. ORA-00936: missing expression

My server is windows 2000, and I'm developing on Visual Web Developer 2005 Express Edition beta

Any help is appreciated.

 

[itbnorris]

Okay, I suspect it is the '@' symbols screwing everything up and that is SQL server specific. Anyone know what syntax I would use to do this with oracle?

 

[cappmgr]

Try using ? for your place holders.
Dim strInsert As String = "Insert into csc.mismgr_calls (caller, subject, comments) values (?, ?, ?)"

I have never used AddWithValue but have used this
System.Data.OleDb.OleDbParameter("@caller", OleDbType.VarChar, 30)).Value = "'itbonorris'"

I'd think about stored procedures, and would wrap your open execute in a try catch
Try
conFIS.Open()
cmdInsert.ExecuteNonQuery()
Catch OleDBEx As System.Data.OleDb.OleDbException
Console.WriteLine(("ora " + OleDBEx.Message))
Catch SysEx As System.Exception
Console.WriteLine(("sys " + SysEx.Message))
Finally
conFIS.Close()
End Try

Marty

 

[cappmgr]

Meant to type,
I have never used AddWithValue but have used this
cmdInsert.Parameters.Add(new System.Data.OleDb.OleDbParameter("@caller", OleDbType.VarChar, 30)).Value = "'itbonorris'"

Marty

 

[itbnorris]

Thanks, before reading your post I just went ahead and did this:

Dim strInsert As String = "Insert into csc.mismgr_calls (caller, subject, comments) values ('" + myvar1 + "','" + myvar2 + "','" + myvar3 + "')"

Is it better to use cmdInsert.parameters.add etc?

Thanks again!

 

[itbnorris]

I'm reading into how to make a call to an Oracle stored procedure from ASP.NET, but there seems to be slim pickings on the subject.