Read data from SQL

[ShemoPT]

I am trying to read data from remote SQL base. I have written the function that works great if you need to get 1 Value , but when I want to read 2 or more value at from one query it doesn't work

how can I fix it?

Imports System.Data.SqlClient
Public Class SQL
    Public MySQLconnection As SqlConnection
    Public MySQLcommand As SqlCommand
    Dim MySQLreader As SqlDataReader

    Public Function connectToSQL(ByVal ConnectionString As String) As String
        Try

            MySQLconnection = New SqlConnection("Data Source=mysource;Initial Catalog=testbase;User ID=MYuser;password=MyPass")
            MySQLconnection.Open()
            MySQLcommand = New SqlCommand(ConnectionString, MySQLconnection)
            If ConnectionState.Open Then

                MySQLreader = MySQLcommand.ExecuteReader()
            End If


        Catch ex As SqlException
            MsgBox("Connection Eror")
            MsgBox(ex.Message)
        End Try

        Dim ReadData As String
        Dim i As Integer = -1

        While MySQLreader.Read
            i += 1
            ReadData += MySQLreader.GetValue(i).ToString

        End While

        Return ReadData
        MySQLconnection.Close()



    End Function
   
End Class

I am using this function like this:

Dim connectionstring As String
        connectionstring = ("SELECT ID, username  FROM [mybase].[myuser].[users] where username ='" & DataGridView1.SelectedCells(0).Value & "'")

        Dim GetVaule As New SQL

        MsgBox(GetVaule.connectToSQL(connectionstring))

I want to get from this query both ID and username - but I receive only ID

Please help me to fix it.

[phatJacob07]

Your counter

Dim i as Integer

doesn't do anything for you, I'm afraid.

If you want to retrieve the UserID and UserName, and assuming they're the only two fields retrieved, simply do this:

Dim ReadUserName as String
Dim ReadUserID as String
If Reader.Read then
ReadUserID = Reader(0) 'This is the first field returned, most likely UserID
ReadUserName = Reader(1) 'This is the second field returned
End If

You can also substitute the integer values within Reader() with string values that are the actual column names. Like so:

Dim ReadUserName as String
Dim ReadUserID as String
If Reader.Read then
ReadUserID = Reader("UserID") 'This is the first field returned, most likely UserID
ReadUserName = Reader("UserName") 'This is the second field returned
End If

Keep in mind this is still just one row being returned.

[G_Waddell]

Hi
As I understand it you are passing a query into your class and wish to read the data from it. It looks to me like the issue is caused here:

Dim i As Integer = -1        
 While MySQLreader.Read            
            i += 1            
             ReadData += MySQLreader.GetValue(i).ToString         
End While

1.The line While MySQLreader.Read will cause the reader to read from each row in turn
2. You are not moving through each field returned with the record as your i value is not increasing within the record.
3. I also would not assume a value limit for i so that you can pass queries with various number of fields in the result.

Here is how I would do it:

Dim ReadData As String        
Dim i As Integer    
      
While MySQLreader.Read               'Read in the row
    i=0 'reset i to start from first field
    'loop through each returned field and add to the string
    for i = 0 to MSQLreader.FieldCount - 1  
                ReadData += MySQLreader.Item(i).ToString
    next
End While
Return ReadData

[ShemoPT]

Hi
As I understand it you are passing a query into your class and wish to read the data from it. It looks to me like the issue is caused here:

Dim i As Integer = -1        
 While MySQLreader.Read            
            i += 1            
             ReadData += MySQLreader.GetValue(i).ToString         
End While

1.The line While MySQLreader.Read will cause the reader to read from each row in turn
2. You are not moving through each field returned with the record as your i value is not increasing within the record.
3. I also would not assume a value limit for i so that you can pass queries with various number of fields in the result.

Here is how I would do it:

Dim ReadData As String        
Dim i As Integer    
      
While MySQLreader.Read               'Read in the row
    i=0 'reset i to start from first field
    'loop through each returned field and add to the string
    for i = 0 to MSQLreader.FieldCount - 1  
                ReadData += MySQLreader.Item(i).ToString
    next
End While
Return ReadData

You have read my thoughts :)) This is exactly what I was looking for.

Thank you !!!

[G_Waddell]

You have read my thoughts :)) This is exactly what I was looking for.

Thank you !!!

Glad I could help, I hope it is all resolved for you now.