Datareader problem...

L

lidds

Well-known member
Joined
Nov 9, 2004
Messages
210
I need some help as I cant seem to see whats wrong, probably to tired or something :confused:

What I have got is a class that holds my connection / update etc code for my project, as shown below:

Code: 
Public Class DBAccess

    Private connStr As String = Nothing

    Private myConn As New OleDb.OleDbConnection()

     Here have

    Public Sub New()
         checks what type of connection mode ICAS is in e.g. IP or server name
        If ICASData.Instance.ConnectionMode = "ServerName" Then
            connStr = ICASData.Instance.ICASConnStr
        Else
            connStr = ICASData.Instance.ProjConnStr
        End If
    End Sub New

    Public Function Connect() As Boolean
         connect to database
        myConn.ConnectionString = connStr
        myConn.Open()

        If myConn.State = ConnectionState.Open Then
            Return True  or false if failed
        Else
            Return False  or false if failed
        End If
    End Function Connect

    Public Function DisConnect() As Boolean
         connect to database
        myConn.Close()
        Return True  or false if failed
    End Function DisConnect

    Public Function RunMyDataQuery(ByVal myCmd As OleDb.OleDbCommand) As OleDb.OleDbDataReader
        myCmd.Connection = myConn
        Return myCmd.ExecuteReader()
    End Function RunMyDataQuery
End Class

I then am able to access these function from anywhere in my project, so saves me writing the code again. The problem I have is it seems that RunMyDataQuery works OK until I have a stored procedure that has parameters to pass. Below is the code that is causing me problems, it just returns nothing in my datareader, not sure if it a problem with my class or with the code below?? :o

Code: 
Private Function checkLogin()        
        Dim myDB As New DBAccess()

        If myDB.Connect = False Then
            MsgBox("Error connecting to server....", MsgBoxStyle.Critical, "Server Connection Error")
            Exit Function
        Else
            Dim myCmd As New OleDb.OleDbCommand("spQryUser")
            myCmd.CommandType = CommandType.StoredProcedure
            myCmd.Parameters.Add(New OleDb.OleDbParameter("@userName", OleDb.OleDbType.VarChar)).Value = Me.txtUsername.Text
            myCmd.Parameters.Add(New OleDb.OleDbParameter("@password", OleDb.OleDbType.VarChar)).Value = Me.txtPassword.Text

            Dim myProjectReader As OleDb.OleDbDataReader = Nothing
            myProjectReader = myDB.RunMyDataQuery(myCmd)
            Do While myProjectReader.Read
              msgbox("works")
            Loop

            myDB.DisConnect()

    End Function

Any help that anyone can give me would be appreshiated.

Thanks inadvance

Simon
 
How is the stored procedure defined? Specifically check the order you add the parameters matches the order they are declared - IIRC oledb commands do not support named parameters.
 
Back
Top