Datareader problem...

[lidds]

I need some help as I can't seem to see what's 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:

 

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

 

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

[PlausiblyDamp]

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.