Problem with update. getting ORA-01036: illegal variable name/number

[Rodrigo DeJuana]

Howdy,

Can someone tell me how im suppsoed to do this? Im new to this and im sure
im doing something stupid. Im trying to update a table. I have the sql
there, and i was able to update the table if i hardcoded everything, but i
would really like to use parameters. Here is my code.


thank you,
rodrigo
(e-mail address removed)
----------------

public void UpdateReportDetails(ReportDetailInfo reportDetailInfo)
{
OracleConnection dbConnection = null;
OracleCommand dbCommand = null;
OracleDataReader dbReader = null;


string UpdateString = "UPDATE REPORTS SET "
+ "TXT_RPT_FULL_NAME = @fullName "
+ "WHERE NM_RPT_SQR_NAME = 'ccf04o' "
+ " AND NM_RPT_SQR_VER = '00' " ;

dbConnection = new OracleConnection(connectionString);
dbCommand = new OracleCommand(UpdateString, dbConnection);


dbCommand.Parameters.Add(new OracleParameter("@fullName",
OracleType.VarChar,32));
dbCommand.Parameters["@fullName"].Value = reportDetailInfo.fullName;


try
{
dbConnection.Open();
dbCommand.Transaction = dbConnection.BeginTransaction();

OracleString rowid;
int rows = dbCommand.ExecuteOracleNonQuery(out rowid);
if (rows<=0)
{
throw new ApplicationException("Update for Template failed.");
}

dbCommand.Transaction.Commit();
dbCommand.Connection.Close();
dbConnection.Close();
}
catch (OracleException ex)
{
GetTemplateException te = new
GetTemplateException("ARC_DOCS_ERR_METADATA_ACCESSOR_DB_CONNECT", "A
metadata accessor could not establish a connection to the database.",
"UpdateFormTemplate", ex);
if (dbCommand != null)
dbCommand.Transaction.Rollback();
throw te;
}
catch (Exception ex)
{
if (dbCommand != null)
dbCommand.Transaction.Rollback();
throw ex;
}
finally
{
if (dbReader != null)
dbReader.Close();
if (dbConnection != null)
dbConnection.Close();
}
}

 

[Bjorn Abelli]

...

Can someone tell me how im suppsoed to do this?

string UpdateString = "UPDATE REPORTS SET "
+ "TXT_RPT_FULL_NAME = @fullName "
+ "WHERE NM_RPT_SQR_NAME = 'ccf04o' "
+ " AND NM_RPT_SQR_VER = '00' " ;

How to format the variablename in a commandstring for parameterization is
dependant on which provider you use.

AFAIK, SQL Server use the "@", while Oracle instead uses a ":", like:


string UpdateString = "UPDATE REPORTS SET "
+ "TXT_RPT_FULL_NAME = :fullName "
+ "WHERE NM_RPT_SQR_NAME = 'ccf04o' "
+ " AND NM_RPT_SQR_VER = '00' "

Furthermore, that symbol isn't a part of the actual name, but just a marker
for it, hence it shouldn't be used when you add the parameters.

dbCommand.Parameters.Add(
new OracleParameter("fullName",
OracleType.VarChar,32));

dbCommand.Parameters["fullName"].Value =
reportDetailInfo.fullName;

However, the behavior of parameter-settings is actually depending on which
DataProvider you're using, so I can't guarantee that these changes is
sufficient.

In some providers it's possible to use a simple question-mark as holder in
the commandstring (still depending on which provider you use):

string UpdateString = "UPDATE REPORTS SET "
+ "TXT_RPT_FULL_NAME = ? "
+ "WHERE NM_RPT_SQR_NAME = 'ccf04o' "
+ " AND NM_RPT_SQR_VER = '00' "

In this case (if it works with your dataprovider), you don't have any name
to connect the parameter to, but it's just as easy. If you have more than
one parameter, just add the parameters in the right order (which by the way
is recommended even if you're using named parameters).

OracleParameter par1 = new OracleParameter();
par1.OracleDbType = OracleType.VarChar;
par1.Size = 32;
par1.Value = reportDetailInfo.fullName;

dbCommand.Parameters.Add(par1);

However, I believe microsoft.public.dotnet.framework.adonet would be a more
appropriate group for this kind of questions, as it has more to do with
ADO.NET, than with C#.


// Bjorn A