Script task Error

I have a script task that is supposed to perform some task but it fails and throws this exception

"Unable to cast COM object of type System._OComObject to class System.Data.Odbc.odbcConnection". Instances of types that represent com components cannot be cast to the types that represent COM components; they can be caste to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the inteface".

Here is the code that throws this

Dim sqlString As String

Dim conn As Odbc.OdbcConnection

Dim da As Odbc.OdbcDataAdapter

Dim ds As Data.DataSet = New Data.DataSet()

sqlString = " SELECT count(*)FROM tmpDAY INNER JOIN tblData ON tmpDAY.CusID = tblData.datFKCusID WHERE(tmpDAY.Serial = tblData.datSerial)"

Dim connName As String = Dts.Connections(0).Name

Try

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

da = New Odbc.OdbcDataAdapter(sqlString, conn)

Catch ex As Exception

MsgBox(ex.Message.ToString())

End Try

Any suggestion will be greatly appreciated?

you could try replacing your CType with a normal ODBC connection constructed from a string
i.e. replace
conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with

Dim connString As String = Dts.Connections(0).ConnectionString

conn = New Odbc.OdbcConnection(connString)|||

replace:

conn = CType(Dts.Connections(0).AcquireConnection(Nothing), Odbc.OdbcConnection)

with:

conn = New Odbc.OdbcConnection(connectionString)

conn.Open()

SCOPE_IDENTITY() SqlCe problem

I unable to do the SCOPE_IDENTITY on a insert query at SQL Mobile.

I need to perform on this query the insert on Client table and to get the ID.

I am programming on Visual Basic on Visual Studio 2005

My code is:

Dim ClientID as Integer

Dim sql As String = "INSERT INTO Client(Number) VALUES('5'); SELECT scope_identity()"

Dim cmd As New SqlCeCommand(sql, connection)

connection.Open()

ClientID = Convert.ToInt32(cmd.ExecuteNonQuery())

connection.Close()

And I get an error like this:

There was an error parsing the query. [ Token line number = 1,Token line offset = 76,Token in error = SELECT ]

Thanks!

You cannot have more queries in the same batch and scope_identity is not supported by SQL CE/Mobile. Use "SELECT @.@.IDENTITY" in a second .ExecuteNonQuery call.