OleDb Parameter Into OleDbCommand Not Passing into Query

Question

I'm attempting to pass a string of numbers into a query that will run against our AS400 connection however it doesn't seem that my value is actually making it to the query. I am returning a result when I debug to the point of the parameter so I know I'm getting my number.

var command = new OleDbCommand("SELECT UMACT, UMCUS, UMNAM, UMAD1, UMAD2, UMAD3, UMZIP, UMOPH, UMSLC FROM CFFILES.UMST WHERE trim(UMEMT) = '?' ", connection);
                //Test meter number: 59115796
                connection.Open();
                OleDbParameterCollection paramCollection = command.Parameters;
                paramCollection.Add(meternumber.ToString(), OleDbType.LongVarChar);

When I pass a number directly into the query it returns a perfect result however when I try to pass a number from the parameter it returns null.

Here is the variable itself that I know is being passed to my parameter that I created above because I get a result when I debug to that line.

public IEnumerable<CustomerInfoModel> GetCustomerInfo()
{
    int meternumber = 59115796;
    getAS400Data(meternumber);
    return customerList;
}

For my AS400 query to work I have to have the ticks around the ? in the query so I'm not sure if that is perhaps causing the issue.

Answer 1

You have the call to Add method wrong. The first parameter of Add is the Name of the parameter, the second is the DataType and finally you should set the value of the parameter in this way

OleDbParameterCollection paramCollection = command.Parameters;
paramCollection.Add("whatever", OleDbType.LongVarChar).Value = meternumber.ToString();

See OleDbParameterCollection.Add

And no, you shouldn't add ticks around the question mark placeholder. If you do you transform your placeholder in a literal string and obviously you don't have any row with a question mark as value.

A final note, in OleDb parameters doesn't have a name, but when you add them to your collection you should provide one so you are free to choose whatever you like for the name. Just remember to keep them in the same order in which the placeholders (?) appears in your query string