Getting result of query within vb.net

[ninel]

I am trying to execute a query from .net. The query is just a count of a table: "SELECT Count(*) FROM Table1"

How do I get the result of the query into a variable? I don't need to set it to any kind of control.

Thanks,
Ninel

 

[aBsOlUt]

Best solution for you is to use ADO.NET and use the Commands object ExecuteScalar() function.

If you know ADO.NET this should be very easy.

Imports System.Data.SqlClient

Const conStr As String = "data source=yourdatasource;initial catalog=yourdatabasename;" & _
                                  "integrated security=SSPI;persist security info=True;workstation id=yourWID;"

Dim sqlCon As New SqlConnection(conStr)
Dim result as Integer = 0

        sqlCon.Open()

        Dim sqlCom As New SqlCommand() 'New command object
        With sqlCom
            .Connection = sqlCon
            .CommandText = "SELECT Count(*) FROM Table1"
            .CommandType = CommandType.Text
            .CommandTimeout = 5
        End With

result = sqlCom.ExecuteScalar() 'Store the query result into your variable.

Note:The above works best with SQL Server as it is using the SQL objects of ADO.NET. If you are using Access or something else for a database use OLEDB which should be VERY similar to the above.