How to view the result of select query from vb.net

2013-07-01

Question

_{Monday, July 1, 2013 8:20 AM}

Hi,

I have created a code to select some columns from tables. But i dont know how to see the output in vb window itself. But when i run the code in SSMS i am able to see the results. But i wanted to see results in Vb window. How do i see it. Please help me out with this issue.

My code is as follows.

Imports System.Data.SqlClient
Public Class TJfailed
** Dim dbname As String = "syed8"**
** Dim mycommand As New SqlCommand**
** Dim myConn2 As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=True;Initial Catalog=" & dbname & ";")**

** Public Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click**
** Dim qry As String**
** myConn2.Open()**

qry = ("Select c.date_time FROM dbo.division_table AS j JOIN dbo.station_table AS m ON j.station_name = m.station_name JOIN dbo.bmu_table AS n ON m.tj_id = n.tj_id JOIN dbo.sensor_input_table AS c ON c.bmu_id=n.bmu_id")

mycommand = New SqlCommand(qry, myConn2)
** mycommand.ExecuteNonQuery()**
** MessageBox.Show("Select completed")**
** 'MsgBox()**
myConn2.Close()
** 'End If**
End Sub

End Class

Regards Syed

All replies (14)

_{Monday, July 1, 2013 3:20 PM ✅Answered}

Hi syed javed ali,

try this code

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Using connection As New SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=SamplePractiseDB;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False")
connection.Open()
Dim command As New SqlCommand(
"Select EmpNo,EmpName,Salary,DeptNo from EmployeeDetails",
connection)
Using reader As SqlDataReader = command.ExecuteReader()
While reader.Read()
MessageBox.Show(String.Format("EmpName: {0}", reader.GetValue(1)))
End While
End Using
End Using
End Sub

_{Monday, July 1, 2013 8:55 AM}

Hi Syed,

to fetch multiple rows you need a SqlDataReader:

        Dim dbname As String = "syed8"
        Using connection As New SqlConnection("Data Source=(local);Integrated Security=True;Initial Catalog=" & dbname & ";")
            connection.Open()
            Dim command As New SqlCommand(
                "Select c.date_time FROM dbo.division_table AS j JOIN  dbo.station_table AS m ON j.station_name = m.station_name JOIN dbo.bmu_table AS n  ON m.tj_id = n.tj_id JOIN dbo.sensor_input_table AS c ON c.bmu_id=n.bmu_id",
                connection)
            Using reader As SqlDataReader = command.ExecuteReader()
                While reader.Read()
                    Console.WriteLine("Date_Time: {0}", reader.GetValue(0))
                End While
            End Using
        End Using

A second way is using a DataSet together with a SqlDataAdapter.

For an overview see DataAdapters and DataReaders

Regards, Elmar

_{Monday, July 1, 2013 10:29 AM}

Hi Sayed javed ali,

in ur code for select command u r using mycommand.ExecuteNonQuery() we will use this for insert, delete, update

for select we have to use mycommand.ExecuteReader() or mycommand.ExecuteScalar()

please check the links and google for more examples

http://sameercode.wordpress.com/2013/06/19/executereader-executenonquery-executescalar-examples-in-asp-net-when-to-use-what-to-use-in-c-net/

http://stackoverflow.com/questions/2974154/what-is-the-difference-between-execute-scalar-execute-reader-and-executenonque

http://www.aspdotnet-suresh.com/2012/09/executereader-executenonquery.html

http://www.dotnetpools.com/Article/ArticleDetiail/?articleId=9

http://www.c-sharpcorner.com/Interviews/answer/5458/explain-the-use-of-executenonquery-executereader-executesc

_{Monday, July 1, 2013 10:51 AM}

Hi Kumar Jalli,

I changed from mycommand.executenonquery() to mycommand.executereader() but still i may not able to see the results in vb screen. Where i have went wrong. Please let me know.

Regards Syed

_{Monday, July 1, 2013 12:41 PM}

Hi Olaf,

I am getting a error in these lines when i try to execute. At the below lines of ur program i get the errors.

While reader.Read()
                    Console.WriteLine("Date_Time: {0}", reader.GetValue(0))
                End While

In all these 3 lines i am getting a blue underlined error. I am not able to solve it. Can you please help me out.

Regards Syed

_{Monday, July 1, 2013 4:06 PM}

Hi Syed,

Paste the code snippet into your Button1_Click procedure.

The Console class requires eventually an Imports System.

If it doesn't fix the error, post the complete code you are using, please.

The output can be viewed in the output window or the immediate window - that can be configured by Redirect all Output window text to the Immediate window.

For multiple row outputs that's much more handy than clicking a message box OK for each row.

Regards, Elmar

_{Monday, July 1, 2013 4:40 PM}

The ExecuteNonQuery is not meant for the Select but for the use of the SQL statements Insert, Delete or Update.

For the Select are the DataReader or the ExecuteScalar (or classes using those like the DataAdapter, Linq to SQL and Linq to Entities).

A simple sample using the ExecuteScalar on our website.

http://www.vb-tips.com/ExecuteScalarText.ASPX

Success
Cor

_{Monday, July 1, 2013 5:29 PM}

Hi Jalli kumar,

Your program is getting executed, but output i may not able to see in screen. Are you able to see the output on screen as this program executs

Regards Syed

_{Tuesday, July 2, 2013 2:56 AM}

Hi Elmar,

The below is my code. The code is as follows.

Imports System.Data.SqlClient
Imports System.Data.SqlClient.SqlDataReader
Public Class TJfailed
Dim dbname As String = "syed8"
  Dim mycommand As New SqlCommand
Dim myConn2 As SqlConnection = New SqlConnection("Data Source=(local);Integrated Security=True;Initial Catalog=" & dbname & ";")
Public Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim qry As String
myConn2.Open()
   qry = ("Select c.date_time FROM dbo.division_table AS j JOIN dbo.station_table AS m ON j.station_name = m.station_name JOIN dbo.bmu_table AS n ON m.tj_id = n.tj_id JOIN dbo.sensor_input_table AS c ON c.bmu_id=n.bmu_id") '//select query for joining tables

mycommand = New SqlCommand(qry, myConn2)
Dim reader As SqlDataReader = mycommand.ExecuteReader()
While (reader.Read)
MessageBox.Show(String.Format("date_time: {0}", reader.GetValue(1)))
End While

   myConn2.Close()

End Sub

End Class

Regards Syed

_{Tuesday, July 2, 2013 3:36 AM}

Hi Elmar,

I have got the answer by executing your steps. There was no value inserted in my table, thats y it was showing nothing in the output. Now i am getting the output.

Regards Syed

_{Tuesday, July 2, 2013 3:39 AM}

Hi Kumar Jalli,

I have got the answer by executing your steps. There was no value inserted in my table, thats y it was showing nothing in the output. Now i have inserted values and executed, i am now getting the output.

Regards Syed

_{Tuesday, July 2, 2013 5:23 AM}

for the result u can do this also.

reader("Date_time") or reader.item(0)

first one is wrting ur col name second one is writing index of ur col name.

so msgbox(reader("Date_time") & reader.item(0)) will give u the same output if the zero is refering to date_time

_{Tuesday, July 2, 2013 6:12 AM}

Hi Syed,

your code as posted has two bugs:
Imports System.Data.SqlClient.SqlDataReader is not valid and necessary as it is a class.

reader.GetValue(1) must be reader.GetValue(0), as the Value access is zero based, as usual.

Some of the problems could be avoided if you use the examples as posted. There some good reasons why I use using in my examples (about 10 years ADO.NET experience ;) You should also avoid class fields (mycommand, myConn2) where a local variable is enough.

Regards, Elmar

_{Tuesday, July 2, 2013 1:19 PM}

happy coding....

שתף באמצעות

How to view the result of select query from vb.net

Question

All replies (14)

משאבים נוספים