Answered by:
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
- Edited by FenixRising Thursday, October 16, 2014 10:25 PM Took out commented code.
Thursday, October 16, 2014 2:50 PM
Answers
-
See how to retrieve identity value through .net here
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, October 24, 2014 2:03 AM
Thursday, October 16, 2014 3:33 PMAnswerer
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 -
-
See how to retrieve identity value through .net here
Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs
- Marked as answer by Lydia ZhangMicrosoft contingent staff Friday, October 24, 2014 2:03 AM
Thursday, October 16, 2014 3:33 PMAnswerer -
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
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 AMAnswerer