We help IT Professionals succeed at work.

"Update" Problem Record to Database

319 Views
Last Modified: 2013-12-05
Hi, I'm using VB 2005, WinForms. I'm trying to add a new record to my Database Tables and am getting 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 error happens at this line of code and the code I'm using to perform this operation is listed in the snippet below.

m_daDataAdapter.Update(m_dtItems)   <<< Error Here

The DataTable referenced here (m_dtItems) gets its data from multiple tables in the database, which are JOINED. However, all the fields that I'm adding (for my new record) are all in the same table.

Any thoughts on how I can resolve this?

Thanks,
Fulano
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

Open in new window

Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You have to construct a separate update/insert statement that only contains the target table.  Since your original datasource has multiple tables you are getting error as this will try to update against multiple tables which is not allowed.

So use the new data to construct an insert statement to database and then refresh original query that gets data with JOINs and you will now have original data plus new row you added getting you both update of tables and of page.

/kev

Author

Commented:
Hi mwvisa1,

OK, so what I did is added an "SQLString" variable, assigned a value to that variable, and then changed the DataAdapter code to reflect the use of that SQLString variable. My new code is listed in the snippet below. However, now I get a different error message, which is listed below.

>>An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll
>>Additional information: Update requires a valid UpdateCommand when passed DataRow collection with modified rows.

I don't think I my SQL Statement is structured correctly nor do I think I'm using the DataRows correctly.

Can you please edit my code in a way that you think it would work?

Thanks,
Fulano
Private Sub addNewItem()
Dim SQLString As String
SQLString = "INSERT INTO Items (Items.ItemName, Items.ItemType, Items.ItemPrice) VALUES "
'
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_daDataAdapter = New OleDb.OleDbDataAdapter(SQLString, m_DBConnection)
m_daDataAdapter.Update(m_dtItems)                         
m_DBConnection.Close()
'
End Sub

Open in new window

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi mwvisa1,

I conducted some research and found that CommandBuilder does not work with tables that are "Joined", which mine are, so it will never work for me in this scenario.

So, I edited my code to reflect your changes and came up with the code below, but I'm having a problem passing the new parameters to the SQL string. If I hard-code my values, then it works fine, however, if I try to pass any of the parameters, it gives me the error message listed below. How do I pass the parameter correctly?

Error message:
>>An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
>>Additional information: Parameter Items.ItemName has no default value.

Also, how do you "refresh" the DataAdapter since you're not using it?

Thanks for the help!
Fulano
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

Open in new window

Author

Commented:
Hi mwvisa1,

I figured out the INSERT issue. I changed the Parameter statement to this below:

cmd.Parameters.Add(New OleDb.OleDbParameter("Items.ItemName", Trim(tbxItemName.Text)))

as apposed to this...

cmd.Parameters.Add("Items.ItemName", OleDb.OleDbType.VarChar, 50, Trim(tbxItemName.Text))

This change made the difference.

The only question that I have left from your suggestion is when you suggest for me to: >> "Call code to refresh data adapter"

How would you do that since I don't see a "Refresh" method for the DataAdapter?

Thanks,
Fulano

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Glad you found solution to insert.

For the refresh, I was thinking along the lines of calling the sub that fills/selects the DataAdapter in the first place.

However, you can use the Fill() to perform the re-select of data to existing DataAdapter.  Here is a discussion on the microsoft forums that has some sample code and resolution to make it work at bottom.

http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=1691617&SiteID=1

Author

Commented:
Hi mwvisa1, thank you so much for your help. It was extremely valuable in assisting me with my question. Undoubtedly, I'll have more questions in the coming days as I address the Edit and Delete buttons on my applicator, but those will be other questions I hope you participate in.

Thanks again,
Fulano

Author

Commented:
EXCELLENT HELP!!! Thank you very much!!!
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
You are most welcome.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.