SSIS 2005 - ODBC through ADO.Net connection - query with Parameter not working

[GlobalOrder]

I have a execute sql task in my SSIS package and my i am using ADO.Net
ODBC for my connection and was able to get queries with out parameters
working but as soon as i add a parameter it gives me below error

[Execute SQL Task] Error: Executing the query "SELECT COUNT(ID) As
CatNumRec FROM Client WHERE Name= ?"
failed with the following error: "The OdbcParameterCollection only
accepts non-null OdbcParameter type objects, not SqlParameter
objects.".


Below query works fine
SELECT COUNT(ID) As CatNumRec FROM Tbl_Chd.AssmntCategory

Any ideas as i am kinda stuck????

Regards

[Sukesh Hoogan]

Without seeing your code, try, if you are using SqlClient (SqlCommand /
SqlParameter)

"SELECT COUNT(ID) As CatNumRec FROM Client WHERE Name= @abc"

@abc is the parameter name

HTH

Regards
Sukesh Hoogan
Bombay, India
[Enterprise Resource Planning & Business Intelligence]

[GlobalOrder]

It doesnt seem to work Sukesh

Check the error below

Execute SQL Task] Error: Executing the query "SELECT COUNT(ID) As

CatNumRec FROM Client WHERE Name= @Category" failed with the following
error: "ERROR [42000] [Caché ODBC][State : 37000][Native Code 12] [C:
\Program Files\Microsoft SQL Server\90\DTS\binn\DtsDebugHost.exe]
[SQLCODE: <-12>:<A term expected, beginning with one of the following:
identifier, constant, aggregate, $$, :, (, +, -, %ALPHAUP, %EXACT,
%SQLSTRING, %SQLUPPER, %STRING, or %UPPER>] [Cache Error:
<<SYNTAX>errdone+1^%qaqqt>] [Details: <Prepare>] [%msg: < SQL ERROR
#12: A term expected, beginning with either of: identifier, constant,
aggregate, $$, (, :, +, -, %ALPHAUP, %EXACT, %SQLSTRING, %". Possible
failure reasons: Problems with the query, "ResultSet" property not set
correctly, parameters not set correctly, or connection not established
correctly.

Any ideas????

> > Regards- Hide quoted text -
>
> - Show quoted text -

[Sukesh Hoogan]

Post your SSIS/ADO.NET Code, may be one would be able to help.

[GlobalOrder]

Just to mention ODBC driver i am using is 5.00.6305.00 and Cache
version is 5.0.20. I really appreciate any help with this.

here is my code in ScriptMain class

public Class ScriptMain

Inherits UserComponent
Dim odbcConn As OdbcConnection
Dim odbcCmd As OdbcCommand
Dim odbcParam As OdbcParameter
'Dim odbcdread As OdbcDataReader
Dim connMgr As IDTSConnectionManager90
Dim myMetadata As IDTSComponentMetaData90

'odbcdread = null
'odbcdread = Nothing
Public Overrides Sub AcquireConnections(ByVal Transaction As
Object)
Dim connectionString As String
connectionString =
Me.Connections.ACategoryConn.ConnectionString
odbcConn = New OdbcConnection(connectionString)
odbcConn.Open()
End Sub

Public Overrides Sub PreExecute()
odbcCmd = New OdbcCommand("SELECT Name FROM
Tbl_Chd.AssmntCategory WHERE (Name=@Category)",
odbcConn)
odbcParam = New OdbcParameter("@Category", OdbcType.NVarChar)
odbcCmd.Parameters.Add(odbcParam)

End Sub
Public Overrides Sub ACategoryInput_ProcessInputRow(ByVal Row As
ACategoryInputBuffer)
Dim odbcdread As OdbcDataReader

Try

myMetadata = Me.ComponentMetaData

If (Row.AssmntCategory_IsNull And Row.DCDate_IsNull) Then
'Skip current row
Exit Sub
End If

With odbcCmd
.Parameters("@Category").Value = Row.AssmntCategory
.ExecuteNonQuery()
End With

If odbcdread.HasRows = True Then
myMetadata.FireInformation(0, "IN dread hasrows", "",
"", 0, False)
Else
myMetadata.FireInformation(0, "IN dread No Rows
Returned", "", "", 0, False)
End If
Catch ex As Exception
'MsgBox(ex.Message)
myMetadata.FireInformation(0, "ERROR=", "did not return
anything " + ex.Message.ToString(), "", 0, False)
'MsgBox(ex.ToString())
Exit Sub

End Try
End Sub
Public Overrides Sub ReleaseConnections()
odbcConn.Close()
End Sub

End Class

> >> - Show quoted text -- Hide quoted text -

[Sukesh Hoogan]

Change as follows and try

odbcCmd = New OdbcCommand("SELECT Name FROM Tbl_Chd.AssmntCategory WHERE

(Name=?)",odbcConn)

[GlobalOrder]

I was trying that before but with out any sucess, then i tried as you
suggested.
With Name=? it is giving me Obejct not Set to reference error on data
read, i believe it is not returning any rows.
I would like to check if the value passed in parameter exists before
insert, if exists ignore the record otherwise insert the record into
table.

As soon as it executes the query on the next line when i try to see
whether it returned any rows or not it gives me below error
Object reference not set to an instance of an object and i was not
able to get around that.

really appreciate all your feedback

Regards

[GlobalOrder]

Sukesh,

I have some success and i believe the problem is adding and passing
parameter values. If i use the below query it returns value and works
fine as it should
Select Name From MyTable Where Name='test entry'

I dont know what is wrong with my below code that it is not assigning
the paramerter values

odbcParam = New OdbcParameter("@Name", OdbcType.NVarChar)
odbcCmd.Parameters.Add(odbcParam)
Dim odbcdread As OdbcDataReader
odbcCmd .Parameters("@Name").Value = Row.CategoryVal
odbcdread = odbcCmd.ExecuteReader

If you need all code let me know.

Regards

[Sukesh Hoogan]

a) Have you tried to retrieve data using
System.Data.SqlClient class
instead of
System.Data.Odbc class ?

1 do not use .Net anymore.
However some suggestions
b) Try

With odbcCmd
.Parameters("@Category").Value = Row.AssmntCategory

.Prepare()
.ExecuteNonQuery()
End With

c) Transfer the parameter coding to
ACategoryInput_ProcessInputRow procedure and see if the parameter value
does not get set to null.

d) Simplified code (not sure if it would work)

str= "SELECT whatever FROM SomeTable WHERE name=?"
odbccommand = New OdbcCommand(str, odbcconn)
odbccommand.Parameters.Add(New OdbcParameter)
odbccommand.Parameters.Item(0).Direction = ParameterDirection.Output
odbccommand.Parameters.Item(0).Size = somesize
odbccommand.Parameters.Item(0).Value = somevalue
odbccommand.Prepare()
odbccommand.ExecuteNonQuery()

If all this does not work, log your problem with ISC Support.

[Sukesh Hoogan]

Did not see your last post before posting.
I am glad your problem has been solved.
No I would not need the code, thanks, as I do not use .net

Regards

[Sukesh Hoogan]

It is past midnight here, I am not reading the posts clearly, too
sleepy. Misunderstood your last post.

Try what I have suggested and let me know.

Regards
Sukesh

Sukesh Hoogan
Bombay, India
[Enterprise Resource Planning & Business Intelligence]