|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
OleDbException on Access DB QueryHi,
I get this error where calling a query in an Access DB with the Nz function contained within it: System.Data.OleDb.OleDbException : Undefined function 'Nz' in expression. I don't believe this should happen. How can I fix it. Hi,
I believe NZ is a Access-specific function, which is not recognized by Jet provider as a valid one. There are some functions (like VBA one), which could be used in Access itself, but could not pass syntax verification when they called from outside using Jet. What you could do is to store your query in an Access database and call this query as a stored procedure from your application. It should eliminate this syntax-related issue. Show quoteHide quote "Nick" <N***@discussions.microsoft.com> wrote in message news:052E5BE0-F2C4-4756-998C-DD6B9D557D9E@microsoft.com... > Hi, > > I get this error where calling a query in an Access DB with the Nz > function > contained within it: > > System.Data.OleDb.OleDbException : Undefined function 'Nz' in expression. > > I don't believe this should happen. How can I fix it. Are you sure this works.
I have my query already stored in the database. Perhaps you can send me working sample code which demonstrates what you mean. Show quoteHide quote "Val Mazur" wrote: > Hi, > > I believe NZ is a Access-specific function, which is not recognized by Jet > provider as a valid one. There are some functions (like VBA one), which > could be used in Access itself, but could not pass syntax verification when > they called from outside using Jet. What you could do is to store your query > in an Access database and call this query as a stored procedure from your > application. It should eliminate this syntax-related issue. > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > "Nick" <N***@discussions.microsoft.com> wrote in message > news:052E5BE0-F2C4-4756-998C-DD6B9D557D9E@microsoft.com... > > Hi, > > > > I get this error where calling a query in an Access DB with the Nz > > function > > contained within it: > > > > System.Data.OleDb.OleDbException : Undefined function 'Nz' in expression. > > > > I don't believe this should happen. How can I fix it. > > > Hi,
I believe (I could be wrong) it should work because in this case provider does not know what is inside of the stored query. Code should be like Dim loConn As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Path\MyDatabase.mdb;User ID=Admin;Password=" ) Dim loCMD As OleDbCommand = New OleDbCommand("MyQueryNameHere", nwindConn) loCMD.CommandType = CommandType.StoredProcedure loConn.Open() Dim myReader As OleDbDataReader = loCMD.ExecuteReader() Show quoteHide quote "Nick Zdunic" <NickZdu***@discussions.microsoft.com> wrote in message news:6AD1EEB3-896B-4294-9665-44A278E26CD2@microsoft.com... > Are you sure this works. > > I have my query already stored in the database. > > Perhaps you can send me working sample code which demonstrates what you > mean. > > "Val Mazur" wrote: > >> Hi, >> >> I believe NZ is a Access-specific function, which is not recognized by >> Jet >> provider as a valid one. There are some functions (like VBA one), which >> could be used in Access itself, but could not pass syntax verification >> when >> they called from outside using Jet. What you could do is to store your >> query >> in an Access database and call this query as a stored procedure from your >> application. It should eliminate this syntax-related issue. >> >> -- >> Val Mazur >> Microsoft MVP >> >> http://xport.mvps.org >> >> "Nick" <N***@discussions.microsoft.com> wrote in message >> news:052E5BE0-F2C4-4756-998C-DD6B9D557D9E@microsoft.com... >> > Hi, >> > >> > I get this error where calling a query in an Access DB with the Nz >> > function >> > contained within it: >> > >> > System.Data.OleDb.OleDbException : Undefined function 'Nz' in >> > expression. >> > >> > I don't believe this should happen. How can I fix it. >> >> >> Nope that doesn't work.
I have replaced Nz with an IIf(IsNull... expression. That works. Interesting that some functions work and others don't. Do you have some information as to way this is the case. Show quoteHide quote "Val Mazur (MVP)" wrote: > Hi, > > I believe (I could be wrong) it should work because in this case provider > does not know what is inside of the stored query. Code should be like > > Dim loConn As OleDbConnection = New > OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data > Source=C:\Path\MyDatabase.mdb;User ID=Admin;Password=" ) > > Dim loCMD As OleDbCommand = New OleDbCommand("MyQueryNameHere", nwindConn) > loCMD.CommandType = CommandType.StoredProcedure > > loConn.Open() > > Dim myReader As OleDbDataReader = loCMD.ExecuteReader() > > -- > Val Mazur > Microsoft MVP > > http://xport.mvps.org > > "Nick Zdunic" <NickZdu***@discussions.microsoft.com> wrote in message > news:6AD1EEB3-896B-4294-9665-44A278E26CD2@microsoft.com... > > Are you sure this works. > > > > I have my query already stored in the database. > > > > Perhaps you can send me working sample code which demonstrates what you > > mean. > > > > "Val Mazur" wrote: > > > >> Hi, > >> > >> I believe NZ is a Access-specific function, which is not recognized by > >> Jet > >> provider as a valid one. There are some functions (like VBA one), which > >> could be used in Access itself, but could not pass syntax verification > >> when > >> they called from outside using Jet. What you could do is to store your > >> query > >> in an Access database and call this query as a stored procedure from your > >> application. It should eliminate this syntax-related issue. > >> > >> -- > >> Val Mazur > >> Microsoft MVP > >> > >> http://xport.mvps.org > >> > >> "Nick" <N***@discussions.microsoft.com> wrote in message > >> news:052E5BE0-F2C4-4756-998C-DD6B9D557D9E@microsoft.com... > >> > Hi, > >> > > >> > I get this error where calling a query in an Access DB with the Nz > >> > function > >> > contained within it: > >> > > >> > System.Data.OleDb.OleDbException : Undefined function 'Nz' in > >> > expression. > >> > > >> > I don't believe this should happen. How can I fix it. > >> > >> > >> > > > It is just because some Access-specific functions and some VBA functions are
not supported by Jet provider. I remember it was an article about it but I cannot find it anymore. If I do, I will post it here Show quoteHide quote "Nick Zdunic" <NickZdu***@discussions.microsoft.com> wrote in message news:FCBEE59A-4DC7-4179-A41B-620B14ABD168@microsoft.com... > Nope that doesn't work. > > I have replaced Nz with an IIf(IsNull... expression. > > That works. Interesting that some functions work and others don't. Do > you > have some information as to way this is the case. > > "Val Mazur (MVP)" wrote: > >> Hi, >> >> I believe (I could be wrong) it should work because in this case provider >> does not know what is inside of the stored query. Code should be like >> >> Dim loConn As OleDbConnection = New >> OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data >> Source=C:\Path\MyDatabase.mdb;User ID=Admin;Password=" ) >> >> Dim loCMD As OleDbCommand = New OleDbCommand("MyQueryNameHere", >> nwindConn) >> loCMD.CommandType = CommandType.StoredProcedure >> >> loConn.Open() >> >> Dim myReader As OleDbDataReader = loCMD.ExecuteReader() >> >> -- >> Val Mazur >> Microsoft MVP >> >> http://xport.mvps.org >> >> "Nick Zdunic" <NickZdu***@discussions.microsoft.com> wrote in message >> news:6AD1EEB3-896B-4294-9665-44A278E26CD2@microsoft.com... >> > Are you sure this works. >> > >> > I have my query already stored in the database. >> > >> > Perhaps you can send me working sample code which demonstrates what you >> > mean. >> > >> > "Val Mazur" wrote: >> > >> >> Hi, >> >> >> >> I believe NZ is a Access-specific function, which is not recognized by >> >> Jet >> >> provider as a valid one. There are some functions (like VBA one), >> >> which >> >> could be used in Access itself, but could not pass syntax verification >> >> when >> >> they called from outside using Jet. What you could do is to store your >> >> query >> >> in an Access database and call this query as a stored procedure from >> >> your >> >> application. It should eliminate this syntax-related issue. >> >> >> >> -- >> >> Val Mazur >> >> Microsoft MVP >> >> >> >> http://xport.mvps.org >> >> >> >> "Nick" <N***@discussions.microsoft.com> wrote in message >> >> news:052E5BE0-F2C4-4756-998C-DD6B9D557D9E@microsoft.com... >> >> > Hi, >> >> > >> >> > I get this error where calling a query in an Access DB with the Nz >> >> > function >> >> > contained within it: >> >> > >> >> > System.Data.OleDb.OleDbException : Undefined function 'Nz' in >> >> > expression. >> >> > >> >> > I don't believe this should happen. How can I fix it. >> >> >> >> >> >> >> >> >> On Mon, 28 Feb 2005 21:19:02 -0800, "Nick Zdunic" <NickZdu***@discussions.microsoft.com> wrote: ¤ Nope that doesn't work.¤ ¤ I have replaced Nz with an IIf(IsNull... expression. ¤ ¤ That works. Interesting that some functions work and others don't. Do you ¤ have some information as to way this is the case. ¤ The following may help: How to configure Jet 4.0 to prevent unsafe functions from running in Access 2000 and Access 2002 http://support.microsoft.com/default.aspx?scid=kb;en-us;239482 The Nz function is not available via sandbox mode. Paul ~~~ pclem***@ameritech.net Microsoft MVP (Visual Basic)  
Other interesting topics
Dataset for crystal report
how to construct a Schema filling a dataset taking toooo much time Problem with SQL Server Script... Many data components - use one transaction? CommandBuilder generates incorrect SQL How do I "refresh" my bound control? Transaction please ?? Store Procedure return values to VB.net app Problem to restore an SQL database.... |
|||||||||||||||||||||||
