[RESOLVED] VBA SQL scalar stmt

Whats the quickest way in vba for access to perform vb.net

Execute Scalar where the sqlstmt will return a value

i need to run a sqlstmt that will return a value

Quote:

---

Originally Posted by Strider

Whats the quickest way in vba for access to perform vb.net

---

I completely don't understand this question. You want to write vba code that doesn't something in .NET? Or, something like .NET can do?

Quote:

---

Originally Posted by Strider

Execute Scalar where the sqlstmt will return a value

---

What is Scalar?

i use this function below in vb.net to return a value from a table

P_sqlstmt = SELECT COUNT(*) FROM tbl_People

VB Code:

Public Function ExecScalarSQLStmt(ByVal p_sqlstmt As String) As String
        Dim sqlcmd As SqlCeCommand
        Try
            sqlcmd = New SqlCeCommand(p_sqlstmt, dbConn.getInstance().GetConnection())
            sqlcmd.CommandText = p_sqlstmt
            Return sqlcmd.ExecuteScalar()
        Catch err As SqlCeException
            Throw New Exception(err.Message)
        End Try
    End Function

If I remember correctly, scalar only returns one value. Not sure if Access supports it, but you could get a similar result with SELECT TOP 1 fldFoo...
Although your SQL should only return one value anyway. You don't have to use ExecuteScalar in .Net, it's just more efficient for that language. Your SQL should only return one result anyway.

From .Net help...

Quote:

---

Use the ExecuteScalar method to retrieve a single value (for example, an aggregate value) from a database. This requires less code than using the ExecuteReader method, and then performing the operations necessary to generate the single value using the data returned by a SqlDataReader.

A typical ExecuteScalar query can be formatted as in the following C# example:

CommandText = "select count(*) as NumberOfRegions from region";
Int count = (int) ExecuteScalar();
Example
[Visual Basic, C#] The following example creates a SqlCommand and then executes it using ExecuteScalar. The example is passed a string that is a Transact-SQL statement that returns an aggregate result, and a string to use to connect to the data source.

[Visual Basic]
Public Sub CreateMySqlCommand(myScalarQuery As String, myConnection As SqlConnection)
Dim myCommand As New SqlCommand(myScalarQuery, myConnection)
myCommand.Connection.Open()
myCommand.ExecuteScalar()
myConnection.Close()
End Sub 'CreateMySqlCommand
[C#]
public void CreateMySqlCommand(string myScalarQuery, SqlConnection myConnection)
{
SqlCommand myCommand = new SqlCommand(myScalarQuery, myConnection);
myCommand.Connection.Open();
myCommand.ExecuteScalar();
myConnection.Close();
}
[C++, JScript] No example is available for C++ or JScript. To view a Visual Basic or C# example, click the Language Filter button in the upper-left corner of the page.

---

Currently the way i get a value from a table is to
Create a recordset
Execute SQL stmt on recordset
Check if it is EOF or BOF
then get the value of the field and assign it to a variable


surely there is a better way

I don't think so, in VBA.