0

I have VB.Net application connected to Oracle.

I'm creating a generic form to allow the user to type data in any table of the database.

The form should work event if the user doesn't have SELECT privilege on the table. So if he has only INSERT privilege, he should be able to use the form.

The question now is how to bind controls to the database without having SELECT privileges ?!

For this I create a strongly-typed datatable and I bind the form controls to the its columns :

Here is the code I'm using to create the datatable based on his name and an active oracle connection :

Public Function GetDataTableScheme(ByVal TableName As String, ByVal Owner As String) As DataTable
    Dim DataTable As DataTable = Nothing

    Dim DataColumn As DataColumn
    Dim ColumnName As String
    Dim DataType As String
    Dim Nullable As String

    Dim SQL As String = "select * from all_tab_columns where owner = '" & Owner & "' and TABLE_NAME = '" & TableName & "'"
    Dim OracleCommand As New OracleCommand(SQL, Me.Connection)
    Dim SQLResult As OracleDataReader

    SQLResult = OracleCommand.ExecuteReader()

    While SQLResult.Read
        If IsNothing(DataTable) Then
            DataTable = New DataTable
        End If

        ColumnName = SQLResult.Item("COLUMN_NAME")
        DataType = SQLResult.Item("DATA_TYPE")
        Nullable = LCase(SQLResult.Item("NULLABLE"))

        DataColumn = New DataColumn(ColumnName, GetDataType(DataType))

        DataColumn.AllowDBNull = If(Nullable = "y", True, False)

        DataTable.Columns.Add(DataColumn)
    End While

    If Not IsNothing(DataTable) Then
        Dim TempArray(1) As DataColumn
        TempArray(0) = DataTable.Columns("ID")
        DataTable.PrimaryKey = TempArray
    End If

    Return DataTable
End Function

I create then an OracleAdapter :

DataAdapter = New OracleDataAdapter

But then I'm unable, to create the InsertCommand :

OracleCommandBuilder = New OracleCommandBuilder(DataAdapter)
DataAdapter.InsertCommand = OracleCommandBuilder.GetInsertCommand(True)

It throws an error saying : Operation is not valid due to the current state of the object.

Does anyone have an idea ?

Thomas Carlton
  • 5,344
  • 10
  • 63
  • 126

0 Answers0