SQL Data Reader

[ahmdod5]

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]

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]

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

 

[kulrom]

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]

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

 

[kulrom]

I am really confused. what is this? You code against a database (MSSQL) so what is this html code?

 

[ahmdod5]

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

 

[JohnH]

Each call to reader.Read advances reader by one row, whereby you get the id from first row item, which is reader(0).

 

[ahmdod5]

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 thank you soo much
Request : Request For Close This Topic pls

 

[kulrom]

This cannot work. reader(index) refer the column in the query not the record number.
reader(0).ToString is the way to go

 

[ahmdod5]

lol then why it works with me well ( anyway all i care's now ( ITS WORK ))

 

[kulrom]

Hey i am trying to help you while You are trying to be funny. And again it does not work. At least it does not work in a way you explained that you want it to work.

However if you are happy with that then i have no reason to insist. It works woohooo .. cool

 

[ahmdod5]

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]

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