|
Ok, first question, what platform? Access or SQL Server?
I've seen this happen on Access and never solved it. I don't think Access is as aware of commit status as a normal sql server.
If this is on Sql Server, are you explicitly commiting after each insert? Also, you may be running into the page cacheing. I know there is a way to turn it off (It's NOCACHE in Oracle) but I'm not sure what it is in Sql Server.
Mark Conger
Sonork:100.28396
|
|
|
|
|
|
I am writing an IRC bot that keeps track of peoples usernames and ip address's whenever they join my channel. Its a pretty active channel so I am adding records quite often. I was just curious how I should deal with duplicate records. The way I see it, I have 3 options:
1. Before adding a record to my database, check to see if it already exists.
2. Add record to db, then once a week or something run a procedure to purge all duplicate records
3. Dont worry about having dupes in the DB.
Option 3 is obviously the easiest  But least efficient (i think? ) I was planning on doing searching and reports against the data, so with that in mind, which option is my best bet?
|
|
|
|
|
Why not add a constraint to your database and make their username a primary key, then check if the record exists before adding, and offer the user the chance to update their record ( assuming they have passed some check to ensure they are the original owner of the username )
Christian
No offense, but I don't really want to encourage the creation of another VB developer.
- Larry Antram 22 Oct 2002
C# will attract all comers, where VB is for IT Journalists and managers - Michael
P Butler 05-12-2002
Again, you can screw up a C/C++ program just as easily as a VB program. OK, maybe not
as easily, but it's certainly doable. - Jamie Nordmeyer - 15-Nov-2002
|
|
|
|
|
|
The simple way to solve your problem is to run
DELETE * FROM Table WHERE sUserName = 'whatever'
INSERT INTO Table (...) VALUES (...)
every time. This way you will have no dupes. You can also make a sorted procedure like
IF EXISTS( SELECT * FROM Table WHERE sUserName = 'whatever' ) THEN
UPDATE Table SET ....
ELSE
INSERT INTO Table (...) VALUES (...)
END
|
|
|
|
|
I see the fastest and easiest option as being number 1. However, what is your DB running on - Access, SQL?
If you're running running on either of those (at least with SQL) it wouldbe extremely easy to implement option 1.
Also, if your storing effectively a hashtable of usernames and IP's, there is rarley going to be dupes.
I might sign in 5 times over 5 days and be listed as 5 unique entries for all of my new IP's. Very few IRC go'ers willhave a static IP.
Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript)
tatham@e-oddie.com
+61 414 275 989
|
|
|
|
|
Tatham wrote:
Very few IRC go'ers willhave a static IP.
You mean except for those people who IRC from behind corporate gateways and those on Cable and DSL modems (with static IPs). 
|
|
|
|
|
Reinout Hillmann wrote:
who IRC from behind corporate gateways
IRC isn't exactly the most popular thing behind a company firewall and use of it behind a COMPANY firewall means your not doing your job.
Reinout Hillmann wrote:
and those on Cable and DSL modems
Not everybody has cable/DSL.
Reinout Hillmann wrote:
(with static IPs)
Even with cable/DSL, if you turn the momem off then back on your IP can still change.
FEW will have a static IP. Not NONE, FEW.
Tatham Oddie (VB.NET/C#/ASP.NET/VB6/ASP/JavaScript)
tatham@e-oddie.com
+61 414 275 989
|
|
|
|
|
I got a table mytable in SQL server database. The table contains 20 records. If i execute query select * from mytable, total 20 records are returned. If i want the first four records, i execute the query select top 4 * from mytable, and it returns the first four records.
Can anyone tell me about the queries in following cases:
1) Selecting last 4 records from table.
2) Selecting records 9 to 15 from the table.
|
|
|
|
|
|
The answer to both of the above depends on the table design.
If the table has a defined primary key, then you could do 1) as
select top 4 * from mytable order by mykey ASC/DESC (whether you need ASCending or Descending depends on how you defined the key...)
how to get a range (2) is more challenging
if the table contains an identity field (need not be part of the primary key), and records in the table never get deleted, you could use the identity as record index: select * from mytable where myindex > 8 and myindex < 16
otherwise, you would need something cumbersome like:
select top 6 * from mytable where myidentity not in (select top 8 myidenty from mytable)
if there is no identity field, then the notion of nth record is possibly arbitrary and variable over time...
|
|
|
|
|
I have a project I'd like to tackle for a friend, but I'm not sure of the best way to attack it. He runs a Karaoke business, and currently runs a thoroughly inadequate system for tracking his tunes and printing songbooks. I can bang out a better one in an hour using Access, but he can't afford to buy the program to get the runtime stuff he needs to use what I build him. I can distribute MSDE, and have it available to do so, but I'm not sure what is the smartest way to tackle this - build a db in SQL Server directly and export it for use with MSDE, or do the whole app in Access and use to upsizing wizard to mate it with MSDE. Does anyone have any experience with this that would like to share some wisdom on the matter?
"How many times do I have to flush before you go away?" - Megan Forbes, on Management (12/5/2002)
|
|
|
|
|
Roger Wright wrote:
build a db in SQL Server directly and export it for use with MSDE
MSDE = SQL, no need to export, just detach the file, and attach it to MSDE 
Personally I have found moving to SQL(MSDE) from Access was painfull, there were alot of queries that needed to be changed. Best bet is to start straight on SQL. Perhaps the design part can be done in Access then use the upsizing wizard to create the SQL db, and then continue from there.
Secondly, depending on the language (.NET will be a big RAD boost here) VS.NET has adequete tools to allow you to create all the queries you need.
Thirdly, I have found access is slow, very slow compared to SQL, even just for small queries.
Lastly, regarding licensing. If you will be only using an Access file and write your own frontend, that should not be a problem, and that is the route to go for MSDE as well.
I think the choice is clear
WebBoxes - Yet another collapsable control, but it relies on a "graphics server" for dynamic pretty rounded corners, cool arrows and unlimited font support.
|
|
|
|
|
leppie wrote:
I think the choice is clear
Methinks you're right. Thanks!
"How many times do I have to flush before you go away?" - Megan Forbes, on Management (12/5/2002)
|
|
|
|
|
I second Leppies advice. If you have access to either SQL Server tools (enterprise manager, Query analyzer) or to Visual Studio .Net, I would work directly in SQL server. If using .Net for the application use the System.Data.SqlClient namespace rather than the Oledb namespaces; they are much more performant. You can either just "detach" your developer db and distribute a copy (use OSQL to attach it: see sp_attach_single_file_db stored proc documentation) or export scripts that can be read with OSQL to build it after installing MSDE. You may also want to write some scripts to schedule routing backups and other maintenance (note that if you build these with Enterprise manager, the get installed in MSDB not your database, making life a bit more difficult.
VS.Net has a rather good integration with SQL for developers (server explorer)...
|
|
|
|
|
Thanks for the suggestion! This is going to be fun...
"How many times do I have to flush before you go away?" - Megan Forbes, on Management (12/5/2002)
|
|
|
|
|
I blindly installed SQL Server 2000 (I know - bad idea...) and have a service that is launched as \\MYCOMPUTER\NetSDK. When I'm following other code examples, they generally use "localhost" at the server. I would rather not change every example and just use localhost. How do I change mine from "MYCOMPUTER\NetSDK" to "localhost"?
Thanks in Advance!
Derek
|
|
|
|
|
As long as you are running on the same system as the server 'localhost' should always work.
you can also try to use '127.0.0.1'
Paul Watson wrote:
"At the end of the day it is what you produce that counts, not how many doctorates you have on the wall."
George Carlin wrote:
"Don't sweat the petty things, and don't pet the sweaty things."
|
|
|
|
|
I tried both 'localhost' and '127.0.0.1' and get the following error:
"Sql Error: SQL Server does not exist or access denied."
If I specify 'MYCOMPUTER\NetSDK', it works fine.
My SQL Server system tray icon displays:
"Running - \\MYCOMPUTER\NetSDK - MSSQLServer"
Is there any SQL Server 2000 setting I'm missing?
|
|
|
|
|
It looks as if you installed a named instance of the server.
You could access it using localhost\NetSDK or 127.0.0.1\NetSDK or .\NetSDK
|
|
|
|
|
Thanks for the reply. 127.0.0.1\NetSDK and .\NetSDK worked, but localhost\NetSDK didn't. Should localhost be added to my hosts file to be recognized?
Also, is there a way to rename the instance of the server and drop the "\NetSDK" part? Or do I have to delete the instance and start over?
Thanks in Adavance!
|
|
|
|
|
The easiest and most foolprof answer is to uninstall the current installation and re-install choosing Default instance instead of Named instance...
|
|
|
|
|
Uninstalling and re-installing worked - thanks for the help!
|
|
|
|
|
Try (localhost)\NetSDK instead. That might work.
Also, you're probably better off just reinstalling the SQL Server. Detach any databases you've set up and reattach them once you've reinstalled SQL.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
