Concurrency error on Insert
- From: PickwickBob3 <PickwickBob3@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 17 Jan 2007 09:24:03 -0800
I have an occasional concurrency error on inserting a row into sql database.
It occurs every 4-6 th time a row is added. Here is the code.
StoredProcedure:
ALTER PROCEDURE dbo.InsertPatientData
(
@Stat char(10),
@AppTime DateTime,
@PatientName char(20),
@ChartNumber char(15),
@Dictated char(15),
@WaveFile char(30),
@PhysicianNumber char(4),
@Letter char(10),
@Date DateTime,
@TranscriptionStatus char(10),
@LocationIndex int,
@Identity bigint OUTPUT
)
AS
INSERT INTO PatientData
( AppTime, PatientName, ChartNumber, Dictated,
PhysicianNumber, Stat, Date, WaveFile,
Letter,TranscriptionStatus,LocationIndex)
VALUES ( @AppTime, @PatientName, @ChartNumber, @Dictated,
@PhysicianNumber, @Stat, @Date, @WaveFile,
@Letter,@TranscriptionStatus,@LocationIndex)
/* SET NOCOUNT ON */
SET @Identity = SCOPE_IDENTITY()
The Code:
SqlConnection con = new
SqlConnection(sqlConnection1.ConnectionString);//connectionString
DateTime datatime = (DateTime)dataView1[hi.Row][1];//get time of present
record
datatime = datatime.AddSeconds(5); //add 5 seconds to time
SqlDataAdapter catDa = new SqlDataAdapter("SELECT
Number,AppTime,PatientName,ChartNumber,Dictated,WaveFile,PhysicianNumber,Stat,Date,Letter,TranscriptionStatus,LocationIndex
FROM PatientData WHERE (PhysicianNumber =@PhysicianNumber)AND (Date = @Date)
",con);
catDa.InsertCommand= new SqlCommand("InsertPatientData",con);
catDa.InsertCommand.CommandType = CommandType.StoredProcedure;
catDa.InsertCommand.Parameters.Add("@Stat",SqlDbType.Char,10,"Stat");
catDa.InsertCommand.Parameters.Add("@AppTime",SqlDbType.DateTime,
8,"AppTime");
catDa.InsertCommand.Parameters.Add("@PatientName",SqlDbType.Char,20,"PatientName");
catDa.InsertCommand.Parameters.Add("@ChartNumber",SqlDbType.Char,15,"ChartNumber");
catDa.InsertCommand.Parameters.Add("@Dictated",SqlDbType.Char,15,"Dictated");
catDa.InsertCommand.Parameters.Add("@WaveFile",SqlDbType.Char,30,"WaveFile");
catDa.InsertCommand.Parameters.Add("@PhysicianNumber",SqlDbType.Char,4,"PhysicianNumber");
catDa.SelectCommand.Parameters.Add("@PhysicianNumber",SqlDbType.Char,4,"PhysicianNumber");
catDa.SelectCommand.Parameters.Add("@Date",SqlDbType.DateTime,8,"Date");
catDa.InsertCommand.Parameters.Add("@Letter",SqlDbType.Char,10,"Stat");
catDa.SelectCommand.Parameters["@Date"].Value =
currentDateNow.ToShortDateString();//datetime.Date.ToString();
catDa.SelectCommand.Parameters["@PhysicianNumber"].Value = phyNo;
catDa.InsertCommand.Parameters["@AppTime"].Value = datatime;
catDa.InsertCommand.Parameters.Add("@Date",SqlDbType.DateTime,8,"Date");
catDa.InsertCommand.Parameters.Add("@TranscriptionStatus",SqlDbType.Char,10,"TranscriptionStatus");
catDa.InsertCommand.Parameters.Add("@LocationIndex",SqlDbType.Int,4,"LocationIndex");
SqlParameter myParm =
catDa.InsertCommand.Parameters.Add("@Identity",SqlDbType.BigInt,0,"Number");
myParm.Direction = ParameterDirection.Output;
con.Open();
dataSet21.Clear();
int i = catDa.Fill(dataSet21,"PatientData");
DataRow newRow = dataSet21.Tables["PatientData"].NewRow();
newRow["AppTime"]= datatime;
newRow["PatientName"] = this.additonString.ToString();
newRow["ChartNumber"] = dataView1[hi.Row][3].ToString() ;
newRow["Dictated"] = "FALSE";
newRow["WaveFile"]= null;
newRow["PhysicianNumber"] = dataView1[hi.Row][6].ToString() ;// physno.
newRow["Stat"] = dataView1[hi.Row][7].ToString() ;// stat.
newRow["Letter"] = dataView1[hi.Row][8].ToString() ;// letter.
newRow["Date"] =
(DateTime)currentDateNow.Date;//datetime.Date.ToString();datatime.Date.ToShortDateString();
newRow["TranscriptionStatus"] = " ";
newRow["LocationIndex"] = 0;
dataSet21.Tables["PatientData"].Rows.Add(newRow);
int y = catDa.Update(dataSet21,"PatientData");
con.Close();
Can someone offer some advice as to where to start troubleshooting this
problem?
How can I determine what caused the error. The table is set up to Identiy
Increment. Thanks.
--
BP
.
- Follow-Ups:
- Re: Concurrency error on Insert
- From: Bob Barrows [MVP]
- Re: Concurrency error on Insert
- Prev by Date: Re: Column <ColumnName> does not belong to table <TableName>
- Next by Date: Re: Concurrency error on Insert
- Previous by thread: Re: Column <ColumnName> does not belong to table <TableName>
- Next by thread: Re: Concurrency error on Insert
- Index(es):