?
Solved

Saving DataGridView changes back to SQL database

Posted on 2011-10-20
3
Medium Priority
?
262 Views
Last Modified: 2012-05-12
Greetings experts,

I have a supposedly simple application that will enable end users to modify account contact information for our order acknowledgement email system.  I would like to simply present them with a grid that they can make changes to, and it saves after you leave the row a la Access.

I have the beginnings under way and am using a button to test my db save code, but I keep running into an error saying that I dont have a key value column defined.

"Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."


Below is my code snipet, I tried searching through the previously asked questions here but was not able to find any help specific to my needs.  If anyone here can provide any insight I would be most grateful.  Thanks.



 
Private adapter As SqlDataAdapter
Private myDataSet As DataSet

Private Sub frmMain_Load(ByVal sender As Object, ByVal e As System.EventArgs) _
Handles Me.Load

        PBPPEconn = modPBPPurchasingEmailPODB.GetConnectString
        Dim myBindingSource As New BindingSource

        Dim strSQL As String = "SELECT * FROM PBPEDIByEmailList"
        Dim conn As New SqlConnection(PBPPEconn)
        Dim cmd As New SqlCommand(strSQL, conn)
        adapter = New SqlDataAdapter(cmd)
        myDataSet = New DataSet
        adapter.Fill(myDataSet)
        myBindingSource.DataSource = myDataSet.Tables(0).DefaultView

End Sub

Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles cmdUpdate.Click
        Try

            Dim cb As SqlCommandBuilder = New SqlCommandBuilder(Me.adapter)
            cb.GetUpdateCommand()
      
            adapter.Update(myDataSet)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

End Sub

Open in new window

0
Comment
Question by:gwosgood
  • 2
3 Comments
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 2000 total points
ID: 37000591
As the error suggests, you need to have a primary key defined in the table so that the update command can use it in Where clause.
0
 
LVL 2

Author Comment

by:gwosgood
ID: 37000643
I actually kind of feel like an idiot now.  I've been using a linked table in Access this whole time to test and see changes made to the db table.  Apparenty, even if you dont assign a key field in SQL, Access will mark the first column as "key".

Lesson:  follow through and check the SQL db instead of assuming its setup properly.

Thanks for your help.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 37002477
Glad to help :-)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2
Suggested Courses

850 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