How to use msadox and msado15 dll together - Database Discussion Boards

Zeeshan Bilal16-Jun-04 20:03

16-Jun-04 20:03

I m using VC++ and Access as database

ti insert a Date it take following format...

db->ExecuteSQL ("INSERT INTO Tab values ('20-JUNE-1991') ");

But to delete a date it take #mon/dd/yyyy# format
db->ExecuteSQL ("DELETE from Tab WHERE [Tab].[date] = #7/21/1991# ");

i not under stand y the fornat differnce in delete there is a hash # ..

Now i want to fetch records between two dates

i m using following query
but it yields only one record .. but i have many record in database

if(!rs->IsOpen())
{

rs->Open(AFX_DB_USE_DEFAULT_TYPE, "SELECT * from Tab WHERE [Tab].[date] BETWEEN #6/25/1992# AND #6/21/1994# ");

int n =rs->GetRecordCount ();

if(n==0)
{

rs->Close ();
db->Close();
AfxMessageBox (" NO record found");

}
else

{

db->Close();
CString ss;
ss.Format ("%d" , n);
AfxMessageBox (" found no of records... " +ss);
}

}

plz rely me thanx

how can i get all records between two given dates .

Re: How to Fetch records Between two Dates

Anonymous16-Jun-04 21:06

Anonymous

16-Jun-04 21:06

The reason you don't need # for the insert is because the date is in DD-MMMM-YYYY format.
When you insert a date, it is preferable to use ISO format, which is YYYY-MM-DD or YYYY/MM/DD (I know, ISO is supposed to be YYYYMMDD, but it doesn't work with Access without the / or - ).

Here's an example on inserting date:
INSERT INTO tblTest(dtmDate) VALUES (#2004-06-17)

When you query data using daterange, also use the ISO format otherwise the DD part may be assumed as MM or vice versa.
Here's an example on specifying date range condition:
SELECT * FROM tblTest WHERE tblTest.dtmDate BETWEEN #2001-01-01# AND #2002-01-15#

Hope it helps. Wink | ;)

Re: How to Fetch records Between two Dates

Zeeshan Bilal16-Jun-04 21:29

Zeeshan Bilal

16-Jun-04 21:29

I tried it but .. again it is fetching only ONE record.

is there any problme of datatype. BCOZ in ACCESS
datatype is Date/Time .. is Time plays a pert in it
when u using only date ..

problm is still there ..

Re: How to Fetch records Between two Dates

EdbertP17-Jun-04 13:06

EdbertP

17-Jun-04 13:06

If you only have Date in your Date/Time column you don't need to specify the Time part.
I have tested the date range condition and it is supposed to return all date in between the specified range.
You might want to check whether the Day and Month of the date in the database is stored correctly, i.e. 01/06/2004 is 1 June 2004 not 6 January 2004.
Do some test with the data with date in between the range, e.g. with SELECT * WHERE dtmDate = #2004-06-01#

Hope it helps.

Re: How to Fetch records Between two Dates

Zeeshan Bilal17-Jun-04 19:17

Zeeshan Bilal

17-Jun-04 19:17

u wrote:
You might want to check whether the Day and Month of the date in the database is stored correctly, i.e. 01/06/2004 is 1 June 2004 not 6 January 2004.

My reply:
I think and i checked it is 6th January 2004....

Well if u have a query that chk the records between range of dates n return all ,,plz send to me at
zahid_ash@hotmail.com

my querey is
SELECT * from Tab WHERE [Tab].[date] BETWEEN #25/01/1980# AND #1/10/1999#

but it return only one record..

Provided that Database is Access.

thanx

Re: How to Fetch records Between two Dates

Zeeshan Bilal17-Jun-04 19:25

Zeeshan Bilal

17-Jun-04 19:25

I think will it work ??

I m chking

SELECT *
FROM Tab
WHERE (((Tab.date) Between #1/25/1991# And #1/10/1992#));

I m confused with its # sign in Access ..ufffffff

Re: How to Fetch records Between two Dates

Zeeshan Bilal17-Jun-04 19:29

Zeeshan Bilal

17-Jun-04 19:29

But it is still showing me 1 record in VC++ front end.

but in Access it givs all records ..

i cant got it

How to use msadox and msado15 dll together

Kaustubh_deo16-Jun-04 20:00

Kaustubh_deo

16-Jun-04 20:00

I want to write a function in VC++ which will create a table(in a new mdb file) in MsAccess and also fill the table with values in the same function. As for creating the database i have to use msadox.dll and for populating database i have to use msado15.dll. But if i import both these dlls i get the following error.
< error C2011: 'DataTypeEnum' : 'enum' type redefinition >
This is probably due to DataTypeEnum being defined in both msadox.tlh and msado15.tlh.
Is there any way in which i can use both the dlls
or any other way i can create and populate the table in same function.
Note :(I got to create database in Access 2000)

With Regards,
Kaustubh

Re: How to use msadox and msado15 dll together

RichardGrimmer17-Jun-04 3:49

RichardGrimmer

17-Jun-04 3:49

Couple of ways....simplest is to use namespaces

#import "msado15.dll" rename_namespace "ADONamespace"
#import "msadox.dll" rename_namespace "ADOXNamespace"

or

#import "msadox" exclude ("DataTypeEnum")

Remembering to do it only for one of them!

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

how i can take the sum of times

syed saba16-Jun-04 1:01

syed saba

16-Jun-04 1:01

Hi,
I am using sql server 7.0 in that i am having a table of 3 feild i.e.
empName, Logindate, HoursWorked

select * from tablename
Result:
empName LoginDate HoursWorked

------- ------------ ------------
Amit 06/03/04 13:44:45
Amit 06/04/04 14:44:45

now i want the sum of the field "HoursWorked"
Can any body help?

Actually im taking 'LoginTime' and 'LogoutTime' Datatype
'DateTime' from "login" table and subtraction of this two field is strored in field 'TimeDiffrence' datatype varchar
Query i m using is
"convert(varchar(8),(logoutTime-loginTime),14)Timediffrence"
now i want the sum of the field "Timediffrence"

Re: how i can take the sum of times

WoutL16-Jun-04 3:39

WoutL

16-Jun-04 3:39

try:

Select empName, Sum(Cast(HoursWorked as int))
From tablename
Group by empName

Select empName, HoursWorked = Sum(DateDiff(ss, LoginTime, LogoutTime)) / 3600
From login
Group by empName

Wout Louwers

Re: how i can take the sum of times

syed saba16-Jun-04 3:50

syed saba

16-Jun-04 3:50

Thanks let me check it out

Pulling the latest entry for ALL products BY DATE from a table

lostsheep00715-Jun-04 22:22

lostsheep007

15-Jun-04 22:22

Hi
As almost a continuation of my last question, I'm now trying to select the latest date entry record for ALL products out of a table - the latest date varies for the products. I'm using a SELECT DISTINCT productID, but when I try to use MAX(date) it of course is choosing only those with literally the latest date....

Hope you can help again! I am learning.. Smile | :)

Patrick Collins

Everything & Nothing
http://www.lostsheep.com

Re: Pulling the latest entry for ALL products BY DATE from a table

WoutL15-Jun-04 22:53

WoutL

15-Jun-04 22:53

Try:

<br />
Select ProductID, Max(Date)<br />
Group by ProductID<br />

Wout Louwers

Re: Pulling the latest entry for ALL products BY DATE from a table

lostsheep00715-Jun-04 23:24

lostsheep007

15-Jun-04 23:24

Thank you Wout,

How embarrassingly easy! Blush | :O

Just to check, if I select other fields for the product from that record, it has effectively limited them to the record with the Max Date for that product?

Thanks in advance

Patrick Collins

Everything & Nothing
http://www.lostsheep.com

Re: Pulling the latest entry for ALL products BY DATE from a table

Colin Angus Mackay16-Jun-04 0:14

Colin Angus Mackay

16-Jun-04 0:14

No, the GROUP BY clause will only permit you to select the fields in the GROUP BY clause itself and any field in an aggregate function.

To do what you want you would have to create some sort of subquery

SELECT * FROM my_table 
    INNER JOIN 
        (SELECT product_id, MAX(date_field) 
         FROM my_table GROUP BY product_id) 
    AS inner_table 
    ON my_table.product_id = inner_table.product_id

This will allow you to return all the fields from your table.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

The Second EuroCPian Event will be in Brussels on the 4th of September

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

My Blog

Re: Pulling the latest entry for ALL products BY DATE from a table

lostsheep00716-Jun-04 21:48

lostsheep007

16-Jun-04 21:48

Thanks again Colin,

Got there - It would have helped if any of the tables I was working with were normalized!

Patrick

Everything & Nothing
http://www.lostsheep.com

Enterprise Manager problem

TPN15-Jun-04 21:44

TPN

15-Jun-04 21:44

I'm currently using Enterprise Manager to connect to some SQL 2000 Servers to manage them. The problem is the Enterprise cannot connect with some servers but can with other servers, the error is: "SQL Server does not existe or access denied ConnectionOpen(Connect())". To verify the connection, I use Query Analyzer and it can connect all servers. (All connection library was enable for client and servers).

Please help me.

Re: Enterprise Manager problem

RichardGrimmer15-Jun-04 22:15

RichardGrimmer

15-Jun-04 22:15

Would check that the registration properties are correct, and that they are using the default instance name (May be barking up the wrong tree however!)

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: Enterprise Manager problem

fweinrebe16-Jun-04 1:59

fweinrebe

16-Jun-04 1:59

It could be that your connection times out before it can connect to the SQL Server. Try a larger setting.

How to set timeout for SQL server check access rights ?

vgrigor115-Jun-04 21:06

vgrigor1

15-Jun-04 21:06

How to set timeout for SQL server check access rights ?

SQL server on remote computer either
connects very fast if succeeded,
but if user has no access right connects 10-30 seconds
and makes imression that application is hanged.

So question is:
how to decrease access rights time of check ?
Mostly needed to requrement to program it fast.

How such a problem is usually resolved in else ways ?

Asyncro connection works bad due to driver
not releases Connection.open() request fully,
but sends messages after it,
during that peiod application hangs.

Does that need full multi - threading programing?

Thanks.

Re: How to set timeout for SQL server check access rights ?

RichardGrimmer15-Jun-04 22:16

RichardGrimmer

15-Jun-04 22:16

Believe that one of the properties of the connection string is "ConnectionTimeout"

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: How to set timeout for SQL server check access rights ?

vgrigor116-Jun-04 1:02

vgrigor1

16-Jun-04 1:02

I tried to set all connection properties
wit word "Timeout"
but for checking rights it os not works.

Re: How to set timeout for SQL server check access rights ?

RichardGrimmer16-Jun-04 1:50

RichardGrimmer

16-Jun-04 1:50

My mistake, it's a property of the connection Object rather than the connstring....

An example can be found at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdproconstringx.asp[^]

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: How to set timeout for SQL server check access rights ?

vgrigor116-Jun-04 4:00

vgrigor1

16-Jun-04 4:00

For check rights it is not working.

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.