How to execute a stored procedure containing INSERT and SELECT both statements

Question

In my stored procedure, first i do INSERT query and then have SELECT statement as follow:

        INSERT ()

        SELECT * FROM preferences WHERE ID=@ID

In ADO.NET, i have used - datareader to execute this procedure.

But, it does not return the SELECT value and not even executed INSERT statemnt.

Can any one suggest me what is wrong here ???

CODE:

             strSPROC = "sproc_SaveAndRetrieve"
            Using cmd As New Data.SqlClient.SqlCommand
                cmd.CommandText = strSPROC
                cmd.CommandType = Data.CommandType.StoredProcedure
                cmd.Parameters.Clear()
                cmd.Parameters.AddWithValue("@tvp", CreateDatatableFromCollection(s, isValueOn))
                cmd.Parameters.AddWithValue("@GroupID", syGroupID)

                Using myReader As SqlDataReader = DBManager.GetDataReader(cmd)
                    If myReader.HasRows Then
                        NotFoundRecords= New List(Of String)
                        While myReader.Read()
                            NotFoundRecords.Add(DataHelper.GetString(myReader, "Student"))
                        End While
                    End If

                    Return NotFoundRecords
                    myReader.Close()
                End Using
            End Using

Thank You

Procedure is working file while executing from SQL SERVER manually.

Please show the code used in your net application. ExecuteReader executes the StoredProcedure and should return the resultset originated by the last SELECT statement — Steve, Jun 24 '14 at 14:36

paparazzo · Answer 1 · 2014-06-24T18:18:51.857

1

If that is not even executing the insert then you need to catch the error.

try 
{
    ...
}
catch (SqlException ex) 
{
    Debug.WriteLine(ex.message);
}
finally 
{
    // close connection
}

The select should be in NextResultSet but need to first fix the insert.

You don't show DataHelper.GetString.
That insert would return an integer not a string.
I don't believe your statement that no exception is thrown.
.NET will report a SQL error.
Even if that insert effected 0 rows then you should get 0.
If it fails then you should get an exception.
If it is inserting rows in SSMS but not in .NET (and not throwing an error) then you are not executing the same parameters in .NET.

What does DataHelper.GetString(myReader, "Student")) do?
NotFoundRecords is just a list of string.

This is highly suspicious
That first statement should return an int

While myReader.Read()
    NotFoundRecords.Add(DataHelper.GetString(myReader, "Student"))
End While

I would expect something like this

myReader.Read()
int rowInserted = myReader.GetInt32()
myReader.NextResultSet()
while myReader.Read()
  string sss = myReader.GetString()
End While
myReader.Close()

edited Jun 24 '14 at 18:18

answered Jun 24 '14 at 14:50

paparazzo

44,497
23
105
176

Why the down vote? The insert is not even executed. That statement is failing. How else are you going to determine the cause of the failure? – paparazzo Jun 24 '14 at 15:32
Procedure is working file in SQL server and no exception in .NET. I have updated question with details. – user3711357 Jun 24 '14 at 15:39
If that is failing in .NET then .NET would be throwing an error. A procedure does not fail silently. Did you try a try catch as I indicated in my answer? I don't see where you are assigning a connection let alone opening the connection. Is that you that down voted? – paparazzo Jun 24 '14 at 15:45
No, I have not down voted. Connection works properly as same helper method work for all other methods. And I am not understand, why procedure works completely fine when do - `EXECUTE [PROCEDURE] [PARAM]` but not work from .NET, (not giving any error but no data insertion orccured or no data retrieval). – user3711357 Jun 24 '14 at 16:00
To be clear you have tested that in a try catch as I have shown? Did you read that last paragraph. Insert would be returning and Int. If you are trying to read a string that should throw an error. Statements do not fail silently in .NET. – paparazzo Jun 24 '14 at 16:07

How to execute a stored procedure containing INSERT and SELECT both statements

1 Answers1