Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand

[kank]

Hi,

I am newbie ones, pls help me I am trying to update change from datagridview to database of sql server but got the error.

Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. (I use visual studio 2008 with sql server 2005)

My code is

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.OleDb
Imports System.Windows.Forms
Imports Console = System.Console


Public Class fLoginMgt
    Dim row As Integer
    Dim col As Integer
    Dim currentTime As System.DateTime = System.DateTime.Now
    Dim SelectQry As String = "SELECT * FROM tblLogin"

    
    Dim cb As OleDb.OleDbCommandBuilder
    Dim adapter As SqlDataAdapter
    Dim myCommand As New SqlCommand()
    Dim myDataAdapter = New SqlDataAdapter()
    Dim ds As New DataSet
    Dim TableView As DataView
    'Another ones
    Private da As New SqlDataAdapter
    Private conn As SqlConnection
    Private bsource As BindingSource = New BindingSource()
    Dim cmdBuilder As SqlCommandBuilder
    
    Dim sql As String
    'Dim myData As DataTable



    Private Sub Label1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblLoginMgt.Click

    End Sub

    Private Sub fLoginMgt_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'FinDataSet.tblLogin' table. You can move, or remove it, as needed.

        Try
      
            sqlconn.ConnectionString = connString
            sqlconn.Open()

        Catch ex As Exception
            MessageBox.Show("Connection Failed!", ex.Message)
        End Try
        If sqlconn.State = 1 Then
            MessageBox.Show("Connection Successful!")
        End If


        'Try

        'dapter = New SqlDataAdapter(SelectQry, sqlconn)
        'dapter.Fill(ds)
        'Connection.Close()
        'ataGridView1.DataSource = ds.Tables(0)

        'TableView = ds.Tables(0).DefaultView
        'Catch ex As Exception
        'hrow ex
        'nd Try

        da = New SqlDataAdapter(SelectQry, sqlconn)
        'conn.Open()
        ds = New DataSet()
        Dim commandBuilder As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Fill(ds, "Login")
        bsource.DataSource = ds.Tables("Login")
        DataGridView1.DataSource = bsource




    End Sub



    Private Sub DataGridView1_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellContentClick
     
    End Sub

    Private ReadOnly Property Connection() As SqlConnection
        Get
            Dim ConnectionToFetch As New SqlConnection(connString)
            ConnectionToFetch.Open()
            Return ConnectionToFetch
        End Get
    End Property


    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    End Sub


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

      

        'myDataAdapter.Update(ds)

        'sql = "update tblLogin set userid='" & DataGridView1.CurrentRow.Cells(0).Value & "',password='" & DataGridView1.CurrentRow.Cells(1).Value & "',level='" & DataGridView1.CurrentRow.Cells(2).Value & "',active='" & DataGridView1.CurrentRow.Cells(3).Value & "',created_date='" & DataGridView1.CurrentRow.Cells(4).Value & "',login_date='" & DataGridView1.CurrentRow.Cells(5).Value & "' "
       

        Dim dt As DataTable = ds.Tables("Login")
        Me.DataGridView1.BindingContext(dt).EndCurrentEdit()
        Me.da.Update(dt)


    End Sub

Thanks

 

[jmcilhinney]

Does your table have a primary key? With no PK, the command builder cannot uniquely identify a record in order to update or delete it. You should normally add an identity column to your table and use that as the PK.

 

[kank]

Why even I change to set the PK at the first field, it still occur same error.(PS, I don't know how to set identity, I can't change to 'Yes' as on the picture).

Thanks

 

[jmcilhinney]

identities can only be integer data types, normally 'int' or 'long'. If you want to use 'userid' as the PK then it cannot be an identity. I would tend change the name of that column to 'user_name' and add another column named 'user_id' of type 'int' or 'long' and make it an identity. If your table has a PK and the command builder still won't work, try setting the MissingSchemaAction property of the data adapter to AddWithKey.