Answered by:
Issue when using a stored procedure that inserts data and returns rows to write a file with a dataset

Question
-
User1733860820 posted
Code has been in place for years that calls an sp and then writes the result set to a file on disk and also creates a file to download in the browser. When I added logging to the sp that is called, I found that the sp seems to be invoked twice. I'm not sure but my guess is that it is executed a second time when the dataset is populated. I do need to pass a dataset to WriteDT() (not included in the code below). First am I right that sda.fill(ds) executes the sp again? And if so how can I get a dataset without re-running the sp?
conn.Open()
With cmd
.CommandType = CommandType.StoredProcedure
End With
cmd.ExecuteNonQuery()Dim sda As New SqlDataAdapter(cmd)
sda.Fill(ds)
dtSourceTable = ds.Tables(0)strExportFilePath = "C:\Inetpub\wwwroot\FileUpload\Export.csv"
Using writer As StreamWriter = New StreamWriter(strExportFilePath)
WriteDT(dtSourceTable, writer, True, "1234")
End Using
Response.Clear()
Response.AddHeader("content-disposition", "attachment;filename=Export.csv")
Response.End()Wednesday, November 14, 2018 3:21 AM
Answers
-
User753101303 posted
sevi
First am I right that sda.fill(ds) executes the sp again?Correct. The purpose of the "Adapter" is to use a command to read data in a datatable or dataset (and possibly to update the db from a dataset/datatable).
sevi
And if so how can I get a dataset without re-running the sp?The problem is rather it seems you think you have to run it first using cmd.ExecuteNonQuery() ? It jusr run it one time without doing anything with the resultset. Just drop this line.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 14, 2018 12:38 PM
All replies
-
User475983607 posted
Yes, the code executes the command (cmd) twice.
Once
cmd.ExecuteNonQuery()
Twice
Dim sda As New SqlDataAdapter(cmd) sda.Fill(ds)
Wednesday, November 14, 2018 12:25 PM -
User753101303 posted
sevi
First am I right that sda.fill(ds) executes the sp again?Correct. The purpose of the "Adapter" is to use a command to read data in a datatable or dataset (and possibly to update the db from a dataset/datatable).
sevi
And if so how can I get a dataset without re-running the sp?The problem is rather it seems you think you have to run it first using cmd.ExecuteNonQuery() ? It jusr run it one time without doing anything with the resultset. Just drop this line.
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Wednesday, November 14, 2018 12:38 PM -
User1733860820 posted
Thanks to both esp PatriceSc; I thought ExecuteNonQuery was wrong, but somehow it never occurred to me that the dataset fill in itself was sufficient.
Wednesday, November 14, 2018 3:58 PM