Database

Re: Getting AutoNumber from last inserted row (C++)?

24-Feb-07 7:46

What database do you use ?
If SQL Server you can use this query:
Select @@Identity()

Hesham A. Amin
blog

MasterShin24-Feb-07 7:52

MasterShin

24-Feb-07 7:52

Currently I'm connecting to an Access 2000 database. That might change to Sql Server later on, but not planned to be soon.

And the second thing is, this might be called a lot in different threads, so I can't be sure that between the insert and the Select @@Identity() stuff won't change on me.

Re: Getting AutoNumber from last inserted row (C++)?

Hesham Amin25-Feb-07 7:01

Query to Display records from 20 to 30 from total of 100 records

25-Feb-07 7:01

Hi @@Identity is supported in JET 4.0 (the engine that runs MS Access queries)
Check: http://support.microsoft.com/kb/232144[^]

Also @@Identity gets the last insert id for the current open session (Connection), so you should not worry about multiple threads.

Hesham A. Amin
blog

array parameter

illusionFinder23-Feb-07 22:22

illusionFinder

23-Feb-07 22:22

Hi everyone,
I am trying to send parameters to a stored procedure as an array using VB 6 , is that possible, and how Frown | :(

?

Please help

Re: array parameter

Harini N K24-Feb-07 1:02

Harini N K

24-Feb-07 1:02

Hi

It is not possible to pass array to Stored Procedure but instead of that pass comma-separated values. (eg. 1,2,4,5).

Write a separate function to read & return value as '1','2','3','5'

For example Pass value as '1,2,3,4,5' and function should return as '1','2','3','5'.

Harini

indian14323-Feb-07 18:05

indian143

23-Feb-07 18:05

Sir,
can you please tell me query to display records from 20th to 30th from 100 records in SQL server and Access database. How they differ. Thank you.

Regards,

S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.

Re: Query to Display records from 20 to 30 from total of 100 records

Hesham Amin24-Feb-07 7:29

Re: Query to Display records from 20 to 30 from total of 100 records

24-Feb-07 7:29

This works in SQL Server but not sure in MS Acceess

Select top 10 * from TALE_NAME
where ID not in
(Select top 20 ID from TALE_NAME order by ID )
order by ID

another method in MS SQL Server 2005:

With ctp as 
(
Select row_number() over (order by ID) a, * from TABLE_NAME
)
Select * from ctp
where a between 21 and 30
order by a

Hesham A. Amin
blog

Atif Ali Bhatti26-Feb-07 0:24

Atif Ali Bhatti

26-Feb-07 0:24

well its so simple.
Do one thing first add a Field which is autonumber.
then u'll have hundered ids in tht column then use its value as follows.

select * from TableName where id between 20 and 30;

hope it works.

Re: Query to Display records from 20 to 30 from total of 100 records

indian14326-Feb-07 0:35

indian143

26-Feb-07 0:35

Ok. But could you please tell me to create the dynamic column from within the query itself only. And it will not be permanently created in the table. The query should be in SQL Server 2000. Thank you.

Regards,
Aleem,

S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.

Ms access-how to get the difference between two time fields

jeya krishnan.v23-Feb-07 17:37

jeya krishnan.v

23-Feb-07 17:37

We having two fields 1)shift time 2)Actual time .Actually when subtracting these two time we are not able to get in time format for another field.plz give me ur valuable suggestion

Jeyan

Re: Ms access-how to get the difference between two time fields

Hesham Amin24-Feb-07 7:31

Re: Easy DateTime question

24-Feb-07 7:31

Do you need the difference in hours, minutes or what ?
Check :DateDiff Function[^]

Hesham A. Amin
blog

Easy DateTime question

gantww23-Feb-07 9:14

gantww

23-Feb-07 9:14

I know that this can't be too hard, but for whatever reason, I haven't been able to figure out how to do it.

I have a query that is returning a group of fields, including some date fields. The data in the field is formatted as follows:
2007-02-08 00:00:00.000

The date part is fine, but I need the time to be standard time (hour:minute:second:millisecond AM/PM) instead of military because of a brittle application that reads the data.

Can anybody help me?

kubben23-Feb-07 9:53

Re: Easy DateTime question

23-Feb-07 9:53

select convert(varchar(28),@dt,131)

Where @dt is your datetime variable.

Ben

gantww23-Feb-07 10:00

gantww

23-Feb-07 10:00

That's odd. Now it's coming out as:
3/02/1428 12:00:00:000AM

Now the time is right, but the date is odd. The offset seems about right for the Arabic year though (if I remember my Middle East history).

Re: Easy DateTime question

kubben23-Feb-07 10:16

23-Feb-07 10:16

Sorry I didn't look close enough to the format it puts it in:
dd/mm/yy hh:mi:ss:mmmAM

Here is what I know:
Change the 131 to:

100 you get mon dd yyyy hh:miAM (or PM)
109 you get mon dd yyyy hh:mi:ss:mmmAM (or PM)

So then your date isn't formatted right. It is almost starting to look like you need to take the date part that is ok and then take the time part from 109

Anyway it is a bit more of a pain, but if you need that specific format here it is:
declare @dt datetime,
@time varchar(28),
@date varchar(30)
select @time = convert(varchar(28),@dt,109)
select @date = convert(varchar(30),@dt,112)
Select substring(@date,1,4)+'-'+substring(@date,5,2)+'-'+Substring(@date,7,2)+' '+ltrim(substring(@time,12,15))

Ben

timeStart,timeEnd

bernie_01128-Feb-07 21:00

28-Feb-07 21:00

idol...

i have 3 columns in my table the room,timeStart,timeEnd.
For example i have inserted this data
room: room1
timeStart: 2007-03-02 8:00 AM
timeEnd: 2007-03-02 3:00 PM

Here's the data that will not be accepted if try to insert
room: room1
timeStart: 2007-03-02 9:00 AM
timeEnd: 2007-03-02 4:00 PM
because the timestart is between the timeStart and the timeEnd. Room1 will only be occupied after 3:00 PM.

Can you help me with this?.
Thank you very Much

Bernie

kubben1-Mar-07 11:16

1-Mar-07 11:16

I don't know if there is a very good way of doing this. No matter which way you do it, it will be a lot of work. Most likely you will need to have some table that keeps track of when each class can be in each room and then check that table before inserting the record. Good luck.

Ben

bernie_0111-Mar-07 18:45

1-Mar-07 18:45

Can you tell me how to use the check Constraint for that?

Bernie

kubben2-Mar-07 0:44

2-Mar-07 0:44

I have never used a check constraint in the way you are wanting to use it. I am not sure it can be done. If all records getting inserted had a certain rule then a check constraint would be the correct one to use. I am under the impression that each record might have a different rule. In that case I would not use the check constraint.

Ben

bernie_0112-Mar-07 15:48

2-Mar-07 15:48

ok can you teach me how to solved that? I dont know how to start my code for that.

Bernie

kubben2-Mar-07 15:58

2-Mar-07 15:58

It is going to be a complicated solution. I think you should just try your best and figure it out any way you can.

Ben

bernie_0112-Mar-07 16:03

2-Mar-07 16:03

ok.. i will..thanks for that.
I have another question if ok.
you are so blessed ben,

How about rowfilter using textbox and dropdown?.

Bernie

kubben2-Mar-07 16:06

2-Mar-07 16:06

The rowfilter you can use in a DataView. I can't remeber if you do vb.net or C# but it is something like this:
DataView dv = yourdatatable.DefaultView;
dv.RowFilter = "columname = "+textbox.Text;

gridview.datasource = dv;
gridview.databind();

Anyway, that is what the C# looks like.

Ben

bernie_0112-Mar-07 16:11

2-Mar-07 16:11

i got the answer Ben, just now. I just configure it in SQL data source.
thank you very much. I am just a beginner Ben thats why i am asking.

Can i have another question?..Hehe

How about computing difference of two time to be display in textbox before inserting

Bernie

kubben5-Mar-07 3:05