Solved

"Update" Problem Record to Database

Posted on 2008-10-05
9
292 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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

12 Experts available now in Live!

Get 1:1 Help Now