Link to home
Create AccountLog in
Avatar of DeniseGoodheart
DeniseGoodheart

asked on

Update Records Using the DataGridView Control

Good Day:

I am creating a Window Forms application using VB.NET 2005 with SQL Server 2000 and the DataGridView control. After I execute the following code to save the grid updates; I get an error that reads, "Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information."  The code that gets the error is as follows:

da.Update(ds, "SODetail")

My entire code for the grid is as follows:
Private Sub LoadGridEdit(ByVal sSO)
        sSQL = "Select * from vwSalesOrderDetailGrid where SONumber='" & sSO & "'"
        sCon = "Data Source=Beatles;Initial Catalog=SgtPepper;User Id=" & sLoginID & ";Password=" & sPassword & ";"
        Dim con As SqlConnection = New SqlConnection(sCon)
   
        Me.grdSODetail.AutoGenerateColumns = False
        cmd.Connection = New SqlConnection(sCon)
        cmd.CommandText = sSQL

       
        da.Fill(ds, "SODetail")
        Me.grdSODetail.DataSource = ds.Tables("SODetail")
        cmd.CommandType = CommandType.Text

        Dim column As DataGridViewColumn = _
            New DataGridViewTextBoxColumn()

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "ID"               'The Column Name in the View
        column.Name = "ID"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("ID").Width = 60
        Me.grdSODetail.Columns("ID").Visible = True

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "SONumber"
        column.Name = "SONumber"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("SONumber").Width = 20
        Me.grdSODetail.Columns("SONumber").Visible = False

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "ItemSequence"
        column.Name = "Cust Item"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Cust Item").Width = 70

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "CustomerItem"
        column.Name = "Cust P/N"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Cust P/N").Width = 130

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "Description"
        column.Name = "Description"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Description").Width = 160

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "Quantity"
        column.Name = "Qty"
        Me.grdSODetail.Columns.Add(column)
        Me.grdSODetail.Columns("Qty").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        grdSODetail.Columns("Qty").Width = 60

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "UnitPrice"
        column.Name = "Unit Price"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Unit Price").Width = 60
       
        column = New DataGridViewComboBoxColumn
        column.DataPropertyName = "UOM"
        column.Name = "UOM"
        CType(column, DataGridViewComboBoxColumn).Items.AddRange("C", "E", "M")
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("UOM").Width = 40

        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "ExtendedPrice"
        column.Name = "Extended Price"
        Me.grdSODetail.Columns.Add(column)
             Me.grdSODetail.Columns("Extended Price").DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleRight
        Me.grdSODetail.Columns("Extended Price").DefaultCellStyle.Format = "#,###,###.00"
        grdSODetail.Columns("Extended Price").Width = 60

        column = New DataGridViewCheckBoxColumn()
        column.DataPropertyName = "Pick"
        column.Name = "Pick"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Pick").Width = 60

        column = New DataGridViewCheckBoxColumn()
        column.DataPropertyName = "Invoice"
        column.Name = "Invoice"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Invoice").Width = 60

        column = New DataGridViewCheckBoxColumn()
        column.DataPropertyName = "Label"
        column.Name = "Label"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Label").Width = 60

        Dim cellStyle As DataGridViewCellStyle = New DataGridViewCellStyle
        column = New DataGridViewTextBoxColumn()
        column.DataPropertyName = "Comment"
        column.Name = "Comment"
        Me.grdSODetail.Columns.Add(column)
        grdSODetail.Columns("Comment").Width = 250
              Me.grdSODetail.Refresh()
        Controls.Add(Me.grdSODetail)
        Me.AutoSize = True
        Me.grdSODetail.Refresh()
        Controls.Add(Me.grdSODetail)
        Me.AutoSize = True
        cmd.Connection.Close()

'''''My ID is a primary key.

Any Suggestions?
Thank You,
Denise
Avatar of Sancler
Sancler

It's difficult to tell from the code you show exactly what's going on.  The vast majority of the code is to do with setting up the datagridview rather than with actually getting the data.

But you are using

        da.Fill(ds, "SODetail")

to get the data.  Is that successful?  Does the dgv actually fill up with the data you are expecting.

Assuming it is, somewhere, you must have declared and instantiated da.  Can we see the code for that, please?  Also (as the implication seems to be that the da.Fill is somehow related to the code you show for cmd) can we please see where you declare and instantiate that?  Finally (for the moment) that your line

da.Update(ds, "SODetail")

is throwing the exception you say - that is, that it cannot autogenerate commands rather than that there are no commands - it looks as though, at some point, you must have something on the lines of

    Dim cb As New SqlCommandBuilder(da)

Can we please see that bit of code, and how it relates to the other bits I've asked about above?

Roger
Avatar of DeniseGoodheart

ASKER

Hello Sancler:

Thank you for your response.  This code actually works when I do a batch insert but does not work for a batch update. And the grid gets populated with data from the code I posted.

Public Class frmSalesOrderDetail
    Private cmd As New SqlCommand
    Private da As New SqlDataAdapter(cmd)
.......
.......
End Class

Thank You,
Denise
Do you change the .CommandText of cmd between using the commandbuilder on da and setting it to sSQL in the sub you show?  If so, you should call

    myCommandBuilder.RefreshSchema

The code you show in this sub includes what you say is your primary key.  That is needed for autogeneration of Update and Delete commands, but not for autogeneration of Insert commands.  But the commandbuilder works with reference to the SelectCommand that it has at the time it is first used.  If that was different from the sSQL value you are now assigning to that SelectCommand's CommandText it may be that it is not recognising the primary key.  Forcing it to .RefreshSchema will send it back to the database to get the metadata relevant to the new select command.

The other thing you could try is

        da.MissingSchemaAction = MissingSchemaAction.AddWithKey

If your primary key is properly declared in your database table then it should not be necessary.  But it's always worth trying if there's a complaint that Primary Key information is missing.

Roger
Hello Roger:

I tried your suggestion with no success.  I have used this code using a small table with test data and it works fine for inserts and updates using the following:

da.Update(ds, "SODetail")

Thank You,
Denise
Does that mean that your original problem is solved?

Roger
Hello Roger:

No, it just means that it worked using a simple table with little data validation, and does not work with a complex table and validation.
ASKER CERTIFIED SOLUTION
Avatar of Sancler
Sancler

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Good Day Roger:

Thanks you for all your help.  I created a composite/combination key and it works like a charm now.  I visited the UK last summer and had a blast.  I look forward to going back in 2009.  I visited London, Liverpool (Toured a lot of The Beatles sites), Scotland, and Wales.

Cheers,
Denise