wow, this is a good one :(

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
 
S

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
 
J

Jim Underwood

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

Steve

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