Results 1 to 6 of 6

Thread: Auto fill on PK doesn't work.....

  1. #1

    Thread Starter
    Member
    Join Date
    Nov 2013
    Posts
    42

    Auto fill on PK doesn't work.....

    Hi,
    I am having trouble inserting a row into a table. I have the create table code, the function that does the insert, and an attachment. The attachment shows information about the error: error#, cause of error, etc.

    Code:
    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]
    Code:
       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 varAccountID As Integer = CInt(selectCommand.ExecuteScalar)
                gm_AccountID = varAccountID
                If insertCount < 1 Then
                    LineItemList = Nothing
                End If
                MessageBox.Show(CStr(MessageBox.Show(" These are the values ", ("varAccountID: " & varAccountID & " gm_AccountID: " & gm_AccountID), MessageBoxButtons.OK, MessageBoxIcon.Information)))
            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
    Attached Images Attached Images  

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Auto fill on PK doesn't work.....

    Is that column actually the PK? Your first snippet doesn't indicate that it is. I wouldn't have thought that that would matter mind you.

  3. #3

    Thread Starter
    Member
    Join Date
    Nov 2013
    Posts
    42

    Re: Auto fill on PK doesn't work.....

    Hi jmcilhinney,
    Yes, AccountID is the PK. I doubled checked in VS Server Explorer and SQL Server. Are you saying that might be the problem?
    Regards
    Bob

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Auto fill on PK doesn't work.....

    Quote Originally Posted by fenixrising View Post
    Hi jmcilhinney,
    Yes, AccountID is the PK. I doubled checked in VS Server Explorer and SQL Server. Are you saying that might be the problem?
    Regards
    Bob
    Normally it will be the PK if you have an identity column but it doesn't have to be, therefore I don't think that it should have mattered. I was clutching at straws though because your code looks OK to me.

    Can you confirm that the exception is thrown on this line:
    Code:
    Dim insertCount As Integer = insertCommand.ExecuteNonQuery

  5. #5

    Thread Starter
    Member
    Join Date
    Nov 2013
    Posts
    42

    Re: Auto fill on PK doesn't work.....

    Hi,

    I just copied over the DB again to VS and the code worked. There must have been a glitch in the process. I have no idea what it was.

    Thanks
    Bob

  6. #6
    Hyperactive Member
    Join Date
    Jan 2012
    Location
    Florida
    Posts
    285

    Re: Auto fill on PK doesn't work.....

    Looks to me like you aren't having your AccountID auto increment, you should never be inserting a value into a PK as it auto increments when new records are added

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width