Cannot Get Value When OleDbParameter Used

Question

It is not good form to create a query like

let fnam_query =
    "select * from file_name_info where fnam_origin = 'invoice_cloud'"

But the code block below has two problems. First, the fnam_readOk returns false from the read.

Second, how can the OleDbParameter be disposed? I tried using use, but got a compile-time error saying OleDbType.Char could not be used within a use.

let fnam_query = 
    "select * from file_name_info where fnam_origin = '?' "

use fnam_cmd = new OleDbCommand(fnam_query, db_con)
let local_params = new OleDbParameter("fnam_origin", OleDbType.Char)
fnam_cmd.Parameters.Add(local_params) |> ignore

let fnam_reader = fnam_cmd.ExecuteReader ()
let fnam_readOK = fnam_reader.Read ()

let ic_lb_fnam =
    if fnam_readOK then
        fnam_reader.GetString(2)
    else
        "ic_lockbox.txt"

@TheQuickBrownFox So, how can it be cleaned up, if created with new? I'm beginning to think that part of my code doesn't need to be there, because there is a `Parameters` array that is part of the `OleDbCommand`. — octopusgrabbus, Jun 16 '17 at 14:38
Are you forgetting to put an actual value into the parameter? That would explain why it seems like no rows are returned. Try replacing `OleDbType.Char` with `"invoice_cloud"`. That uses a different overload of the `OleDbParameter` constructor. — TheQuickBrownFox, Jun 16 '17 at 14:42
RE `OleDbParameter`: It doesn't need to be cleaned up because it's not holding any external resources. — TheQuickBrownFox, Jun 16 '17 at 15:01
@octopusgrabbus post the *actual* error, not its a summary of the error text. You confused people by talking about `use`. The error has nothing to do with the `use` keyword and everything to do with invalid query syntax — Panagiotis Kanavos, Jun 19 '17 at 13:27

Panagiotis Kanavos · Accepted Answer · 2017-06-19T13:54:02.163

There are a lot of problems with this code. One of them, is that you used the OleDbParameter overload that passes a name and value. The line new OleDbParameter("fnam_origin", OleDbType.Char) specifies a parameter with the name fnam_origin and an integer value equal to whatever the underlying value of OleDbType.Char is.

The other problem is that you don't use that parameter at all. '?' is just a string that contains ?.

You don't need to quote parameters in a parameterized query. They aren't placeholders for string replacement. They specify actual, strongly typed parameters, just like an F# function parameter.

Your query should be :

let fnam_query = 
"select * from file_name_info where fnam_origin = ? "

You should also use the correct parameter type. Char is used only for fixed-length parameters. You should use VarChar or even better, NVarchar.

Finally, you should pass the parameter value that you want. Your code doesn't specify a parameter value at all.

The entire function should look like this :

let fnam_query =  "select * from file_name_info where fnam_origin = ? "
use db_con = new OleDbConnection("...")
use fnam_cmd = new OleDbCommand(fnam_query, db_con)
let local_params = new OleDbParameter("origin", SqlDbType.NVarChar,100)
fnam_cmd.Parameters.Add(local_params) |> ignore
local_params.Value <- "GR"


db_con.Open()
let fnam_reader = fnam_cmd.ExecuteReader ()
let fnam_readOK = fnam_reader.Read ()
...

A better implementation though, would be to create the command once and reuse it with different connections and values:

let build_cmd = 
    let fnam_query =  "select * from file_name_info where fnam_origin = ? "
    let fnam_cmd = new OleDbCommand(fnam_query)
    let local_params = new OleDbParameter("whatever", SqlDbType.NVarChar,100)
    fnam_cmd.Parameters.Add(local_params) |> ignore
    fnam_cmd



use db_con = new OleDbConnection("...")
build_cmd.Connection <- db_con
build_cmd.Parameters.[0].Value <- "GR"
db_con.Open()

score 1 · Answer 2 · answered Jun 19 '17 at 16:46

Based on the excellent answer I got from Panagiotis Kanavos, I've altered a different place in my code where I needed parameters that were not embedded in the query string. I chose to use cmd.Parameters.Add rather than have a separate OleDbParameter val.

(* by looking at the xfer_type, really the arg passed to main, 
we can determine the report type parameter for the Access 
database. *)
let select_report_type xfer_type =
    match xfer_type with
    | "/al" -> 0
    | "/am" -> 1
    | "/ap" -> 2
    | "/pm" -> 3
    | "/pp" -> 4
    | _     -> 99

let query = "select count(*) from ProcessStatus where ReportType = ? and ReportDate = ? and ReportFileName = ? "
use cmd = new OleDbCommand(query , db_con)
cmd.Parameters.Add(new OleDbParameter("ReportType",(OleDbType.VarChar,5))) |> ignore
cmd.Parameters.[0].Value <- ((select_report_type xfer_type).ToString())

cmd.Parameters.Add(new OleDbParameter("ReportDate",OleDbType.VarChar, 11)) |> ignore
cmd.Parameters.[1].Value <- report_date

cmd.Parameters.Add(new OleDbParameter("ReportFileName",OleDbType.VarChar, 100)) |> ignore
cmd.Parameters.[2].Value <- fn

let sql_reader = cmd.ExecuteReader ()
        if (sql_reader.Read ()) then

Cannot Get Value When OleDbParameter Used

2 Answers2