none
QueriesTableAdapter does not execute Stored Procedure but does not produce Exception. RRS feed

  • Question

  • I am having an issue using a Dataset and the QueriesTable Adapter to call a Stored Procedure on an instance on another box.  However, I have no issues if I use a DataReader approach.  I wanted to use the QueriesTableAdapter because it requires less code and seems like the newer approach.

    The calling application is a Windows Service.  When testing locally, I use SQL Authentication, but when promoted to the working environment, I want to use Windows Authentication, or "Integrated Security=True".  The SQL ConnectionString was created automatically in the app.config when I added the Dataset (using SQL Authentication), so I figured I could just adjust the app.config to switch from SQL to Integrated.

    However, what I found was that it works fine (using the QueriesTableAdapter) as long as the SQLConnectionString in the app.config is exactly as it was in my local environment.  If I make any change to it, the SP does not run... but I don't get an error or Exception either!  Even if I intentionally change the servername/instance name to try and "force" a connection error, I still get no SP execution and no error or Exception.

    When I switch to using a DataReader... everything works as expected.  I can switch from SQL Autentication to Ingegrated without issue, and I get the expected Exceptions if I intentionally break the Connection String.  All of this I can do simply by adjusting the app.config SQLConnectionString.

    I have code snippets for both methods below.  I cannot understand why the QuerisTableAdapter approach does not seem to work by adjusting app.config, but doesn't yeild errors/Exceptions either.  Has anyone seen anything like this before or point me in the right direction to use a QueriesTableAdpater for this?

    The SP has 2 input parameters and one input/output parameter, and is called procTS360SetBasketOneClickMARC.  The Dataset is called OrdersDataSet, and the ConnectionString is named OrdersConnectionString.

    Using Dataset and QueriesTableAdapter:

                Dim tbladaptr As New OrdersDataSetTableAdapters.QueriesTableAdapter

                tbladaptr.procTS360SetBasketOneClickMARC(BasketSummaryID, FTPErrorMessage, "OK")

                'Note that the below values are made accessible by creating a Partial Class related to the DataSet. 
                retvalue = tbladaptr.GetReturnValue(0)
                errMess = tbladaptr.GetErrorMessage(0)

    Using Reader:

            Dim sqlConnection1 As New SqlConnection(My.Settings.OrdersConnectionString)
            Dim cmd As New SqlCommand
            Dim reader As SqlDataReader = Nothing
            Dim messParam As New System.Data.SqlClient.SqlParameter
            Dim retvalParam As New System.Data.SqlClient.SqlParameter
     Dim strStatBox as String = "" 'String to hold the return value and return error message

            Try
                cmd.CommandText = "procTS360SetBasketOneClickMARC"
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("BasketSummaryID", SqlDbType.VarChar)
                cmd.Parameters("BasketSummaryID").Value = BasketSummaryID

                cmd.Parameters.Add("FTPErrorMessage", SqlDbType.VarChar)
                cmd.Parameters("FTPErrorMessage").Value = FTPErrorMessage

                messParam.Direction = ParameterDirection.InputOutput
                messParam.ParameterName = "ErrorMessage"
                messParam.SqlDbType = SqlDbType.VarChar
                messParam.Size = 8000
                messParam.Value = "Sender Says=OK"

                cmd.Parameters.Add(messParam)

                retvalParam.Direction = ParameterDirection.ReturnValue
                retvalParam.SqlDbType = SqlDbType.Int
                cmd.Parameters.Add(retvalParam)

                cmd.Connection = sqlConnection1


                sqlConnection1.Open()
                reader = cmd.ExecuteReader
                ' Data is accessible through the DataReader object here.

                If Not IsNothing(messParam.Value) Then
                    strStatBox = strStatBox & "Return Message = " & messParam.Value.ToString & ". "
                End If

                If Not IsNothing(retvalParam.Value) Then
                    strStatBox = strStatBox & "Return Value = " & retvalParam.Value.ToString & "."
                End If

                reader.Close()

                sqlConnection1.Close()

     


    mp

    Wednesday, June 12, 2013 3:05 PM

Answers

  • After some adjustements to the SP for its ReturnValue and ErrorMessage, I went back and retested using the QueriesTableAdapter approach and as of now it is working perfectly and predictably.  Either method works fine, so I will go with the more elegant QueriesTableAdapter approach.

    Frankly, I have no idea what was happening yesterday when I was getting such bizzare results, but now it is working fine.


    mp

    • Marked as answer by MarvinPerkins Wednesday, June 12, 2013 4:46 PM
    Wednesday, June 12, 2013 4:45 PM

All replies

  • After some adjustements to the SP for its ReturnValue and ErrorMessage, I went back and retested using the QueriesTableAdapter approach and as of now it is working perfectly and predictably.  Either method works fine, so I will go with the more elegant QueriesTableAdapter approach.

    Frankly, I have no idea what was happening yesterday when I was getting such bizzare results, but now it is working fine.


    mp

    • Marked as answer by MarvinPerkins Wednesday, June 12, 2013 4:46 PM
    Wednesday, June 12, 2013 4:45 PM
  • Hi Marvin,

    I'm glad to hear that it works now.

    Have a nice day!

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, June 14, 2013 8:06 AM
    Moderator