wow, this is a good one :(

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Single call to ExecuteNonQuery() with "EXECUTE MyAccessQuery" creates 86
new, identical records.

Here is the code from the Access Query
<AccessQueryCode>
INSERT INTO Tbl_ProtocolSegments ( ProtocolID, FreqBStart, FreqBEnd,
DurationSec, [Count], AmplitudeRampID, OpCode )
SELECT [_protocolID] AS Expr1, [_freqBStart] AS Expr2, [_freqBEnd] AS
Expr3, [_durationSec] AS Expr4, [_count] AS Expr5, [_amplitudeRampID] AS
Expr6, [_opCode] AS Expr7
FROM Tbl_ProtocolSegments;
</AccessQueryCode>

Here is the DAL code:
<DalCode>
m_command.CommandText = GetAccessSprocString(m_spInsertSegment);
m_command.Parameters.Clear();

OleDbParameter param = new OleDbParameter("_protocolID",
segment.ProtocolID);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBStart", segment.Frequency2Start);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBEnd", segment.Frequency2End);
m_command.Parameters.Add(param);

param = new OleDbParameter("_durationSec", segment.Duration);
m_command.Parameters.Add(param);

param = new OleDbParameter("_count", segment.Count);
m_command.Parameters.Add(param);

param = new OleDbParameter("_amplitudeRampID",
segment.AmplitudeRampValue);
m_command.Parameters.Add(param);

param = new OleDbParameter("_opCode", segment.OpCodeValue);
m_command.Parameters.Add(param);


// open the connection and execute the update
m_connection.Open();
m_command.ExecuteNonQuery();
<DalCode>


I have NO idea what is causing this. I have never seen something like this
before. Has anyone here seen this? Any ideas?

Thanks for reading!
Steve
 
My fault(isn't it always???)
I had an erroneous FROM clause at the end of my Query. I thought this was
ADO.NET but then I ran the query from within Access and had the same
problem, so it prompted further investigation.

Sorry for wasting your time
 
It is probably not a good idea to use a select statement at all, since you
are inserting variables and not a record form a table.

Something like this is a safer, and likely more efficient, approach. The
code is also cleaner and thus easier to debug. I assume that you generated
the query using the access UI and cut and pasted it?

INSERT INTO Tbl_ProtocolSegments ( ProtocolID, FreqBStart, FreqBEnd,
DurationSec, [Count], AmplitudeRampID, OpCode )
values ( [_protocolID], [_freqBStart], [_freqBEnd], [_durationSec],
[_count], [_amplitudeRampID], [_opCode])


Steve said:
My fault(isn't it always???)
I had an erroneous FROM clause at the end of my Query. I thought this was
ADO.NET but then I ran the query from within Access and had the same
problem, so it prompted further investigation.

Sorry for wasting your time


Steve said:
Single call to ExecuteNonQuery() with "EXECUTE MyAccessQuery" creates 86
new, identical records.

Here is the code from the Access Query
<AccessQueryCode>
INSERT INTO Tbl_ProtocolSegments ( ProtocolID, FreqBStart, FreqBEnd,
DurationSec, [Count], AmplitudeRampID, OpCode )
SELECT [_protocolID] AS Expr1, [_freqBStart] AS Expr2, [_freqBEnd] AS
Expr3, [_durationSec] AS Expr4, [_count] AS Expr5, [_amplitudeRampID] AS
Expr6, [_opCode] AS Expr7
FROM Tbl_ProtocolSegments;
</AccessQueryCode>

Here is the DAL code:
<DalCode>
m_command.CommandText = GetAccessSprocString(m_spInsertSegment);
m_command.Parameters.Clear();

OleDbParameter param = new OleDbParameter("_protocolID",
segment.ProtocolID);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBStart", segment.Frequency2Start);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBEnd", segment.Frequency2End);
m_command.Parameters.Add(param);

param = new OleDbParameter("_durationSec", segment.Duration);
m_command.Parameters.Add(param);

param = new OleDbParameter("_count", segment.Count);
m_command.Parameters.Add(param);

param = new OleDbParameter("_amplitudeRampID",
segment.AmplitudeRampValue);
m_command.Parameters.Add(param);

param = new OleDbParameter("_opCode", segment.OpCodeValue);
m_command.Parameters.Add(param);


// open the connection and execute the update
m_connection.Open();
m_command.ExecuteNonQuery();
<DalCode>


I have NO idea what is causing this. I have never seen something like this
before. Has anyone here seen this? Any ideas?

Thanks for reading!
Steve
Click to expand...
Click to expand...
 
Jim, yes, that is much cleaner and does make more sense. You are right, I
used the UI generated query thing. I just edited the queries to use
VALUES() but beware, if you dare open the query in design mode, it reformats
it back to use SELECT

Thanks for the tip!
Have a good weekend,
Steve


Jim Underwood said:
It is probably not a good idea to use a select statement at all, since you
are inserting variables and not a record form a table.

Something like this is a safer, and likely more efficient, approach. The
code is also cleaner and thus easier to debug. I assume that you generated
the query using the access UI and cut and pasted it?

INSERT INTO Tbl_ProtocolSegments ( ProtocolID, FreqBStart, FreqBEnd,
DurationSec, [Count], AmplitudeRampID, OpCode )
values ( [_protocolID], [_freqBStart], [_freqBEnd], [_durationSec],
[_count], [_amplitudeRampID], [_opCode])


Steve said:
My fault(isn't it always???)
I had an erroneous FROM clause at the end of my Query. I thought this was
ADO.NET but then I ran the query from within Access and had the same
problem, so it prompted further investigation.

Sorry for wasting your time


Steve said:
Single call to ExecuteNonQuery() with "EXECUTE MyAccessQuery" creates 86
new, identical records.

Here is the code from the Access Query
<AccessQueryCode>
INSERT INTO Tbl_ProtocolSegments ( ProtocolID, FreqBStart, FreqBEnd,
DurationSec, [Count], AmplitudeRampID, OpCode )
SELECT [_protocolID] AS Expr1, [_freqBStart] AS Expr2, [_freqBEnd] AS
Expr3, [_durationSec] AS Expr4, [_count] AS Expr5, [_amplitudeRampID] AS
Expr6, [_opCode] AS Expr7
FROM Tbl_ProtocolSegments;
</AccessQueryCode>

Here is the DAL code:
<DalCode>
m_command.CommandText = GetAccessSprocString(m_spInsertSegment);
m_command.Parameters.Clear();

OleDbParameter param = new OleDbParameter("_protocolID",
segment.ProtocolID);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBStart", segment.Frequency2Start);
m_command.Parameters.Add(param);

param = new OleDbParameter("_freqBEnd", segment.Frequency2End);
m_command.Parameters.Add(param);

param = new OleDbParameter("_durationSec", segment.Duration);
m_command.Parameters.Add(param);

param = new OleDbParameter("_count", segment.Count);
m_command.Parameters.Add(param);

param = new OleDbParameter("_amplitudeRampID",
segment.AmplitudeRampValue);
m_command.Parameters.Add(param);

param = new OleDbParameter("_opCode", segment.OpCodeValue);
m_command.Parameters.Add(param);


// open the connection and execute the update
m_connection.Open();
m_command.ExecuteNonQuery();
<DalCode>


I have NO idea what is causing this. I have never seen something like this
before. Has anyone here seen this? Any ideas?

Thanks for reading!
Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top