Access Query returning 0 records in VB.NET 2003 in Vista

[nram]

We have an application developed in VS 203 [VB.NET]
We aretesting the app in WindowsVista
The problem we are facing is as follows:
The app uses an Access database. When trying to execute a select statement, the application returns 0 records, though when the same query is runin Access it returns all the valid records. Please note that we face this ssue only when the app is run in Vista

The details of the codes are as follows:
Dim daGetData As OleDb.OleDbDataAdapter
dim m_Connection As OleDbConnection
dim m_Command As OleDbCommand
dim m_strConnection as string
Dim dtGetData As New DataTable

m_strConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="mydb.mdb;Jet OLEDB:Database Password=abcd"

pstrSql = "SELECT FG.GroupID, FG.GroupName, iif(isnull(FS.FSID),0,FS.FSID), iif (isnull(FS.name),'',FS.name), iif( iif(isnull(FS.NP),0,FS.NP) = 0, iif(isnull(FS.TN),'',FS.TN) + "-NEW", FS.TN), FG.enabled, iif(isnull(FS.HV),"N",FS.HV) FROM FG LEFT JOIN FS ON FG.FGID = FS.FGID WHERE FG.FGID >= 100 And FG.FGID < 199 and FS.FS_Status = 'Y' and FS.FSID not in (0) and now() >= RDate and now() <= EDate ORDER BY FG.SortOrder desc, FG.FGID DESC , iif(FS.NP=0,"N","V") desc, FS.Priority, FS.FileSetId "

Try
m_Connection = New OleDb.OleDbConnection(m_strConnection)
m_Connection.Open()
m_Command = New OleDbCommand(p_strSql, m_Connection)
daGetData = New OleDbDataAdapter(m_Command)
daGetData.Fill(dtGetData)

m_Connection.Close()
Return dtGetData
Catch ex As Exception
Throw ex
Finally
daGetData = Nothing
m_Command = Nothing
If Not m_Connection Is Nothing Then m_Connection.Close()
m_Connection = Nothing
End Try

When the above Select statement is executed in Access it correctly returns the records but in the above code, it reurns 0 records. No error!!!

Thank you in advance for any suggestion that you might give.

Narayanan Ramanathan

[shweta123]

Hi,

You can try giving other way instead of using OledbCommand

daGetData = New OleDbDataAdapter(sqlquery,connection)
daGetData.Fill(dtGetData)

We have an application developed in VS 203 [VB.NET]
We aretesting the app in WindowsVista
The problem we are facing is as follows:
The app uses an Access database. When trying to execute a select statement, the application returns 0 records, though when the same query is runin Access it returns all the valid records. Please note that we face this ssue only when the app is run in Vista

The details of the codes are as follows:
Dim daGetData As OleDb.OleDbDataAdapter
dim m_Connection As OleDbConnection
dim m_Command As OleDbCommand
dim m_strConnection as string
Dim dtGetData As New DataTable

m_strConnection = "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="mydb.mdb;Jet OLEDB:Database Password=abcd"

pstrSql = "SELECT FG.GroupID, FG.GroupName, iif(isnull(FS.FSID),0,FS.FSID), iif (isnull(FS.name),'',FS.name), iif( iif(isnull(FS.NP),0,FS.NP) = 0, iif(isnull(FS.TN),'',FS.TN) + "-NEW", FS.TN), FG.enabled, iif(isnull(FS.HV),"N",FS.HV) FROM FG LEFT JOIN FS ON FG.FGID = FS.FGID WHERE FG.FGID >= 100 And FG.FGID < 199 and FS.FS_Status = 'Y' and FS.FSID not in (0) and now() >= RDate and now() <= EDate ORDER BY FG.SortOrder desc, FG.FGID DESC , iif(FS.NP=0,"N","V") desc, FS.Priority, FS.FileSetId "

Try
m_Connection = New OleDb.OleDbConnection(m_strConnection)
m_Connection.Open()
m_Command = New OleDbCommand(p_strSql, m_Connection)
daGetData = New OleDbDataAdapter(m_Command)
daGetData.Fill(dtGetData)

m_Connection.Close()
Return dtGetData
Catch ex As Exception
Throw ex
Finally
daGetData = Nothing
m_Command = Nothing
If Not m_Connection Is Nothing Then m_Connection.Close()
m_Connection = Nothing
End Try

When the above Select statement is executed in Access it correctly returns the records but in the above code, it reurns 0 records. No error!!!

Thank you in advance for any suggestion that you might give.

Narayanan Ramanathan