3

I have been pulling my hair out trying to figure out what I can't get parameters to work in my query. I have the code written in VB.NET trying to do a query to an AS/400. I have IBM Access for Windows installed and I am able to get queries to work, just not with parameters. Any time I include a parameter in my query (ex. @MyParm) it doesn't work. It's like it doesn't replace the parameter with the value it should be. Here's my code:

I get the following error: SQL0206: Column @MyParm not in specified tables

Here's my code:

Dim da As New OleDbDataAdapter
Dim dt As New DataTable

da.SelectCommand = New OleDbCommand
da.SelectCommand.Connection = con

da.SelectCommand.CommandText = "SELECT * FROM MyTable WHERE Col1 = @MyParm"
With da.SelectCommand.Parameters
   .Add("@MyParm", OleDbType.Integer, 9)
   .Item("@MyParm").Value = 5
End With

' I get the error here of course
da.Fill(dt)

I can replace @MyParm with a literal of 5 and it works fine. What am I missing here? I do this with SQL Server all the time, but this is the first time I am attempting it on an AS400.

Jeff Stock
  • 3,796
  • 12
  • 46
  • 61
  • I have to say that this question bears a striking similarity to http://stackoverflow.com/questions/493119/as400-sql-query-with-parameter, but I am not familiar enough with the problem domain to mark as duplicate. – spender Mar 24 '10 at 22:12

2 Answers2

1

You're right, same question as AS400 SQL query with Parameter, which contains the solution.

Community
  • 1
  • 1
Jeff Stock
  • 3,796
  • 12
  • 46
  • 61
  • I discovered to use an OLEDB connection I can't use named parameters. I instead have to use question marks and add the parameters as my column names with the @ sign in front. If I want to use named parameters I have to use IBM's connector. – Jeff Stock Mar 29 '10 at 12:36
1

Just a note: Host Integration Server 2006 supports named parameters.

bryanjonker
  • 3,386
  • 3
  • 24
  • 37