How to display query results from SQL server in VB?
63,332
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.
Author by
AbdulAziz
Updated on July 09, 2022Comments
-
AbdulAziz almost 2 years
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
-
Andomar about 12 yearsPlease read a tutorial and post less questions
-
mola10 about 12 years
-
AbdulAziz about 12 years@mola10 Thank you for your answer, can you guide me something in VB.Thanks
-
mola10 about 12 yearsgoogle + "vb SqlDataReader" == first position =) Link: msdn.microsoft.com/en-us/library/haa3afyz(v=vs.71).aspx
-
Joel Coehoorn about 12 yearsDoes this even compile? You're returning an sqldatareader object from a method declared to return a string.
-
João Paladini about 12 yearswhat do you want to display it to? The console?
-
-
AbdulAziz about 12 yearsThank 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.