Results 1 to 3 of 3

Thread: Create Read Update Delete from gridview and save in the database

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2011
    Posts
    58

    Create Read Update Delete from gridview and save in the database

    Hi Everyone,

    I've been dealing with this issue for four hours now and almost giving up. I just want to ask how create, delete, update and delete can be done with the use of datagrid and update SQL server.

    This is what I have:

    Private Sub LoadData()



    Dim connectionString As String = "Data Source=MyComputer\SQLEXPRESS;Initial Catalog=APP;" & "Integrated Security=SSPI;"

    conn = New SqlConnection(connectionString)
    conn.Open()

    Dim adap As New SqlDataAdapter("Select SerialNum , BuildNum, PartBuildID , OrderNum , XPart , YPart ,Rot, Height , PartMass , SupportMass , Special FROM tblBuildPlan where BuildNum =" & cmbBuildNum.SelectedValue(), conn)
    Dim ds As New DataSet
    adap.Fill(ds, "BAM")
    GVBuilPlan.DataSource = ds.Tables(0)

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

    End Try
    For update button this is what I have:
    Code:
     Try
                Dim cmbl As SqlCommandBuilder = New SqlCommandBuilder(da)
                cmbl.GetUpdateCommand()
                da.Update(ds, "BAM")
                MessageBox.Show("Success")
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
    I can successfully display the values in the db in the gridview but the problem happens when I try to edit or add data and hit update button.

    This is the error:

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


    According to what I read, I have to have a primiary key which I do, and it is the SerialNum from the tblBuildMaster table.

    Any help, would be greatly appreciated.

    Thank you everyone!
    Last edited by Serigraphie; Jul 12th, 2015 at 01:29 PM. Reason: for security purposes

  2. #2
    Lively Member GTDriver's Avatar
    Join Date
    Apr 2015
    Location
    Blighty
    Posts
    66

    Re: Create Read Update Delete from gridview and save in the database

    Hi -- what's in the
    Code:
    cmbl.GetUpdateCommand()
    procedure?

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    104,968

    Re: Create Read Update Delete from gridview and save in the database

    First of all, please use the proper names for things. In your post you say "gridview" in some places and "datagrid" in others, so you're creating confusion there. In fact, I'm guessing that it's actually a DataGridView, in which case you have never actually given us accurate information. Please take the fractions of a second it requires to be accurate with the information you provide. We can only go by what you give us so if you don't accurate information then it can be hard for us to give you an accurate solution.

    In this case at least, that part is irrelevant though. You have given us the important part, i.e. the error message. When you use a command builder, it has to be able to uniquely identify a record in order to generate an UPDATE statement and a DELETE statement and for that it uses the primary key. If your table has no primary key or your query doesn't retrieve and identify it then the command builder can't do its job and you get the error message you're seeing.

    So, the solution is to ensure that your table has a primary key in the database first of all. You must then make sure that that PK column is included in your query. Once that's done, the PK column has to be identified in your application. To do that last one, set the MissingSchemaAction property of your data adapter to AddWithKey.

    By the way, get rid of this:
    Code:
    cmbl.GetUpdateCommand()
    It's completely useless.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width