Database Discussion Boards

resetting id while deleting records in runtime

ginola21-Nov-01 14:10

21-Nov-01 14:10

after i had delete the records by the following line:

objConnection.execute "DELETE * FROM tea_session_list"

what should i add to the statement to have the autonumber id also being resetted to be start at 1.

i dun wan to do it by compacting database...can i do it in a new script with the delete statement....what should i add to my above statement???

Re: resetting id while deleting records in runtime

Peter Zajac29-Nov-01 9:05

Peter Zajac

29-Nov-01 9:05

The only resonable solution to this I see is if you control the ID yourself. Either use a MAX value or a second table that stores the next ID value.

Dynamic Recordset

Laksh21-Nov-01 1:09

Laksh

21-Nov-01 1:09

hi,
can I create a dynamic recordset?
i.e. once i have created a recordset using ADO , can that recordset be updated automatically in case if there is a change in the records on which the query is based.
if possible pls. give an example on how to do it.
pls help

lakshmi

Re: Dynamic Recordset

Rashid Thadha21-Nov-01 4:28

Rashid Thadha

21-Nov-01 4:28

See MSDN Examples on ADO at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmscadocodeexamplesinvisualc.asp

Specifically see the CursorType Sample at

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprocursortypexvc.asp

I hope this helps

Cool | :cool:

resetting id after deleting records

ginola20-Nov-01 14:31

ginola

20-Nov-01 14:31

Re: resetting id after deleting records

Rashid Thadha21-Nov-01 4:19

Rashid Thadha

21-Nov-01 4:19

Compacting the database will solve your problem

First, eliminate all records in your table. Then, close the database but leave Access running. The menu bar reduces to three options. Go to Tools > Options > Compact Databases.

I am not sure how you would do this at runtime !!

Cool | :cool:

Database pagination

JEi16-Nov-01 0:31

JEi

16-Nov-01 0:31

Hi all, I'm actually designing an internet application, and I'd like to know how to paginate the information retrieved from the database.

The problem is that I access a huge database (SQLServer 2000) and I don't want tho get all the records everytime I execute a query, I'd like to paginate the records and get them in different queries. I've thought of two methods to do that and I'd like to know which one is better, so if anybody knows it (or knows another way to solve it) please help me!

1.
Use SQL queries of the type

SELECT TOP x
FROM aTable
WHERE aTable.Id > @LastIdReceived

The problem I find in this method, is that I'm accessing a huge table (about 900.000 records) and is a complicated query (with Joins to a lot of other tables) so I think that if I do it this way, each time I have to execute the full query (and there's also the fact that with this way I can't know before the total amount of records affected by the query, I could do a COUNT first, but this would be another query)

2.
Create a temporary table of indexes with the query I need and then, retrieve the information I need by joining it with the real table (with the amount of records I need). The problem I see this way is that if there are a lot of users asking for queries at the same time, creating so many temporary tables might be inefficient.

So, If somebody has ever faced this problem I'd like to know which way is better or if there's another way I've not thought about.

Thanks.

Re: Database pagination

Chris Maunder16-Nov-01 0:56

Chris Maunder

16-Nov-01 0:56

Try this:

http://www.4guysfromrolla.com/webtech/062899-1.shtml

cheers,
Chris Maunder (CodeProject)

Re: Database pagination

JEi16-Nov-01 1:10

JEi

16-Nov-01 1:10

I've read it and I think it's more less what I'd thought as my second option. The problem I see is maybe creating the temporary table won't be efficient when a lot of users are asking queries since a lot of temporary tables will be created on the server (and I expect that situation), am I wrong and it won't affect too much ? Maybe there isn't a better way to do it.

thanks for everything.

Re: Database pagination

Andrew Peace24-Nov-01 4:13

Andrew Peace

24-Nov-01 4:13

The site that Chris suggested would provide an efficient program because the temporary tables are created in memory, and then destroyed as soon as the stored procedure ends.

--
Andrew (first post in this forum Smile | :)

Re: Database pagination

blueei5-Dec-01 21:59

blueei

5-Dec-01 21:59

how about using the standard "allow paging" properties in VS.NET
it makes the paging very simple...

Re: Database pagination

Nick Parker30-Nov-01 8:40

Nick Parker

30-Nov-01 8:40

First off, using a SQL statement such as "Select count(*) from here inner join this on here.id = this.id" will return a lot faster than actually having to return the actual results of the query. I would say that it is always a good idea to look at the size of your returning recordset when dealing with such large datasets.

Second, there are a few pages from another site that I have used in the past for searches that will result in large recordset. I would recommend this first page because you are using SQL Server 2000 you can write your query as a stored procedure and page through it. The second link allows you to do the same thing with a generic recordset via a SQL statement built on the client-side. Good Luck, hope this helps.

Links:

1. Paging Through Records Using A Stored Procedure
2. Paging Though Database Results N Records At A Time

Nick Parker

Database pagination

JEi16-Nov-01 0:27

JEi

16-Nov-01 0:27

Bitwise Operators in SQL

Chris Meech14-Nov-01 6:09

Chris Meech

14-Nov-01 6:09

Is there an equivalent to the C/C++ bitwise operators (&, |, ^) for use in SQL programming? And if not any suggestions on how to "best" emulate them. Using something like

     select ...
       from table t
      where t.columnA in ( 1,2,3,5,7,9,11,13,15 );
or
     select ...
       from table t
      where t.columnA in ( 4,5,6,7,12,13,14,15 );

doesn't sound too inviting from a maintenance standpoint. If I ever add another combination, I have to change all of the 'in' lists.

Thanks.

Chris Meech

Is there an equivalent of the case construct in tSql?

Jason Jystad7-Nov-01 11:32

Jason Jystad

7-Nov-01 11:32

The Case construct I am referring to is like this:

Case {expression}
{result 1} : {code block}
{result 2} : {code block}
{result 3} : {code block}
{result 4} : {code block}
Else {code block}
End Case

I would like to do something like this in tSQL on SQL Server 2000. Rather than using daisy chained If statements.

Any ideas?

Jason Jystad
Cito Technologies
www.citotech.net

Sonork ID 11.9918

>-------------------------------------------------<
Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, one can deduce that every program can be reduced to one instruction that doesn't work.
>-------------------------------------------------<

Re: Is there an equivalent of the case construct in tSql?

AndyG7-Nov-01 18:29

AndyG

7-Nov-01 18:29

In SQL Server BOL, click the index tab then type CASE. Thats it. In case you don't have BOL installed, here's a site. http://www.sqlteam.com/item.asp?ItemID=922

Andy Gaskell, MCSD

Re: Is there an equivalent of the case construct in tSql?

Jason Jystad8-Nov-01 6:09

Jason Jystad

8-Nov-01 6:09

Thanks!

You are correct, I do not have access to the BOL from where I am. Thanks for the site, I was looking for something like that online, but I wasn't having much luck.

Jason Jystad
Cito Technologies
www.citotech.net

Sonork ID 100.9918

>-------------------------------------------------<
Every program has at least one bug and can be shortened by at least one instruction -- from which, by induction, one can deduce that every program can be reduced to one instruction that doesn't work.
>-------------------------------------------------<

Re: Is there an equivalent of the case construct in tSql?

Gerald Schwab8-Nov-01 6:18

Gerald Schwab

8-Nov-01 6:18

http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_5t9v.asp?frame=true

Re: Is there an equivalent of the case construct in tSql?

Carlos Antollini8-Nov-01 7:27

Carlos Antollini

8-Nov-01 7:27

YES...

Like this

Select
  CASE FieldNumber 
   WHEN 0 THEN "Cero"
   WHEN 1 THEN "One"
   .
   .
   .
   WHEN 9 THEN "nine"
   ELSE "No Value"
   END
From 
     MyTable

Best Regards

Carlos Antollini.

Re: Is there an equivalent of the case construct in tSql?

Nick Parker30-Nov-01 8:51

Nick Parker

30-Nov-01 8:51

Yes, you are correct, it is a lot eaiser to write a Case statement than a nested if statement, especially inside those small stored procedure window of SQL Server 2000.

(CASE WHEN Cust_Name = 1 THEN 'Good Customer' ELSE 'BAD Customer' END)

or you can do nested Case statements such as:

(CASE WHEN Cust_Name = 1 then 'Good Customer' ELSE(CASE WHEN Cust_Name = 2 THEN 'SO-SO Customer' ELSE 'Bad Customer' END) END)

Hope this helps.

Nick Parker

Does anybody know why ........ ?

5-Nov-01 2:33

In my application I first connect to SQL server using MFC ODBC classes,
then the same thread tries to to create ADO Connection object
and it fails. What's strange is that if my application first connects to MSAccess using MFC ODBC classes then it can create ADO just fine.
What's the deal with SQL ODBC drivers and ADO? Confused | :confused:

Re: Does anybody know why ........ ?

Rashid Thadha15-Nov-01 3:12

Rashid Thadha

15-Nov-01 3:12

Make sure you have loaded the Ole Libraries

I think in MFC you need to call AfxOLEInit or something like that.

ADO will only work if you load this

changing the value of a recordset's property

Yair Konfino4-Nov-01 22:25

Yair Konfino

4-Nov-01 22:25

Hi,
I'm trying to change the value of a recordset's property. The property is "scroll backwards" (want to change from false to true).

I opened a recordset, and then I got it's properties. I got the wanted property(according to it's index), but I can't change the value - I get an error saying that "the operation can't be done when the object is open".

Which object is the error talking about?
Do I need to change the property after openning the recordset?
What am I doing wrong?

Can someone please send me a code example of what should I do...???

Thanks.

Re: changing the value of a recordset's property

chris foote11-Nov-01 11:20

chris foote

11-Nov-01 11:20

Take a look at your CursorType if is forward only it might be the source of your problem.

ADO GLOBAL CONNECTION

3-Nov-01 10:35

HI.
I´m developing a database application using ado and, although it´s a large program i don´t want to use more than 2 or 3 open connections.
The problem is that i can´t define a global connection pointer, right now, i can only make it work creating a new con in every cpp file.
I´ve tryed defining a global pointer in every way i know but I´m allways getting memory or even weirder errors.
Can anyone help me out?

Pedro Besteiro
LSI systems integration

Pedro Besteiro
LSI systems integration

Last Visit: 31-Dec-99 18:00 Last Update: 19-May-24 14:47

Refresh

ᐊ Prev 1...2445 2446 2447 244824492450 2451 2452 2453 2454 Next ᐅ

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