-
Feb 13th, 2021, 01:55 PM
#1
Thread Starter
New Member
[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.
-
Feb 13th, 2021, 07:56 PM
#2
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
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Feb 13th, 2021, 09:42 PM
#3
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:
Public Class taxYearTV
Public Shared yearConnect As String
Public Function GetYears(bType As String,
rType As Integer,
serverName As String,
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 yearDataAdapter As New SqlDataAdapter("p_taxYearbyBillType", yearConnect)
With yearDataAdapter.SelectCommand
.CommandType = CommandType.StoredProcedure
With .Parameters
.Add("@v_bType", SqlDbType.VarChar, 50).Value = bType
.Add("@i_rid", SqlDbType.Int).Value = rType
End With
End With
'fill datatable using the data adapter
yearDataAdapter.Fill(yearTable)
yearConnect = Nothing
Return yearTable
End Function
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.
-
Feb 13th, 2021, 09:44 PM
#4
Re: VB.NET Public Class returns different results than SQL Server Stored Procedure
Originally Posted by .paul.
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.
-
Feb 14th, 2021, 02:24 PM
#5
Thread Starter
New Member
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
-
Feb 15th, 2021, 04:27 PM
#6
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|