Advanced Identity Fields

Hi!

a) PROBLEM 1:

I have set up my main database like this (of course I'm showing an abbreviation):

company - int (identity)
name - nchar

I have several companies stored in the previous table. Now I have another table with messages:

company - int (related with the company of the previous table)
messageID - int (identity)
contents - nchar

company and messageID are the main key of this table. I want to set the messageID column to change automatically. Since I declared it as identity it is working fine, but I was looking to start it on 0 on every new company:

Company messageID
0 0
0 1
0 2
1 0 <- Here the company changed, so the messageID resets
1 1
1 2
1 3
2 0 <- Again
2 1

Any suggestions?

b) PROBLEM 2:
I have my database stored locally on my computer. When I finished working with the database it has a lot of data for testing. I want to upload the database to my hosting provider or to my customer's. But the identity columns keep incrementing since the last value of my tests, so it's kind of annoying to see values as: 1250, 223, etc. when I expect to see 0,1, 2 and so on. Also, for receipts this is a very important issue.

How can I reset the identity fields?

Thank you very much for your attention and help.

CT

DBCC CHECKIDENT (tblName, RESEED, 0)

so the next record will start from 1

HTH

|||

#1 You have to create trigger on this table for insert which will automatically calculate your message ID based on number of messages for company for which you insert record.

# 2 I suppose that you would like to remove test data from your table so you will be safe using TRUNCATE TABLE to delete all records and reset identity to 1

|||I really appreciate if you can give a sample for this. I'm worried that the trigger procedure wont be able to handle concurrency.

Advance SQL Statement Help

I have all my website access statics logging data into a SQL table with
the following structure:
id int identity
ip nvarchar 23
referer nvarchar 512
request nvarchar 512
website nvarchar 15
bytes int
process_time int
access_time datetime
Each time a page is loaded the values are logged. So if a single user
navigates 20 pages, there are 20 records in the database.
What I want to do is generate a sql statement that will return me all
the accesses to a specific website on a given day, grouped by the ip
address and sorted by the access_time.
Ideally it would return the ip addresses in date order based on their
first entry, with the responses per ip in their date order. Therefore
if
IP 216.113.235.52 had three hits at:
12:15:29
12:15:54
12:16:03
IP 216.113.214.190 had three hits at:
12:15:25
12:15:31
12:15:48
It would return a result set like:
216.113.214.190 @. 12:15:25
216.113.214.190 @. 12:15:31
216.113.214.190 @. 12:15:48
216.113.235.52 @. 12:15:29
216.113.235.52 @. 12:15:54
216.113.235.52 @. 12:16:03
What I'm doing now must not be very efficient as it takes several
seconds to return just a small list of data (roughly 3 seconds to
return 50 or so hits).
Currently I use two queries:
Query 1:
SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
<end_date> AND website LIKE '%<website>%' GROUP BY ip
OR
SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
<= <end_date> AND website LIKE '%<website>%'
Either of these gives me a unique list of ips on the given day
(unfortunately they're not sorted in date order :^( )
Then with this list of unique ips, I perform a second query, looping
through the ip addresses from the first query:
SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
This gives me the users path through the website in date order.
My problems are that:
1. The things just too slow.
2. I don't have a sorted list (the first user of the day may not
necessarily be the first listed).
Is it possible to generate a single query that will return the desired
results in order?
FWIW I'm accessing the database through JDBC.
Thanks in advance.SELECT website, ip, access_time
FROM access_log
WHERE
access_time >= '20060207 00:00:00.000'
AND access_time <= '20060208 00:00:00.000'
ORDER BY website, ip, accesstime ASC
This produces a listing of websites that were access by ip's, ordered by the
access_time. If you added the request column to this query, it would also
show you the path that each ip took through the website.
I've been doing a lot of work with analyzing web access logs lately. Let me
know if this was what you were looking for; if not I'll see what else I can
come up with.
"Tom Cole" wrote:

> I have all my website access statics logging data into a SQL table with
> the following structure:
> id int identity
> ip nvarchar 23
> referer nvarchar 512
> request nvarchar 512
> website nvarchar 15
> bytes int
> process_time int
> access_time datetime
> Each time a page is loaded the values are logged. So if a single user
> navigates 20 pages, there are 20 records in the database.
> What I want to do is generate a sql statement that will return me all
> the accesses to a specific website on a given day, grouped by the ip
> address and sorted by the access_time.
> Ideally it would return the ip addresses in date order based on their
> first entry, with the responses per ip in their date order. Therefore
> if
> IP 216.113.235.52 had three hits at:
> 12:15:29
> 12:15:54
> 12:16:03
> IP 216.113.214.190 had three hits at:
> 12:15:25
> 12:15:31
> 12:15:48
> It would return a result set like:
> 216.113.214.190 @. 12:15:25
> 216.113.214.190 @. 12:15:31
> 216.113.214.190 @. 12:15:48
> 216.113.235.52 @. 12:15:29
> 216.113.235.52 @. 12:15:54
> 216.113.235.52 @. 12:16:03
> What I'm doing now must not be very efficient as it takes several
> seconds to return just a small list of data (roughly 3 seconds to
> return 50 or so hits).
> Currently I use two queries:
> Query 1:
> SELECT ip FROM access_log WHERE date >= <start_date> AND date <=
> <end_date> AND website LIKE '%<website>%' GROUP BY ip
> OR
> SELECT DISTINCT ip FROM access_log WHERE date >= <start_date> AND date
> <= <end_date> AND website LIKE '%<website>%'
> Either of these gives me a unique list of ips on the given day
> (unfortunately they're not sorted in date order :^( )
> Then with this list of unique ips, I perform a second query, looping
> through the ip addresses from the first query:
> SELECT * FROM access_log WHERE ip LIKE '<ip>' ORDER BY date
> This gives me the users path through the website in date order.
> My problems are that:
> 1. The things just too slow.
> 2. I don't have a sorted list (the first user of the day may not
> necessarily be the first listed).
> Is it possible to generate a single query that will return the desired
> results in order?
> FWIW I'm accessing the database through JDBC.
> Thanks in advance.
>

ADO.NET, SqlParameter, and NULL

Im trying to execute the following:

mySqlCmd.Parameters.Add("@.Parent_ID", SqlDbType.Int).Value = (sectionUpdate.iSection.ParentID == 0 ? DBNull.Value : myParentID);

However i get an error that Null cant be converted to Int. Is there any way i can uyse the SqlParameters approach but pass in a Null value for the field or must i wrap the entire connection within a IF/ELSE statement one containing a hard-coded NULL within the query and the other as a standard parameter with a proper int value?

Thanks

I found a solution:

SqlParameter sp = mySqlCmd.Parameters.Add("@.ParentID", SqlDbType.Int);
sp.IsNullable = true;

if (sectionUpdate.iSection.ParentID == 0)
sp.Value = DBNull.Value;
else
sp.Value = sectionUpdate.iSection.ParentID;