CODEWITHSUNDEEP
c#asp.net
user6072031
user6072031

Reputation:

Inserting date into database when we insert data to database every time display inserting date in asp.net

when i inserting data into database i need to insert date also but i pass a insert query in this query i inserted data but it took date column by default SQL database date format but i want to display when the user enter data to insert data base that date i want to display my tried query is: 

string s = "insert into BatchPermissons(BatchId,SubjectId,AuditoName,AudioID,CreatedBy,CreatedDate) values(" + batchno + ", " + subjectid + " , '" + AudiotoName + "', " + AudioId + ",'" + CBY + "'," + DateTime.Now.ToString() + ")";
SqlCommand cmd = new SqlCommand(s, con);
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

but the above query inserting default date in database.i need to insert when i insert data that particular date will be store can any one help me out.

Upvotes: 1

Views: 13973

Answers (3)

sujith karivelil
sujith karivelil

Reputation: 29036

If the date input will always be the current date then you can use the sql method getdate() to fill the value. if it is not, you have to give the date in proper format, that means in "yyyy-MM-dd HH:mm:ss" it is simple to change the format using .ToString() so the query will be :

string s = "insert into BatchPermissons(...,CreatedDate) values(...," + getdate() + ")"; // using standard method date

Or

string s = "insert into BatchPermissons(...,CreatedDate) values(...," + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ")"; // using formated date

Special note:

Can we stop opening way to sql injection using text only queries and start writing parameterised queries? So that we can avoid the unwanted FormatingException and also give protection from injection. In this case you can use parameterised queries like the following:

string QuerySql = "insert into BatchPermissons(BatchId,SubjectId,AuditoName,AudioID,CreatedBy,CreatedDate)" +
"values(@batchno,@subjectid,@AudiotoName,@AudioId ,@CBY,@dtime)";
SqlCommand cmd = new SqlCommand(s);
cmd.CommandType = CommandType.Text;
cmd.CommandText = QuerySql;
cmd.Parameters.Add("@batchno", SqlDbType.VarChar).Value = batchno;
cmd.Parameters.Add("@subjectid", SqlDbType.VarChar).Value = subjectid;
cmd.Parameters.Add("@AudiotoName", SqlDbType.VarChar).Value = AudiotoName;
cmd.Parameters.Add("@AudioId", SqlDbType.VarChar).Value = AudioId;
cmd.Parameters.Add("@CBY", SqlDbType.VarChar).Value = CBY;
cmd.Parameters.Add("@dtime", SqlDbType.DateTime).Value = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

if you are using the getdate() method then you can avoid the last Parameter from adding value, it will be like the following:

"values(@batchno,@subjectid,@AudiotoName,@AudioId ,@CBY,getdate())"

Upvotes: 2

Khairul Islam
Khairul Islam

Reputation: 1215

If your Database column type is datetime then try to insert datetime directly like this-

string s = "insert into BatchPermissons(BatchId,SubjectId,AuditoName,AudioID,CreatedBy,CreatedDate) values(" + batchno + ", " + subjectid + " , '" + AudiotoName + "', " + AudioId + ",'" + CBY + "'," + DateTime.Today + ")";

Upvotes: 0

Dmitri Trofimov
Dmitri Trofimov

Reputation: 763

You can replace the DateTime.Now.ToString() with "getdate()". This will take the date from SQL server. Or add format to the ToString

DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");

Upvotes: 1

Related Questions