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

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.

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

This question has already been answered. Start a new discussion instead.