SOME SQL FUNCTION NOT ALLOWED WITH OLEDB CONNECTION

emi_sastra
emi_sastra used Ask the Experts™
on
Hi All,

I try below code to transfer data from dbf to sql, some functions are not allowed.

ISNULL AND CASE RAISE ERROR.

 Private Function Isi_Detil_Data_From_DP() As DataTable
       
        sdbSQLCommand.AppendLine(" SELECT ")
       
        sdbSQLCommand.AppendLine(" ISNULL(A.CAB, '') AS KOAO")
        'sdbSQLCommand.AppendLine(", CASE A.CAB WHEN '99' THEN '1' ELSE '2' END AS  
       
        sdbSQLCommand.AppendLine(" FROM " & strFileNameA & " AS A")

         Dim strSQLCommand As String = sdbSQLCommand.ToString

        Try

            Dim accessDataSet As New DataSet()
            Dim accessConnection As New OleDbConnection(strDBFFileConnection)
            Dim accessAdapter As New OleDbDataAdapter(strSQLCommand, accessConnection)

            accessAdapter.Fill(accessDataSet, "Data")
            Return accessDataSet.Tables("Data")

        Catch ex As Exception
   
        End Try

        Return Nothing

    End Function

What should I do ?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are you still connecting to SQL? Or Access? Which provider? Jet?

use IIF in place of both IsNull and Case.

IIF(fieldname Is Null, '', fieldname)

Author

Commented:
Hi CodeCruiser,

It works.

Thank you very much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial