Solved

"Update" Problem Record to Database

Posted on 2008-10-05
9
294 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 59

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 59

Accepted Solution

by:
Kevin Cross earned 500 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
 

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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 59

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 59

Expert Comment

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

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now