1

I have a stored procedure that gets data from a table depending on the parameter being passed in:

SELECT * 
FROM [dbo].Table1 
WHERE Column1 = @Param1 AND Column2 = @Param2

Now, I'm calling the SQL Server stored procedure through VB.Net function with this code:

Public Function GetSPValues(ByVal Param1 As Integer,
                            ByVal Param2 As String) As DataTable
    Dim sqlCon As SqlConnection
    Dim dt As DataTable = New DataTable()
    sqlCon = strCon

    Using (sqlCon)
        Dim cmd As New SqlCommand
        cmd.Connection = sqlCon
        cmd.CommandText = "GetSP"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@Param1", Param1)
        cmd.Parameters.AddWithValue("@Param2", Param2)
        sqlCon.Open()

        Dim reader As SqlDataReader = cmd.ExecuteReader()
        dt.TableName = "SPValuesTable"
        dt.Load(reader)
        Return dt
   End Using
End Function

The thing is when I try to execute the stored procedure in SQL Server Management Studio, it works properly and returns all the data I need.

But when I try to call from VB.NET, it doesn't return anything at all.

I'm not sure if the parameters I'm passing are really being to the stored procedure. Would you help me with this?

Update: This is my whole SP:

ALTER PROCEDURE [dbo].[GetSP]
    @Param1 int = null,
    @Param2 nvarchar(50) = null 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT * FROM dbo.tbl1 WHERE Column1 = @Param1 AND Column2 
    = @Param2 
END
GSerg
  • 76,472
  • 17
  • 159
  • 346
Ann
  • 61
  • 7
  • 2
    You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... – marc_s Jul 30 '20 at 15:08
  • Also, it would be helpful see the the **whole** stored procedure - including it's "signature" (header, with the parameter definition) – marc_s Jul 30 '20 at 15:19
  • I did the changes with AddWithValue() but still the same. Just want to add, this vb.function is in Web Service where I invoke through a client ASP.Net GridView – Ann Jul 30 '20 at 15:28
  • Questions: 1) Are you sure that your VB isn't throwing an exception? 2) How are you checking for the data returned? 3) Does "it doesn't return anything" mean A) it returns an empty datatable or B) it literally returns `Nothing` in VB? – RBarryYoung Jul 30 '20 at 15:35
  • Maybe you wanted to write something like `Using sqlCon As New SqlConnection(strCon)` instead? This: `Dim sqlCon As SqlConnection [...] sqlCon = strCon` doesn't make much sense. If you have a thing like `strCon` in your code, that parachutes from nowhere, try to specify what that is or, better, pass it as a method parameter, so the type is included. – Jimi Jul 30 '20 at 15:54
  • @Jimi, strCon is just the string connection coming from the Configuration Manager – Ann Jul 30 '20 at 16:49
  • @RBarryYoung, no it doesn't throw any exceptions. I'm debugging the code. I'm using dataset visualizer so I can see if the [dt] > datatable is returning anything. And it's empty. I can only see the column names but no data. – Ann Jul 30 '20 at 16:57
  • 2
    Since `sqlCon` is a string, how does this: `sqlCon = strCon` make sense? Use the code I posted before. Since you're there, go to your Visual Studio's `Options -> Project and Solutions -> VB default` and set `Option Strict On` permanently. You'll be able to solve a lot of problems *on the spot* after. – Jimi Jul 30 '20 at 16:59
  • @Jimi, this is how I declared it: Dim strCon As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectString1").ConnectionString) – Ann Jul 30 '20 at 17:04
  • Make up your mind: *strCon is just the string connection coming from the Configuration Manager*. A Connection String is not a Connection object. You neither declare or store a Connection object like that. Use the code I posted before, passing in a Connection **String** as shown. BTW, follow the suggestions about `AddWithValue()`, you'll be grateful for that in the future. – Jimi Jul 30 '20 at 17:06
  • @Jimi, I already did follow about AddWithValue() as you can see the above comment I did. I used the using statement if that's what you meant. But the thing is nothing change. When I try to invoke the Webservice itself, it returns an xml with values, now I don't know why when I call the function through debugging coming from the client application which is the binding the gridview, it doesn't return any data – Ann Jul 30 '20 at 17:21
  • 1
    When it's for stored procedures, can we stop banging on about "Can we stop using AddWithValue already" already, like it's the most important thing they have to fix right now? For parameters-in-queries, sure.. But for sprocs.. – Caius Jard Jul 30 '20 at 20:10
  • I wasn't able to reproduce your complaint: https://i.stack.imgur.com/OOGKr.png - even putting delibarately and obviously wrongly typed data into AddWithValue, like `1.0` and `"a"c`, the proc still returns data.. https://i.stack.imgur.com/SjUU4.png Use the debugger and make absolutely sure that your parameters have the values you expect, the right DB is being queried, that it definitely has data etc... – Caius Jard Jul 30 '20 at 20:32
  • Have you tried to enumerate the datatable? I know that there are unobvious cases where data objects sourced from a datareader will not actually fill the datarows in until you try to access/enumerate the datarows and I cannot recall if `Datatable.Load(reader)` is one of them. When this happens, looking at .Count or the rowset with debug Watch usually won't show anything (ie., 0 rows). – RBarryYoung Jul 31 '20 at 13:54
  • The other thing that you can try is to remove the `SET NOCOUNT ON;` from your procedure. I know that ADO.NET generally and DataReaders specifically are not supposed to need the row count returned, but it's something to try. – RBarryYoung Jul 31 '20 at 13:57
  • Quick question, are you sure that the values you’re setting in the params doe match you data? I’m meaning like matching case, white space that type of thing – Hursey Aug 02 '20 at 07:09

0 Answers0