CODEWITHSUNDEEP
.netvb.netms-access
majjam
majjam

Reputation: 1326

Syntax error in PARAMETER clause when passing through parameters as Decimal types using MS Access querydefs

I'm trying to write an insert query that takes financial data and writes it to my Access database from .net

The problem is that I cannot pass through my data via decimal datatype parameters despite being able to select them as Decimal datatype in the Ms Access parameters window, and despite the database being able to create table fields of Decimal datatype.

I was wondering, is what I am trying to do possible? If so how?

Here is a subsection of my query:

PARAMETERS pMidPrice Decimal;
INSERT INTO NonStandardPricingInstructions ( MidPrice)
SELECT pMidPrice as Expr1

If I change Decimal to IEEEDouble then it works, but I would prefer to pass through a Decimal if possible.

Many thanks

Upvotes: 2

Views: 1773

Answers (2)

majjam
majjam

Reputation: 1326

For anyone with the same issue as me, I've detailed a solution here: Store more than four decimal places in an MS Access Currency field

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

The following seems to work for me:

For a saved query in Access named [qryDecimalTest]:

PARAMETERS pDecimalColValue Decimal;
SELECT Clients.ID
FROM Clients
WHERE (((Clients.decimalCol)=[pDecimalColValue]));

the following C# code finds the record where the Decimal field is 3 and returns the correct ID value

cmd.CommandText =
    "SELECT ID FROM qryDecimalTest";
decimal queryParameterValue = 3;
cmd.Parameters.Add("?", OleDbType.Decimal).Value = queryParameterValue;
var thing = cmd.ExecuteScalar();
Console.WriteLine(String.Format("{0} ({1})", thing, thing.GetType().ToString()));

Edit re: comments

I tried another test, but this time I used Jet.OLEDB against an Access 2000 .mdb file. I changed the saved query in Access to

PARAMETERS pMidPrice Decimal;
INSERT INTO NonStandardPricingInstructions ( MidPrice )
SELECT [pMidPrice] AS Expr1;

and used the following C# code

cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText =
    "qryDecimalTest";
decimal queryParameterValue = 3;
cmd.Parameters.Add("?", OleDbType.Decimal).Value = queryParameterValue;
cmd.ExecuteNonQuery();

That worked fine, too.

Upvotes: 2

Related Questions