For the pros

[false420]

Ok this is supposed to be simple but it is not for some reason. i had a post in sql programming that had 37 replies. now im trying here. Im just trying to make a record set with a sql statement against a sql server 2000. here is my error:
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.

/a_invoices.asp, line 433

here is where it is in code. its pointing at the set statement:
Dim strIsApproved
strIsApproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date <' " & toDate & "' AND Service = " & service & " AND Cost_Center = " & Service_CC_hold & " AND Carrier = " & Service_Car_hold & " AND Account = " & Service_Acc_hold & " AND Disapproved = false"
'Response.Write strIsApproved
Dim objRS_IsApproved
Set objRS_IsApproved = Server.CreateObject("ADODB.Recordset")
objRS_IsApproved.Open strIsApproved,objConnHD,adOpenKeyset

Here is what it outputs before it comes to error:

SELECT * FROM InvoiceApproval WHERE Month_Date >= '2/1/2005' AND Month_Date <' 3/1/2005' AND Service = Mobile AND Cost_Center = 101.80360.11151798 AND Carrier = Bluegrass Cellular AND Account = 0-01 0148312 0 AND Disapproved = false

My dsn connections are fine too:
Set objConnHD = Server.CreateObject("ADODB.Connection")
objConnHD.ConnectionString = "DSN=CarHD;UID=****;PWD=****"
objConnHD.Open

If anyone can figure it out the you are a guru. any helpful hints. If you need more info ask or check out my thread in sql programming. Alls i know is that im stuck like chuck and i had stumped 3 others.

 

[pkailas]

Hi again.

Could you post the code where you define your connection? Ie. objConnHD = ......

_______
I love small animals, especially with a good brown gravy....

 

[false420]

I posted the connection above i think... im starting to think that it has to do with bit fields in sql vs. access....... what did you mean by define connection. isnt that it above?

 

[PHV]

Anyway, your select should be like this:
SELECT * FROM InvoiceApproval WHERE Month_Date >= '[highlight]2005-02-01[/highlight]' AND Month_Date <'[highlight]2005-03-01[/highlight]' AND Service = [highlight]'[/highlight]Mobile[highlight]'[/highlight] AND Cost_Center = [highlight]'[/highlight]101.80360.11151798[highlight]'[/highlight] AND Carrier = [highlight]'[/highlight]Bluegrass Cellular[highlight]'[/highlight] AND Account = [highlight]'[/highlight]0-01 0148312 0[highlight]'[/highlight] AND Disapproved = false

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[false420]

it should look like that in OUTPUT? i tried that but i still get same error. what i did was put '" "' around everything

 

[pkailas]

Oh yeah, you are using a DSN connection. if PHV's suggestion still gets you the same error. check your DSN setup again and use the Tsst feature.

_______
I love small animals, especially with a good brown gravy....

 

[false420]

Thats the thing, my dsn tests out fine. and i used it thoughout the page other places. the only difference between this and the others is that this time im trying to select froma bit field at the end of the statements. this db was migrated from and access 2002 db and the bit field is a yes.no field in access. anyone know anything about using bit fields in sql code? maybe its the reason, im at a total loss

 

[pkailas]

normally when using a bit data type you'd search for a 0 for false and a 1 for true

SELECT * FROM InvoiceApproval WHERE Month_Date >= '2005-02-01' AND Month_Date <'2005-03-01' AND Service = 'Mobile' AND Cost_Center = '101.80360.11151798' AND Carrier = 'Bluegrass Cellular' AND Account = '0-01 0148312 0' AND Disapproved = 0

_______
I love small animals, especially with a good brown gravy....

 

[false420]

Wow it is amazing to me how something that should be so simple is giving me such a hard time. everything looks completely perfect. man.... i have no idea whats going on.
Ive tried all that people has said and still no luck.
thanks for trying to help friends

 

[pkailas]

if this is an SQL database, do you have access to Enterprise manager? Try the query in there to see if it works.

_______
I love small animals, especially with a good brown gravy....

 

[Sheco]

Did you add the single quotes?

 

[Sheco]

Or even Query Analyzer

 

[pkailas]

I use Enterprise manager to build my queries often... great way to learn the syntax


_______
I love small animals, especially with a good brown gravy....

 

[false420]

yes, ive played around with the quotes alot in many different way. im sure thats not whats causing it. in the way your talking about wouldnt the sql statment look like this:
strIsApproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '" & Option_Date & "' AND Month_Date <' " & toDate & "' AND Service = '" & service & "' AND Cost_Center = "' & Service_CC_hold & "' AND Carrier = '" & Service_Car_hold & "' AND Account = '" & Service_Acc_hold & "' AND Disapproved = '0'

 

[false420]

Im gonna run it through the analyzer, i havent thought about that

 

[PHV]

Are Option_Date and Month_Date properly formatted (yyyy-mm-dd) ?
Replace this:
Disapproved = '0'"
By this:
Disapproved = 0"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[false420]

Line 1: Incorrect syntax near '='.


is what query analyzer tells me but its like the other error, doesnt tell me much. ....*sigh*

 

[pkailas]

if you want to try a neat trick.. copy and paste your select statement with the values in it.

in Enterrprise Manager, right click on the table and select query. Then paste your query over the default one. then click next to the table displays.. you'll see a graphical display of your query. or get errors.. You can build your query there and get it correct.

_______
I love small animals, especially with a good brown gravy....

 

[false420]

where at in enterprise manager. i put my select statment with values in it in query analyzer and it didnt give me errors. is that what uyou meant?

 

[pkailas]

Try pasting this in your page to see if it works. I do believe it's correct now with all the ' in the right places.

strIsApproved = "SELECT * FROM InvoiceApproval WHERE Month_Date >= '2/1/2005' AND Month_Date < '3/1/2005' AND Service = 'Mobile' AND Cost_Center = '101.80360.11151798' AND Carrier = 'Bluegrass Cellular' AND Account = '0-01 0148312 0' AND Disapproved = 0"

_______
I love small animals, especially with a good brown gravy....