First INSERT in the transaction is not being committed

Hi All
I am having a strange problem. I have an excel sheet from which I fetch
the data using ADODB. I hava a loop which runs through all rows in the
excel sheet and calls a stored procedure to insert the rows into the
database.
The problem is when the statement oConn.CommitTrans is called, the
first row is not being inserted into the database.
But I am able to see the call to the stored procedure in the SQL
Profiler for the first row in addition to the other rows.
One more thing is, in the SQL Profiler, after the call to the stored
procedure for the first row, I am seeing the following:
SET NO_BROWSETABLE ON
SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
set fmtonly off
SET NO_BROWSETABLE OFF
Please help me.
Thanks in advanceI think that SET FMTONLY ON is doing no results appearing. Put on
"hbase19-all@.yahoo.co.in" wrote:

> Hi All
> I am having a strange problem. I have an excel sheet from which I fetch
> the data using ADODB. I hava a loop which runs through all rows in the
> excel sheet and calls a stored procedure to insert the rows into the
> database.
> The problem is when the statement oConn.CommitTrans is called, the
> first row is not being inserted into the database.
> But I am able to see the call to the stored procedure in the SQL
> Profiler for the first row in addition to the other rows.
> One more thing is, in the SQL Profiler, after the call to the stored
> procedure for the first row, I am seeing the following:
> SET NO_BROWSETABLE ON
> SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
> set fmtonly off
> SET NO_BROWSETABLE OFF
> Please help me.
> Thanks in advance
>|||It looks like your XL sheet does not have a header row. Try adding a header
row. SET FMTONLY will only return meta-data to the client and it looks like
this call is being made to determine the structure.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<hbase19-all@.yahoo.co.in> wrote in message
news:1136794436.468218.270140@.g47g2000cwa.googlegroups.com...
> Hi All
> I am having a strange problem. I have an excel sheet from which I fetch
> the data using ADODB. I hava a loop which runs through all rows in the
> excel sheet and calls a stored procedure to insert the rows into the
> database.
> The problem is when the statement oConn.CommitTrans is called, the
> first row is not being inserted into the database.
> But I am able to see the call to the stored procedure in the SQL
> Profiler for the first row in addition to the other rows.
> One more thing is, in the SQL Profiler, after the call to the stored
> procedure for the first row, I am seeing the following:
> SET NO_BROWSETABLE ON
> SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
> set fmtonly off
> SET NO_BROWSETABLE OFF
> Please help me.
> Thanks in advance
>|||Thanks for the reply
The line "SET FMTONLY ON" is appearing automatically. I am not calling
that statement.
Also I found in ms web site that the statement "SET FMTONLY ON" starts
an implicit transaction even if we call "SET implicit_transactions
off". So they suggested MDAC 2.6 latest service pack. I did that. But
even then the problem is persistent.
I copied the series of statements (from BEGIN TRANSACTION to COMMIT
TRANSACTION) from SQL profiler and ran them in SQL Query Analyzer. An
error occurred saying
"The COMMIT TRANSACTION request has no corresponding BEGIN
TRANSACTION".
So I commented out the lines
SET NO_BROWSETABLE ON
SET FMTONLY ON EXEC spInsert ' ', ' ' SET FMTONLY OFF
set fmtonly off
SET NO_BROWSETABLE OFF
and ran the TRANSACTION statements. It worked fine. So the problem
might be in those 4 lines.
Please help me