SQL Error:- parameter object is improperly defined. Incosistent or incomplete information was provid

[Rob W]

Greetings,

I have a database with one table, Im using flash and a utility called MDM
Zinc which connects to the access database.

Whilst running my application the error appears:-

SQL query has failed for the following reason: Paramater object is
improperly defined. Inconsistent or in complete information was provided SQL
Statement:
UPDATE Results SET Question4 = 1 WHERE NAME = "Robbie" and Resultsdate =
#19/2/2008 12:5#

If I run the SQL query from Access I have no problems at all

UPDATE Results SET Results.Question4 = 1
WHERE Results.Name ="Robbie"
AND Results.Resultsdate=#19/2/2008 12:5#;

Can anyone please suggest where the error might be (Im thinking its date
related)?

Thanks

Rob W

 

[Brendan Reynolds]

Greetings,

I have a database with one table, Im using flash and a utility called MDM
Zinc which connects to the access database.

Whilst running my application the error appears:-

SQL query has failed for the following reason: Paramater object is
improperly defined. Inconsistent or in complete information was provided
SQL Statement:
UPDATE Results SET Question4 = 1 WHERE NAME = "Robbie" and Resultsdate =
#19/2/2008 12:5#

If I run the SQL query from Access I have no problems at all

UPDATE Results SET Results.Question4 = 1
WHERE Results.Name ="Robbie"
AND Results.Resultsdate=#19/2/2008 12:5#;

Can anyone please suggest where the error might be (Im thinking its date
related)?

Thanks

Rob W

Probably expects US date format, #2/19/2008#

The time looks strange to me, too, I'm not sure whether it's intended to be
12:50 or 12:05, but maybe that's just me, I don't know if that's relevant to
your problem or not.

 

[Rob W]

I dont think its because of US date format, im thinking Its how I pass the
date parametere in ' or " etc..,

In my flash code the SQL is as follows:-

mdm.Database.MSAccess.runQuery("UPDATE Results SET Question"
+currentQuestionNumber+ "= 1 WHERE NAME = '"+Name+"' AND Resultsdate =
#"+textDate+"#")

 

[Douglas J. Steele]

Access will not correctly recognize dates in dd/mm/yyyy format for the first
12 days of each month. (It will correctly recognize 19/2/2008 only because
there is no 19th month).

Change your code to:

mdm.Database.MSAccess.runQuery("UPDATE Results SET Question"
& currentQuestionNumber & "= 1 WHERE [NAME] = """ & Name & """ AND
Resultsdate =
" & Format(textDate, "\#yyyy\-mm\-dd hh\:nn\:ss\#"))

Note that I've changed the + to & (& is the preferred concatentation
character for text), and changed the single quotes you had around the name
to multiple double quotes so that you'll be able to handle names with
apostrophes in them, like O'Reilly. I've also put square brackets around the
field name Name, but you really should change the name of the field in your
table. Name is a reserved word, and you should never use reserved words for
your own purposes. For a good discussion on names to avoid, see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

I'd also like to comment on something else. It would appear that you've got
field names like Question1, Question2 and so on. That's called a repeating
group, and repeating groups are bad., Instead of multiple fields in a single
row, you should have multiple rows in a second table. You might want to
check out some of the many references on normalization that Jeff Conrad has
at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[Brendan Reynolds]

I dont think its because of US date format, im thinking Its how I pass the
date parametere in ' or " etc..,

In my flash code the SQL is as follows:-

mdm.Database.MSAccess.runQuery("UPDATE Results SET Question"
+currentQuestionNumber+ "= 1 WHERE NAME = '"+Name+"' AND Resultsdate =
#"+textDate+"#")

It could be the field name. As Doug points out elsewhere in this thread,
Name is a reserved word, and I have seen problems similar to this caused by
using reserved words as field names.

The # symbol is the correct date delimiter for a JET query. You could try
replacing it with a single quote and see what happens. I don't think it will
help, but I could be wrong.

 

[Rob W]

Thanks both of you for all your posts.

I have taken your advice restructured my database into two tables instead of
one, no repeating fields, no fields named with reserved words and therfore
so far I havent recieved the error message, though I still have alot to do.

Thanks again
Rob