SQL Data Reader

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
i have made a program to get data from SQL
i have done it before but i lost the files when i tried to make it again
i got ( Index was outside the bounds of the array ) something like that

Here Is The Connection

    Dim Connection As String = String.Empty
        LUsername = TextBox3.Text
        LPassword = TextBox4.Text
        Server = TextBox1.Text
        Database = TextBox2.Text
        Connection = "Data Source=" & Server & ";Initial Catalog=" & Database & ";User Id=" & LUsername & ";Password=" & LPassword & ";Integrated Security=SSPI;MultipleActiveResultSets=True;Trusted_Connection=True;"
        con.ConnectionString = Connection
        Try
            con.Open()
         Catch ex As SqlException
          End Try


and here is the error part

 Dim command As New SqlCommand("SELECT id FROM dbo.users", FLogin.con)
        Dim reader As SqlDataReader
        reader = command.ExecuteReader
        While reader.Read
            MsgBox(reader(1).ToString)
        End While


the strange thing that i have made it before like this and it worked

so any solution ?
________
sry english not my first language
 

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
It's obvious. You do not have a column on index 1 as you only select ID column.
Change the index to zero and it will work:
      While reader.Read
           MessageBox.Show(reader(0).ToString)
       End While
 

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
sry

no i have 6 rows and 6 columns
i don't know what to do

i wanna make program that gets list of all usernames and their count in SQL

________
sry english not my first language
 
Last edited:

kulrom

Well-known member
Joined
May 10, 2005
Messages
2,854
Location
Republic of Macedonia
Programming Experience
10+
Your sql command text does select ONLY ONE column which is ID. Therefor you cannot refer index 1 (please notice that .NET is zero based) unless you change the query e.g.

Dim command As New SqlCommand("SELECT id, username FROM dbo.users", FLogin.con)


now your code will work AS IS. and the msgbox will display the username. I hope that now you see what's wrong with your code.
 

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
the truth

loll i meant id not username here is my table
sry cant upload it to attachment so
i will write the html code and u paste it into a text file and rename the extension to htm or html
as you want
here it is
<br><br><br><br><br><br><br><br><br><br><br><br><div align=center>
<table border=3 bordercolor=blue>
<tr align=center>
<td align=center><b>id</b></td>
<td align=center><b>password</b></td>
<td align=center><b>online</b></td>
</tr>
<tr align=center>
<td align=center>gm1</td>
<td align=center>1</td>
<td align=center>1</td>
</tr>
<tr align=center>
<td align=center>gm2</td>
<td align=center>2</td>
<td align=center>0</td>
</tr>
<tr align=center>
<td align=center>gm3</td>
<td align=center>3</td>
<td align=center>0</td>
</tr>
<tr align=center>
<td align=center>gm4</td>
<td align=center>4</td>
<td align=center>1</td>
</tr>
</table>

so i need the program to write each id in listbox

thank you very much to answer my question :)
 

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
the html code to view you my SQL table i have made it in html language
so u can see it
and help me
( here is my problem )
i have SQL Table 3 columns 6 rows
-id ---|--- password ---|--- online-
gm1------------1------------0
gm2------------2------------0
gm3------------3------------1
gm4------------4------------0
gm5------------5------------0
gm6------------6------------1

so i wanna create a list box with all id's
when i try
this code
Dim command As New SqlCommand("SELECT id FROM dbo.users", FLogin.con)
       Dim reader As SqlDataReader
       reader = command.ExecuteReader
       While reader.Read
           try
                for i = 0 to 1000
listbox1.items.add(reader(i).tostring())
                next
Catch ex as expection
msgbox("All Id's Loaded Successfuly")
end try
       End While

i always get the 1st row only
 
Last edited:

JohnH

VB.NET Forum Moderator
Staff member
Joined
Dec 17, 2005
Messages
15,609
Location
Norway
Programming Experience
10+
Each call to reader.Read advances reader by one row, whereby you get the id from first row item, which is reader(0).
 

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
Thank You Soo much u helped me alot :) ty ty

so i should make it
Dim command As New SqlCommand("SELECT id FROM dbo.users", FLogin.con)
       Dim reader As SqlDataReader
       reader = command.ExecuteReader 
  for i = 0 to 1000
       While reader.Read
       listbox1.items.add(reader(i).tostring())
       End While
 next



edit : It Works :) iam really Happy :D thank you soo much
Request : Request For Close This Topic pls
 
Last edited:

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
iam sorry i was need u to help me ..

but when he said that i became very happy i will write to you all the code that`s work with me

tomorow cuz i really need to sleep

now i have other question how do i check if id is already at SQL ?
 

ahmdod5

Member
Joined
Jan 20, 2011
Messages
14
Programming Experience
5-10
Here Is My Control Panel Form Code On Load
  Private Sub US_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim command As New SqlCommand("SELECT id FROM dbo.users", FLogin.con)
        Dim reader As SqlDataReader
        reader = command.ExecuteReader
        Try
            For i = 0 To 1000
                While reader.Read
                    ComboBox1.Items.Add(reader(i).ToString)
                End While
            Next
        Catch ex As Exception
        End Try
    End Sub


And Here Is My Login SQL Screen on Connect
   Public WithEvents con As New SqlConnection
    Public LUsername As String
    Public LPassword As String
    Public Database As String
    Public Server As String
    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim Connection As String = String.Empty
        LUsername = TextBox3.Text
        LPassword = TextBox4.Text
        Server = TextBox1.Text
        Database = TextBox2.Text
        Connection = "Data Source=" & Server & ";Initial Catalog=" & Database & ";User Id=" & LUsername & ";Password=" & LPassword & ";Integrated Security=SSPI;MultipleActiveResultSets=True;Trusted_Connection=False;"
        con.ConnectionString = Connection
        Try
            con.Open()
            Button4.Enabled = True
            Button3.Enabled = False
            FCP.Show()
        Catch ex As SqlException
            Select Case ex.Number
                Case 0
                    MsgBox("Cannot connect to server (Error 1).", MsgBoxStyle.Exclamation)
                Case 1045
                    MsgBox("Invalid username/password, please try again (Error 2).", MsgBoxStyle.Exclamation)
                Case Else
                    MsgBox(ex.Message)
            End Select
            con.Close()
            Button4.Enabled = False
            Button3.Enabled = True
        End Try
    End Sub


that`s Code worked for me succesfully
 
Top Bottom