Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

"Update" Problem Record to Database

Posted on 2008-10-05
9
Medium Priority
?
303 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

0
Comment
Question by:Mr_Fulano
  • 5
  • 4
9 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22645960
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
0
 

Author Comment

by:Mr_Fulano
ID: 22646287
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

0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 22646369
What I meant was something like below, althought thinking about it more you may be able to do this through data adapter which lets you specify separate SELECT, INSERT, and UPDATE commands based on what is goind on in your dataset and you could then just call Update() as you were previously.

See this from Microsoft on the InsertCommand of DbDataAdapter which is what you will need here:
http://msdn.microsoft.com/en-us/library/system.data.common.dbdataadapter.insertcommand.aspx
Private Sub addNewItem()
Dim SQLString As String
SQLString = "INSERT INTO Items (ItemName, ItemType, ItemPrice)"
SQLString = SQLString & " VALUES (?, ?, ?)"
 
'Open your connection
m_DBConnection.Open()
Dim cmd As New OleDb.OleDbCommand(SQLString, m_DBConnection)
cmd.Parameters.Add("ItemName", OleDbType.VarChar, 255, Trim(tbxItemName.Text))
cmd.Parameters.Add("ItemType", OleDbType.VarChar, 255, Trim(tbxItemType.Text))
cmd.Parameters.Add("ItemPrice", OleDbType.Currency, Trim(tbxItemPrice.Text))
cmd.ExecuteNonQuery()
m_DBConnection.Close()
'Call code to refresh data adapter
End Sub

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Mr_Fulano
ID: 22654886
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

0
 

Author Comment

by:Mr_Fulano
ID: 22656213
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

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22656276
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
0
 

Author Comment

by:Mr_Fulano
ID: 22656472
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
0
 

Author Closing Comment

by:Mr_Fulano
ID: 31503236
EXCELLENT HELP!!! Thank you very much!!!
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22656497
You are most welcome.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question