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.

 

[false420]

hmmm i set disapproved to 0 (which is the "bit" field column) and it didnt get errors but didnt return anything. and all of the bit fields in that table are set to 0.

 

[pkailas]

if you expand the database and click on Tables you should see a list of tables. By right-clicking on the appropriate table, "InvoiceApproval" in this case you will see a dropdown. Left-click on Query from the Open Table selection.

Then you should see where to paste your query...

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

 

[pkailas]

Try removing some of the filters.. and just try one. See if you get something returned.. then add them back one at a time.

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

 

[false420]

DUDE THIS WORKED!!!!!!!
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"

pkailas I THANK YOU, YOU ARE A GREAT HUMAN. NOW I GOTTA DO THIS WITH THE VARIABLES IN IT. GOD YOU HAVE OVERCOME WHAT WAS BRUTAL. YOU SHOULD SEE MY OTHER THREAD IN SQL PROGRAMMING IT WAS 37 LONG. MAN YOU ROCK

 

[false420]

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"


SHOULD i put '" "" around all variable?

 

[Sheco]

Put quotes if the field is of a text datatype (varChar etc) but not around numeric fields

 

[pkailas]

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"

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

 

[false420]

Awesome it had worked. you guys have my great respect as programmers. after i get this migrated and up and running im gonna sit down with a sql book. im awesome at asp,vbs,ado,etc... but i never really did much sql cuz weve always used access. thanks for your help all

 

[Sheco]

One you get to know the SQL it will change the way that you use Access.

 

[snotmare]

I agree with Sheco. I prefer to code SQL statements rather than bind a form to a table. You just have more control over your processing that way. Also, Access doesn't handle a multi-user environment and record locking very well (in my experience anyways).

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[false420]

I found that one out today hehehe. thanks again bro