SQL Query not running due to invalid character

[partha.majumdar]

Dear Sir,

I created a GridView and associated a SQLQuery with the same build using the provided wizard. However, when I specify a condition in the WHERE condition as TF_DELETED = 'N', it given the error ORA-00911: Invalid query.

The query built is as follows:

SELECT "TF_TEAM_CODE", "TF_TEAM_NAME" FROM "TT_TEAM" WHERE ("TF_DELETED" = ?) ORDER BY "TF_TEAM_CODE"

The value for the parameter is N.



Also, the update and delete features do not work as it gives the same error.

Please help.

Regards,
Partha

 

[jmcilhinney]

I'm not very experienced with Oracle but I'm fairly sure that you don't use double quotes to delimit identifiers. Why do you need to delimit the identifiers at all? None of those names could possibly be keywords and none of them contain spaces or the like.

Also, I'm not aware of any circumstance where ? is used for parameters in Oracle. The Jet and ACE providers for Access et al use ? and MySQL uses it to. For Oracle I have only ever seen named parameters with a colon prefix:

SELECT TF_TEAM_CODE, TF_TEAM_NAME FROM TT_TEAM WHERE (TF_DELETED = :TF_DELETED) ORDER BY TF_TEAM_CODE

 

[partha.majumdar]

Dear Sir,

The query is generated as it is by the Visual Studio IDE.

Anyway, I changed the query as suggested to be as follows:

DeleteCommand="UPDATE TT_TEAM SET TF_DELETED = :TF_DELETED WHERE (TF_TEAM_CODE = riginal_TF_TEAM_CODE)"
OldValuesParameterFormatString="original_{0}"
ProviderName="<%$ ConnectionStrings:QADBConnectionString.ProviderName %>"
SelectCommand="SELECT TF_TEAM_CODE, TF_TEAM_NAME FROM TT_TEAM ORDER BY TF_TEAM_CODE"
<DeleteParameters>
<asparameter Name="TF_DELETED" DbType="String" DefaultValue="N" />
<asparameter Name="original_TF_TEAM_CODE" Type="String" DefaultValue="" />
</DeleteParameters>




However, now I get the error ORA-01036: illegal variable name/number


Please suggest.

Regards,
Partha

 

[ghazanfar381]

Why are you using inverted commas several times in your query?

which database r u using?

 

[partha.majumdar]

Dear Sir,

The statements with the inverter commas is generated by .Net. Anyway if I use them without the same, still the result is the same.

DeleteCommand="UPDATE TT_TEAM SET TF_DELETED = :TF_DELETED WHERE (TF_TEAM_CODE = riginal_TF_TEAM_CODE)"
OldValuesParameterFormatString="original_{0}"
ProviderName="<%$ ConnectionStrings:QADBConnectionString.ProviderNam e %>"
SelectCommand="SELECT TF_TEAM_CODE, TF_TEAM_NAME FROM TT_TEAM ORDER BY TF_TEAM_CODE"
<DeleteParameters>



I am using Oracle 11g.

Regards,
Partha