Parameter Collection Question

[EStallworth]

I am creating an app that has been using mainly SQL server connections. I now am having to connect to an Informix DB using ODBC. My question is why I am getting an error when I my parameters are in the same format that I had been using. I am assuming that I will have to actually create a parameters collection instead of just defining it. I am reading the MSDN llibrary and the reference book I have as well. But if anyone can explain it or just tell me why, I would very much appreciate it.


SELECT trans_no, trans_date, check_no, trans_amt, vend_no
FROM informix.transact
WHERE (vend_no = @Param1)


Then I define @Param1 with

OdbcDataAdapter1.SelectCommand.Parameters("@Param1").Value = Combobox1.Text

I get an error that says "An Odbc Parameter with the Parameter name 'Param1' is not contained by this Odbc Parameter Collection."

 

[lingsn]

How about using

SELECT trans_no, trans_date, check_no, trans_amt, vend_no
FROM informix.transact
WHERE (vend_no = ?)

Additional information can get from

http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbcdataadapter.selectcommand.aspx

 

[TechGnome]

The short answer is that the ODBC doesn't support named parameters. Use the ? notation instead.

-tg

 

[EStallworth]

Sorry I didn't include my error message the first time, but I am getting the same error using the ? as well.

"An OdbcParameter with the Parameter name '?' is not contained by this OdbcParameterCollection."

The error message previously stated the same thing listing '@Param1' in the place of the '?'.

 

[EStallworth]

Problem solved guys.

I just went ahead and did it in code and it is working fine now.

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ODBCAdapt [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] System.Data.Odbc.OdbcDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connection [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcConnection([/SIZE][SIZE=2][COLOR=#800000]""[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] selectcommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Odbc.OdbcCommand([/SIZE][SIZE=2][COLOR=#800000]"Select trans_no, trans_date, check_no, trans_amt, vend_no FROM informix.transact WHERE vend_no = ?"[/COLOR][/SIZE][SIZE=2], connection)[/SIZE]
[SIZE=2]selectcommand.Parameters.Add([/SIZE][SIZE=2][COLOR=#800000]"?"[/COLOR][/SIZE][SIZE=2], Odbc.OdbcType.NVarChar, 10, [/SIZE][SIZE=2][COLOR=#800000]"vendor_no"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] param [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = ComboBox1.Text.Remove(2, 1)[/SIZE]
[SIZE=2]selectcommand.Parameters([/SIZE][SIZE=2][COLOR=#800000]"?"[/COLOR][/SIZE][SIZE=2]).Value = param.Remove(5, 1)[/SIZE]
[SIZE=2]ODBCAdapt.SelectCommand = selectcommand[/SIZE]
[SIZE=2]ODBCAdapt.Fill(InformixDataSet11)[/SIZE]