Automatic key assignment not working....

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

 

Answer 1

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())

Answer 2

And which detailed error are you getting?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

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

Answer 4

Answer 5

As I said, you shouldn't be inserting into AccountID anyway.

Remove it from your insert.

Answer 6

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

Answer 7

Hi,

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

Regards,

Bob

Answer 8

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