[RESOLVED] Issue with OleDb : [2002] works, [2005] fails

[NotLKH]

I've been using ODBC to query for data from my companies sqlanywhere database, but recently a particular query failed.

My coworker has been using OleDB for similar transactions, and his query with the same sql string worked without issue.

So, I built a quick test in VB.Net 2002 using OleDB methods, and it also worked without issue.

So then I created the exact same test in .Net 2005, and it failed with the following error:

System.Data.OleDb.OleDbException: 'ASAProv' failed with no error message available, result code: DB_E_ERRORSOCCURRED(0x80040E21).
at System.Data.OleDb.OleDbDataReader.ProcessResults(OleDbHResult hr)
at System.Data.OleDb.OleDbDataReader.GetRowDataFromHandle()
at System.Data.OleDb.OleDbDataReader.GetValueBinding(MetaData info)
at System.Data.OleDb.OleDbDataReader.GetValue(Int32 ordinal)
at System.Data.OleDb.OleDbDataReader.get_Item(Int32 index)
at New_Ole.Form1.GET_TASK_PARM1(String mTaskID, String& mErrMsg) in C:\Documents and Settings\lou\My Documents\Visual Studio 2005\Projects\New_Ole\New_Ole\Form1.vb:line 87



However, if I run the 2005 program on my coworkers machine, it faultlessly works without issue.

We both installed 2005 at the same time, and as far as I can tell, we are both updated to the same level.

I am stumped as to how to what I should do to get this working.

HELP!

{BTW, the data I was trying to collect is only 8985 characters long, and the data format is a LONG VARCHAR}

-Lou

[.NetNinja]

What is the code for line 87?

[Jenner]

Can you post the code with the routine in it? This looks like an odd one.

[stanav]

What MDAC version do you have in your machine?

[NotLKH]

On My Machine, in 2005, It jumps from:

If Not IsDBNull(myReader(0)) Then

to the exception handling in:

    Public Function GET_TASK_PARM1(ByVal mTaskID As String, ByRef mErrMsg As String) As Boolean
        Try
            Dim myCommand As Data.OleDb.OleDbCommand
            Dim myReader As Data.OleDb.OleDbDataReader


            Dim mSql As String
            mSql = "select parm1 from cms.tasks where task_id = '" & mTaskID & "'"

            myCommand = New Data.OleDb.OleDbCommand("", MyODBCConn)
            myCommand.CommandText = mSql

            myReader = myCommand.ExecuteReader()

            If myReader.Read Then
                If Not IsDBNull(myReader(0)) Then
                    mErrMsg = myReader(0)
                Else
                    mErrMsg = "NULL"
                End If
            Else
                mErrMsg = "FAILED!"
            End If
            myReader.Close()
            myCommand.Dispose()
            Return True
        Catch mErr As System.Exception
            mErrMsg = mErr.Message
            Clipboard.SetDataObject(mErr.ToString)
            MessageBox.Show(mErr.ToString)
            Return False
        End Try
    End Function

but not in the 2002 environment on my machine,
nor in the 2005 environment on my coworkers machine.

Also, as an FYI:

Even though the connection is called "MyODBCConn", it is a relic naming convention.
It is defined as:

Public MyODBCConn As OleDb.OleDbConnection


and it is opened via:

    Public Function CONNECT_IF_POSSIBLE_OLEDB(ByRef mDevMode As Boolean) As Boolean
        Try
            If Not IS_CONNECTED Then
                If mDevMode Then
                    mConnectionString = MyDevConnectionString
                Else
                    mConnectionString = MyCMSConnectionString
                End If
                MyODBCConn = New Data.OleDb.OleDbConnection(mConnectionString)
                MyODBCConn.Open()
                IS_CONNECTED = True

            End If
            Return True
        Catch mErr As System.Exception
            Clipboard.SetDataObject(mErr.Message, True)
            MessageBox.Show(mErr.Message)
            Return False

        End Try
    End Function

[NotLKH]

What MDAC version do you have in your machine?

My System, Windows XP Professional Version 2002, SP3, does not have the key as indicated on:

http://en.wikipedia.org/wiki/Microso...ess_Components

And downloading the component checker that was suggested indicates the attached image:

[stanav]

So you've got MDAC 2.8 which is already up to date. We can eliminate this as a cause. And I don't see anything obvious that could throw an exception in your code.

One more question, what database are you connecting to?

[NotLKH]

It is a Sybase SQLAnywhere database.

[Jenner]

Instead of: myReader(0)
Use: myReader.Items(0)

[NotLKH]

Instead of: myReader(0)
Use: myReader.Items(0)

Good Suggestion!

            If myReader.Read Then

                If Not IsDBNull(myReader.Item(0)) Then
                    mErrMsg = myReader.Item(0)
                Else
                    mErrMsg = "NULL"
                End If
            Else
                mErrMsg = "FAILED!"
            End If

Didn't work.
Same error.
FYI:
I went with .Item(0), as .Items(0) does not exist.

[NotLKH]

Yay!

I searched for new sybase anywhere updates,
Found one,
Installed it,
And now my .Net 2005 works!


And My .Net 2002 still works!

woohoo!


Thanks everybody!

[Jenner]

Haha... that figures. The drivers and outdated components will get you every time. I one time spend a week trying to fix something that was corrected in a patch to the controls I was using.

Glad you got it working!