Could not add to Oracle DB with dynamic SQL

[Kai]

Hi all,

I want to use dynamic SQL to add a new dataset to an existing Oracle
Table. The table is 'CONTENTTYPE' and contains only 1 field named
'CONTENTTYPE' which is Char(100).

When I create the SQL-String manually' (see example below, for
'boolDynamicSQL = False') everything works fine and the new value is
added.

But when I try to use Dynamic SQL it always returns an error:
ORA-01036: Variablenname/-nummer ungültig
(= ORA-01036: Variable name /-number invalid)

I could not find any typing error, and the manual SQL-String works
fine :-( I've searched lots of internet sites but without access. Also
my 'ASP.NET Codebook' states it should be working this way...

Anyone any ideas? Could this be caused by single / double quotes? I
did not find a setting in Visual Studio.NET to define which quotes
..NET should use for the dynamic SQL. But when I replace the single
quotes in the manual SQL string with double quotes, it returns
'ORA-00984: column not allowed here ', so not exactly the same error
message. (By the way, one time error message is english, and in other
case it's german. Might this be a hint?)

Thanks in advance!!


Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnSubmit.Click
REM First check whether Application name is entered
Dim cnnORA As OracleConnection
Dim comORA As OracleCommand

Dim DR As DataRow
Dim dtContentType As DataTable
Dim ds As DataSet
Dim sqlCommand As String
Dim sqlInsertCommand As String
Dim boolDynamicSQL As Boolean
Dim da As OracleDataAdapter

Dim boolUpdate As Boolean

'Added for checking Dynamic SQL
boolDynamicSQL = True


If txtContentType.Text = "" Then
boolUpdate = False
ASPNET_MsgBox("You have to enter a Content Type!")
Else
REM Check whether this application name already exists
cnnORA = New OracleConnection(Oracle_Connection_String)
cnnORA.Open()
comORA = New OracleCommand
comORA.Connection = cnnORA

ds = New DataSet

sqlCommand = "SELECT CONTENTTYPE FROM CONTENTTYPE"

comORA.CommandText = sqlCommand
da = New OracleDataAdapter(sqlCommand, cnnORA)

da.Fill(ds, "CONTENTTYPE")

dtContentType = ds.Tables("CONTENTTYPE")
boolUpdate = True
For Each DR In dtContentType.Rows
If txtContentType.Text.ToUpper = _
(Convert.ToString(DR.Item("CONTENTTYPE")).ToUpper)
Then
boolUpdate = False
Exit For
End If
Next
If boolUpdate Then
REM Add new data to database
If boolDynamicSQL Then
sqlInsertCommand = "INSERT INTO CONTENTTYPE (" & _
"CONTENTTYPE) VALUES (@CTYPE)"
Else
sqlInsertCommand = "INSERT INTO CONTENTTYPE (" & _
"CONTENTTYPE) VALUES ("
End If
Dim comInsert As New OracleCommand
comInsert.Connection = cnnORA
comInsert.CommandText = sqlInsertCommand


DR = dtContentType.NewRow()
DR("CONTENTTYPE") = txtContentType.Text


For Each objColumn As DataColumn In
dtContentType.Columns
If boolDynamicSQL Then
Dim param As New OracleParameter
param.OracleType = OracleType.VarChar
param.Direction = ParameterDirection.Input
param.Value = "value"
param.ParameterName = "@CONTENTTYPE"
param.DbType = DbType.AnsiString
comInsert.Parameters.Add(param)
'ORA-01036: Variablenname/-nummer ungültig
Else
sqlCommand =
Convert.ToString(DR(objColumn.ColumnName))
sqlInsertCommand = sqlInsertCommand & _
"'" & sqlCommand & "', "
'Mit Double Quotes:
'ORA-00984: column not allowed here
End If
Next
If Not boolDynamicSQL Then
sqlInsertCommand = Left$(sqlInsertCommand,
Len(sqlInsertCommand) - 2) & ")"
comInsert.CommandText = sqlInsertCommand
End If

Try
comInsert.ExecuteNonQuery()
Catch ex As Exception
LblResponse.Text = ex.Message
End Try

Else
ASPNET_MsgBox("ContentType already exists. Please
choose another name " & _
"or update existing data.")
End If
End If

cnnORA.Close()

End Sub

 

[Uwe Hafner]

Hi,

A quick guess. Didn't dive into it much:

If boolUpdate Then
REM Add new data to database
If boolDynamicSQL Then
sqlInsertCommand = "INSERT INTO CONTENTTYPE (" & _
"CONTENTTYPE) VALUES (@CTYPE)"

If boolDynamicSQL Then
Dim param As New OracleParameter
param.OracleType = OracleType.VarChar
param.Direction = ParameterDirection.Input
param.Value = "value"
param.ParameterName = "@CONTENTTYPE"

As you write above your parameter seems to be called @CTYPE and not
@CONTENTTYPE ??

hth
Uwe

 

[Kai]

Hi Uwe,

you are right. It seems I forgot to modify this back to @CONTENTTYPE
while trying several things....
Nevertheless, even after I changed it back to @CONTENTTYPE the same
error message appears :-(

P.S. Don't be confused by several testing options (like comORA and
comInsert... I just tried several things within the last 2 days but
all without success)

 

[Uwe Hafner]

Hi,

As I recall parameters are declared and used differently to SQL Server.
Example for OracleCommand from MSDN:

cmd = new OracleCommand("INSERT INTO Dept (DeptNo, DName) " +
"VALUES pDeptNo, DName)", conn);

cmd.Parameters.Add("pDeptNo", OracleType.Number, 2, "DeptNo");
cmd.Parameters.Add("pDName", OracleType.NVarChar, 14, "DName");

So i would say:
Your parameters have to be used like this:

":CONTENTTYPE"
and named without the colon (!):
parameter.ParameterName = "CONTENTTYPE"; //No colon

in contrast to
"@CONTENTTYPE"
and declaring the name including the AT:
parameter.ParameterName = "@CONTENTTYPE";

Haven't tested it though.

hth
Uwe

 

[Kai]

Hi Uwe,

thank you very much!! This really fixed the problem which costed me
several hours!!! Great, thanks again!!