datetimestamp into db

as you can see i am trying to figure out how to get the users IP address and a datetimestamp into my db. can anyone assist. i tried to comment out the datetimestamp but the iprequest does not work either.

The site i am workingon is papertrailinvites.com the newsleter on the left hand side there. i am trying to collect the email address, ip address and a datetimestamp. inserting the email address works now i just need to get the ipaddress and the datetimestamp in the db

code is below

Thanks

using (SqlConnection conn =newSqlConnection(ConfigurationManager.ConnectionStrings["cardsConnectionString1"].ConnectionString)) {

String newemail = Newsletter_Email.Text;

SqlCommand cmd =newSqlCommand("INSERT INTO Email (EmailAddress, IPAddress, DateTimeStamp) Values (@.EmailAddress, @.IPAddress, @.DateTimeStamp)", conn);

cmd.CommandType =CommandType.Text;

cmd.Parameters.AddWithValue("@.EmailAddRess", newemail);

cmd.Parameters.AddWithValue("@.IPAddress",HttpContext.Current.Request.UserHostAddress);

//cmd.Parameters.AddWithValue("@.DateTimeStamp",

conn.Open();

cmd.ExecuteNonQuery();

Hi,

You could try changing the commented line to:

cmd.Parameters.AddWithValue("@.DateTimeStamp", DateTime.Now.ToString());

However, I normally set a default value in the table itself to getdate() which means you don't need to worry about this field at all. Alternatively, you could add to your SQL:

DECLARE @.DateCreatedAS DateTimeSET @.DateCreated =GetDate()

This is the same as setting the default value, and again means you don't need to pass it as a parameter.

Hope this helps,

Paul

DateTime help

This simply shouldn't take all morning to figure out but for some reason it has. I simply want to insert the current date and time into a datetime field.

No matter what I try I either get errors (Syntax error converting datetime from character string.) or I get the wrong date (4/11/1900, 1/1/1900).

Here's my current SQL which gives the syntax error.

CREATE PROCEDURE [dbo].[QuoteApprovalWeb_Approve]
@.table nvarchar(50),
@.approvedby nvarchar(100),
@.quote nvarchar(50),
@.dt datetime
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'Update [' + @.table + '] set quoteapproval = ' + "'" + @.approvedby + "', "
Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='
Select @.SQL = @.SQL + "'" + @.quote + "'"
exec (@.sql)
GO

I tried replacing @.dt with getdate() but that would always give me errors also.replace the line

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + @.dt + ' where quoteno ='

with

Select @.SQL = @.SQL + 'quoteapprovaldate = ' + '''' + CAST(@.DT AS VARCHAR(20))+ '''' + ' where quoteno ='

your string would look something like
quoteapprovaldate = 'Apr 5 2004 3:35PM' where quoteno =

Navin|||Sweet - that works great!

Thanks much.

Datetime Filter

For one of the tables on my report i need to allow only the records where a
certain datetime field has Null value. I have not been able to figure out the
expression in the filter that would do this.
Any ideas?
I cannot do this in the select statement as other data regions require
records with values in the same field.You can do a filter at the table level (the Table report object, that is) or
at the list level by high-lighting the Table or List (for instance) and
selecting the filters from the Properties menu and placing a filter on the
data in the object. Hope this helps.
"Nice_Out" wrote:
> For one of the tables on my report i need to allow only the records where a
> certain datetime field has Null value. I have not been able to figure out the
> expression in the filter that would do this.
> Any ideas?
> I cannot do this in the select statement as other data regions require
> records with values in the same field.|||Thanks. I have figured out that I can do a filter.
What I am having trouble with is the expression to use in the filter. I get
the feeling that it does not like to handle Nulls.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> You can do a filter at the table level (the Table report object, that is)
or
> at the list level by high-lighting the Table or List (for instance) and
> selecting the filters from the Properties menu and placing a filter on the
> data in the object. Hope this helps.
> "Nice_Out" wrote:
> > For one of the tables on my report i need to allow only the records
where a
> > certain datetime field has Null value. I have not been able to figure
out the
> > expression in the filter that would do this.
> >
> > Any ideas?
> >
> > I cannot do this in the select statement as other data regions require
> > records with values in the same field.|||Sorry about that. If I had read more closely I would have seen that. Have
you tried the following in your filter?
set Expression to your date/time field
set Operator to "="
set Value to "= Nothing"
This is assuming you do not also need to include a date spread for when the
field does have a value. If so, let me know. I have that figured out as
well.
"Nice_Out" wrote:
> Thanks. I have figured out that I can do a filter.
> What I am having trouble with is the expression to use in the filter. I get
> the feeling that it does not like to handle Nulls.
> "Rand" <Rand@.discussions.microsoft.com> wrote in message
> news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > You can do a filter at the table level (the Table report object, that is)
> or
> > at the list level by high-lighting the Table or List (for instance) and
> > selecting the filters from the Properties menu and placing a filter on the
> > data in the object. Hope this helps.
> >
> > "Nice_Out" wrote:
> >
> > > For one of the tables on my report i need to allow only the records
> where a
> > > certain datetime field has Null value. I have not been able to figure
> out the
> > > expression in the filter that would do this.
> > >
> > > Any ideas?
> > >
> > > I cannot do this in the select statement as other data regions require
> > > records with values in the same field.
>
>|||Hey thanks!
That worked groovy.
I swear I tried everything so close to that. Don't know how I missed it.
"Rand" <Rand@.discussions.microsoft.com> wrote in message
news:DCE9E96F-CD79-4D43-B03A-88204095C6C0@.microsoft.com...
> Sorry about that. If I had read more closely I would have seen that.
Have
> you tried the following in your filter?
> set Expression to your date/time field
> set Operator to "="
> set Value to "= Nothing"
> This is assuming you do not also need to include a date spread for when
the
> field does have a value. If so, let me know. I have that figured out as
> well.
>
> "Nice_Out" wrote:
> > Thanks. I have figured out that I can do a filter.
> > What I am having trouble with is the expression to use in the filter. I
get
> > the feeling that it does not like to handle Nulls.
> >
> > "Rand" <Rand@.discussions.microsoft.com> wrote in message
> > news:DD85D2AC-7562-426A-815B-0B7936D95D9F@.microsoft.com...
> > > You can do a filter at the table level (the Table report object, that
is)
> > or
> > > at the list level by high-lighting the Table or List (for instance)
and
> > > selecting the filters from the Properties menu and placing a filter on
the
> > > data in the object. Hope this helps.
> > >
> > > "Nice_Out" wrote:
> > >
> > > > For one of the tables on my report i need to allow only the records
> > where a
> > > > certain datetime field has Null value. I have not been able to
figure
> > out the
> > > > expression in the filter that would do this.
> > > >
> > > > Any ideas?
> > > >
> > > > I cannot do this in the select statement as other data regions
require
> > > > records with values in the same field.
> >
> >
> >

DateAdd() in Default Value

My goal is to set the default value of a smalldatetime field to the GetDate() plus 4 hours. I figure I need to use DateAdd() for this, but can't figure out how to place it in a default value. I can put GetDate() in default value, but DateAdd(hour,4,GetDate()) throws a syntax error.

Is there a simple way to accomplish this?

bes7252,

What error are you seeing, exactly? I have no problem executing the following code:

create table t(
i int,
d datetime default (dateadd(hour,3,getdate()))
)
go

drop table t

Have you left out a ) somewhere?

Steve Kass
Drew University
http://www.stevekass.com
|||

Logically it doesn’t make any sense to have this expression on your column. Suppose if you generate the expression on your stored date then it is acceptable.

|||

Steve,

I tried it again and didn't have any problems. I don't remember the error I was seeing yesterday, but must have had a typo-o in my expression. (I typed the one on the post from memory, not copy/paste).

Thanks!

Brian