Database

How to age out subscribers accounts?

21-Oct-01 22:40

For a recordset to be bound to a grid, it must support bookmarks. A server side dynamic cursor on SQL Server does not support bookmarks. Try using a keyset cursor.

also see msdn article Q224192

http://support.microsoft.com/support/kb/articles/Q224/1/92.ASP

Jason Jystad18-Oct-01 9:37

Jason Jystad

18-Oct-01 9:37

I have a web classifieds system that I wrote for a client and I am having some trouble with the aging out of accounts. THis system is built with ASP and interfaces with MSSQL Server 7.0.

What happens is this, a person subscribes for a period of 90 days. They pay their fee and get the run of the system for 90 days. When the 90 days are up they are no longer allowed access. They can still log in, but they are only given access to the pay area so they can re-up their subscription. If they do not re-up then after a while their account goes away entirely.

So I have three states of user:
Non-Subscriber
Subscriber
Expired Subscriber

Non-subscribers become subscribers by entering their user info and paying, that is simple enough and it works fine.

Subscribers last for 90 days and then they become Expired Subscribers. (They get flagged in the database as expired)

Expired Subscribers last for another 30 days and then they go away. (They are deleted from the database)

So my question:
What is the most efficient way to test for the age of the accounts? (I am comfortable with databases, but I do not claim to be an expert so I am curious about what you guys think SQL wise)

Also, how would you guys get the system to run the check routine regularly? Say once a day. This is an ASP based system and it is hosted on a virtual hosting provider so I cannot just set up a scheduled job to run on the server like I normally would. I am going to have to find a tricky way to do it.

TIA!

Jason Jystad
Cito Technologies
www.citotech.net

>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<

Wanderley M19-Oct-01 8:42

19-Oct-01 8:42

Hi
I just store the date of my last check in a simple text file. When a user logs in, I compare the dates. If my check is out of date, I run some stored procedures and update the text file.

Not the best solution ever, but it works and you don't have to update your db manually. Smile | :)

HTH

Regards,

Wanderley

Jason Jystad22-Oct-01 7:15

Jason Jystad

22-Oct-01 7:15

Does running the stored procedures slow down the user's login at all? In other words, does the stored procedure have to finish before the asp continues? Or can I just trigger the stored procedure and then continue on with login and browsing, from the script perspective. I have done only limited stored procedure work, so fogive me if I am being dense.

I assume the db server would happily go off and run the stored proc while the user continued with his session, but I am unsure. If the user can continue browsing the site after logging in without any percieved delay, then this would seem to be the solution I am looking for.

Thanks
Jason

Jason Jystad
Cito Technologies
www.citotech.net

>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<

Wanderley M22-Oct-01 7:49

22-Oct-01 7:49

Hi
Does running the stored procedures slow down the user's login at all?

In my case, no - it will depend on how many users you have and if your table is properly indexed. You can run some tests against your db to check how long does it take. Remember: the SP will run only once a day. Even if there's a little delay, it will be just for the first user of the day (and it could be you, if you wake up early Smile | :)

)

does the stored procedure have to finish before the asp continues?

I would say yes, because you may check your data against an out of date table, in case the SP is still running in background.

Regards,

Wanderley

Finding duplicate records

Jason Jystad23-Oct-01 7:40

Jason Jystad

23-Oct-01 7:40

Thanks for the help!

You gave me a couple of ideas I hadn't thought of before. That was what I needed, some fresh angles on the problem.

I wanted to do the check on session start, but I didn't want to slow down all the logins too much. It totally hadn't occured to me to flag myself with a file so it didn't always run. (Slaps self on forehead Roll eyes | :rolleyes:

) Sometimes you just get too involved in a problem and forget the simple solutions.

That coupled with moving the code into an SP should do the trick nicely.

Thanks again,

Jason Jystad
Cito Technologies
www.citotech.net

>------------------------------------------------<
"Luckily," he went on, "you have come to exactly the right place with your interesting problem, for there is no such word as 'impossible' in my dictionary. In fact," he added, brandishing the abused book, "everything between 'herring' and 'marmalade' seems to be missing."
-- Dirk Gently (Douglas Adams)
>------------------------------------------------<

James Spibey17-Oct-01 22:39

17-Oct-01 22:39

What is the most efficient method of identifying duplicate records in my database? The duplication is likely to be in more than one field but within the same table. I don't want to enforce non-duplication using a primary key as the duplicates won't neccessarily need to be removed.

I thought of building up a list of the unique entries and then comparing each record against this but it seems like it would take an awful long time.

Cheers

James Spibey

Well cover me in honey and throw me to the lesbians!

Wanderley M17-Oct-01 22:57

17-Oct-01 22:57

Hi
Imagine you have a table with 2 fields (name and age):

1. To find duplicate names, use SELECT [name], COUNT(*) AS TOTAL FROM [table] GROUP BY [name] HAVING COUNT(*)>1

2. If you need to check both fields, just add [age] in SELECT and GROUP BY.

HTH

Regards,

Wanderley

ps: COUNT(*) will give you the number of times your field is duplicated

James Spibey18-Oct-01 4:10

18-Oct-01 4:10

Thanks for your response - it's a great help.

How would I adjust the query to print out the duplicate records in grouped by the duplicate names ie

Name     Age    Shoe Size
Tom      12     6
Tom      12     7
Tom      12     8
Steve    26     12
Steve    26     13

(Excessive amount of sample data, I know)

Cheers

James Spibey

Well cover me in honey and throw me to the lesbians!

Wanderley M18-Oct-01 9:07

18-Oct-01 9:07

Hi
Do you want just a list of unique names or you need to know the duplicated ones? From your example:

Name     Age    Shoe Size
Tom      12     6
Tom      12     7
Tom      12     8
Steve    26     12
Steve    26     13
John     12     9

Do you want Tom, Steve and John (without duplicates) or Tom and Steve?

Regards,

Wanderley

James Spibey18-Oct-01 10:43

18-Oct-01 10:43

Hi Wanderley - thanks for the help so far,

I want to find all the records which are duplicated. Those which are duplicated, I want to be able to list each of the duplicated records giving the user the option to pick which one is the correct one. All the records for which there is only one entry should not be displayed. Does this make sense?

So I want to display the information as displayed above. The database would probably also contain many records which were not duplicated which have not been displayed.

Can this be done?

James Spibey

Well cover me in honey and throw me to the lesbians!

Wanderley M18-Oct-01 11:53

18-Oct-01 11:53

Hi
Assuming that you're checking for duplicated names, you could do something like this:

1. Create a combobox and fill it with all duplicated names using SELECT [Name] FROM [your table] GROUP BY [Name] HAVING COUNT(*)>1

2. Create a listbox (or listview) and fill it with all records that match with the name selected on combobox using SELECT * FROM [your table] WHERE [Name] = 'selected name on combo'

This way, you'll have all duplicated names on the combobox - whenever you select a name, you can clear and fill the listbox and select the correct one.

Is it too confusing? Smile | :)

Please let me know if you still have problems.

Regards,

Wanderley

James Spibey18-Oct-01 22:22

Query to retrive table structure from sybase databae

18-Oct-01 22:22

Thanks again Wanderley.

I understand what you said and it's pretty much what I thought in the first place. Thanks for your help.

James Spibey

Well cover me in honey and throw me to the lesbians!

Renu17-Oct-01 21:29

Renu

17-Oct-01 21:29

Hi all...
How do i query the Sybase database to retrieve the "table structure" ?
i.e, if i want to have the results of the command
sp_help table_name
through a query, then what should i do ?

I'm new to Sybase.Could anybody help me out ?

Thanking u..
Renu

RS,
Software engrr ,
Pune - INDIA

Creating dBase files (tables) with ADO

17-Oct-01 2:53

Does anybody know, if it is possible to create a new dBase file using ADO? During my tests I had to learn that my provider does not support creation of this file. Is there any other provider? Sample code below:

Using the connection string to create an Access-file is no problem (see strcnn).

Thanks.

#include "stdafx.h"

#import "c:\winnt\system32\dllcache\\msadox.dll" no_namespace
#import "c:\winnt\system32\dllcache\\msado15.dll"

#include "iostream.h"
#include "stdio.h"
#include "conio.h"

void CreateDatabaseX(void);

int main(int argc, char* argv[])
{
printf("Start...\n");

HRESULT hr = S_OK;

hr = ::CoInitialize(NULL);

if(SUCCEEDED(hr))
{
CreateDatabaseX();

printf("\nPress any key to continue...");
getch();

::CoUninitialize();
}

return 0;
}

void CreateDatabaseX()
{
HRESULT hr = S_OK;

_CatalogPtr m_pCatalog = NULL;

//Set ActiveConnection of Catalog to this string
//_bstr_t strcnn("Provider=Microsoft.JET.OLEDB.4.0;"
// "Data source = c:\\Projekte_test\\create\\new.mdb");

_bstr_t strcnn("DSN=dBASE-files");

try
{
hr = m_pCatalog.CreateInstance(__uuidof (Catalog));

m_pCatalog->Create(strcnn);
}

catch(_com_error &e)
{
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());

printf("\nSource\t\t: %s \nDescription\t: %s \n ",(LPCSTR)bstrSource,(LPCSTR)bstrDescription);
}

catch(...)
{
cout << "Error occured in include files...."<< endl;
}
}

Re: Creating dBase files (tables) with ADO

Rashid Thadha20-Oct-01 12:39

Re: Creating dBase files (tables) with ADO

20-Oct-01 12:39

I Suggest that you try and find a OLE DB provider for dbase or (xbase) before attempting to do this.

You are trying to execute functionality for dbase with the Access OLE DB provider.

There are various third parties that sell OLE DB Provider for xbase.

Mike Eriksson5-Nov-01 2:23

Mike Eriksson

5-Nov-01 2:23

Hello!

You need to tell the Jet engine that you're using 'dBase 5.0' then you also need to install Borland BDE otherwise you will only be able to use the database in read-only.

I always use an .udl file to specify the connection (no change of the program if I need to change database).

Hope this will help!

// Mike

PS! I have not tried to create a dBase database only reading and writing to existing databases.

Re: Creating dBase files (tables) with ADO

DonBush7-Nov-03 3:13

DonBush

7-Nov-03 3:13

Hi,
I, also, am tasked with creating new dBase files using C# in the Visual Studio .NET and ADO .NET.
If you got yours working, would you please share your steps and solution with me, by email?

Thanks, Don.

Ordering of rows

16-Oct-01 18:14

Hi,
I'm Using an SQL statement "Select * from User_info order by user_name" i am getting the results in ascending order the problem is the Null values are arriving at the last when i want then to be first in acsending order the same applies with descending the appear first when i want them last can any one help me out with this\
Thanks in Advances Rose | [Rose]

Re: Ordering of rows

Rashid Thadha17-Oct-01 12:29

db fetch : issue accessing table with more than 255 columns

17-Oct-01 12:29

You could filter the NULL valued records out and make this into a two stepped process

e.g. first select all the NULL values

Select * from user_info where user_name IS NULL

then

Select * from user_info where user_name IS NOT NULL order by user_name

kshathrya13-Oct-01 8:01

kshathrya

13-Oct-01 8:01

Hi,
I am using db fetch for accessing data from the database, in bulk, but when my table has more than
255 columns, bulk fetch neglects the remaining columns abnd fetched only 255 of them. please advice.
these are experimental data and there are no other ways except to have them in single tables.

regards
ks

ADO events

11-Oct-01 22:07

I want to handle the WillChangeField event in my RecordsetEventsVt derived class

One of the parameters is a VARIANT FIelds that is a SAFEARRAY.

I verify that it is a VT_ARRAY | VT_DISPATCH variant, and the parray if well filled.

But when I try to find an element whith the COleSafeArray::GetElement, the resulting variant is nothing.

Is somebody can tell me hown to trap the FieldPtr inside the SAFEARRAY Fields parameters?

Re: ADO events

Rashid Thadha11-Oct-01 22:42