Filter records

[RobinTheHood]

Hi all,

I’m trying to re-write a solution from Ms. Access to VB.Net

Previously I had a number of queries that filtered records depending on what value of a filed on my main menu, if the filter was empty (Null) than all records were returned.

Here’s the criteria part of my Ms. Access query’s

(((TblOrganisation.Site)=[Forms]![MainMenu]![CmbSite])) OR ((([Forms]![MainMenu]![CmbSite]) Is Null))

Can someone please give me the VB.Net equivalent?

Also, am I right in thinking that I should be using Datasets.The only reason I ask is that I’ve been watching Microsoft Linq videos by the lovely Beth, and it’s left me a little confused as to if I should be using Datasets or Linq to SQL.

Thanks

[Teme64]

Here's how you do it with ODBC

Imports System.Data.Odbc

Dim oConn As OdbcConnection
Dim oCmd As OdbcCommand
Dim oReader As OdbcDataReader
Dim strSQL As String

' Set a proper connection string
oConn = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\test.mdb;Uid=;Pwd=;")
Try
  ' Open the connection
  oConn.Open()
Catch ex As Exception
  ' Failed to open DB
End Try
' The next line is what you asked for. Replace CmbSite with a proper criteria. For example, Site='" & TextBox1.Text "'...
strSQL = "SELECT * FROM TblOrganisation WHERE Site='" & CmbSite "' OR Site IS NULL"
' Create a command object
oCmd = New OdbcCommand(strSQL, oConn)
Try
  ' Execute command
  oReader = oCmd.ExecuteReader()
Catch ex As Exception
  ' Failed to execute command
End Try

See Connection strings for Access for a suitable connection string. And you could also use OleDb instead of ODBC. The main point is strSQL = "SELECT * FROM TblOrganisation WHERE Site='" & CmbSite "' OR Site IS NULL" which is equivalent to your Access filter.

I can't say if you should use DataSet or Linq. I've started with DataSets so I do use them. This is not to say that you shouldn't consider learn to use Linq.

[RobinTheHood]

Thank you so much Team64.

I'll be trying this out later on today. (seems a little longwinded when copared to the access one liner, but at least it'll be a good stating point and a good reason for me to keep bashing at the keyboard).

NB
I'm not keeping the old MDB but starting from scratch with a sql backend

Thank you again