sql - SqlCommandBuilder With Convert Statement -

i have application that's built in .net language.

in application read/write database (sql server 2005). (for single input) use sql query, example:

commandtext = "insert test_table (number) values ('10')"; command = new system.data.sqlclient.sqlcommand(commandtext, connection); command.executenonquery(); 

if want update bunch of records in database, use sqlcommandbuilder class, in example:

adapter = new system.data.sqlclient.sqldataadapter("select * test_table number = '9'",connection);  commandbuilder = new system.data.sqlclient.sqlcommandbuilder(adapter);  dataset = new system.data.dataset(); adapter.fill(dataset,"example");  dataset.tables["example"].rows[0].["number"] = 10; adapter.update(dataset,"example"); 

these work great. now, reason need insert/update datetimes , use convert function on it.

the single sql query works great:

t = system.datetime.now; commandtext = "insert test_table (datetime) values (datetime, 't.tostring()', 103)"; command = new system.data.sqlclient.sqlcommand(commandtext, connection); command.executenonquery(); 

this works without problem, have no idea how achieve same thing using sqlcommandbuilder scripts. change single query, take week.

i have tried following, without success:

t = system.datetime.now; adapter = new system.data.sqlclient.sqldataadapter("select * test_table number = '9'", connection);  commandbuilder = new system.data.sqlclient.sqlcommandbuilder(adapter);  dataset = new system.data.dataset(); adapter.fill(dataset, "example");  dataset.tables["example"].rows[0].["datetime"] = "convert(datetime,'" + t.tostring() + "',103); adapter.update(dataset, "example"); 

this line of code weird:

dataset.tables["example"].rows[0].["datetime"] = "convert(datetime,'" + t.tostring() + "',103); 

does compile? specifically, this:

.rows[0].["datetime"]  

i think should be:

.rows[0]["datetime"]  

but regardles of syntax...i don't think right way go. datatable (in dataset) expects datetime object (btw, don't name attributes datatype, causes confusion) , providing incompatible. sytem.datetime.now returns datetime object, concatenating string (again, compile?) , assume expect injected insert statement?

since said take week change everything, assume have lot of similar code repair. see 3 possible solutions, require work:

1. create database trigger

https://msdn.microsoft.com/en-us/library/ms189799.aspx

2. add default value datetime field in database , remove datetime select query.

http://www.w3schools.com/sql/sql_default.asp

https://www.youtube.com/watch?v=infi7jkdxc8 (start watching @ around 2:00)

3. you can write function actual text replacing can tricky:

dasdas as

private string changedate(string insertquery)     {     // find location of date column     // replace actual value "convert(datetime,'" + actualvalue + "',103)"     // return new value , store in sqlcommandbuilder.insertstatement     } 

admittedly, 3 require work , not "elegant". go option 2, because seems less work. don't know if solves problem..