problem with update query in c# .net

Question

Dear sir/Madam This is vishnu vardhani got some error while update an field of a table which is connected by using oledb connection. Table name is totallogins contents are id(autonumber)token_no(text)user_id(number)from_date(date/time)to_date(date/time). my require output is at the time of login idtoken_nouser_idfrom_date are filled and to_date column is fill when exit button clicks.In the attached query i got error msg as Data type mismatch.Could you please check and suggest us how to resolve my problem. THANKS IN ADVANCE.

Amit Choudhary · Answer

well param1 is an object of SqlParameter class that helps to map the .Net Type data to the Sql Types data. Hope it make sense.

vishnuvardhan diyyala · Answer

Dear amit By your example i got my mystake. But im unable to resolve that. y because i m a beginer(student) in .net. so unable to catch u r solution. SqlParameter param1 = new SqlParameter(); param.ParameterName = @NewDate; and SqlParameter param2 = new SqlParameter(); param.ParameterName = @OldDate; what is param1parameter name in second line of above two sentences given by u.My database table name is totallogins. on that table id--------Auto no tokenno----text userid-----number fromdate----date/time and todate----date time. Could you pleae explain me about parameter method Thanks in advance.

Amit Choudhary · Answer

Thanks for clarification suthish...

Suthish Nair · Answer

1. Passing datetime as a string value. 2. Due to that Datatype mismatch happening. 3. SQLParameter will take care of all the formats between SQL DB table and your commands. 4. Best way if your DB table datatype column is DateTime then SqlParameter param = cmd.Parameters.Add(@datetime System.Data.SqlDbType.DateTime).Value = DateTime.Now; 5. Do not convert to string value can use Convert.ToDateTime() method.

Amit Choudhary · Answer

I have faced the same problem a long time ago.. when i was try to insert the date into database using DateTime.Now. Suthish is right by using the Parametrized query your problem will be solve. Here is the sample: string st6 = update totallogins set To_date =@NewDate where from_date=@OldDate; cmd.CommandText = st6; SqlParameter param1 = new SqlParameter(); param.ParameterName = @NewDate; param.Value = DateTime.Now; SqlParameter param2 = new SqlParameter(); param.ParameterName = @OldDate; param.Value = b; cmd.Parameters.Add(param1); cmd.Parameters.Add(param2); cmd.Connection = con; con.Open(); cmd.ExecuteNonQuery(); con.Close(); Now Suthish i have question here and need your input.. why DateTime Could'nt save directly using the command object ?? Why do we need to use parameter to save the Date using DateTime class. Thanks.

Suthish Nair · Answer

This not a good way of coding.. Leads to SQL Injection always use Parameterized Query. This will solve your problem give same datatype during creating parameters. Parameterized Query and SQL Injection Attacks