none
SQLDataAdapter Insert Primary Key Issue.. RRS feed

  • Question

  •  

    HI:
    Can someone tell me HOW to insert a automatic number after my user inserts new data from text boxes into the dataset. I keep getting this error:

    Cannot insert the value NULL into column 'id', table; column does not allow nulls. INSERT fails. (ID IS MY KEY)

    I can update & delete fine, but when I try to insert, I cannot figure out how to get the primary key inserted. I'm new @ this & I think I've confused myself....
    THANK YOU

    Dim conn As SqlConnection = GetSQLConnection()

    Try
    Dim sql As String = "Select * from table"
    Dim sa2 As SqlDataAdapter = New SqlDataAdapter(sql, conn)
    Try
    If dataset.HasChanges Then
    sa2.Update(ds, "table")
    dataset.AcceptChanges()
    End If
    Finally
    sa2.Dispose()
    End Try

    Finally
    conn.Close()
    conn.Dispose()
    End Try
    Saturday, September 29, 2007 4:23 PM

All replies

  • You cannot insert identity value directly (except in a cases when this functionality is disabled) and you should allow database engine to insert it automatically. After it is inserted, you need to use SCOPE_IDENTITY() function or @@IDENTITY variable to get that latest generated ID. Here is sample hot to do this with SQL Server

     

    http://support.microsoft.com/kb/320141/en-us
    Sunday, September 30, 2007 12:29 PM
    Moderator