ASP.NET - parameter passing - Asked By prasanth on 05-Sep-11 12:22 AM

update tablename set username=@username,last_dateof_update='" + System.DateTime.Now + "';

hw to convert the field last_dateof_update  using parameter passing?

plz help

Sreekumar P replied to prasanth on 05-Sep-11 12:34 AM
Hi,

u can do like this..

update tablename set username=@username,last_dateof_update=CONVERT(datetime,'" + System.DateTime.Now + "');
aneesa replied to prasanth on 05-Sep-11 12:36 AM
create proc abc
@username varchar(max),
@date datetime
as
begin
update tablename set username=@username,last_dateof_update=@date
end

in C#

con.Conn.Open();

SqlCommand cmd = new SqlCommand("abc",con.Conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@username",txtuser.text);

cmd.Parameters.AddWithValue("@date", System.DateTime.Now );

cmd.ExecuteNonQuery();

cmd.Dispose();

con.Conn.Close();

Web Star replied to prasanth on 05-Sep-11 12:38 AM
You just put the parameter name in query string and assign value of paramter later as follows

 string sqlString = "update tablename set username=@username,last_dateof_update=@CurrentDate";

SqlParameter param1 = new SqlParameter();

param1.ParameterName = "@
CurrentDate";

param1.Value =  System.DateTime.Now.ToString();

param1.DbType = System.Data.DbType.VarChar;

//and finally add that parameter to sqlcommand 
 mCon = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
    if (mCon.State == ConnectionState.Closed)
    mCon.Open();
    mDataCom = new SqlCommand(sqlString, mCon);
    mDa = new SqlDataAdapter(mDataCom);

mDa.SelectCommand .Parameters.Add(param1);

    ds = new DataSet();
    mDa.Fill(ds, "Name");

Sreekumar P replied to prasanth on 05-Sep-11 12:39 AM
and the best method i can say is that

  if (mCon == null)
    mCon = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
  if (mCon.State == ConnectionState.Closed)
    mCon.Open();
  SqlCommand cmd;
   
 
string ss="update tablename set username=@username,last_dateof_update=@currDate"
cmd = new SqlCommand(ss, con);
cmd.Connection.Open();
cmd.Parameters.AddWithValue("@username", youUserName);
cmd.Parameters.AddWithValue("@currDate", DateTime.Now);
cmd.ExecuteNonQuery();
Web Star replied to prasanth on 05-Sep-11 12:39 AM
sorry just use cmd.ExecuteNonQuery(); in above post by mistake that put fill method of dataset.
dipa ahuja replied to prasanth on 05-Sep-11 04:11 AM
Try this:

void update()
{
  string s = "update tablename set username=@username,last_dateof_update=@dt";
  SqlConnection conn = new SqlConnection("coonstring");
  SqlCommand comm = new SqlCommand(s,conn);
         
  comm.Parameters.AddWithValue("username", TextBox1.Text);
  comm.Parameters.AddWithValue("dt", System.DateTime.Now);
  conn.Open();
  comm.ExecuteNonQuery();
  conn.Close();
}