Devart Forums

Just wondering if you had a chance to review the sample I sent.

Sorry forgot the sample data

SET DEFINE OFF;
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 3);
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 4);
Insert into FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
Values
(1, 2, 5);
COMMIT;

Using the basic below sample you can also re-create the issue.

Dim tConnectionString As String = "USER=SCOTT; PASSWORD=TIGER; SERVER=ORADB;"
Dim objConn As New CoreLab.Oracle.OracleConnection(tConnectionString)
Dim oCommand As New CoreLab.Oracle.OracleCommand
Dim objDataTable As New CoreLab.Oracle.OracleDataTable
Dim tSQL As String = "SELECT FIELD1, FIELD2 FROM FIELD_KEY_ISSUE"
objConn.Open()

oCommand.CommandType = CommandType.Text
oCommand.CommandText = tSQL
oCommand.Connection = objConn

objDataTable = New OracleDataTable

With objDataTable
.Connection = objConn
.ReturnProviderSpecificTypes = False
.SelectCommand = oCommand
.FetchAll = True
.ReadLobMode = ReadLobMode.DefferedCachedDirect

.CachedUpdates = True
.Active = False
.Fill()

End With


CREATE TABLE FIELD_KEY_ISSUE
(
FIELD1 INTEGER,
FIELD2 INTEGER,
FIELD3 INTEGER
)
/


CREATE UNIQUE INDEX FIELD_KEY_ISSUE_PK ON FIELD_KEY_ISSUE
(FIELD1, FIELD2, FIELD3)
/


ALTER TABLE FIELD_KEY_ISSUE ADD (
CONSTRAINT FIELD_KEY_ISSUE_PK
PRIMARY KEY
(FIELD1, FIELD2, FIELD3))
/

I just sent an email to you. Please note this is for the OraDirect provider.

Hi,

Although processed on a different thread, this is only called once.
Even adding the following above the fill method doesn't help.

If Not (_objDataTable Is Nothing) Then
_objDataTable.Dispose()
_objDataTable.Clear()
End If

If I load an OracleDataset I don't seem to get this issue. Unfortunately I need the datatable object instead.

Hello,

I've got an issue when running a Select statement there the underlying table has primary key constraints.

For example if I have a primary key on Field1, Field2, and Field3 and I write a select statement that returns Field1 and Field2 it error with constrained to be unique. This due to the fact that Field1 and Field2 can have the same value when select if field3 is not included.

My issue is that I just want to select the data, it is ok for contraint not to apply. Is there anyway to turn it off?

The user can enter sql so I can not create a datatable and set all of the rows to unique = false

Is there anything I can do to turn off the constraints?

oCommand.CommandType = CommandType.Text
oCommand.CommandText = tSQL
oCommand.Connection = GProperties.DBSettings.Connection

_objDataTable = New OracleDataTable
With _objDataTable
.Connection = DBSettings.Connection
.ReturnProviderSpecificTypes = False
.SelectCommand = oCommand
.FetchAll = True
.ReadLobMode = ReadLobMode.DefferedCachedDirect

.CachedUpdates = True
.Active = False
.Fill()

End With

Thanks Alex. Is there an estimated delivery date for a fix?

Hello,

I noticed an issue that happened sometime in the last three builds and was wondering if there is a work around for this.

Using the sample dataset application provided with the install you can re-create this.

SELECT ROWID, FROM DEPT

In my application RowId is shown and of course is a read-only column. But when update is issued on the DataTable, DataAdapter, or pretty much anything it errors.

Basically I have a DataGrid that looks at tables, and I need to display the RowId if the user needs to see that detail. It used to work without issue, but the latest build doesn't allow for this.

Any ideas how to address this?

Thanks that worked. Are there plans to support Provider Specific types?

Hello,

When using the OracleDataTable object I'm running into issues using the DataTable.Select() feature. Is there anyway to implement Select filtering in this fashion? Several third party DataGrids use this method to provide a filtering and sorting method.

When I implement something similar to the following I get the below error.

Code Implemented:

Dim objTab As New OracleDataTable

Dim objCommand = New OracleCommand
objCommand .CommandText = "SELECT PRIVILEGE FROM SESSION_PRIVS"

With objTab
.Connection =MyConnection
.SelectCommand = objCommand
.ReadLobMode = ReadLobMode.DefferedCachedDirect
.FetchAll = True
.ReturnProviderSpecificTypes = True
.CachedUpdates = False
.Active = True
End With

msgbox(
objTab.Select("PRIVILEGE IN ('CREATE PUBLIC SYNONYM','CREATE ANY SYNONYM')").Length
)

Exception Generated:
cannot perform '=' operation on CoreLab.Oracle.OracleString and System.String

Hello,

Thanks for all of your help so far with this. After switching .ReturnProviderSpecificTypes = True the table loads. It does seem to have issues with the OracleDate when binding to a grid. That seems to have been an issue in the MS Provider while ago. I've included the link below.

I'm not sure if I'm using this feature correctly.
If I do the following,

_objDataTable = New OracleDataTable
With _objDataTable
.Connection = myConnection
.ReturnProviderSpecificTypes = True
.SelectCommand = MyCommand
.FetchAll = True
.CachedUpdates = True
.Active = False
.Fill()
End With

I now seem to have additional columns in my data grid such as MyColumnName_IsNull, MyColumnName_Lenght, MyColumnName_Value, etc. This also doesn't allow for me to edit the values within a grid on my form. Is there a method that I can use to get large numbers such as 649301515676727000000000000000000000 and still have an object that I can bind for update against a grid ( within a winform ) ?

It seems as though numbers are the only issue I've run into that causes this "Decimal's scale value must be between 0 and 28, inclusive.
Parameter name: scale " issue.

Any help would greatly be appreciated since I really not sure to address this.

Oracle Date Link.

http://kbalertz.com/926357/exception-oc ... table.aspx

Ran into something interesting when binding to a grid with .ReturnProviderSpecificTypes = True

It seems as though the OracleDate value is throwing an exception.

Is there anyway to fill an OracleDataTable with .ReturnProviderSpecificTypes=true without running into the OracleDate issue below? It is almost like you'd need the ability to select a data type mapping.

Below is the error I get on the bind event.

System.Reflection.TargetInvocationException: Property accessor 'Value' on object 'CoreLab.Oracle.OracleDate' threw the following exception:'Year, Month, and Day parameters describe an un-representable DateTime.' ---> System.ArgumentOutOfRangeException: Year, Month, and Day parameters describe an un-representable DateTime.
at System.DateTime.DateToTicks(Int32 year, Int32 month, Int32 day)
at CoreLab.Oracle.OracleDate.get_Value()

Thanks Alex, that did the trick.

Hello,

Found an interesting issue with the OracelDataTable and large numbers.

If you attempt to the OracleDataTable in the standard way included in the Corelab sample within the documentation and select from the below table you'll get the following message:

Decimal's scale value must be between 0 and 28, inclusive.
Parameter name: scale

I'm using the 4.50.30 build against Oracle 10gR2. Is there a work around for this type of issue, while still using the OracelDataTable since I need to allow the user to update the data through a wired grid.

CREATE TABLE B_TJOTR
(
N FLOAT(126),
V VARCHAR2(10 BYTE)
)
/



SET DEFINE OFF;
Insert into B_TJOTR
(N, V)
Values
(88.1, NULL);
Insert into B_TJOTR
(N, V)
Values
(343, NULL);
Insert into B_TJOTR
(N, V)
Values
(0.00632958471747584, 'FUN');
INSERT INTO B_TJOTR
(N,V)
VALUES
(649301515676727000000000000000000000,'BIGNUMBER');
COMMIT;

Switched to the OracleDataTable and everything seemed to work without issue.

Thanks for your help.