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 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 & "'" 'The connection is opened somewhere else and used here as Me.Connection... 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"))
'GetDataType is a function that returns a type based on the column datatype name in the database 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
This function works properly and returns an empty DataTable object, replicating the table in the database.
I create then an OracleAdapter :
DataAdapter = New OracleDataAdapter
But after that, I'm unable to create the InsertCommand :
OracleCommandBuilder = New OracleCommandBuilder(DataAdapter) DataAdapter.InsertCommand = OracleCommandBuilder.GetInsertCommand(True)
The last line throws an error saying : Operation is not valid due to the current state of the object.
Does anyone have an idea on how to create a DataAdapter without passing by a SELECT query ? (The user may not have select privilege)
- Edited by Thomas Carlton Monday, January 13, 2014 4:28 AM
The command builder takes a Select SQL and automatically creates a Insert, Update, and Delete SQL commands. The database could have Insert priviledge and not Updates and Delete priviledges. But I don't think this is causing the problem. I suspect the Orcacle Client handled an exception that wasn't returned to you code and the client is in an invalid state. You may need call a new constructor for the client. I would check the Control Panel : Adminsitrative Tools : Event Viewer to see if there were any error messages created when you ran the application.