Results 1 to 6 of 6

Thread: [RESOLVED] VB.NET Public Class returns different results than SQL Server Stored Procedure

  1. #1

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Location
    Louisiana, US
    Posts
    8

    Resolved [RESOLVED] VB.NET Public Class returns different results than SQL Server Stored Procedure

    I have written a Public Function to retrieve data from a SQL Sever database and use the data in a WebControl.treeview. The Public Function is below. I'm using a command type of stored procedure and I send criteria to the stored procedure from code behind of an asp page. If I run the stored procedure using specific criteria from the SQL Server database, it returns 10 records, but when the stored procedure is run from the Public Function using the same specific criteria, it returns only 8 records.
    I have checked permissions in the database as well as permissions within the ASP application and they match. I checked the code behind to ensure that I'm not applying any filtering and I found none. Can anyone review the Function below and see where I may have written something incorrectly? If more information is required, please respond.

    Code:
    Imports System.IO
    Imports System.Data.SqlClient
    Public Class taxYearTV
        Public Shared yearConnect As String
        Public Function GetYears(ByVal bType As String, ByVal rType As Integer, ByVal serverName As String, _
                                       ByVal dataBase As String) As DataTable
    
            If idCount = 1 Then
                yearConnect = mySqlConnection.pConnect(serverName, dataBase)
            End If
    
            ' execute stored procedure to retreive years
            ' place results in a temporary data table
            Dim yearTable As New DataTable
            Dim yearConnection As New SqlConnection(yearConnect)
            Dim yearDataAdapter As New SqlDataAdapter
            yearDataAdapter.SelectCommand = New SqlCommand("p_taxYearbyBillType", yearConnection)
            With yearDataAdapter.SelectCommand
                .CommandType = CommandType.StoredProcedure
                .Parameters.Add("@v_bType", SqlDbType.VarChar)
                .Parameters(0).Value = bType
                .Parameters.Add("@i_rid", SqlDbType.Int)
                .Parameters(1).Value = rType
            End With
    
            'fill datatable using the data adapter
            yearDataAdapter.Fill(yearTable)
            yearConnect = Nothing
    
            Return yearTable
        End Function
    End Class
    Last edited by gpNovice; Feb 13th, 2021 at 01:58 PM.

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,458

    Re: VB.NET Public Class returns different results than SQL Server Stored Procedure

    I can't answer your question, but i can point you towards the ASP.Net Forum

    https://www.vbforums.com/forumdispla...d-ASP-NET-Core

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB.NET Public Class returns different results than SQL Server Stored Procedure

    There's obviously something different between the way you're executing the stored procedure in each case but we can't really see much from what you've provided. That code will do what it does. I would say two things about the code you've provided:

    1. What's the value of yearConnect if idCount is not 1?
    2. That code could be a LOT more succinct:
    vb.net Code:
    1. Public Class taxYearTV
    2.  
    3.     Public Shared yearConnect As String
    4.  
    5.     Public Function GetYears(bType As String,
    6.                              rType As Integer,
    7.                              serverName As String,
    8.                              dataBase As String) As DataTable
    9.         If idCount = 1 Then
    10.             yearConnect = mySqlConnection.pConnect(serverName, dataBase)
    11.         End If
    12.  
    13.         ' execute stored procedure to retreive years
    14.         ' place results in a temporary data table
    15.         Dim yearTable As New DataTable
    16.         Dim yearDataAdapter As New SqlDataAdapter("p_taxYearbyBillType", yearConnect)
    17.  
    18.         With yearDataAdapter.SelectCommand
    19.             .CommandType = CommandType.StoredProcedure
    20.  
    21.             With .Parameters
    22.                 .Add("@v_bType", SqlDbType.VarChar, 50).Value = bType
    23.                 .Add("@i_rid", SqlDbType.Int).Value = rType
    24.             End With
    25.         End With
    26.  
    27.         'fill datatable using the data adapter
    28.         yearDataAdapter.Fill(yearTable)
    29.         yearConnect = Nothing
    30.  
    31.         Return yearTable
    32.     End Function
    33. End Class
    Notice that I have specified the size for the parameter of type varchar too. If you don't, it may work or it may not.

    As I said though, that code will do what it does and it shouldn't do anything else, so how are we supposed to tell you why it doesn't do some other specific thing? You need to provide a FULL and CLEAR explanation of the problem. That would include the data you have, the data you expect, how you're executing the sproc in SSMS, what values you're using here, etc. I would also suggest that you doubly confirm that you're connecting to the correct database. Even if you think you have the right connection string, if it's still not working then you can do a 'SELECT *' query on a table with a specific value you just added and see if it is included in the result set. If it's not then you're not connecting to the right database. I've seen that solve the problem before when someone swore blue in the face that they were connecting to the right database.

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,274

    Re: VB.NET Public Class returns different results than SQL Server Stored Procedure

    Quote Originally Posted by .paul. View Post
    I can't answer your question, but i can point you towards the ASP.Net Forum
    I don't think that this really has anything to do with ASP.NET. There's nothing in that code that would change if it were any other type of application. If we were to move it, I think that Database Development would be the place.

  5. #5

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Location
    Louisiana, US
    Posts
    8

    Re: VB.NET Public Class returns different results than SQL Server Stored Procedure

    First, thank you for taking an interest in my issue. I will try to provide the info you need and at the same time not give you more than you need.
    I currently have a VB.net application that I use to collect data for a couple of business ventures I have and I'm making an attempt to convert the VB.net app to an ASP.net app.
    I have been collecting this data from 1995 to present. In order to eliminate yearly data, I have a column in one of my database tables, "showYear" that has a data type of "bit" and it allows me to eliminate those
    earlier years that I no longer want to see in my application. I then created a database View to retrieve all of the records for the years that I collected data. I do not filter anything out.
    The Select statement for that view is below.
    Code:
    SELECT DISTINCT TOP (100) PERCENT dbo.taxYear_tbl.YEAR_ID, dbo.taxYear_tbl.TAX_YEAR, dbo.taxYear_tbl.showYear, UtilityBills.dbo.utilityBill_tbl.TYPE_ID, dbo.stateRegion_tbl.region_id
    FROM            dbo.taxYear_tbl INNER JOIN
                             UtilityBills.dbo.utilityBill_tbl ON dbo.taxYear_tbl.TAX_YEAR = UtilityBills.dbo.utilityBill_tbl.YEAR_ID INNER JOIN
                             dbo.bilTypel_tbl ON UtilityBills.dbo.utilityBill_tbl.TYPE_ID = dbo.bilTypel_tbl.BILL_TYPE INNER JOIN
                             dbo.stateRegion_tbl ON UtilityBills.dbo.utilityBill_tbl.REGION_ID = dbo.stateRegion_tbl.region_id
    ORDER BY dbo.taxYear_tbl.TAX_YEAR
    I then use a sproc to retrieve specific data from the above View. The sproc contains parameters that are provided from the application. The sproc is below.
    Code:
    USE [UtilityBills]
    GO
    /****** Object:  StoredProcedure [dbo].[p_taxYearbyBillType]    Script Date: 2/14/2021 12:44:50 PM ******/
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[p_taxYearbyBillType](@v_bType varchar(50), @i_rid int)
    
    AS
    
    /****this procedure is used to retrieve tax year****/
    SELECT *
    FROM commonDB.dbo.v_taxYearbyBillType
    WHERE type_id=@v_bType AND region_id=@i_rid AND showYear = 1
    ORDER BY type_id, TAX_YEAR ASC
    When I run the sproc from SQL Server,
    I select "Execute Stored Procedure' and then enter the following values type_id ="Appartment Rent", region_id= 2 as the parameters and it provides the following records.
    As you can see it contains data from 2012 to 2021. This data is accurate.

    Code:
    YEAR_ID	TAX_YEAR	showYear	TYPE_ID	region_id
    18	2012	1	Appartment Rent	2
    23	2013	1	Appartment Rent	2
    24	2014	1	Appartment Rent	2
    25	2015	1	Appartment Rent	2
    26	2016	1	Appartment Rent	2
    27	2017	1	Appartment Rent	2
    28	2018	1	Appartment Rent	2
    29	2019	1	Appartment Rent	2
    30	2020	1	Appartment Rent	2
    31	2021	1	Appartment Rent	2
    When I run the asp.net application I populate the webcontrol.treeview using the following vb code. This line of code " cTable = taxYearDA.GetYears(cNode.Text, rID, GlobalDeclarations._sValue, GlobalDeclarations._databaseValue)" is what executes the Class that runs the sproc "p_taxYearByBillType".
    This class is run for every bill type that I have.

    Code:
    Private Sub PopulateTreeView()
            Dim billTypeDA As New billTypeDB
            Dim billTypeREG As New billTypeByRegion
            Dim taxYearDA As New taxYearTV
            Dim taxYearVen As New taxYearVendorWeb
            Dim billTypes As New utilityBillTypeClass
            Dim regionNames As New stateRegionClass
            Dim years As New taxYearClass
            Dim eTree As TreeView
            Dim tn As New TreeNode("Bill Types")
            Dim bTable As DataTable
            Dim eRow As DataRow
    
            Try
                ' send stored procedure name to class
                Dim _spName As String = "p_stateRegion_webList"
    
                ' Louisiana menu item
                If GlobalDeclarations._LAitem = 2 Then
                    GlobalDeclarations._regionID = 2
                    ' Mississippi menu item
                ElseIf GlobalDeclarations._LAitem = 3 Then
                    GlobalDeclarations._regionID = 3
                    ' Grocery by year menu item
                ElseIf GlobalDeclarations._LAitem = 5 Then
                    ' Grocery by Vendor menu item
                    GlobalDeclarations._regionID = 2
                ElseIf GlobalDeclarations._LAitem = 6 Then
                    GlobalDeclarations._regionID = 2
                End If
    
                ' assign datatable variable to billtypeDB class
                bTable = billTypeREG.GetTypes(GlobalDeclarations._regionID, _spName, GlobalDeclarations._sValue, GlobalDeclarations._databaseValue)
    
                eTree = Me.svTreeView
                eTree.Nodes.Add(tn)
    
    
                ' loop through types temporary data table to retireve types for treeview
                For Each eRow In bTable.Rows
                    Dim pNode As New TreeNode
                    Dim rNode As New TreeNode
    
                    regionNames.regionid = eRow.Item(0)
                    Dim rID As Integer = regionNames.regionid
    
                    regionNames.regionName = eRow.Item(1)
                    Dim bName As String = regionNames.regionName
                    pNode.Text = CStr(bName)
                    tn.ChildNodes.Add(pNode)
                    GlobalDeclarations._sNewTag = "_1"
    
                    pNode.Expand()
    
                    ' set variable to reference temporary data table
                    ' temporary data table is created in the entityChildDB class
                    ' and passed to this event
                    Dim cTable As New DataTable
                    Dim btTable As New DataTable
    
                    ' this is for the server data table
                    Dim rw As DataRow
                    Dim yw As DataRow
    
                    ' send stored procedure name to class
                    If GlobalDeclarations._LAitem = 2 Or GlobalDeclarations._LAitem = 3 Then
                        _spName = "p_billTypeByRegionWEBapp"
                        ' Grocery by year menu item
                    ElseIf GlobalDeclarations._LAitem = 5 Then
                        _spName = "p_BillTypeGroceryByRegionWEBapp"
                        ' Grocery by vendor menu item
                    ElseIf GlobalDeclarations._LAitem = 6 Then
                        _spName = "p_groceryByVendor_list"
                    End If
    
                    btTable = billTypeREG.GetTypes(rID, _spName, GlobalDeclarations._sValue, GlobalDeclarations._databaseValue)
    
                    Dim cList As String
                    ' loop through the records in temporary table
                    ' add the bill types to the treeveiw list
                    For Each rw In btTable.Rows
                        Dim cNode As New TreeNode
                        ' Grocery by vendor menu item
                        If GlobalDeclarations._LAitem = 6 Then
                            billTypes.typeName = rw.Item(0)
                        Else
                            billTypes.typeName = rw.Item(1)
                        End If
                        ' add type name
                        cList = billTypes.typeName
                        cNode.Text = cList
                        'GlobalDeclarations._sNewTag = "_2"
                        'cNode.Tag = sNewTag
                        pNode.ChildNodes.Add(cNode)
                        ' Grocery by vendor menu item
                        If GlobalDeclarations._LAitem = 6 Then
                            cTable = taxYearVen.GetYearsWeb(cNode.Text, GlobalDeclarations._sValue, GlobalDeclarations._databaseValue)
                        Else
                            cTable = taxYearDA.GetYears(cNode.Text, rID, GlobalDeclarations._sValue, GlobalDeclarations._databaseValue)
                        End If
    
                        Dim yCount = cTable.Rows.Count
                        Dim cCount = cTable.Columns.Count
                        ' add the years to the bill types
                        For Each yw In cTable.Rows
                            If GlobalDeclarations._LAitem = 6 Then
                                Dim yNode As New TreeNode
                                GlobalDeclarations._sNewTag = "_3"
                                'yNode.Tag = sNewTag
                                years.taxYear = yw.Item(0)
                                ' add year name
                                cList = years.taxYear
                                yNode.Text = cList
                                cNode.ChildNodes.Add(yNode)
                            ElseIf yw.Item(2) = True Then
                                Dim yNode As New TreeNode
                                GlobalDeclarations._sNewTag = "_3"
                                'yNode.Tag = sNewTag
                                years.taxYear = yw.Item(1)
                                ' add year name
                                cList = years.taxYear
                                yNode.Text = cList
                                cNode.ChildNodes.Add(yNode)
                            End If
                        Next
                    Next
                Next
    
            Catch ex As Exception
                ' the catch block where exception will be handled
                Dim script As String = ex.Message
                Page.ClientScript.RegisterStartupScript(Me.GetType(), "alert", "<script language=javascript>alert('" & script & "');</script>", False)
            End Try
    
        End Sub
    When I step thru the class code, I able to see the data in the sql data adpater and I have the results listed below. As you can see, it does not contain the years 2020 and 2021. That is my problem. It retrieves all of the years except 2020 and 2021.
    Code:
    	18	2012	True	Appartment Rent	2
    	23	2013	True	Appartment Rent	2
    	24	2014	True	Appartment Rent	2
    	25	2015	True	Appartment Rent	2
    	26	2016	True	Appartment Rent	2
    	27	2017	True	Appartment Rent	2
    	28	2018	True	Appartment Rent	2
    	29	2019	True	Appartment Rent	2
    You asked about the IDcount = 1. That indicates that I my logon ID is listed in the database and connection string is made. If you require additional information or you have further questions, please respond.
    Last edited by gpNovice; Feb 14th, 2021 at 02:29 PM. Reason: Correct errors

  6. #6

    Thread Starter
    New Member
    Join Date
    Jan 2021
    Location
    Louisiana, US
    Posts
    8

    Re: [RESOLVED] VB.NET Public Class returns different results than SQL Server Stored P

    Resolved my issue by changing the database connection string in the asp application. I only had the server name in the connection string. Resolved the issue by adding the "server name\database instance"
    Pervious connection String:
    "Data Source=Server Name;Initial Catalog=Database Name;User ID=******;Password=******"

    New connection String:
    Data Source=Server Name\Database Instance;Initial Catalog=Database Name;User ID=*****;Password=*****"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width