issues with database class

[Scott Collens]

Hello,

I am having issues returning the results from the database using a data
access class. The class is called dbconnect.vb.

Imports System
Imports System.Data
Imports System.Data.SqlClient


Namespace articles

Public Class DBConnect
Public Shared Function GetReader(ByVal p_storProc As String) As
SqlDataReader

Dim errorMessage As String
Dim connection = New SqlConnection(DbUrl)

Try
connection.Open()

Dim command = New SqlCommand()
command.Connection = connection
command.CommandText = p_storProc
command.CommandType = CommandType.StoredProcedure

Dim dataReader = command.ExecuteReader
(CommandBehavior.CloseConnection)
Return dataReader

Catch e As Exception

errorMessage = e.ToString()

Finally
connection.Close()

End Try

End Function


Public Shared ReadOnly Property DbUrl() As String
Get
Return ConfigurationSettings.AppSettings("DbConnUrl")
End Get
End Property

End Namespace



I'm not getting any errors but the recordset is not being returned to the
page.

In a separate class file, mbl_plus.vb I am making an instance of the
dbconnect class and filling a data reader.

Both classes reside in a project called articles.

here is mlb_plus.vb

Imports System
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Imports articles
Imports System.Configuration
Imports System.Text.RegularExpressions


Public Class DisplayMLBPlus : Inherits UserControl
Private gdID1 As String
Private gdTitle1 As String
Private gdAuthor1 As String

Private gdID2 As String
Private gdTitle2 As String
Private gdAuthor2 As String

Private gdID3 As String
Private gdTitle3 As String
Private gdAuthor3 As String


Public ReadOnly Property ID1() As String
Get
Return gdID1
End Get
End Property

Public ReadOnly Property title1() As String
Get
Return gdTitle1
End Get
End Property

Public ReadOnly Property author1() As String
Get
Return gdAuthor1
End Get
End Property


Public ReadOnly Property ID2() As String
Get
Return gdID2
End Get
End Property

Public ReadOnly Property title2() As String
Get
Return gdTitle2
End Get
End Property

Public ReadOnly Property author2() As String
Get
Return gdAuthor2
End Get
End Property

Public ReadOnly Property ID3() As String
Get
Return gdID3
End Get
End Property

Public ReadOnly Property title3() As String
Get
Return gdTitle3
End Get
End Property

Public ReadOnly Property author3() As String
Get
Return gdAuthor3
End Get
End Property

Sub Page_Load()
If Not Page.IsPostBack Then

Dim newConn As DBConnect = New DBConnect()
Dim dtrGameday As SqlDataReader

Try
dtrGameday = newConn.GetReader("getMLBPlus")

Dim cc As Integer
cc = 0

Do While dtrGameday.Read()

Dim theID As String
Dim theTitle As String
Dim theAuthor As String

If cc = 0 Then
theID = dtrGameday("art_id")
gdID1 = theID

theTitle = dtrGameday("art_title")
gdTitle1 = theTitle

theAuthor = dtrGameday("art_author")
gdAuthor1 = theAuthor
ElseIf cc = 1 Then
theID = dtrGameday("art_id")
gdID2 = theID

theTitle = dtrGameday("art_title")
gdTitle2 = theTitle

theAuthor = dtrGameday("art_author")
gdAuthor2 = theAuthor
ElseIf cc = 2 Then
theID = dtrGameday("art_id")
gdID3 = theID

theTitle = dtrGameday("art_title")
gdTitle3 = theTitle

theAuthor = dtrGameday("art_author")
gdAuthor3 = theAuthor
End If

cc = cc + 1
Loop

Catch e As Exception
Dim errorMessage As String
errorMessage = e.ToString()
dtrGameday.Close()
Finally
dtrGameday.Close()
End Try


End If

End Sub


End Class


I've tested the stored procedure in Analyzer and the results are
returned. I've also moved the connection code out of the DBConnect class
and into the mlb_plus class and it worked as well. I'm quite confused by
this. I've added the dll reference to the project so I'm not sure what
the issue could be. Any help would be much appreciated. Thanks...Scott

[George Durzi]

search for "Application Blocks" on microsoft.com. Then download the Data
Access Application Blocks for .NET.

It's an excellent, prewritten, pretested data access class by Microsoft,
available in both vb.net and c#. I use it on all my projects. Never write
data access code again!


"Scott Collens" <scottcollens@nospam.hotmail.com> wrote in message
news:Xns93D0BC223498Bscottcollensnospamho@207.46.2 48.16...

> Hello,
>
> I am having issues returning the results from the database using a data
> access class. The class is called dbconnect.vb.
>
> Imports System
> Imports System.Data
> Imports System.Data.SqlClient
>
>
> Namespace articles
>
> Public Class DBConnect
> Public Shared Function GetReader(ByVal p_storProc As String) As
> SqlDataReader
>
> Dim errorMessage As String
> Dim connection = New SqlConnection(DbUrl)
>
> Try
> connection.Open()
>
> Dim command = New SqlCommand()
> command.Connection = connection
> command.CommandText = p_storProc
> command.CommandType = CommandType.StoredProcedure
>
> Dim dataReader = command.ExecuteReader
> (CommandBehavior.CloseConnection)
> Return dataReader
>
> Catch e As Exception
>
> errorMessage = e.ToString()
>
> Finally
> connection.Close()
>
> End Try
>
> End Function
>
>
> Public Shared ReadOnly Property DbUrl() As String
> Get
> Return ConfigurationSettings.AppSettings("DbConnUrl")
> End Get
> End Property
>
> End Namespace
>
>
>
> I'm not getting any errors but the recordset is not being returned to the
> page.
>
> In a separate class file, mbl_plus.vb I am making an instance of the
> dbconnect class and filling a data reader.
>
> Both classes reside in a project called articles.
>
> here is mlb_plus.vb
>
> Imports System
> Imports System.Web.UI
> Imports System.Web.UI.WebControls
> Imports System.Data
> Imports System.Data.SqlClient
> Imports articles
> Imports System.Configuration
> Imports System.Text.RegularExpressions
>
>
> Public Class DisplayMLBPlus : Inherits UserControl
> Private gdID1 As String
> Private gdTitle1 As String
> Private gdAuthor1 As String
>
> Private gdID2 As String
> Private gdTitle2 As String
> Private gdAuthor2 As String
>
> Private gdID3 As String
> Private gdTitle3 As String
> Private gdAuthor3 As String
>
>
> Public ReadOnly Property ID1() As String
> Get
> Return gdID1
> End Get
> End Property
>
> Public ReadOnly Property title1() As String
> Get
> Return gdTitle1
> End Get
> End Property
>
> Public ReadOnly Property author1() As String
> Get
> Return gdAuthor1
> End Get
> End Property
>
>
> Public ReadOnly Property ID2() As String
> Get
> Return gdID2
> End Get
> End Property
>
> Public ReadOnly Property title2() As String
> Get
> Return gdTitle2
> End Get
> End Property
>
> Public ReadOnly Property author2() As String
> Get
> Return gdAuthor2
> End Get
> End Property
>
> Public ReadOnly Property ID3() As String
> Get
> Return gdID3
> End Get
> End Property
>
> Public ReadOnly Property title3() As String
> Get
> Return gdTitle3
> End Get
> End Property
>
> Public ReadOnly Property author3() As String
> Get
> Return gdAuthor3
> End Get
> End Property
>
> Sub Page_Load()
> If Not Page.IsPostBack Then
>
> Dim newConn As DBConnect = New DBConnect()
> Dim dtrGameday As SqlDataReader
>
> Try
> dtrGameday = newConn.GetReader("getMLBPlus")
>
> Dim cc As Integer
> cc = 0
>
> Do While dtrGameday.Read()
>
> Dim theID As String
> Dim theTitle As String
> Dim theAuthor As String
>
> If cc = 0 Then
> theID = dtrGameday("art_id")
> gdID1 = theID
>
> theTitle = dtrGameday("art_title")
> gdTitle1 = theTitle
>
> theAuthor = dtrGameday("art_author")
> gdAuthor1 = theAuthor
> ElseIf cc = 1 Then
> theID = dtrGameday("art_id")
> gdID2 = theID
>
> theTitle = dtrGameday("art_title")
> gdTitle2 = theTitle
>
> theAuthor = dtrGameday("art_author")
> gdAuthor2 = theAuthor
> ElseIf cc = 2 Then
> theID = dtrGameday("art_id")
> gdID3 = theID
>
> theTitle = dtrGameday("art_title")
> gdTitle3 = theTitle
>
> theAuthor = dtrGameday("art_author")
> gdAuthor3 = theAuthor
> End If
>
> cc = cc + 1
> Loop
>
> Catch e As Exception
> Dim errorMessage As String
> errorMessage = e.ToString()
> dtrGameday.Close()
> Finally
> dtrGameday.Close()
> End Try
>
>
> End If
>
> End Sub
>
>
> End Class
>
>
> I've tested the stored procedure in Analyzer and the results are
> returned. I've also moved the connection code out of the DBConnect class
> and into the mlb_plus class and it worked as well. I'm quite confused by
> this. I've added the dll reference to the project so I'm not sure what
> the issue could be. Any help would be much appreciated. Thanks...Scott