Very Strange Error Message in VB.NET App.

I think you have a memory overrun... Are you crossing array boundaries.. i hope you are building dynamic SQL?

wondering if you are dealing with sockets?

Hi SameerJagdale, I am not building dynamic SQL strings. I was using the DataAdapter and the CommandBuilder until I realized that I cannot use it with tables that are JOINED.  So, my only option is building my own SQL strings and commands.

What do you mean by "dealing with sockets"? If you give me an example, I can say yea or no.

Thank
Fulano

>> So, my only option is building my own SQL strings and commands.
Can you show me how you are doing this?

Hi SameerJagdale, sorry for the delay, but I wanted to get you as much information as possible. I think I'm causing this problem myself.  This is what's happening. -- I'm trying to add a record to one of my database tables.  I've been using DataAdapter and CommandBuilder throughout my application and they've been working fine. However, one table is JOINED to other tables and when I tried to add a record to that table, I got the following error message:

>> An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
>> Additional information: Dynamic SQL generation is not supported against multiple base tables.

The code that caused this error is listed below:
Private Sub addNewItem()
'
Dim drNewRow As DataRow = m_dtItems.NewRow()
m_DBConnection.Open()
m_daDataAdapter.Fill(m_dtItems)
drNewRow("ItemName") = Trim(tbxItemName.Text)
drNewRow("ItemType") = Trim(tbxItemType.Text)        
drNewRow("ItemPrice") = Trim(tbxItemPrice.Text)
m_dtItems.Rows.Add(drNewRow)
m_cbCommandBuilder = New OleDb.OleDbCommandBuilder(m_daDataAdapter)
m_daDataAdapter.Update(m_dtItems)            <<< The error happens here...
m_DBConnection.Close()
End Sub

I then posted a question here on EE and got some good help from one of the Experts. The code that he helped me compose is below:

Private Sub addNewItem()
'
Dim SQLString As String
SQLString = "INSERT INTO Items (ItemName, ItemType, ItemPrice) VALUES (Items.ItemName, Items.ItemType , Items.ItemPrice)"
'
Dim cmd As New OleDb.OleDbCommand(SQLString, m_DBConnection)
cmd.Parameters.Add("Items.ItemName", OleDb.OleDbType.VarChar, 50, Trim(tbxItemName.Text))
cmd.Parameters.Add("Items.ItemType", OleDb.OleDbType.VarChar, 50, Trim(tbxItemType.Text))
cmd.Parameters.Add("Items.ItemPrice", OleDb.OleDbType.Currancy, Trim(tbxItemPrice.Text))
m_DBConnection.Open()
cmd.ExecuteNonQuery()
m_DBConnection.Close()
End Sub

This worked well, but then the HEAP problem emerged. I think that since I'm using DataAdapeter elsewhere on this Form with this table (to read from it), I am introducing the problem when I use the second approach.

Do you think this is the reason I'm getting the HEAP error? Also, do you have any suggestions on how I can go back to using my original approach with DataAdapter and avoid the error it was causing?

Thanks,
Fulano

well, as i had mentioned earlier
>>i hope you are building dynamic SQL?
in one of my comments, this can definitely be an issue. however, you haven't mentioned if you are still getting the error!
what interests me more in your first approach is you are using commandbuilder and i cannot find the selectcommand for dataadapter..hence, when you try to update the dataadapter, it will have an emplty updatecommand (which gets generated if selectcommand is set)..and may throw an exception..my thought :-)
thanks
Sameer

Hi Sameer, the reason I do not have a SELECT command is that the DataTable (m_dtItems) is declared at the module level and is loaded in the Form's Load Event. So, by the time I use it in my addNewItem() method, it is already filled with records. I do however agree with you that the command builder will be looking for a SELECT statement. However, that's not the error I was getting. I was getting an error that stated: "Dynamic SQL generation is not supported against multiple base tables."  Hence, it didn't like the fact that I had JOINed tables.

Any thoughts...?
Fulano

Hi Sameer, I think I was able to solve the problem. I'm updating one table at a time and I created a new SELECT command for my addNewItem() method. Regardless, I appreciate your help and I did learn a couple of things along the way. Also, the error has not occurred again.

Thanks,
Fulano