Database

Wendelius3-Oct-08 21:28

3-Oct-08 21:28

Only thing that comes in mind is an ODBC-driver, which can be used with OdbcConnection Class. I think Microsoft may have free drivers against dBase.

The need to optimize rises from a bad design

Jaime Olivares4-Oct-08 2:12

Jaime Olivares

4-Oct-08 2:12

Yeah, I know about the ODBC alternative, but:
* Is it always pre-installed in common client Windows OSs? (XP home/pro, Vista home/pro) ?
* If not, how easy or complex is to configure it when deploying an application that need to use DBF files?

Best regards,
Jaime.

Wendelius4-Oct-08 4:18

4-Oct-08 4:18

Don't know if it's pre-installed. I would believe that not. I think that MDAC installs also dBase drivers, but I'm not sure.

The installation of a single driver is basically copying the dll files and then registering the dll's. After that a DSN can be created programmatically or using Control Panel.

The need to optimize rises from a bad design

Hi , I need all dates of the current month

Jaime Olivares4-Oct-08 6:52

Jaime Olivares

4-Oct-08 6:52

Recently I installed MDAC to have a look to an old VB6 application. It causes me some troubles while installing.
So, I think maybe would be a good idea to use my own DBF class, so I won't have deployment problems...

Best regards,
Jaime.

Sneha Bisht3-Oct-08 19:48

Sneha Bisht

3-Oct-08 19:48

Hi , i need all dates of the current month . m using sql server 2000 .
Thanx in Advance .

Re: Hi , I need all dates of the current month

Sneha Bisht3-Oct-08 20:35

Sneha Bisht

3-Oct-08 20:35

Thanx i got it . And the sol is :

declare @current varchar(100)
set @current =(select convert(varchar,getdate(),101))
declare @firstdate varchar(100)
declare @lastdate varchar(100)
set @firstdate=(SELECT convert( varchar,DATEADD(dd,-(DAY(DATEADD(mm,1,@current))-1),DATEADD(mm,0,@current)),101))
set @lastdate=(SELECT convert(varchar, DATEADD(dd, -DAY(DATEADD(m,1,@current)), DATEADD(m,1,@current)),101))
print @firstdate
print @lastdate

declare @i int
declare @k varchar(100)
select @i = datediff(dd,@firstdate,@lastdate)
print @i
declare @j int
set @j=0
while(@j<@i)
begin
select @k = dateadd( dd,1,@firstdate)
print @k
set @firstdate=@k
set @j =@j + 1
end

Adding duplicate entry [modified]

AghaKhan3-Oct-08 17:14

3-Oct-08 17:14

I have a table named test in SQL 2005 database. It has three columns (ourKey, ourData1, ourData2). Its first column (ourKey) is primary key column and it updates itself. It has one entry (1,45,56).
I would like to have another entry based on any other entry. I would like to insert entry which should produce (2,46,56)
I would like to read first entry from ourData1 then add one and rest of the table should be same.
Any help will be very much appreciated.

in previous thread
Re: delete duplicate rows by Ashfield
#table is used. What is #table?

Agha Khan

modified on Friday, October 3, 2008 11:42 PM

Wendelius3-Oct-08 21:06

Re: Adding duplicate entry [modified]

3-Oct-08 21:06

Do yo mean something like:

INSERT INTO test (ourData1, ourData2) SELECT MAX(ourData1) + 1, MAX(ourData2) FROM test;

Tables prefixed with # are local temporary tables.

The need to optimize rises from a bad design

AghaKhan4-Oct-08 3:34

4-Oct-08 3:34

Thank you. but I was looking to copy of Nth row not the Max of colum. i.e. I want to copy 3rd row or 2nd row.

My table has three entries.

1,45,30
2,3,40
3,9,80

the 4th should be if 2 row (2,3,40) is selected
4,3,40

if 1st row (1,45,30) is selected
4,46,30

This means answer depends on selection.
But your answer helped me to understand.

this works
INSERT INTO test (ourData1, ourData2) SELECT (select ourData1 from test where ourkey = 3 ) + 1 , (select ourData2 from test where ourkey = 3 );
but is there short syntex? Sleepy | :zzz:

I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1

insert into test select * from #table

But I cant not insert identity column, it is my primey key and it is default. I not have to supply it.
Otherwise it looks it works.

Kind regards
Agha

modified on Saturday, October 4, 2008 11:05 AM

Wendelius4-Oct-08 4:21

Re: Adding duplicate entry [modified]

4-Oct-08 4:21

If I understood you correctly, you could modify the insert statement to following:

INSERT INTO test (ourData1, ourData2) 
SELECT ourData1 + 1 , ourData2
FROM test
WHERE  ourkey = 3 ;

The need to optimize rises from a bad design

AghaKhan4-Oct-08 5:21

4-Oct-08 5:21

This works fine.
But that was just an example. In real application I have many columns, so I tried with #table, which works fine, but unable to update primey key column. Please check previous reply.
Kind regards
Agha

I tried something like this
Select * into #table from test where ourkey = 3
select * from #table
update #table set ourData1 = ourData1 + 1

insert into test select * from #table

unable to update primey key column

modified on Saturday, October 4, 2008 11:28 AM

Wendelius4-Oct-08 6:31

4-Oct-08 6:31

Yes, since the primary key is autogenerated, you cannot insert into it or update it. Can't you list all of the columns instead of * and leave the primary key out? Like:

INSERT INTO Test (NotKeyColumn1, NotKeyColumn2, ...)
SELECT NotKeyColumn1, NotKeyColumn2, ... FROM #table

An alternative could be that you drop the key column from temp table before insert. Something like:

...
UPDATE #table SET ourData1 = ourData1 + 1
ALTER TABLE #table DROP COLUMN KeyColumn
INSERT INTO test SELECT NULL, * FROM #table

However, I'm not sure if it works. I added NULL to the beginning, because if you do not list the target columns in insert, you must supply a value for all columns (in the order they appear in the target table).

The need to optimize rises from a bad design

AghaKhan4-Oct-08 8:14

4-Oct-08 8:14

Thank you.
There is no need for adding null,
but it works very well.
Thank you again.

Kind regards
Agha

AghaKhan4-Oct-08 8:16

4-Oct-08 8:16

Thank you.
It works.

Best regards
Agha

Wendelius4-Oct-08 8:33

4-Oct-08 8:33

You're welcome.

The need to optimize rises from a bad design

delete duplicate rows

ss4444443-Oct-08 7:51

ss444444

3-Oct-08 7:51

Can any one tell the query to delete only duplicate rows from any table ?

Ashfield3-Oct-08 8:26

Ashfield

3-Oct-08 8:26

You need to do it in steps:

Select * into #table from mytable where 1 = 2

insert into #table select distinct * from mytable

truncate table mytable

insert into mytable select * from #table

Bob
Ashfield Consultants Ltd

Niraj_Silver3-Oct-08 20:55

Niraj_Silver

3-Oct-08 20:55

Example Table :
Cl1 Cl2
ID SecndClmnNAme

Delete From Tab Where ID NOt In Select Distinct ID From Tab)

Ashfield5-Oct-08 21:16

Ashfield

5-Oct-08 21:16

Are you sure this works? Surely, if there are duplicate rows there are duplicate ids?

I mean, if you have this

id name
1 bob
2 fred
1 bob

your query will not delete anything as all ids occur in the distinct.

Or have I missed something?

Bob
Ashfield Consultants Ltd

Blue_Boy4-Oct-08 2:56

Blue_Boy

4-Oct-08 2:56

delete from tablename where id not in (select distinct id from tablename )

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Ashfield5-Oct-08 21:15

Ashfield

5-Oct-08 21:15

Bob
Ashfield Consultants Ltd