wow, this is a good one :(

microsoft.public.dotnet.framework.adonet

4 Nov 2005 5:11 PM

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

4 Nov 2005 5:16 PM

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

Show quote

"Steve" <s**@sss.com> wrote in message
news:%231jCELW4FHA.472@TK2MSFTNGP15.phx.gbl...
> 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
>
>

4 Nov 2005 6:17 PM

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])

Show quote

"Steve" <s**@sss.com> wrote in message
news:edyV$NW4FHA.3904@TK2MSFTNGP15.phx.gbl...
> 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" <s**@sss.com> wrote in message
> news:%231jCELW4FHA.472@TK2MSFTNGP15.phx.gbl...
> > 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
> >
> >
>
>

4 Nov 2005 6:43 PM

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

Show quote

"Jim Underwood" <james.underw***@fallonclinic.com> wrote in message
news:uWv9IwW4FHA.1188@TK2MSFTNGP12.phx.gbl...
> 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" <s**@sss.com> wrote in message
> news:edyV$NW4FHA.3904@TK2MSFTNGP15.phx.gbl...
> > 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" <s**@sss.com> wrote in message
> > news:%231jCELW4FHA.472@TK2MSFTNGP15.phx.gbl...
> > > 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
> > >
> > >
> >
> >
>
>