Using ExecuteScalar in VB .NET


Active member
Jun 15, 2004
Programming Experience

I have just learned from the link to use the database with VB .NET

Then, I want to use the ExecuteScalar to verify the table Student in the link above as a reference table. For example, I can validate the SNo already existed or the SName is matching with the SNo. The following code is the simple test to verify if I have any SNo existed in the database Student.

Dim SqlConnection1 As SqlConnection

SqlConnection1 = New SqlConnection("server = localhost;uid=;password=;database=testdb")

Dim SQLStatement As String = "SELECT * FROM student"

Dim RecordCount As Integer
Dim SelectCommand As New SqlClient.SqlCommand (SQLStatement, SqlConnection1)

RecordCount = CInt(SelectCommand.ExecuteScalar())
Catch ex As Exception
MessageBox.Show("Failed to execute command")
End Try

MessageBox.Show("Customers table contains " & RecordCount & " records")

It seems to me that the parameters SqlConnection1 does not work well,
but I do not know which parameters. Since I always get
the catch error message: ("Failed to execute command").
I do not know what I did wrong. Could someone help me out, please.

The only error message you'll receive in the case of an error is "Failed to execute command" because you have hard-coded it that way. Try showing the actual error you are receiving by changing:
Catch ex As Exception
MessageBox.Show("Failed to execute command")

'to this:
Catch ex as Exception
At least you'll know more specifically what's wrong.
still got error from ExecuteScalar

I follow your suggestion to change to ex.message. Then, I got the error:"SQL server does not exist or access denied". I do not use SQL server, I only use MS access to create the table Student.

Could you give me some hints how to fix it, please.

Have a look at the System.Data.OleDb namespace. It is interchangable with the System.Data.SqlClient namespace. Change all the SQL methods and classes to OleDb methods and classes. For instance, the System.Data.SqlClient.SqlConnection class becomes a System.Data.OleDb.OleDbConnection class. You'll also have to change the connection string: OleDbConnection1 = New System.Data.OleDb.OleDbConnection("connectionStringGoesHere"). has examples of connection strings.

You could also create the connection using the Server Explorer, then drag the connection onto the form designer to create a Connection component. The provider to use for MS Access is the Microsoft Jet 4.0 OLE DB Provider.
Execute Scalar

Two things:

First, you are not specifying the user name and password in your connection string, but I am assuming you add these when you run the app. Also, remove the spaces around the equals sign,
so "server = localhost;uid=;password=;database=testdb" becomes "server=localhost;uid=;password=;database=testdb". However, this is an ODBC connection string, the equivalent ADO connection string would be:
"data source=(local);user id=;password=;initial catalog=testdb".

Second, the command you are using as posted would not return the recordcount for the table; try "select count(*) from student" to do this.