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