Could you...

[jamesfreddyc]

....walk me thru this very simple example?

Perhaps I am just not thinking this thru correctly, but my attempt to create
a DataAccess class that will accept generic SQLConnection, SQLCommandText,
and Parameters is not working. Ideally, I want to map the Classes to the
StoredProcedures (SQLServer2005), but really just need to get thru this first
one.

So, I have a Windows Form with 4 controls: 2 DateTimePickers, a Button, and
A DataGridView. The btnClick gets the 2 date values (done!) and then I'd
like to pass these 2 dates (parameters in the SQLCommand), and the
SQLConnection to the DataAccess class "clsSRCustomers".

As it stands, it compiles ok but throws an exception error in the btnClick
(so perhaps I am missing something simple here):

"Unable to cast object of type 'clsSRCustomers' to type
'System.Data.IDataReader'"

Please help me understand!!!

thanks,

j



'Windows form class
Public Class frmGetVCMSData
Private Sub btnGetVCMSData_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetVCMSData.Click

sDate1 = Me.DTPicker1.Value
sDate2 = Me.DTPicker2.Value

Dim theConn As New SqlConnection(GetConnectionString)
dr = New clsSRCustomers(theConn, sDate1, sDate2)

Dim custDT As DataTable = New DataTable
custDT.Load(dr)

MsgBox(custDT.Rows.Count & " Rows in custDT DataTable")

dr.Dispose()

Me.dg1.DataSource = custDT
Me.dg1.Refresh()

End If
End Sub


'clsSRCustomers Class
Public Class clsSRCustomers
Implements IDisposable
Private m_vcmsDataReader As SqlDataReader

Public Sub Dispose() Implements System.IDisposable.Dispose
' Perform termination
End Sub

Property vcmsDataReader() As SqlDataReader
Get
Return m_vcmsDataReader
End Get
Set(ByVal value As SqlDataReader)
m_vcmsDataReader = value
End Set
End Property
Public Sub New(ByVal pConn As SqlConnection, _
ByVal sD1 As Date, _
ByVal sD2 As Date)


'Dim SR_DataReader As SqlDataReader
Using pConn

Dim command As SqlCommand = New SqlCommand()
command.Connection = pConn
command.CommandText = "AO_GetServiceRequests"
command.CommandType = CommandType.StoredProcedure

' Add the input parameter and set its properties.
Dim parameter1 As New SqlParameter()
parameter1.ParameterName = "@Date1"
parameter1.SqlDbType = SqlDbType.DateTime
parameter1.Direction = ParameterDirection.Input
parameter1.Value = sD1

Dim parameter2 As New SqlParameter()
parameter2.ParameterName = "@Date2"
parameter2.SqlDbType = SqlDbType.DateTime
parameter2.Direction = ParameterDirection.Input
parameter2.Value = sD2

' Add the parameter to the Parameters collection.
command.Parameters.Add(parameter1)
command.Parameters.Add(parameter2)

pConn.Open()

m_vcmsDataReader = command.ExecuteReader()

End Using

End Sub

 

[jamesfreddyc]

Ok, I think I am getting close to figuring this out. Still throwing the same
exception, so perhaps it is something stupid I am missing.

Updated Code:

'Windows form
Private Sub btnGetVCMSData_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnGetVCMSData.Click

sDate1 = Me.DTPicker1.Value
sDate2 = Me.DTPicker2.Value

Dim d1 As String, d2 As String
d1 = Format(Convert.ToString(sDate1), "MM/DD/YYYY") & " " &
"12:00:01 AM"
d2 = Format(Convert.ToString(sDate2), "MM/DD/YYYY") & " " &
"11:59:59 PM"

Dim theConn As New SqlConnection(GetConnectionString)
Dim dr As New clsSRCustomers(theConn, sDate1, sDate2)

Dim custDT As DataTable = New DataTable
custDT.Load(dr)

MsgBox(custDT.Rows.Count & " Rows in custDT DataTable")

Me.dg1.DataSource = custDT
Me.dg1.Refresh()
dr.Dispose()

End If
End Sub

'clsSRCustomers
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataAdapter
Public Class clsSRCustomers
Implements IDisposable
Private m_vcmsDataReader As SqlDataReader
Private theConn As SqlConnection
Private frmDate As Date
Private toDate As Date

Public Sub Dispose() Implements System.IDisposable.Dispose
' Perform termination
End Sub
Public Property SRCustomerDataReader() As SqlDataReader
Get
Return m_vcmsDataReader
End Get
Set(ByVal value As SqlDataReader)
m_vcmsDataReader = value
End Set
End Property
Sub GetSRCustomers()
Using theConn

Dim command As SqlCommand = New SqlCommand()
command.Connection = theConn
command.CommandText = "AO_GetServiceRequests"
command.CommandType = CommandType.StoredProcedure

' Add the input parameter and set its properties.
Dim parameter1 As New SqlParameter()
parameter1.ParameterName = "@Date1"
parameter1.SqlDbType = SqlDbType.DateTime
parameter1.Direction = ParameterDirection.Input
parameter1.Value = frmDate

Dim parameter2 As New SqlParameter()
parameter2.ParameterName = "@Date2"
parameter2.SqlDbType = SqlDbType.DateTime
parameter2.Direction = ParameterDirection.Input
parameter2.Value = toDate

' Add the parameter to the Parameters collection.
command.Parameters.Add(parameter1)
command.Parameters.Add(parameter2)

theConn.Open()

m_vcmsDataReader = command.ExecuteReader()


End Using

End Sub
Public Sub New(ByVal pConn As SqlConnection, _
ByVal sD1 As Date, _
ByVal sD2 As Date)

theConn = pConn
frmDate = sD1
toDate = sD2
End Sub
End Class

 

[jamesfreddyc]

Figured it out.

I was close the second try.

And third try a charm!