How to display query results from SQL server in VB?

Question

I am trying to display query results from SQL server in VB. I wrote following code, but not getting how to "Just display the results";

 Public Function ConnectToSQL() As String
        Dim con As New SqlConnection
Try
            con.ConnectionString = "Data Source=(local);Initial Catalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=afm"
            Dim cmd As New SqlCommand("SELECT username FROM users WHERE username='user'", con)
            con.Open()
            Console.WriteLine("Connection Opened")

            ' Execute Query
            Dim reader As SqlDataReader = cmd.ExecuteReader()
            While reader.Read()
                Console.WriteLine(String.Format("{0}, {1}", _
                   reader(0), reader(1)))

            End While
        Catch ex As Exception
            MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
        Finally
            con.Close() 'Whether there is error or not. Close the connection.
        End Try
        Return reader
    End Function

Can any one guide? Thank you

csharp-station.com/Tutorials/AdoDotNet/Lesson04.aspx Listing 1 — mola10
– mola10, Commented Apr 8, 2012 at 10:51
@mola10 Thank you for your answer, can you guide me something in VB.Thanks — AbdulAziz
– AbdulAziz, Commented Apr 8, 2012 at 11:33
google + "vb SqlDataReader" == first position =) Link: msdn.microsoft.com/en-us/library/haa3afyz(v=vs.71).aspx — mola10
– mola10, Commented Apr 8, 2012 at 13:01
Does this even compile? You're returning an sqldatareader object from a method declared to return a string. — Joel Coehoorn
– Joel Coehoorn, Commented Apr 8, 2012 at 21:00

RBarryYoung · Accepted Answer · 2012-04-09 00:09:10Z

5

I corrected a few things and now your function works fine for me:

Public Function ConnectToSQL() As String
    Dim con As New SqlConnection
    Dim reader As SqlDataReader
    Try
        con.ConnectionString = "Data Source=(local);Initial Catalog=TestDatabase;Persist Security Info=True;User ID=sa;Password=afm"
        Dim cmd As New SqlCommand("SELECT username, WindowsLogin FROM users WHERE username='user'", con)
        con.Open()
        Console.WriteLine("Connection Opened")

        ' Execute Query    '
        reader = cmd.ExecuteReader()
        While reader.Read()
            Console.WriteLine(String.Format("{0}, {1}", _
               reader(0), reader(1)))
            'NOTE: (^^) You are trying to read 2 columns here, but you only        '
            '   SELECT-ed one (username) originally...                             '
            ' , Also, you can call the columns by name(string), not just by number '

        End While
    Catch ex As Exception
        MessageBox.Show("Error while connecting to SQL Server." & ex.Message)
    Finally
        con.Close() 'Whether there is error or not. Close the connection.    '
    End Try
    'Return reader { note: reader is not valid after it is closed }          '
    Return "done"
End Function

Let me know if you have any questions.

answered Apr 9, 2012 at 0:09

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AbdulAziz Over a year ago

Thank you very much. It really helps me and its working as well. And I also know from your answer, where I am mistaken. Thank you once again.

Collectives™ on Stack Overflow

How to display query results from SQL server in VB?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related