Using ExecuteScalar in VB .NET

[CharlieChau]

Hi,

I have just learned from the link http://support.microsoft.com/kb/821765/EN-US/ 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)

Try
SqlConnection1.Open()
RecordCount = CInt(SelectCommand.ExecuteScalar())
Catch ex As Exception
MessageBox.Show("Failed to execute command")
Finally
SqlConnection1.Close()
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.


Thanks,
/CC.

 

[Paszt]

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:

'this:
Catch ex As Exception
MessageBox.Show("Failed to execute command")

'to this:
Catch ex as Exception
MessageBox.Show(ex.Message)

At least you'll know more specifically what's wrong.

 

[CharlieChau]

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.

Thanks,
/CC.

 

[Paszt]

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"). www.ConnectionString.com 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.

 

[ser]

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.