0

This has to be by far the worst error message I've ever seen in any context. Coming from SQL Server and now revamping a legacy Access database, this error just keeps coming again and again and it's a complete trial and error every time, being a huge time waster.

Any helpful tips on how to debug this error effectively? Like giving even the slightest hint on where to look? I'm using C#.

hk1980
  • 95
  • 1
  • 7
  • Is it possible that you have a fild [xyz] that not exists in the database (table)? Than this will be handled like a parameter. – Frank May 26 '20 at 10:16
  • @Frank The question is: given this error, how do I effectively determine that I have a field xyz that does not exist in the database without manually going through all fields, checking typo's, checking other causes, etc. Especially in long queries with many joins, a typo in a field name can be very hard to spot. – Erik A May 26 '20 at 10:39
  • Best approach? spit out the actual sql string, fire up access and paste the sql into the query builder. If any invalid column? Then will prompt you by NAME of the missing column, so that will/is be your missing column. So, you see instant the name of the column that is incorrect. – Albert D. Kallal May 26 '20 at 11:59

2 Answers2

1

Well, if using say sql server, one would fire up SQL studio, and try the sql that way.

And the same approach works for Access. Take your sql (debug.print it out), and then cut + paste the sql into the access query builder (in sql view mode). If you run the query, then for any missing field, it will spit out a prompt with the "name" of the column.

Access uses (when from Access) a prompt system for any column that is not in the table, and automatic prompts the user for the value. If you use odbc, or oleDB, then missing columns spit out that missing parameter (but without the name of the column as you note).

So, most easy is to fire up access and use the sql view in the query builder - paste in your sql.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

You can use ADO to create the query, and then list all parameters the query expects:

ADODB.Connection conn = new ADODB.Connection();
conn.Open("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Path\\To\\db.accdb");
ADODB.Command cmd = new ADODB.Command
{
    CommandText = "SELECT * FROM tblUsers WHERE username = bob", 
    //Oops, forgot to quote the username, results in No value given for parameter error
    ActiveConnection = conn
};
foreach(ADODB.Parameter param in cmd.Parameters)
{
    Console.WriteLine(param.Name); //bob
}
Console.ReadLine();

This requires a reference to ADO, which can be entered through the COM references.

You could also use late binding to prevent the extra reference, which might be desirable if you only use this as debug code but want it in your project, see this Q&A.

Since OLEDB doesn't support named parameters, you unfortunately can't use OLEDB for this afaik.

Of course, you can rewrite this to a function that takes a query string and returns expected parameters as a string, and then use that function in the immediate window when debugging.

Erik A
  • 31,639
  • 12
  • 42
  • 67