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 ?