Multiple records being inserted using stored proc in ASP.Net

Question

Multiple records being inserted using stored proc in ASP.Net

SSC-Addicted

Points: 400

September 18, 2005 at 11:18 am

I have an ASP.Net application that is designed to insert a new client record using a stored procedure. The procedure has worked fine until the last few day, when is started inserting multiple records when run in the .Net application.

When running it in Query Analyzer it behaves properly, inserting the record and returning it @@IDENTITY for the inserted record.

This is the proc.

CREATE PROCEDURE dbo.TCHS_Client_Add2
/****************************************
Procedure to add a new client record to the database
*****************************************
Created by John K. 13-May-2005
****************************************/
 @MedRecNum NvarChar(50),
 @ClientFN nvarchar(30),
 @ClientMN nvarchar(30),
 @ClientLN nvarchar(30),
 @ClientRaceCode tinyint,
 @ClientOtherName nvarchar(50),
 @ClientDOB datetime,
 @PrimaryPhone nvarchar(30),
 @ClinicID int,
 @DateCreated datetime,
 @CreatedBy nvarchar(50),
 @MyID int OUTPUT
AS

SET NOCOUNT ON

INSERT INTO T_CLIENTS
(
 MedRecNum,
 ClientFN,
 ClientMN,
 ClientLN,
 ClientRaceCode,
 OtherName,
 ClientDOB,
 PrimaryPhone,
 ClinicID,
 DateCreated,
 CreatedBy
) VALUES
(
 @MedRecNum,
 @ClientFN,
 @ClientMN,
 @ClientLN,
 @ClientRaceCode,
 @ClientOtherName,
 @ClientDOB,
 @PrimaryPhone,
 @ClinicID,
 @DateCreated,
 @CreatedBy
)

PRINT @@IDENTITY 
SET @MyID = @@IDENTITY
PRINT @MyID

The .Net code used to call this procedure is:

oConn.Open()

oCmd.Connection = oConn

oCmd.CommandType = CommandType.StoredProcedure

oCmd.CommandTimeout = 60

oCmd.CommandText = "[TCHS_Client_Add2]"

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MedRecNum", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientFN", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientMN", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientLN", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientRaceCode", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientDOB", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClientOtherName", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PrimaryPhone", System.Data.SqlDbType.NVarChar, 30, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ClinicID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DateCreated", System.Data.SqlDbType.DateTime, 8, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Now()))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CreatedBy", System.Data.SqlDbType.NVarChar, 50, System.Data.ParameterDirection.Input, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Session("User")))

oCmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MyID", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.Output, False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

'Start Assigning the Parameter Values

'Medical Record Number

If Me.tbMedRecNum.Value <> "" Then

oCmd.Parameters("@MedRecNum").Value() = Me.tbMedRecNum.Value

Else

oCmd.Parameters("@MedRecNum").Value = DBNull.Value

End If

'Client's First Name

oCmd.Parameters("@ClientFN").Value = Me.tbClientFN.Value

'Client's Middle Name

If Me.tbClientMN.Value <> "" Then

oCmd.Parameters("@ClientMN").Value = Me.tbClientMN.Value

Else

oCmd.Parameters("@ClientMN").Value = DBNull.Value

End If

'Client's Last Name

oCmd.Parameters("@ClientLN").Value = Me.tbClientLN.Value

'Client's Race Code

oCmd.Parameters("@ClientRaceCode").Value = Me.lstRace.Value

'Client's Other Name

If Me.tbOtherName.Value <> "" Then

oCmd.Parameters("@ClientOtherName").Value = Me.tbOtherName.Value

Else

oCmd.Parameters("@ClientOtherName").Value = DBNull.Value

End If

'Client's DOB

If Me.tbClientDOB.Value <> #12:00:00 AM# Then

'If Not Me.tbClientDOB.Value Is DBNull.Value Then

oCmd.Parameters("@ClientDOB").Value = Me.tbClientDOB.Value

Else

oCmd.Parameters("@ClientDOB").Value = DBNull.Value

End If

'Client's Primary Phone Number

If Me.tbPrimaryPhone.Value <> "" Then

oCmd.Parameters("@PrimaryPhone").Value = Me.tbPrimaryPhone.Value

Else

oCmd.Parameters("@PrimaryPhone").Value = DBNull.Value

End If

'Clinic ID

oCmd.Parameters("@ClinicID").Value = Me.lstClinic.Value

oCmd.Parameters("@myID").Value = 0

Dim oDr As SqlClient.SqlDataReader

oCmd.ExecuteNonQuery()

oCmd.ExecuteNonQuery()

Session("ClientEditID") = oCmd.Parameters("@MyID").Value

Anyone see anything that I'm missing?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

I haven't analysed your code, but my suggestion is to that you run Profiler and check out what SQL is being sent from the ASP.NET app. and troubleshoot from there.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

David A. Long SSCrazy Eights Points: 8421 More actions · Answer 2

Change use of @@IDENTITY to SCOPE_IDENTITY() for the @MyID assignment. See BOL for the diggerences.

Andy

DanKennedy Hall of Fame Points: 3736 More actions · Answer 3

Why are you declaring a DataReader as Dim oDr As SqlClient.SqlDataReader, you don't need/use one?

Also why do you call the stored procedure/command twice i.e.

oCmd.ExecuteNonQuery()

This might be why it's inserting twice ?

Dan
www.firstcs.co.uk

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Ninja's_RGR'us

SSC Guru

Points: 294069

September 19, 2005 at 8:09 am

#590955

Now those are nice questions .

John Kress SSC-Addicted Points: 400 More actions · Answer 5

One of those oCmd.ExecuteNonQuery lines is commented out in the actual vbCode. I must have missed that when copying the code into the posting.

It turned out that a followup segement of ASP.Net code was not assigning the @@IDENTITY value to a session variable, thus the vbcode was cycling more than once. Too much coffee, too little sleep plus trying to meet a deadline... Programmer Error!!!

All along, I thought that SQL couldn't have been the culprit!

noeld SSC Guru Points: 96590 More actions · Answer 6

All along, I thought that SQL couldn't have been the culprit!

It has been my experience that it is the DB the first one to get blamed, though

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Ok course... their code works, they can't imagine that it can be slow and working at the same time .