从字符串转换为datetime 时发生语法错误

Question

        
public void saveStocks(Stocks[] stocks)
        {            sql = " INSERT INTO Stocks VALUES   " +
                "('@ListNumber'  ,  '@CustomerName'  , '@Company' , '@ProductName' , " +
                "'@ProductType' , '@ProductUnit' , '@Count' , '@Price' , '@Money' , " +
                "'@ImportOrExport' , '@StockInDate' , '@StockOutDate' , '@Demo' , " +
                "'@UserId' , '@EnterDate') ";
            try
            {                for (int i = 0; i < stocks.Length; i++)
                {
                    cmd = new SqlCommand(sql, con);
                    
                    cmd.Parameters.AddWithValue("@ListNumber", stocks[i].ListNumber);
                    cmd.Parameters.AddWithValue("@CustomerName", stocks[i].CustomerName);
                    cmd.Parameters.AddWithValue("@Company", stocks[i].Company);
                    cmd.Parameters.AddWithValue("@ProductName", stocks[i].ProductName);
                    cmd.Parameters.AddWithValue("@ProductType", stocks[i].ProductType);
                    cmd.Parameters.AddWithValue("@ProductUnit", stocks[i].ProductUnit);
                    cmd.Parameters.AddWithValue("@Count", stocks[i].Count);
                    cmd.Parameters.AddWithValue("@Price", stocks[i].Price);
                    cmd.Parameters.AddWithValue("@Money", stocks[i].Money);
                    cmd.Parameters.AddWithValue("@ImportOrExport", stocks[i].ImportOrExport);
                    cmd.Parameters.AddWithValue("@StockInDate", stocks[i].StockInDate);
                    cmd.Parameters.AddWithValue("@StockOutDate", stocks[i].StockOutDate);
                    cmd.Parameters.AddWithValue("@Demo", stocks[i].Demo);
                    cmd.Parameters.AddWithValue("@UserId", stocks[i].UserId);
                    cmd.Parameters.AddWithValue("@EnterDate", System.DateTime.Now);                    con.Open();
                    trans = con.BeginTransaction();
                    cmd.Transaction = trans;
                    cmd.ExecuteNonQuery();
                    trans.Commit();
                    cmd.Dispose();
                    con.Close();
                }
                
            }
            catch (Exception ex)
            {
                //throw ex;
                throw new Exception("StocksData数据库表Stocks保存时出错，退出！" + ex.ToString());
            }
            finally
            {
                cmd.Dispose();
                con.Close();
            }
        
        }
报错为：从字符串转换为 datetime 时发生语法错误。单步跟踪三个数值为：stocks[i].StockInDate={2009-11-09 00:00:00}
stocks[i].StockOutDate = {1900-01-01 00:00:00}stocks[i].EnterDate = {2009-11-09 19:42:05}请大家帮忙看一下，错在哪？？？

Answer 1

单步跟踪三个数值为： stocks[i].StockInDate={2009-11-09 00:00:00} 
stocks[i].StockOutDate = {1900-01-01 00:00:00} stocks[i].EnterDate = {2009-11-09 19:42:05} 
这里没错,
字符型是要用''引起来的,你的插入语句是否正确?

Answer 2

试试:
cmd.Parameters.AddWithValue("@StockInDate",  DateTime.Parse(stocks[i].StockInDate.ToString()));
cmd.Parameters.AddWithValue("@StockOutDate", DateTime.Parse(stocks[i].StockOutDate.ToString()));

Answer 3

INSERT INTO Stocks         
VALUES   ('@ListNumber','@CustomerName','@Company','@ProductName','@ProductType','@ProductUnit','@Count','@Price','@Money','@ImportOrExport','@StockInDate','@StockOutDate','@Demo','@UserId','@DateTime EnterDate') 

Answer 4

INSERT INTO Stocks         
VALUES   ('@ListNumber','@CustomerName','@Company','@ProductName','@ProductType','@ProductUnit','@Count','@Price','@Money','@ImportOrExport','@StockInDate','@StockOutDate','@Demo','@UserId','@EnterDate') 上面是错的，插入语句是这个！！

Answer 5

 把字符型的日期类型转换下
DateTime.Parse('yourtime');

Answer 6

你把时间类型都不放到那，它怎么转化啊？这样试试吗：SqlParameter[] par = new SqlParameter[3];
                par[0] = new SqlParameter("@StockInDate", SqlDbType.DateTime);
                par[0].Value = stocks[i].StockInDate;
                par[1] = new SqlParameter("@StockOutDate", SqlDbType.DateTime);
                par[1].Value = stocks[i].StockOutDate;
                par[2] = new SqlParameter("@EnterDate", SqlDbType.DateTime);
                par[2].Value = System.DateTime.Now;
cmd.Parameters.AddRange(par);

Answer 7

那我也有点晕了，你的那三个时间都是string的吗？用par[0].Value = stocks[i].StockInDate.ToString().SubString(0,19)呢？

Answer 8

三个时间都是DateTime类型！！

Answer 9

不要单引号VALUES   (@ListNumber,@CustomerName,