oledb exception was raised

Question

0.00/5 (No votes)

See more:

Hi guys,
i just faced a problem when trying to execute the following query.I searched for the exception on the internet and found that using a reserved keyword might raise this issue,and using brackets may solve this problem.But i use brackets all the time in my sql statements but still the exception 'IErrorInfo.GetDescription failed with E_FAIL(0x80004005).' comes up.
Please give me some hand.

VB

qry2 = "SELECT * FROM [per_diem_accomodation] WHERE [project number]='" & projcode & "' AND [employee number]='" & empcode & "'" & _
        "AND [current month]= (SELECT MAX(current month) FROM [per_diem_accomodation] WHERE [project number]='" & projcode & "' AND [employee number]='" & empcode & "')"

Posted 11-Apr-14 4:17am

Nebilo

Add a Solution

Comments

Richard C Bishop 11-Apr-14 10:21am

You don't have a space before the 2nd "AND" in your query.

Maciej Los 11-Apr-14 10:25am

Hawk eye!

Richard C Bishop 11-Apr-14 10:34am

Thank you Maciej! Now if only I could be that diligent with my own work. :laugh

[no name] 11-Apr-14 10:28am

I would upvote that one.

Richard C Bishop 11-Apr-14 10:35am

Very well, how about we both post a solution as yours is good as well.

[no name] 11-Apr-14 10:24am

Using a proper parameterized query would reduce the number of potential errors that you might get and get rid of this SQL injection attack waiting to happen.

Maciej Los 11-Apr-14 10:29am

And i would upvote that one ;)

Nebilo 11-Apr-14 10:40am

@Richard C Bishop
I think it's not the spacing issue,i've tried it but still it doesn't work

Richard C Bishop 11-Apr-14 11:17am

Well, that might not be the only issue, but is certainly is one. Can you show the rest of the code that is associated with that query?

2 solutions

Solution 1

Your spacing in your query is slightly off, see the alteration below:

qry2 = "SELECT * FROM [per_diem_accomodation] WHERE [project number]='" & projcode & "' AND [employee number]='" & empcode & "'" & _

     " AND [current month]= (SELECT MAX(current month) FROM [per_diem_accomodation] WHERE 

[project number]='" & projcode & "' AND [employee number]='" & empcode & "')"

The second "AND" in your query has a space before it.

Posted 11-Apr-14 4:37am

Richard C Bishop

Comments

Maciej Los 11-Apr-14 11:14am

I need to repeat it: Hawk eye ;)
+5!

Richard C Bishop 11-Apr-14 11:18am

Too kind!

[no name] 11-Apr-14 12:11pm

And there you go.

Richard C Bishop 11-Apr-14 12:12pm

Thank you sir!

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-04-11T05:26:00

Nebilo wrote:

@Richard C Bishop I think it's not the spacing issue,i've tried it but still it doesn't work

OK, second error is here: MAX(current month), replace with: MAX([current month])

You had used one of Access 2007 reserved words and symbols[^]: 'month' ;)

[EDIT]
But i'd suggest you to change the way you're trying to achieve that. As Wes Aday had mentioned, use parametrized queries:

SQL

PARAMETERS [projcode] CHAR, [empcode] CHAR; 
SELECT *
FROM [per_diem_accomodation]
WHERE [project number]= [projcode] AND [employee number]= [empcode] AND [current month]= 
    (SELECT MAX([current month])
     FROM [per_diem_accomodation]
     WHERE [project number]=[projcode AND [employee number]=[empcode]);

Then run OleDbCommand with named parameters[^].

More about: PARAMETERS declaration (MS Access)[^]

[/EDIT]

oledb exception was raised

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

oledb exception was raised

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us