Automatic Field Entry

Hi,

I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:

ID
UserId
Date
Description

(UserId is a foreign key to asp_net_Users as I am supporting user accounts)

Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).

Can anyone point me in the right direction on the steps needed to create this scenario?

Thanks for any help

Daniel

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

|||

wtroom:

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

To be fully accurate, getDate() returns a datetime datatype value that is set to the current date and time.

It's dangerous to mix in the word "timestamp" into such a discussion because timestamp is also a sql server datatype. A timestamp datatype does NOT contain a date or a time and so cannot be used for this purpose.

|||

Hi,

Thanks for your help. The UserId is coming from the Membership provider in asp.net.

I'm relatively new to ASP.NET and am struggling to implement the getDate() on the SQL side. I have created the SQL Datasource to return the table and have then tried to add a WHERE statement.

I assume that the column needs to Date and the Operator = however I am unsure on the control and parameter properties. I have played around and tried Control and QueryString for the Control property and then added getDate() to the Parameter Property Default Value however this just breaks the Details view which is attached to the SQL Datasource.

Any more help would be greatly appreciated.

Thanks

Daniel

|||thanks for your help but I managed to fix this problem.

for the username I created a label with the Id UserIdValue and deleted the text and turned visible to false.

I then added code for the label onload

 Protected Sub UserIdValue_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserIdValue.Load UserIdValue.Text = Membership.GetUser().UserName.ToString() End Sub

This made the label display my username when logged on.

The next part for the detailsview code for iteminserting I added

Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
e.Values("Writtenby") = Membership.GetUser().UserName
e.Values("Date") = DateTime.Now
End Sub

I then deleted Writtenby and Date fields from the details view.

Now when I insert and enter only title and description the Writtenby and Date fields are automatically updated.

Daniel

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
fill
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
David Portas
SQL Server MVP
--

auto increment

I have a table that has teh primary key set so it auto increments.
How can I make it to where if I delete a record, the next entry will fill
the spot that was vacated?
For example;
I have 5 records,
1
2
3
4
5
If I delete record 3 I will have
1
2
4
5
Then if I add another reocrd, I will have
1
2
4
5
6
I would like 6 to actually be 3 to fill in the space.
or, when I delete a record, have everythign shift locations, or at least
have teh last record fill the spot of the deleted one.Number the rows when you SELECT from the table. Here's an example from the
Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS id
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||I agree with Dave, you don't need to STORE this number, just return it when
you run your SELECT (http://www.aspfaq.com/2427). You can do this
quasi-transparently by using a view instead of a query, but the effect is
the same.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||Why. SQL doesn't charge by the each for auto-increment row numbers. If you
use BigInt, please call me if you use up all your numbers. I will be happy
to refill the mfree of charge.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"johnfli" <john@.here.com> wrote in message
news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> I have a table that has teh primary key set so it auto increments.
> How can I make it to where if I delete a record, the next entry will fill
> the spot that was vacated?
> For example;
> I have 5 records,
> 1
> 2
> 3
> 4
> 5
> If I delete record 3 I will have
> 1
> 2
> 4
> 5
> Then if I add another reocrd, I will have
> 1
> 2
> 4
> 5
> 6
> I would like 6 to actually be 3 to fill in the space.
> or, when I delete a record, have everythign shift locations, or at least
> have teh last record fill the spot of the deleted one.
>|||What I am doing is I have a database of the drivers for our company.
We have to do random drug tests.
I wrote a program that randomly pick numbers from 1 to the number of records
in the database.
But if a driver gets the axe, I delete him, then add another, the program
will periodicly pick the number for the driver that was deleted. I have
already changed my program to deal with that by if I get a BOF or an EOF, to
go pick another number. But the problem is, is that I may have five records
1,2,4,5,6 It will pick a number from 1 to 5, but it will never get number
6.
"Geoff N.Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:enKwjs17DHA.632@.TK2MSFTNGP12.phx.gbl...
> Why. SQL doesn't charge by the each for auto-increment row numbers. If
you
> use BigInt, please call me if you use up all your numbers. I will be happy
> to refill the mfree of charge.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "johnfli" <john@.here.com> wrote in message
> news:OzIL7j17DHA.2712@.tk2msftngp13.phx.gbl...
> > I have a table that has teh primary key set so it auto increments.
> >
> > How can I make it to where if I delete a record, the next entry will
fill
> > the spot that was vacated?
> >
> > For example;
> >
> > I have 5 records,
> > 1
> > 2
> > 3
> > 4
> > 5
> >
> > If I delete record 3 I will have
> >
> > 1
> > 2
> > 4
> > 5
> >
> > Then if I add another reocrd, I will have
> > 1
> > 2
> > 4
> > 5
> > 6
> >
> > I would like 6 to actually be 3 to fill in the space.
> > or, when I delete a record, have everythign shift locations, or at least
> > have teh last record fill the spot of the deleted one.
> >
> >
>|||Try picking a random driver like this
SELECT TOP 1 driver_name
FROM Drivers
ORDER BY NEWID()
--
David Portas
SQL Server MVP
--