Click here to Skip to main content
15,791,892 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi i use this code to entry information to the database
"AddAudit_Trail(UserID, "Journal Update " & Journal_id & "Acc# " & intAccount_Nbr, "Gl Transaction")"
instead of one rows it will sometime enter more than one rows please check the attachment
44093 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44094 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44095 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44096 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44097 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44098 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44099 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44100 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44101 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44102 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44103 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction
44104 6 2017-10-07 Journal Update 0Acc# 28250 2:23:16 PM Gl Transaction

What I have tried:

i try to figure out why its entering duplicate rows into the database i am using stored procedure to pass the data from my application to the database


Sub AddAudit_Trail(Var1 As Object, Var2 As Object, Var5 As Object)
strTime = TimeOfDay
VDate = System.DateTime.Now.ToShortDateString
add5Value("AddAuditTrail", Var1, VDate, Var2, strTime, Var5)
End Sub
here is the stored procedure

Create PROCEDURE [dbo].[AddAuditTrail]
-- Add the parameters for the stored procedure here
@Var1 int,
@Var2 Date ,
@Var3 Varchar(80),
@Var4 VarChar(50),
@Var5 NVarchar(20)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
INSERT INTO TBL_Audit_Trail
(UserID, Audit_Date, Action, Timex, Type)

VALUES (@Var1,@Var2,@Var3,@Var4,@Var5)

END
Sub add5Value(ByVal ProcedureName As String, ByVal Var1 As Object, ByVal Var2 As Object, ByVal Var3 As Object, _
ByVal Var4 As Object, ByVal Var5 As Object)
Try


Dim SqlCmd As New SqlCommand
SqlCmd.CommandType = CommandType.StoredProcedure
SqlCmd.CommandText = ProcedureName
SqlCmd.Connection = objConn

Dim sqlPrm As New SqlParameter("Var1", Var1)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var2", Var2)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var3", Var3)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var4", Var4)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var5", Var5)
SqlCmd.Parameters.Add(sqlPrm)
If objConn.State = 1 Then
objConn.Close()
End If
objConn.Open()
SqlCmd.ExecuteNonQuery()
objConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub
its not doing all the time its doing some times and its not only with this table
Posted
Updated 16-Nov-17 7:58am
v3
Comments
CHill60 16-Nov-17 4:14am    
Without knowing the code behind AddAuditTrail we can't possibly help. You cannot attach files here. Use the Improve question link to add the code that is doing the insert
inahebel 16-Nov-17 5:16am    
thanks for the replay please check the ? again i added more information
phil.o 16-Nov-17 5:27am    
then we need the add5Value method... Please show the piece of code responsible for actually interacting with the stored prcedure.
inahebel 16-Nov-17 12:56pm    
Sub add5Value(ByVal ProcedureName As String, ByVal Var1 As Object, ByVal Var2 As Object, ByVal Var3 As Object, _
ByVal Var4 As Object, ByVal Var5 As Object)
Try


Dim SqlCmd As New SqlCommand
SqlCmd.CommandType = CommandType.StoredProcedure
SqlCmd.CommandText = ProcedureName
SqlCmd.Connection = objConn

Dim sqlPrm As New SqlParameter("Var1", Var1)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var2", Var2)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var3", Var3)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var4", Var4)
SqlCmd.Parameters.Add(sqlPrm)
sqlPrm = New SqlParameter("Var5", Var5)
SqlCmd.Parameters.Add(sqlPrm)
If objConn.State = 1 Then
objConn.Close()
End If
objConn.Open()
SqlCmd.ExecuteNonQuery()
objConn.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)
End Try

End Sub
ZurdoDev 16-Nov-17 9:47am    
You must be calling the stored procedure more than once.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900