File System IO...Not All Records Written to Text File

[tjb2]

I am returning a recordset from SQL Stored Proc and writing them to a text file. All of the records are not getting written to the text file. Earlier I got 350 out of 386 and now I am getting 335 out of 386. It is almost as if the connection or session with the sql server is closing before I can write all of the records to the text file.

Can some one help? Below is the code I am using for my SQLReader:

Try
Me.Cursor = Cursors.WaitCursor

With SQLCmd

.CommandText = "ESH_AP_LoadPOSPay_sp"
.CommandType = CommandType.StoredProcedure
.Connection = SQLCon
.Parameters.AddWithValue("@dbname", sDBName)

End With

SQLReader = SQLCmd.ExecuteReader()

If SQLReader.HasRows = True Then
Do While (SQLReader.Read)
oWrite.WriteLine(SQLReader.Item(0) & " " & SQLReader.Item(1))
SQLReader.NextResult()
Loop


Else

End If


objPlsWait.CloseWaitScreen()
objPlsWait = Nothing
Me.Show()


Catch sqlex As SqlException

ShowError("A SQL error has ocurred. -- " & sqlex.Message & vbCrLf)

End Try

Thanks for any and all help.

SimpleMusings

 

[Sorwen]

No matter what is actually happening I always suggest dropping everything to a DataTable first then writing from that. Generally speaking there is no reason to leave a connection to a database open like that. You would be locking up the table for no benefit. If your connection is being dropped for some reason that might prevent it from being an issue (all depends on why it is dropped). Though the time it take to write to the text file should generally be insignificant at any given time (depending on how much data is actually written).

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[mansii]

Not sure about this. But have you tried executing the loop without [blue]SQLReader.NextResult()[/blue]?

Regards.

 

[tjb2]

Thanks mansii and Sorwen. Yes mansii I have tried to perform the operation without the NextResult.

Sorwen I will try the datatable. If that does not work then I will use BCP inside the sql.


Thanks,

SimpleMusings