Question sql select statement question

[markthien]

Hi guys,

Below is my code :

=====================================================

Dim ds As New DataSet
Dim da As OleDbDataAdapter
Dim con As OleDbConnection

Dim forever As Boolean
forever = True
While forever = True
con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\AddressBook.mdb")
Try
da = New OleDbDataAdapter("select * from tblContacts", con)
da.Fill(ds, "AddressBook1")
Dim cnt As Integer
cnt = ds.Tables("AddressBook1").Rows.Count
MsgBox(cnt)
da = New OleDbDataAdapter("select * from tblContacts where surname = 'Smith'", con)
da.Fill(ds, "AddressBook2")
cnt = ds.Tables("AddressBook2").Rows.Count
MsgBox(cnt)
da.Dispose()
ds.Clear()
con.Close()
Catch
Console.WriteLine("Error Opening {0}", con.DataSource)
End Try
Threading.Thread.Sleep(10000)
End While

=====================================================

Sorry I just started using vb.net. I was a java developer. I am just wondering the above code is correct. I just need to first check totally how many row inside the table then select the number of record with condition. Since this is a forever loop, I am wondering if I keep on executing the "New OleDbDataAdapter" and "New OleDbConnection" is appropriate ? Appreciate if someone could advice me on this. Thanks !

regards,
Mark

 

[markthien]

I have change to the following:

Dim databaseFilePath As String = "C:\AddressBook.mdb"
Dim tableName As String = "tblContacts"
Dim columnNameOfInUseTerminalFlag As String = "surname"
Dim sqlGetNumberOfTerminal As String = "select count(*) from " & tableName
Dim sqlGetNumberOfInUseTerminal As String = "select count(*) from " & tableName & " where " & columnNameOfInUseTerminalFlag & " = 'Smith'"
Dim conString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & databaseFilePath
Dim conn As New OleDbConnection(conString)
Dim cmd As OleDbCommand

While forever = True
Try
conn.Open()

cmd = New OleDbCommand(sqlGetNumberOfTerminal, conn)
numberOfTerminal = Convert.ToInt32(cmd.ExecuteScalar())

cmd = New OleDbCommand(sqlGetNumberOfInUseTerminal, conn)
numberOfInUseTerminal = Convert.ToInt32(cmd.ExecuteScalar())

cmd.Dispose()
conn.Close()
Catch Ex As Exception
Console.WriteLine(Ex.Message)
End Try
End While

would it be cleaner?