"Update" Problem Record to Database

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

Mr_FulanoAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Mr_FulanoAuthor 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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mr_FulanoAuthor 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

0
 
Mr_FulanoAuthor 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

0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Mr_FulanoAuthor 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
0
 
Mr_FulanoAuthor Commented:
EXCELLENT HELP!!! Thank you very much!!!
0
 
Kevin CrossChief Technology OfficerCommented:
You are most welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.