日期时间格式在oledbCommand.executeNonQuery上更改 [英] datetime format changes upon oledbCommand.executeNonQuery

问题描述

我在我的网站上有一个sql插入查询，该查询会插入一些字符串和整数，并在"dd/MM/yyyy HH:mm:ss"中插入一个日期时间，直到今天它仍然运行良好.但是，从今天起，由于某种奇怪的原因，在查询的executeNonQuery方法期间，日期时间的格式更改为"MM/dd/yyyy HH:mm:ss".我不知道为什么会这样，这让我发疯.任何人都可以阐明为什么会发生这种情况以及如何防止这种变化吗?任何帮助将不胜感激.

i have an sql insert query in my website,which inserts a few strings and ints, and a datetime in "dd/MM/yyyy HH:mm:ss", and until today it worked great. however, from today, for some odd reason, during the executeNonQuery method of the query, the format of the datetime changes to "MM/dd/yyyy HH:mm:ss". i have no clue as for why this is happening, and it is driving me crazy. can anyone please shed some light on why this happens and how i can prevent this change? any help would be appreciated.

查询:

"INSERT INTO Orders(OrderDate,MemberID,CityID,OrderAdress,CreditCardID,OrderStatus)VALUES(#" + o.OrderDate + "#," + o.MemberID + ","+o.CityID+",'" + o.OrderAdress + "',"+o.CreditCardID+",'Not sent')" 

o是保存所有数据的对象.

o is an object holding all of the data.

推荐答案

在连接字符串时尝试构建查询时出现大问题.这是暴露于SQL注入的巨大事物.最好的方法是使用PARAMETERIZED查询，您可以四处查找并找到它们，而您可能根本不知道它们.

Big problem when trying to build a query when concatenating strings. This is a HUGE thing for exposure to SQL-Injection. The best way to do it is with using PARAMETERIZED queries and you can look all over and find them, you probably were just unaware of them.

基本上在查询中，您使用?"作为所需参数的占位符，然后添加具有实际值/数据类型的参数对象，OleDb查询将其放置在其位置并具有正确的数据类型，因此您不必担心格式化字符串从特定的日期开始.

Basically in your query, you use a "?" as a place-holder for the parameter you want, then add a parameter object with the actual value / data type and the OleDb querying will put it in its place and have proper data type so you don't have to worry about formatting the string from a date in a specific order.

另外，对于姓名，如果您的名字叫"O'Conner"，该怎么办.您刚刚终止了查询字符串，否则将失败.您将严重挠头.

Also, for names, what if you had a person's name of "O'Conner". You have just pre-terminated your query string and would fail otherwise. You would be severely scratching your head.

说了这么多，让我们回到您的查询，使其更具可读性，并将其参数化...

Having said all that, lets get back to your query, make it a little more readable, and parameterize it...

您将ms-access称为数据库和OleDb，这意味着您正在用C#或VB(也许是其他)编写.我将演示使用C#，您可以根据需要更改您的开发语言.

You refer to ms-access as the database and OleDb which implies you are writing in either C#, or VB, maybe other. I will demonstrate using C#, you could change as needed to your dev language.

using(OleDbConnection connection1 = new OleDbConnection( WhateverYourConnectionString ) 
{
   connection1.Open();  
   using(OleDbCommand sqlcmd = new OleDbCommand("", connection1))
   {
      // simplified query and you can see the "?" place-holders
      sqlcmd.CommandText =
@"INSERT INTO Orders
  ( OrderDate,
    MemberID,
    CityID,
    OrderAdress,
    CreditCardID,
    OrderStatus )
  VALUES
  ( ?,
    ?,
    ?,
    ?,
    ?,
    'Not sent' )";

      // Now, add your parameters in the SAME ORDER as the "?" in the query
      sqlcmd.Parameters.AddWithValue("parmForDate", o.OrderDate ); 
      sqlcmd.Parameters.AddWithValue("parmForMember", o.MemberID ); 
      sqlcmd.Parameters.AddWithValue("parmForCity", o.CityID ); 
      sqlcmd.Parameters.AddWithValue("parmForAddress", o.OrderAddress ); 
      sqlcmd.Parameters.AddWithValue("parmForCard", o.CreditCardID ); 
      // since the last parameter is fixed, you can put that in explicitly.
      // you can similarly put fixed field of other strings, numbers.

      // Now you can execute it
      sqlcmd.ExecuteNonQuery();
   }

   connection1.Close()
}

这篇关于日期时间格式在oledbCommand.executeNonQuery上更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！