locked
Automatic key assignment not working.... RRS feed

  • Question

  • Hi,

      The automatic key assignment is trying to assign a null value to the primary key(AccountID) on saving the record causing an error. Line is bold.

    Regards, Bob

    CREATE TABLE [dbo].[AppGlobal](
    	[AccountID]  [int] Identity (1,1)  NOT NULL,
    	[AppID] [varchar](50) NOT NULL,
    	[AppPassword] [varchar](50) NOT NULL,
    	[AppNickName] [varchar](50) NOT NULL,
    	[AppCreateDate] [datetime] NOT NULL
    ) ON [PRIMARY]

        Public Shared Function AddLogin(thisADB As AppDB, ByRef AuditHdr As auditHdr) As List(Of AuditLineItem)
            Dim LineItemList As New List(Of AuditLineItem)
            Dim LineItemSeq As Integer = 0
            Dim connection As SqlConnection = BTDB.GetConnection
            Dim insertStatement As String =
                "INSERT AppGlobal  " &
                "(AppID, AppPassword, AppNickName, AppCreateDate) " &
                "VALUES (@AppID, @AppPassword, @AppNickName, @AppCreateDate)"
    
            Dim insertCommand As New SqlCommand(insertStatement, connection)
    
            AuditHdr = New auditHdr
            'AuditHdr.AuditID = 0
            AuditHdr.AccountID = 999999
            'Dim auditHdr As auditHdr
            AuditHdr.CreateDate = Now
            AuditHdr.TableName = "AppGlobal"
            AuditHdr.Action = "A"
            AuditHdr.ScreenID = gm_ScrID
    
            Dim auditLineItem As AuditLineItem
    
            insertCommand.Parameters.AddWithValue("@AppID", thisADB.AppID)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "E-mail"
            auditLineItem.BeforeValue = ""
            auditLineItem.AfterValue = thisADB.AppID
            LineItemList.Add(auditLineItem)
    
            insertCommand.Parameters.AddWithValue("@AppPassword", thisADB.AppPassword)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "Password"
            auditLineItem.BeforeValue = ""
            auditLineItem.AfterValue = thisADB.AppPassword
            LineItemList.Add(auditLineItem)
    
            insertCommand.Parameters.AddWithValue("@AppNickName", thisADB.AppNickName)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "NickName"
            auditLineItem.BeforeValue = ""
            auditLineItem.AfterValue = thisADB.AppNickName
            LineItemList.Add(auditLineItem)
    
            insertCommand.Parameters.AddWithValue("@AppCreateDate", thisADB.AppCreateDate)
            auditLineItem = New AuditLineItem
            auditLineItem.AuditID = AuditHdr.AuditID
            LineItemSeq = LineItemSeq + 1
            auditLineItem.AuditSequence = LineItemSeq
            auditLineItem.FieldName = "CreateDate"
            auditLineItem.BeforeValue = ""
            auditLineItem.AfterValue = CStr(thisADB.AppCreateDate)
            LineItemList.Add(auditLineItem)
    
            Try
                connection.Open()
                Dim insertCount As Integer = insertCommand.ExecuteNonQuery
                Dim selectStatement As String =
                     "SELECT IDENT_CURRENT('AppGlobal') FROM AppGlobal"
                Dim selectCommand As New SqlCommand(selectStatement, connection)
                Dim AccountID As Integer = CInt(selectCommand.ExecuteScalar)
                gm_AccountID = AccountID
                If insertCount < 1 Then
                    LineItemList = Nothing
                End If
            Catch ex As SqlException
                MessageBox.Show("SQL Server error #4  " & ex.Number & ": " & ex.Message, ex.GetType.ToString)
            Finally
                connection.Close()
            End Try
    
            Return LineItemList
    
        End Function

     
    • Edited by FenixRising Thursday, October 16, 2014 10:25 PM Took out commented code.
    Thursday, October 16, 2014 2:50 PM

Answers

All replies

  • Not being super awesome in C, it looks to me like you're trying to insert a value into an identity column. Don't. Insert the other column values only, in SQL this would be performed like so:

    DECLARE @AppGlobal TABLE (AccountID  INT IDENTITY (1,1)  NOT NULL, AppID VARCHAR(50) NOT NULL, AppPassword VARCHAR(50) NOT NULL, AppNickName VARCHAR(50) NOT NULL, AppCreateDate DATETIME NOT NULL)
    
    INSERT INTO @AppGlobal (AppID, AppPassword, AppNickName, AppCreateDate)
    VALUES  (1, 'onetwothree', 'abc', GETDATE())

    • Proposed as answer by Kalman Toth Thursday, October 16, 2014 11:20 PM
    Thursday, October 16, 2014 2:56 PM
  • And which detailed error are you getting?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 16, 2014 3:22 PM
  • See how to retrieve identity value through .net here

    http://www.aspsnippets.com/Articles/Return-Identity-Auto-Increment-Column-value-after-record-insert-in-SQL-Server-Database-using-ADONet-with-C-and-VBNet.aspx


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, October 16, 2014 3:33 PM
    Answerer
  • Thursday, October 16, 2014 7:36 PM
  • As I said, you shouldn't be inserting into AccountID anyway.

    Remove it from your insert.

    Thursday, October 16, 2014 8:31 PM
  • Hi Patrick,

    I took out a statement.I think this is the one you want me to take out. Take a look above. If there is anything else to take out let me know. I still get the error.

    I'm a novice.

    Regards,

    Bob

    Thursday, October 16, 2014 11:08 PM
  • Hi,

      I resolved my issues and now my code works. Thanks for all the help.

    Regards,

    Bob

    Friday, October 17, 2014 6:19 AM
  • Hi,

      I resolved my issues and now my code works. Thanks for all the help.

    Regards,

    Bob

    Can you mark relevant post as answer for others benefit please?

    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Friday, October 17, 2014 6:35 AM
    Answerer