Problem inserting small date time in a sql server database

Problem inserting small date time in a sql server database

• Dieter D

  I have the following in my code behind page, i would like to add a parameter:
  
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  dim fname as string
  fname = "Lindsey"
  sqlstring = "SELECT pid, fname, lname, dob, street, city, state, zip, description, imgdata FROM sexoffender where fname = '"& fname & "' "
  
  SqlDataSource1.SelectCommand = sqlstring
  
  End Sub
  
  
  How can I add a parameter to pass the fname variable to my sqlstring which is used by a datasource
  
  

• kmorrill

  Why didn't you create parameters for all the other arguments as well

  Why do you only create a parameter for the datetime field, and not for the description field

  And why didn't you create a parameter in your select query as well The same rules apply there... This error is caused because of the same reason like your first problem.

  (Oh, and I don't think that you want to show your password to everybody...)

  
  
  

• priya1

  Hi Frederik,

  The parametrized query worked great but now I have a problem where when I do select command it is telling me that the smalldatetime is out of range. I am a little confused as I have only entered the value using the parametrized query. Would it be a setting of the smalldatetime format that stores the values in mm/dd/yyyy format.

  Below is my code:

  Parameter enDate = new Parameter("en", TypeCode.DateTime, CurDate);

  SqlDataSource1.SelectParameters.Add(enDate);

  int Num = 1;

  string text = "Test1";

  Response.Write(CurDate);

  SqlDataSource1.SelectCommand = "INSERT INTO Weekly_DB (Gen_Desc,Date ) VALUES ('" + text + "', @en)";

  //New test part to try and return the date value

  SqlConnection conn = new SqlConnection("Data Source=DATASERV;Initial Catalog=DeRoyal_Backup;User ID=backups;Password=Backups");

  conn.Open();

  SqlCommand comm = new SqlCommand("SELECT * FROM Weekly_DB WHERE Date = '" + TodayDate.ToShortDateString() + "'", conn); //WHERE Gen_Desc LIKE '%" + cheat + "%'"

  SqlDataReader dr = comm.ExecuteReader();

  try

  {

  if (dr.Read())

  {

  string DataDate = (dr["Date"].ToString());

  Response.Write(DataDate);

  }

  }

  finally

  {

  if (dr != null)

  {

  dr.Close();

  }

  if (conn != null)

  {

  conn.Close();

  }

  }

  The reason I am doing this is that I want to be able to comapre the the dates already entered into the database compared to todays date.

  
  

• Price Brattin

  Really, it takes little effort to create a parameter for the description field, and it will really pay of.

  Your query is now vulnerable for sql injection, which means that somebody can just drop tables in your database.

  Please, re-read the link I've given to you. You have to add a parameter to your command, not to your connection

  SqlCommand command = new SqlCommand();

  command.Connection = conn;

  command.CommandText = "SELECT * FROM Weekly_DB Where [Date] = @theDate";

  command.Parameters.Add ("@theDate", SqlDbType.DateTime);

  command.Parameters["@theDate"].Value = aDateVariable;

  conn.Open();

  try

  {

  SqlDataReader dr = command.ExecuteReader();

  }

  finally

  {

  conn.Close();

  }

  
  
  

• GReg Bonebrake

  Frederik, please forgive me for my mistakes, the description field is unimportant as the moment and I can change that to a parameter at a later date.

  I have tried passing in the value of the parameter en to the data reader but it tells me that I must declare "en". I have tired added the parameter to the command as so

  conn.Selectparameters.add(enDate)

  and

  comm.selectparameters.add(enDate)

  but no joy, I think the problem now is that I cannot add the parameter to the new command.

  Any suggestions

  
  

• Hemantkum

  Thanks Frederik that worked out perfect for me. You are too kind.
  

• Kembreg

  The problem is because you use string concatenation to create your insert query.
  This is not safe and might cause problems when you insert datetimes in your database (what about datetime regional settings)
  You can solve this problem by using parametrized queries:

  
  http://fgheysels.blogspot.com/2005/12/avoiding-sql-injection-and-date.html

  
   

  
  
  
• Problem inserting small date time in a sql server database