error:the string was not recognized as a valid DateTime

[mercea]

hi all, i'm trying to insert the time/date a button was clicked on a gridview and it generates an error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0 i have changed the culture to en-US but it still doesn't work. i actually created the date/time column after some data had been entered into the table so the column allows nulls. this is my code:

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Date/Time)VALUES (@Surname, @Names, @Registration,@DateTime,getdate())"

<Insert Parameters><asp:Parameter DefaultValue= DateTime.Now() Type=DateTime Name="DateTime" /></Insert Parameters>

any suggestions?

[nikkiH]

Don't use reserved words (i.e DateTime) for variables, it can often do bad things.
Other than that, why is getdate() in InsertCommand? You're already setting the value with the InsertParameter. Either remove getdate() or remove the parameter.

[mercea]

HI, Thanks for the suggestion but it didnt work. i changed the column name to LoginTime and removed the getdate() from the insert command but it still says error:the string was not recognized as a valid DateTime.There is an unknown word starting at index 0.

[vinyl-junkie]

Did you also change @DateTime? I believe you need to do that as well.

[nikkiH]

InsertCommand="INSERT INTO test101(Surname,Names,Registration,Date/Time)

Hey, what's that bolded part? I don't think you can have a slash in a column name, at least not without escaping it (depends on DBMS, perhaps your allows it?)

Also, what vinyl-junkie said.

[mercea]

i changed the name to LoginTime and the insert command to @loginTime but it still gives the same error!

[nikkiH]

Can you post the current code you have please?
Did you change that part I bolded?

[mercea]

this is my new code:
asp:SqlDataSource ID="compdatasource" runat="server" ConnectionString="<%$ ConnectionStrings:engineeringConnectionString %>"
InsertCommand="INSERT INTO [Complaints Table]
(Registration, Complaints, LoginTime)VALUES (@Registration, @Complaints, @LoginTime)"
ProviderName=System.Data.SqlClient
ConflictDetection=CompareAllValues >
<InsertParameters>
<asp:ControlParameter ControlID="regcompt" Name="Registration" DefaultValue="regcompt.Text"
PropertyName="Text" Size=9 Type=string />
<asp:ControlParameter ControlID="studcomp" Name="Complaints" DefaultValue="studcomp.Text"
PropertyName="Text" Type=string />
<asp:Parameter Name=LoginTime DefaultValue=getdate() Type=DateTime />
</InsertParameters>
</asp:SqlDataSource>

[vinyl-junkie]

You must also change your database field name to LoginTime. Did you do that?

[mercea]

yeah i did that and it still doesn't work. dont know what to do

[SouthwaterDave]

I haven't checked that I am right but aren't you mixing SQL Server functions with ASP.NET functions?<asp:Parameter Name=LoginTime DefaultValue=getdate() Type=DateTime />

If all you need is the time of when the row is inserted then set a default value on your SQL Server database column:create table [Complaints Table] ( ...
LoginTime datetime default getedate(), ...
)

Or you could change your INSERT statement: insert into [Complaints Table] (Registration, Complaints, LoginTime)
values (@Registration, @Complaints, getdate())

[rafiki]

you could use the mysql function NOW()

[mercea]

hey guys! thanks for all your help. i put this code in the code behind page

sqldatasource1.InsertParameters["Login_Time"].Default value=DateTime.Now.ToString();
sqldatasource.Insert();

and it worked!!

thanks for all your input