从字符串转换日期和时间时转换失败 [英] conversion failed when converting date and time from character string

问题描述

我有一个将数据保存到sql db的表单。和它的工作相关

保存代码

  public   void  saveRegister（）
 {
  //  将数据保存在内存中 
 
 SqlCommand cmd =  new  SqlCommand（）; 
  string  dat =  null ; 
 dat = regdate.Year +    -  + regdate.Month +    -  + regdate.Day; 
 
  string  dat1 =  null ; 
 dat1 = dateofbirth.Year +    -  + dateofbirth.Month +    -  + dateofbirth.Day; 
 
  string  sqlQuery =  null ; 
 sqlQuery =  插入tblPersonal值（' + dat +  '，' + assemblys +  '，' + surname +  '，' + othername +  '，' + gender +   '，' +国籍+   '，' + dat1 +  '，' + postaladdress +  '，' + residentialaddress +  '，' + hometownaddress +  '，' + tele电话+  '，' +电子邮件+  '，' +占用+  ' ，' + maritalstatus +  '，' + nameofspouse +  '，' + motherfullname +  '，' + motherlivingstatus +  '，' + motherhometown +  '，' + fatherfullname +   '，' + fatherlivingstatus +  '，' + fatherhometown +  '，' + nokname +  '，' + nokresidence +  < span class =code-string>'，' + noktelephone +  '，' + images +  '）; 
  //   sqlQuery =插入tblPersonal值（'+ membershipidid +'，'+ dat +'，'+汇编+'，'+姓氏+'，'+ name +'，'+性别+'，'+国籍+'，'+ dat1 + '，'+ postaladdress +'，'+ residentialaddress +'，'+ hometownaddress +'，'+ phone +'，'+ email +'，'+ occupation +'， '+ maritalstatus +'，'nameofspouse +'，'+ motherfullname +'，'+ motherlivingstatus +'，'+ motherhometown +'，'+ fatherfullname +'，' + fatherlivingstatus +'，'+ fatherhometown +'，'+ nokname +'，'+ nokresidence +'，'+ noktelephone +'，'+ images +'）;  
 
 cmd.Connection = conn; 
 cmd.CommandText = sqlQuery; 
 cmd.CommandType = System.Data.CommandType.Text; 
 conn.Open（）; 
 cmd.ExecuteNonQuery（）; 
 cmd.Dispose（）; 
 conn.Close（）; 
} 

我尝试在两个日期之间生成报告，我收到错误消息转换失败时转换日期和时间来自字符串'



报告代码

 SqlDataAdapter adp = new SqlDataAdapter（  SELECT * from tblPersonal WHERE Regdate BETWEEN' + this.dtpDate1。文本 +  'AND' + this.dtpDate2。 文字 +  '按姓氏排序 ，conn）; 

请帮帮我

解决方案

更改你的代码使用参数化查询，修复 SQL Injection [ ^ ]漏洞。将日期参数作为日期而不是字符串传递。

  const   string  sqlQuery =  插入tblPersonal值（@regate，@ assembly，@姓，@ name ，@ gender，@ Nonality，@ DateOfBirth，@ postaladdress，@ theresidealaddress，@ hometownaddress，@ telephone，@ email，@ role，@ maritalstatus，@ nameofspouse，@ motherfullname，@ motherlivingstatus，@ motherhometown，@ fatherfullname，@ fatherlivingstatus，@ fatherhometown，@ nokname，@ nokresidence，@ noktelephone，@ images）; 
 
 使用（SqlCommand cmd =  new  SqlCommand（sqlQuery，con））
 {
 cmd.CommandType = System.Data.CommandType.Text; 
 
 cmd.Parameters.AddWithValue（  @ regdate，regdate）; 
 cmd.Parameters.AddWithValue（  @ assemblys，assemblys）; 
 cmd.Parameters.AddWithValue（  @ surname，姓氏）; 
 cmd.Parameters.AddWithValue（  @ othername，othername）; 
 cmd.Parameters.AddWithValue（  @ gender，性别）; 
 cmd.Parameters.AddWithValue（  @ national，国籍）; 
 cmd.Parameters.AddWithValue（  @ DateOfBirth，dateofbirth）; 
 cmd.Parameters.AddWithValue（  @ postaladdress，postaladdress）; 
 cmd.Parameters.AddWithValue（  @ residentialaddress，residentialaddress）; 
 cmd.Parameters.AddWithValue（  @ hometownaddress，hometownaddress）; 
 cmd.Parameters.AddWithValue（  @ telephone，电话）; 
 cmd.Parameters.AddWithValue（  @ email，email）; 
 cmd.Parameters.AddWithValue（  @ occupation，职业）; 
 cmd.Parameters.AddWithValue（  @ maritalstatus，maritalstatus）; 
 cmd.Parameters.AddWithValue（  @ nameofspouse，nameofspouse）; 
 cmd.Parameters.AddWithValue（  @ motherfullname，motherfullname）; 
 cmd.Parameters.AddWithValue（  @ motherlivingstatus，motherlivingstatus）; 
 cmd.Parameters.AddWithValue（  @ motherhometown，motherhometown）; 
 cmd.Parameters.AddWithValue（  @ fatherfullname，fatherfullname）; 
 cmd.Parameters.AddWithValue（  @ fatherlivingstatus，fatherlivingstatus）; 
 cmd.Parameters.AddWithValue（  @ fatherhometown，fatherhometown）; 
 cmd.Parameters.AddWithValue（  @ nokname，nokname）; 
 cmd.Parameters.AddWithValue（  @ nokresidence，nokresidence）; 
 cmd.Parameters.AddWithValue（  @ noktelephone，noktelephone）; 
 cmd.Parameters.AddWithValue（  @ images，images）; 
 
 conn.Open（）; 
 尝试 
 {
 cmd.ExecuteNonQuery（）; 
} 
 最后 
 {
 conn.Close（）; 
} 
} 

此外，修复报告的SQL Injection漏洞：

 SqlDataAdapter adp =  new  SqlDataAdapter（  SELECT * from tblPersonal WHERE Regdate BETWEEN @StartDate AND @EndDate ORDER BY Surname，conn）; 
 adp.SelectCommand.Parameters.AddWithValue（  @ StartDate，此 .dtpDate1.Value）; 
 adp.SelectCommand.Parameters.AddWithValue（  @ EndDate，此 .dtpDate2.Value）; 

I have a form which saves data into sql db. and its working correting
CODE FOR SAVING

public void saveRegister()
        {
            //saves the data in memory

            SqlCommand cmd = new SqlCommand();
            string dat = null;
            dat = regdate.Year + "-" + regdate.Month + "-" + regdate.Day;

            string dat1 = null;
            dat1 = dateofbirth.Year + "-" + dateofbirth.Month + "-" + dateofbirth.Day;

            string sqlQuery = null;
            sqlQuery = "Insert into tblPersonal values('" + dat + "','" + assemblys + "','" + surname + "','" + othername + "','" + gender + "','" + Nationality + "','" + dat1 + "','" + postaladdress + "','" + residentialaddress + "','" + hometownaddress + "','" + telephone + "','" + email + "','" + occupation + "','" + maritalstatus + "','" + nameofspouse + "','" + motherfullname + "','" + motherlivingstatus + "','" + motherhometown + "','" + fatherfullname + "','" + fatherlivingstatus + "','" + fatherhometown + "','" + nokname + "','" + nokresidence + "','" + noktelephone + "','" + images + "')";
            //sqlQuery = "Insert into tblPersonal values('" + membershipid + "','" + dat + "','" + assemblys + "','" + surname + "','" + othername + "','" + gender + "','" + Nationality + "','" + dat1 + "','" + postaladdress + "','" + residentialaddress + "','" + hometownaddress + "','" + telephone + "','" + email + "','" + occupation + "','" + maritalstatus + "','" + nameofspouse + "','" + motherfullname + "','" + motherlivingstatus + "','" + motherhometown + "','" + fatherfullname + "','" + fatherlivingstatus + "','" + fatherhometown + "','" + nokname + "','" + nokresidence + "','" + noktelephone + "','" + images + "')";

            cmd.Connection = conn;
            cmd.CommandText = sqlQuery;
            cmd.CommandType = System.Data.CommandType.Text;
            conn.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            conn.Close();
        }

I try generating report between two dates and I got the error message 'conversion failed when converting date and time from character string'

CODE FOR THE REPORT

SqlDataAdapter adp = new SqlDataAdapter("SELECT * from tblPersonal WHERE Regdate BETWEEN '" + this.dtpDate1.Text + "' AND '" + this.dtpDate2.Text + "' ORDER BY Surname", conn);

please help me out

解决方案

Change your code to use a parameterized query, fixing the SQL Injection[^] vulnerability. Pass your date parameters as dates, rather than strings.

const string sqlQuery = "Insert into tblPersonal values(@regdate, @assemblys, @surname, @othername, @gender, @Nationality, @DateOfBirth, @postaladdress, @residentialaddress, @hometownaddress, @telephone, @email, @occupation, @maritalstatus, @nameofspouse, @motherfullname, @motherlivingstatus, @motherhometown, @fatherfullname, @fatherlivingstatus, @fatherhometown, @nokname, @nokresidence, @noktelephone, @images)";

using (SqlCommand cmd = new SqlCommand(sqlQuery, con))
{
    cmd.CommandType = System.Data.CommandType.Text;

    cmd.Parameters.AddWithValue("@regdate", regdate);
    cmd.Parameters.AddWithValue("@assemblys", assemblys);
    cmd.Parameters.AddWithValue("@surname", surname);
    cmd.Parameters.AddWithValue("@othername", othername);
    cmd.Parameters.AddWithValue("@gender", gender);
    cmd.Parameters.AddWithValue("@Nationality", Nationality);
    cmd.Parameters.AddWithValue("@DateOfBirth", dateofbirth);
    cmd.Parameters.AddWithValue("@postaladdress", postaladdress);
    cmd.Parameters.AddWithValue("@residentialaddress", residentialaddress);
    cmd.Parameters.AddWithValue("@hometownaddress", hometownaddress);
    cmd.Parameters.AddWithValue("@telephone", telephone);
    cmd.Parameters.AddWithValue("@email", email);
    cmd.Parameters.AddWithValue("@occupation", occupation);
    cmd.Parameters.AddWithValue("@maritalstatus", maritalstatus);
    cmd.Parameters.AddWithValue("@nameofspouse", nameofspouse);
    cmd.Parameters.AddWithValue("@motherfullname", motherfullname);
    cmd.Parameters.AddWithValue("@motherlivingstatus", motherlivingstatus);
    cmd.Parameters.AddWithValue("@motherhometown", motherhometown);
    cmd.Parameters.AddWithValue("@fatherfullname", fatherfullname);
    cmd.Parameters.AddWithValue("@fatherlivingstatus", fatherlivingstatus);
    cmd.Parameters.AddWithValue("@fatherhometown", fatherhometown);
    cmd.Parameters.AddWithValue("@nokname", nokname);
    cmd.Parameters.AddWithValue("@nokresidence", nokresidence);
    cmd.Parameters.AddWithValue("@noktelephone", noktelephone);
    cmd.Parameters.AddWithValue("@images", images);

    conn.Open();
    try
    {
        cmd.ExecuteNonQuery();
    }
    finally
    {
        conn.Close();
    }
}

Also, fix the SQL Injection vulnerability for the report:

SqlDataAdapter adp = new SqlDataAdapter("SELECT * from tblPersonal WHERE Regdate BETWEEN @StartDate AND @EndDate ORDER BY Surname", conn);
adp.SelectCommand.Parameters.AddWithValue("@StartDate", this.dtpDate1.Value);
adp.SelectCommand.Parameters.AddWithValue("@EndDate", this.dtpDate2.Value);

这篇关于从字符串转换日期和时间时转换失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！