2

The following code crashes with this error:

System.Data.OleDb.OleDbException: 'Too few parameters. Expected 1'

Dim selectString As String = "SELECT * FROM Products WHERE id = ?;"
Dim cmd As OleDbCommand = New OleDbCommand(selectString, dbOleDB)
cmd.Parameters.AddWithValue("ID", id)
Dim reader As OleDbDataReader = cmd.ExecuteReader()

If I do a repair on the Access Database Engine installation the error disappear for a day.
The same thing happens on multiple machines running different versions of Windows.
This problem started about 2 weeks ago.

Anyone have any idea whats happening?

djv
  • 15,168
  • 7
  • 48
  • 72
Jza
  • 21
  • 1
  • Is there possibly an Access update/patch occurring (and recurring after the repair)? – UnhandledExcepSean Feb 19 '20 at 18:45
  • No updates/patches since February 12. I got it working yesterday and it stopped working today. – Jza Feb 19 '20 at 19:07
  • I've had weird issues when naming parameters with Oledb (it's actually not supposed to be possible). Try using `cmd.Parameters.Add(id)` and see how this goes. – Jimmy Smith Feb 19 '20 at 19:08
  • And you have verified your column is id in table Products and it is a numeric type? – UnhandledExcepSean Feb 19 '20 at 19:14
  • I am pretty sure its not a problem in my code or in database. It has been working for over a year with no problem and i can make it work again(for a day) by doing a repair on the Access Database Engine install. – Jza Feb 19 '20 at 20:24
  • 1
    If repair fixes it then it is safe to assume that the dbase is getting corrupted. That's a bummer problem. It *might* be related to a repair also compacting the dbase, 2 jiggabytes isn't much. But all and all, you'd start shopping for another engine. – Hans Passant Feb 19 '20 at 22:00
  • 1
    @JimmySmith The overload of .Add that takes a single parameter is expecting a Parameter object. – Mary Feb 20 '20 at 20:11
  • @Mary you're right. I've corrected this in my answer below. – Jimmy Smith Feb 20 '20 at 20:39

1 Answers1

0

OleDbCommand does not behave right with named parameters.

Try this instead,

Dim selectString As String = "SELECT * FROM Products WHERE id = ?;"
Dim cmd As OleDbCommand = New OleDbCommand(selectString, dbOleDB)
cmd.Parameters.Add("@ID", OleDbType.BigInt).Value = id; '@ID essentially means nothing here.  The Adds you make have to be sequential
Dim reader As OleDbDataReader = cmd.ExecuteReader()
Jimmy Smith
  • 2,452
  • 1
  • 16
  • 19
  • 1
    I will give this a try if/when the code stop working. At the moment the code runs fine for the last 2 days. – Jza Feb 21 '20 at 07:57
  • @Jza that makes me feel that it's encountering some data that is throwing it off. .AddWithValue is notorious for causing this, because it guesses the datatype involved. What type is the column ID? – Jimmy Smith Feb 21 '20 at 14:10
  • From comments, if Access databases get to 2GB they are notorious for getting corrupted. I have to backup data from a few I use, to slim databases down, or I encounter some of this funkiness. – Jimmy Smith Feb 21 '20 at 14:12
  • 1
    The type is the standard type for the ID column. The size of the database is 5MB. – Jza Feb 22 '20 at 12:07
  • Then AddWithValue is likely the issue :) – Jimmy Smith Feb 22 '20 at 13:23
  • Now the error is back and i tried your suggestion, but sadly it fails in the exact same way. – Jza Feb 24 '20 at 13:57
  • sorry to hear. Do you have the datatype `OleDbType.VarChar` this may be `OleDbType.Integer` ? We need to figure out if it's just 1 record or any? Is this potentially a Windows Update issue? A few months back, Microsoft made a doozy that messed up my Access applications. – Jimmy Smith Feb 24 '20 at 14:09
  • 1
    The datatype is OleDbType.BigInt. The whole can be read using Select *, it only fails when using Where Id = ? – Jza Feb 25 '20 at 12:54
  • @Jza then that seriously keeps me feeling that the data it's pulling has a `dbnull` or string value gumming up the works. Have you verified each and every id this is processing is valid? – Jimmy Smith Feb 25 '20 at 14:08
  • 1
    I am sure its not a problem with the database. On one machine it works, on another it doesnt. Doing a repair on the Microsoft Access Database Engine 2016 (not the database itself) fixes the problem for a random time periode (one day+) – Jza Feb 26 '20 at 09:03
  • @Jza I think I know what the deal is. Microsoft had some buggy office updates late last year. Is the machine you're working on running with every latest update? They had to release an update to fix another update (it was a PAIN IN THE ... for me) – Jimmy Smith Feb 26 '20 at 14:19
  • Also, I would remove all Access engines and reinstall that latest. Sometimes you have conflicts with those. – Jimmy Smith Feb 26 '20 at 14:20
  • 1
    I hit that late last year bug too. I only have one Database Engine installed. But now the frequncy for the error to pop has greatly diminish. At one time it was within hours it days. But now I have a easy fix that takes one minute, so this bug is on the backburner. – Jza Mar 02 '20 at 14:23