Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

vb.net Update database using dataAdapter failing, "Dynamic SQL generation UpdateCommand is not supported..."

Posted on 2004-09-20
6
Medium Priority
?
8,853 Views
Last Modified: 2012-08-14
Hi experts,

I have a datagrid that displays the contents of a database table via a dataAdapter.  I am under the impression that ADO.NET in disconnected mode allows dynamically changing the datagrid contents and writing it back to the original database via the dataAdapter....  However, I keep getting an error: "Dynamic SQL generation UpdateCommand is not supported against a SelectCommand that does not return key column information".

Could someone please take a look at my code and let me know where I went astray?  The initial datagrid view works fine.  Clicking the "SaveChanges" button illicits the correct message box as well, which means the tables and temporary tables are being accessed properly as well....

"dgCustomers" is the DataGrid on the Windows Form.

Windows Form variables:

   Dim daDataTransfer As New OleDbDataAdapter
   Dim dtCustomerTemp As New DataTable

OnLoad():

  Try
            Dim cmdCommand As New OleDbCommand
            Dim conConnect As New OleDbConnection

            conConnect.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source = " & System.AppDomain.CurrentDomain.BaseDirectory & "\dbOrders.mdb"

            conConnect.Open()

            cmdCommand.Connection = conConnect
            cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,FROM CustomerInfo;"

            daDataTransfer.SelectCommand = cmdCommand
            daDataTransfer.Fill(dtCustomerTemp)

            dtCustomerTemp.DefaultView.AllowNew = False
            Me.dgCustomers.DataSource = dtCustomerTemp
            Me.btnSave.Visible = True

        Catch ex As Exception
            MsgBox("Unable to create DataGrid view.  ERROR: " & ex.ToString, MsgBoxStyle.Information)
        End Try


On Clicking the SaveChanges button():

Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click

        Dim dtChanged As DataTable
        Dim comBuild As OleDbCommandBuilder
        Dim drRows As Integer

        'check to see if there were any changes made
        dtChanged = dtCustomerTemp.GetChanges()

        If Not IsNothing(dtChanged) Then
            Try
                comBuild = New OleDbCommandBuilder(daDataTransfer)
                drRows = daDataTransfer.Update(dtChanged)
                MsgBox("Updates successful.", MsgBoxStyle.Information)
            Catch ex As Exception
                MsgBox("Update ERROR:  " & ex.Message, MsgBoxStyle.Exclamation)
            End Try
        Else
            MsgBox("There was nothing to update.", MsgBoxStyle.Information)
        End If

    End Sub

Thanks!
0
Comment
Question by:Larry_T
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 15

Expert Comment

by:praneetha
ID: 12105949
cmdCommand.CommandText = "SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,FROM CustomerInfo;"


change the select command to contain the primary key ...liek customer Id.

good luck
0
 
LVL 28

Expert Comment

by:mmarinov
ID: 12105995
Hi Larry_T,
are you sure that this line is executed : SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo,FROM CustomerInfo; ?
i think it should be:

SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo FROM CustomerInfo;

HTH
B..M
0
 
LVL 1

Author Comment

by:Larry_T
ID: 12106053
SELECT CustomerInfo.FirstName, CustomerInfo.LastName, CustomerInfo.TelephoneNo FROM CustomerInfo;

Sorry that was a typo on my behalf....  I actually took out a few of the fields for the SQL statement.

I know the SQL statement works, because the datagrid is displaying the information properly.  It's in the dataAdapter Command where I think the problem is- it crashes is there's any changes.

Praneetha, how would I change the SELECT statement to include the Primary Key information?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 16

Accepted Solution

by:
RobertRFreeman earned 800 total points
ID: 12106055
Unless you include a primary key column in your initial select, you must manually enter your insert, update, and delete commands for your adapter.

I prefer to do this through the Visual Studio GUI.  Just right click on your data adapter, then select Configure Data Adapter.

For instructions on using the GUI to add an adapter go here.
http://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_21133673.html

For visuals, see this site:
http://www.c-sharpcorner.com/Tutorials/OleDbDataAdapterDesign.asp

You can also do it manually through the oleDbAdapter.InsertCommand .UpdateCommand and .InsertCommand properties.
0
 
LVL 1

Author Comment

by:Larry_T
ID: 12106073
>>You can also do it manually through the oleDbAdapter.InsertCommand .UpdateCommand and .InsertCommand properties.

I forgot to mention that I am using OLEDB.  I've read many articles so far but none of the implementations seem to work properly....

any suggestions?  :)
0
 
LVL 1

Author Comment

by:Larry_T
ID: 12106196
Thanks- I got it.

I forgot to include the Table Column that had the Primary key... because I didn't want the viewer to be able to overwrite it.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Flash (http://en.wikipedia.org/wiki/Adobe_Flash) has evolved over the years to where it has become a masterful tool for displaying content screen.  It has excellent layout placement, UI precision as well as rendering capabilities. This, along with t…
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

604 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