Error (Index was outside the bounds of the array.)

[Maha Sh.]

Hi all,

I am using DataReader for the first time and i faced more than one error and this is the last one

((Index was outside the bounds of the array.))

and it's point to this line

Dim result As Data.SqlClient.SqlDataReader = dr(Data.CommandBehavior.CloseConnection)

I think I have problem with closing the data reader but I tried several things and its still not working

Public Sub LogIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim connStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\sony\Documents\Visual Studio 2010\Projects\SADA\SADA\App_Data\SADA2.mdf';Integrated Security=True;User Instance=True"
        Dim sqlconnet As Data.SqlClient.SqlConnection
        Dim MyComm As Data.SqlClient.SqlCommand
        Dim dr As SqlDataReader
        Dim UserType As String
        Dim Pateint As String
        Dim SLP As String
        Dim Admin As String
        Dim logID As String
        Dim PassW As String

        sqlconnet = New Data.SqlClient.SqlConnection()
        sqlconnet.ConnectionString = connStr
        MyComm = New Data.SqlClient.SqlCommand("", sqlconnet)
        MyComm.CommandType = Data.CommandType.Text
        '  MyComm.CommandText = "SELECT * FROM LoginTable WHERE (loginId ='" & TextBox1.Text & "') AND (Password = '" & TextBox2.Text & "') "
        MyComm.CommandText = "SELECT * FROM LoginTable WHERE (loginId ='" & TextBox1.Text & "') AND (Password = '" & TextBox2.Text & "') "
        sqlconnet.Open()
        dr = MyComm.ExecuteReader()
        dr.Read()
        logID = dr.ToString(0)
        PassW = dr.ToString(1)
        Admin = dr.ToString(2)
        SLP = dr.ToString(3)
        Pateint = dr.ToString(4)
        UserType = dr.ToString(5)





        Dim result As Data.SqlClient.SqlDataReader = dr(Data.CommandBehavior.CloseConnection)
        If result.HasRows = False Then
            MsgBox("Error")

        ElseIf result.HasRows = True And UserType = "S" Then
            '  Session("logAccount") = TextBox3.Text
            Response.Redirect("SLPMain.aspx")
        ElseIf result.HasRows = True And UserType = "A" Then
            Response.Redirect("Admin.aspx")
        ElseIf result.HasRows = True And UserType = "P" Then
            Response.Redirect("WebForm4.aspx")

        End If
        result.Close()
    End Sub

Plz help me !!!

[hericles]

Data.CommandBehavior.CloseConnection returns an enumeration (the number 6 from what I just looked up on MSDN). This means you are in effect calling

Dim result As Data.SqlClient.SqlDataReader = dr(6)

Which probably doesn't exist as your dr has 6 items or less. As you already have the dataReader set up I'm not even sure why you are creating another dataReader equal to the existing one just to see if it has rows.

[Maha Sh.]

Hi hericals, thanks for your replay

the problem is that i was trying for a while to create a login form for diffrent type of users and ( admin, Patient, and SLP)

the login table contains

loginId (PK)
password
AdminID(FK) ( if the user is the admin there will be data if not it wil be null and it's the same for the rest of the uder IDs)

SLP_ID(fk)
Pateint ID(FK)
User Type (will be A, P, or S)

so i want to check the user type to direct hem to the write page and i want to save the userID(Admin, Patient, or SLP) to use it for the session

after searching i tried to use the dataReader but it keeps giving me errors.

I read about dataReader but i didn't know how to use it in my code obviously i miss things up thanks for the clarification.

and if you have any suggestions that's will be great :)

[hericles]

You have already stored the UserType from the first read of the dataReader so the second attempt is unnecessary. Simply use a switch statement to check the value held in UserType and redirect as need be.

switch(UserType) {
    case "S":
        redirect...
    case "A":
        redirect...
    case "P":
        redirect...
}

[Maha Sh.]

I used switch but it didn't work in vb so i used Select insted
but i still have a problem its doesn't read my database table

Public Sub LogIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim connStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\sony\Documents\Visual Studio 2010\Projects\SADA\SADA\App_Data\SADA2.mdf';Integrated Security=True;User Instance=True"
        Dim sqlconnet As Data.SqlClient.SqlConnection
        Dim MyComm As Data.SqlClient.SqlCommand
        Dim dr As SqlDataReader
        Dim UserType As String
        Dim Pateint As String
        Dim SLP As String
        Dim Admin As String
        Dim logID As String
        Dim PassW As String

        sqlconnet = New Data.SqlClient.SqlConnection()
        sqlconnet.ConnectionString = connStr
        MyComm = New Data.SqlClient.SqlCommand("", sqlconnet)
        MyComm.CommandType = Data.CommandType.Text

        MyComm.CommandText = "SELECT * FROM LoginTable WHERE (loginId ='" & TextBox1.Text & "') AND (Password = '" & TextBox2.Text & "') "
        sqlconnet.Open()
        dr = MyComm.ExecuteReader()
        dr.Read()
        logID = dr.ToString(0)
        PassW = dr.ToString(1)
        Admin = dr.ToString(2)
        SLP = dr.ToString(3)
        Pateint = dr.ToString(4)
        UserType = dr.ToString(5)
        Select Case (UserType)
            Case "S"
                Response.Redirect("SLPMain.aspx")
            Case "A"
                Response.Redirect("Admin.aspx")
            Case "P"
                Response.Redirect("WebForm4.aspx")
            Case Else

                MsgBox("error" & UserType)
        End Select

    End Sub

for the user type it should be a number which is "1" its gives me "m" insted and when i put the user type its gives me "s"
thats mean it doesn't read from the tables at all ?!!

plz help :(

[hericles]

Now I'm confused. If the user type in the data base is stored as a number why are you testing to see if it equal to A, S or P? What is the data type of the userType column in the database? If the reader is holding numbers and you are checking for letters your MsgBox should fire everytime because cases A, S and P will never work. Is that was is happening?

[Maha Sh.]

Thanks a lot for your help hericles

The userType is char(1)

the login table contains

loginId (PK)
password
/////////////////////////////////////////////////////////////////////////////////////////////
AdminID(FK) ( if the user not the admin the ID will be null)
SLP_ID(fk) ( if the user not the SLP the ID will be null)
Pateint ID(FK) ( if the user not the pateint the ID will be null )
////////////////////////////////////////////////////////////////////////////////////////////// I added this three columns insted of the user Id because it connected to severral tables and this was the best way
User Type (will be A, P, or S) ///////////////////////////// I used the user type column because its easier to check the user type insted of checking the Admin, SLP, And Pateint ID if its null or not

so the input of my table is
loginId: 1
Password:111
AdminID: null
SLP_ID : null
PateintID: 8
UserType:P

I hope its clear know

[hericles]

Have you actually debugged and checked the output of the reader? It is correctly pulling the information from the database?If it it and UserType then contains either A, S or P you should get redirected...

[Maha Sh.]

hericles it's giving me this

UserType= m
loginID = S
Password = y
AdminId= s
PateintID=e
SLP_ID=t

even when i I login with a diffrent user which means there is a problem with this part of the code

 logID = dr.ToString(0)
 PassW = dr.ToString(1)
 Admin = dr.ToString(2)
 SLP = dr.ToString(3)
 Pateint = dr.ToString(4)
 UserType = dr.ToString(5)

it's should change when I am changing the user but its not?

[Maha Sh.]

I solved the problem finally :)

in case any one faced the same problem

I was using ToString to read from the DB which was wrong. I'm not sure its used for what??!!

So all my datatype are integers and I had just the UserType which was char()

So I used GetInt32 for the integer

and GetString for char()

I changed my code just a little because i discover the GetInt32() dosen't take null values

My Final code

Public Sub LogIn(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click


        Dim connStr As String = "Data Source=.\SQLEXPRESS;AttachDbFilename='C:\Users\sony\Documents\Visual Studio 2010\Projects\SADA\SADA\App_Data\SADA2.mdf';Integrated Security=True;User Instance=True"
        Dim sqlconnet As Data.SqlClient.SqlConnection
        Dim MyComm As Data.SqlClient.SqlCommand
        Dim dr As SqlDataReader
        Dim UserType As String
        Dim Pateint As String
        Dim SLP As String
        Dim Admin As String
        Dim logID As String
        Dim PassW As String

        sqlconnet = New Data.SqlClient.SqlConnection()
        sqlconnet.ConnectionString = connStr
        MyComm = New Data.SqlClient.SqlCommand("", sqlconnet)
        MyComm.CommandType = Data.CommandType.Text
        '  MyComm.CommandText = "SELECT * FROM LoginTable WHERE (loginId ='" & TextBox1.Text & "') AND (Password = '" & TextBox2.Text & "') "
        MyComm.CommandText = "SELECT * FROM LoginTable WHERE (loginId ='" & TextBox1.Text & "') AND (Password = '" & TextBox2.Text & "') "
        sqlconnet.Open()
        dr = MyComm.ExecuteReader()
        dr.Read()
        logID = dr.GetInt32(0)
        PassW = dr.GetInt32(1)



        UserType = dr.GetString(5)
        Select Case (UserType)
            Case "S"
                SLP = dr.GetInt32(3)
                Response.Redirect("SLPMain.aspx")

            Case "A"
                Admin = dr.GetInt32(2)
                Response.Redirect("Admin.aspx")
            Case "P"
                Pateint = dr.GetInt32(4)
                Response.Redirect("WebForm4.aspx")
            Case Else

                MsgBox(" user type Error  " & UserType & "                      loginId" & logID & "                           Pass" & PassW & "                                Admin" & Admin & "                           Pateint" & Pateint & "                  SLP" & SLP)
        End Select
        )




    End Sub