未处理的SqlException：'12'附近的语法不正确 [英] SqlException unhandled : Incorrect syntax near '12'

问题描述

亲爱的用户，



我在将日期插入SQL服务器时遇到问题。我的代码如下：

Dear users,

I am having problem with inserting date into SQL server. My code is as following:

protected void btnSave_Click(object sender, EventArgs e)

        {
            using (SqlConnection con = new SqlConnection("Data Source=MEHDI-PC\\SQLEX;Initial Catalog=PIMS;Integrated Security=True"))
            {
                using (SqlCommand cmd = new SqlCommand())
                {

                    string DatePrinted = "";

                    if (txtDatePrinted.Text != "")
                        DatePrinted = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DatePrinted = "NULL";

                    string DateReceived = "";

                    if (txtDateReceived.Text != "")
                        DateReceived = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DateReceived = "NULL";

                    string DueDate = "";

                    if (txtDueDate.Text != "")
                        DueDate = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        DueDate = "NULL";

                    string ActualDate = "";

                    if (txtActualDate.Text != "")
                        ActualDate = DateTime.ParseExact(txtDateReceived.Text, "G", CultureInfo.GetCultureInfo("en-US")).ToString("yyyy-MM-dd");
                    else
                        ActualDate = "NULL";

                    String sql = "Insert into dbo.Documents(Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,[Content],Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response) values ('" + txtRef.Text + "','" + txtSubject.Text + "','" + ddlSource.Text + "' ,'" + ddlDestination.Text + "','" + ddlMedium.Text + "','" + DatePrinted + "','" + DateReceived + "','" + ddlDocumentType.Text + "','" + cbxAction.Checked + "','" + DueDate + "','" + ActualDate + "','" + txtContent.Text + "','" + txtTag.Text + "','" + txtIssue.Text + "','" + txtAttachment.Text + "','" + txtNotes.Text + "','" + ddlAssignedTo.Text + "','" + txtReplyRef.Text + "','" + ddlPriority.Text + "','" + ddlStatus.Text + "','" + ddlResponse.Text + "')";
  
                    cmd.Connection = con;
                    cmd.CommandText = sql;

                    con.Open();
                    //dataset object to get all select statement results
                    //DataSet ds = new DataSet();

                    //sql dataadoptor to fill dataset
                    cmd.ExecuteNonQuery();
                }
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                }

                MultiView1.SetActiveView(vRecord);
                txtRef.Text = string.Empty;
                txtSubject.Text = string.Empty;
                ddlSource.Text = string.Empty;
                ddlDestination.Text = string.Empty;
                ddlMedium.Text = string.Empty;
                txtDatePrinted.Text = string.Empty;
                txtDateReceived.Text = string.Empty;
                ddlDocumentType.Text = string.Empty;
                cbxAction.Checked = false;
                txtDueDate.Text = string.Empty;
                txtActualDate.Text = string.Empty;
                txtContent.Text = string.Empty;
                txtTag.Text = string.Empty;
                txtIssue.Text = string.Empty;
                txtAttachment.Text = string.Empty;
                txtNotes.Text = string.Empty;
                ddlAssignedTo.Text = string.Empty;
                txtReplyRef.Text = string.Empty;
                ddlPriority.Text = string.Empty;
                ddlStatus.Text = string.Empty;
                ddlResponse.Text = string.Empty;
            }

            }

运行此代码后，输入任何日期值datefield，它抛出这条消息：



SqlException未处理：'12'附近的语法不正确





我没看到我的代码有什么问题。请帮我确认一下这个问题。



非常感谢你的帮助。



关心

After I run this code, and enter any date value in the datefield, it throws this message:

SqlException unhandled : Incorrect syntax near '12'


I am failed to see anything wrong with my code. Please help me identify the problem.

You help will be much appreciated.

Regards

推荐答案

您的代码有很多错误。从主要的低效率开始，进入危险的SQL注入敏感性，当然还有你抱怨的问题...



为什么你这么多次解析txtDateReceived.Text ？当然你可以看到，如果你将它解析为DateTime值一次（并且可能使用了TryParse，那么你可以向用户报告问题，如果他输错了）会更清楚吗？



然后是SQl ...不要连接字符串来构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。

There is plenty wrong with your code. Starting with major inefficiency, moving into dangerous SQL Injection susceptibility, and of course the problem you are complaining about...

Why are you parsing txtDateReceived.Text so many times? Surely you can see that it would be a lot clearer if you parsed it to a DateTime value once (and probably used TryParse instead so you could report a problem to the user if he types it wrong) instead?

Then the SQl...Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

String sql = "INSERT INTO dbo.Documents(Ref,Subject,Src,Dst,Medium,Date_Printed,Date_Received,Document_Type,Action_Required,Due_Date,Actual_Date,[Content],Tag,Issue_No,Attachment,Notes,Assigned_To,Reply_Ref,Priority,Status,Response) VALUES (@REF, @SUBJ, @SRC, @DEST...

cmd.Connection = con;
cmd.CommandText = sql;
cmd.Parameters.AddWithValue("@REF", txtRef.Text);
cmd.Parameters.AddWithValue("@REF", txtSubject.Text);
cmd.Parameters.AddWithValue("@REF", ddlSource.Text);
...

您报告的问题几乎肯定会消失..

And your reported problem will almost certainly disappear as well..

这篇关于未处理的SqlException：'12'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！