Error ORA-00903: invalid table name when updating data source from dataset

[my_lou]

ok, i have this function that populates my dataset (In VB.NET application using Oracle database):

 

Public Function FillDataTable(ByVal argsPartialFill As Boolean, ByVal argsTableName As String, ByRef argsDataSet As DataSet)

Dim localConn As OracleConnection

Dim localOracleCommand As OracleCommand

Dim locAdapter As New OracleDataAdapter

 

localConn = New OracleConnection

localConn.ConnectionString = OracleConnectionString

 

'Open connection to an instance of the Oracle database.

Try

localConn.Open()

 

'Initialize Oracle command and set necessary parameters.

localOracleCommand = New OracleCommand

localOracleCommand.Connection = localConn

localOracleCommand.CommandText = DataTableString(argsTableName, argsDataSet)

 

 

locAdapter.SelectCommand = localOracleCommand

 

argsDataSet.Tables(argsTableName).BeginLoadData()

locAdapter.Fill(argsDataSet, argsTableName)

argsDataSet.Tables(argsTableName).EndLoadData()

 

Catch Ex As Exception

Throw Ex

Finally

'Close database connection.

localConn.Close()

End Try 'End Try

End Function

 

So far so good, the dataset is filled with data and i can browse through throug it, etc.

 

A problem occurs though when i want want to save the changes back to the data source. I use the following function to do so:

 

Public Sub UpdateDataSource(ByVal argsRowState As DataRowState, ByRef argsDataTable As DataTable, ByRef argsDataSet As DataSet)

Dim localConn As OracleConnection

Dim locCommand As OracleCommand

Dim locCmdBuilder As OracleCommandBuilder

Dim locAdapter As New OracleDataAdapter

Dim chngDataTable As DataTable

 

localConn = New OracleConnection

localConn.ConnectionString = OracleConnectionString

locCommand = New OracleCommand

 

'Open connection to an instance of the Oracle database.

Try

localConn.Open()

 

locCommand.Connection = localConn

locCommand.CommandText = DataTableString(argsDataTable.TableName, argsDataSet)

locAdapter.SelectCommand = locCommand

locCmdBuilder = New OracleCommandBuilder(locAdapter)

locCmdBuilder.QuotePrefix = "["

locCmdBuilder.QuoteSuffix = "]"

locAdapter.InsertCommand = locCmdBuilder.GetInsertCommand

locAdapter.Update(argsDataSet, argsDataTable.TableName)

 

Catch Ex As Exception

MsgBox(Ex.Message)

Throw Ex

End Try

 

argsDataSet.AcceptChanges()

 

localConn.Close()

End Sub

 

SO i get the following error on the "Catch Ex As Exception" line:

 

ORA-00903 : invalid table name

 

I am pretty sure the table name is right, but i guess i was wrong.

 

Any ideas?

Thanks.

[JABE]

I'm guessing the problem lies within locCmdBuilder.GetInsertCommand. Have you verified the value of locCmdBuilder.GetInsertCommand().CommandText?

 

Btw, instead of building commands and re-configuring the data adapter for each update call, you may want to initialize these once since these info are static. In your update call, you can simply call locAdapter.Update(argsDataSet, argsDataTable.TableName)

[my_lou]

you were right, the problem was in the CommandBuilder. to be more exact, the problem was in the following two lines:

 

locCmdBuilder.QuotePrefix = "["

locCmdBuilder.QuoteSuffix = "]"

 

basically what happened was it put the brackets ("[" and "]") around the table name and each field name, hence the table name was not recognized. so i just set those two properties to "":

 

locCmdBuilder.QuotePrefix = ""

locCmdBuilder.QuoteSuffix = ""

 

and that took care of the problem.

 

per the second part of your answer, are you suggesting global variables for the data adapter? if so, i don't see that as being more efficient than what i have. the idea behind that function that had the error in it was to be able to handle any sort of update, i.e. insert, delete, edit, to the data source from any table in the dataset, hence it was universal enough to channel all data source updates through it. And besides, not everything about the data adapter is static, the CommandText of the SelectCommand property changes for each table. Also, a global variable would require a place in memory that would not be released until the program/window was closed, while with the local variables in a function, they only exist while the fuction executes and are afterwards discarded, hence they reside in memory for much shorter amount of time, i.e. less resource intensive.

 

maybe i misunderstood what you meant though. would you care to elaborate?

[JABE]

I usually declare data adapters at the form-level; I've used 2 data adapters at most in a single form for a 2-grid master-detail form. The data adapters do take up memory while the form is alive but I think the penalty is marginal. It's just my opinion that building the data adapter and command objects each time an update is required is more memory-consuming than just keeping the data adapter(s) around while the form is alive.

[my_lou]

o, i see what you are saying now. well, i've taken a different approach altogether. my data adapters do not reside in my form, rather i've put them in a separate module where they could service any and all of my forms, hence i only need to write the function "UpdateDataSource", which you saw in my first post, once and just perform calls to it from any form for any dataset as needed. it's all about code reuse, i.e. getting the maximum amount of work done with the minimum amount of code.

 

 

I usually declare data adapters at the form-level; I've used 2 data adapters at most in a single form for a 2-grid master-detail form. The data adapters do take up memory while the form is alive but I think the penalty is marginal. It's just my opinion that building the data adapter and command objects each time an update is required is more memory-consuming than just keeping the data adapter(s) around while the form is alive.