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()
If IsNothing(DataTable) Then
DataTable = New DataTable
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)
If Not IsNothing(DataTable) Then
Dim TempArray(1) As DataColumn
TempArray(0) = DataTable.Columns("ID")
DataTable.PrimaryKey = TempArray
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)
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.