插入语句的问题(我认为)

I'm working on a quote manager for my boss at work and I'm having some issues. This is a WPF C# application and it's the first time I've ever built anything that works with a SQL Server database. I'm currently having three issues.

我在为我的老板做报价经理，我有一些问题。这是一个WPF c#应用程序，也是我第一次使用SQL Server数据库构建任何东西。我目前有三个问题。

Background:

背景:

When the user opens the application they're greeted with a DataGrid, a new quote button and several other controls that I haven't yet created. When they press the new quote button a new window pops up with a form that will have text boxes for things like Customer Name, quantity, etc. At the bottom of that form is a submit button, at which point the window will close and the information they added will be inserted into the DataGrid as a new row.

当用户打开应用程序时，会收到一个DataGrid、一个新的quote按钮和几个我还没有创建的控件。按新报价按钮时弹出一个新窗口,其中包含一个表单,将文本框为客户名称、数量、等形成的底部是一个提交按钮,此时窗口将关闭,他们添加的信息将作为新行插入到数据网格。

Problem One:

问题一:

One of the fields in my database is called Open_Quote and it is supposed to be hold the date we received the order. This is handled programmatically and is what my first question involves. I'll include all of the code at the bottom of this post, but when the user hits submit I receive the following error: "Conversion failed when converting date and/or time from character string."

我的数据库中有一个字段叫做Open_Quote，它应该保存我们收到订单的日期。这是通过编程处理的，这是我的第一个问题。我将在这篇文章的底部包含所有代码，但是当用户点击提交时，我收到以下错误:“转换失败时，从字符串转换日期和/或时间。”

Problem Two:

问题二:

In an attempt to test the rest of my code and go back later to fix the date issue, I commented that code out and tried running my program again. This time I get a different error: "Incorrect syntax around 'newQuote.Qty'."

为了测试代码的其余部分并稍后返回以修复日期问题，我注释了代码并再次尝试运行程序。这一次我得到了一个不同的错误:“newQuote.Qty”语法错误。

Problem Three:

问题三:

Again, commenting that code out in order to finally test the rest of my code, I received a third error: "string or binary data would be truncated. This process has been terminated."

再次，为了最终测试代码的其余部分，我注释了这段代码，并收到了第三个错误:“字符串或二进制数据将被截断。”这一进程已经结束。”

My hope is that there's one piece of code that's causing all three of these issues, but I could be completely off there. I've been pulling my hair out for over a day trying to figure this out. Anyway, here's the code:

我的希望是有一段代码导致了这三个问题，但我完全可以做到。我花了一天的时间想弄明白这一点。无论如何,这是代码:

newQuote.xaml.cs:

    private void SubmitQuotebtn_Click(object sender, RoutedEventArgs e)
    {
        CustomerData newQuote = new CustomerData();

        int quantity;
        quantity = Convert.ToInt32(Qtytxt.Text);

        string theDate = System.DateTime.Today.Date.ToString("d");

        newQuote.OpenQuote = theDate;
        newQuote.CustomerName = CustNametxt.Text;
        newQuote.OEMName = OemNametxt.Text;
        newQuote.Qty = quantity;
        newQuote.QuoteNumber = QuoteNumtxt.Text;
        newQuote.FdNumber = FabDrawingNumtxt.Text;
        newQuote.RfqNumber = RfqNumtxt.Text;
        newQuote.RevNumber = RevNumtxt.Text;

        try
        {
            string insertConString = Sqtm.Properties.Settings.Default.SqtmDbConnectionString;

            using (SqlConnection insertConnection = new SqlConnection(insertConString))
            {
                insertConnection.Open();

                SqlCommand cmd = new SqlCommand("INSERT INTO General_Info(Open_Quote, Customer_Name, OEM_Name, Qty, Quote_Num, Fab_Drawing_Num, "
                                                + "Rfq_Num, Rev_Num) values('newQuote.OpenQuote', 'newQuote.CustomerName', 'newQuote.OemName', 'newQuote.Qty' "
                                                + "'newQuote.QuoteNumber', 'newQuote.FdNumber', 'newQuote.RfqNumber', 'newQuote.RevNumber')", insertConnection);

                cmd.ExecuteNonQuery();

            }
        }

        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

    }

CustomerData.cs:

    class CustomerData
{
    private string _CustomerName;
    private string _OEMName;
    private string _OpenQuote;
    private int _Qty;
    private string _QuoteNumber;
    private string _FdNumber;
    private string _RfqNumber;
    private string _RevNumber;

    public CustomerData()
    {
        // empty constructor
    }

    public string CustomerName
    {
        get { return _CustomerName; }
        set { _CustomerName = value; }
    }

    public string OpenQuote
    {
        get { return _OpenQuote; }
        set { _OpenQuote = value; }
    }

    public string OEMName
    {
        get { return _OEMName; }
        set { _OEMName = value; }
    }

    public int Qty
    {
        get { return _Qty; }
        set { _Qty = value; }
    }

    public string QuoteNumber
    {
        get { return _QuoteNumber; }
        set { _QuoteNumber = value; }
    }

    public string FdNumber
    {
        get { return _FdNumber; }
        set { _FdNumber = value; }
    }

    public string RfqNumber
    {
        get { return _RfqNumber; }
        set { _RfqNumber = value; }
    }

    public string RevNumber
    {
        get { return _RevNumber; }
        set { _RevNumber = value; }
    }
}

And as a reference, here's how I set up this table in SQLServer:

作为参考，我如何在SQLServer中设置这个表:

Open_Quote, date, not null
Customer_Name, varchar(25), not null
OEM_Name, varchar(25), null
Qty, int, not null
Qute_Num, varchar(20), null
Fab_Drawing_Num, varchar(20), not null
Rfq_Num, varchar(10), null
Rev_Num, varchar(10), null

Thanks in advance to anyone who helps me out,

提前感谢所有帮助我的人，

Andrew
安德鲁

2 个解决方案

#1

Try again with parameters and let us know how it goes. http://www.dotnetperls.com/sqlparameter

再试一次参数，让我们知道它是如何进行的。http://www.dotnetperls.com/sqlparameter

Edit: Or what Tieson T. said. That'd be even better just a little more involved.

编辑:或者蒂森·t说什么。那就更好了，只要多参与一点。

To get the data to show in the datagrid, after you do the insert, you can rebind the grid. Make sure your update the datasource so it repulls the data you just inserted. If you have problems, show where/how you are setting the datasource for the grid.

要在datagrid中显示数据，在进行插入之后，可以重新绑定网格。确保您更新了数据源，以便它重新提取刚才插入的数据。如果有问题，请显示在何处/如何为网格设置数据源。

#2

Try this:

试试这个:

SqlCommand cmd = new SqlCommand("INSERT INTO General_Info(Open_Quote, Customer_Name, OEM_Name, Qty, Quote_Num, Fab_Drawing_Num, "
                                            + "Rfq_Num, Rev_Num) values('" + newQuote.OpenQuote + "','" + newQuote.CustomerName + "','" + newQuote.OemName + "','" + newQuote.Qty + "','" + newQuote.QuoteNumber + "','" + newQuote.FdNumber + "', '" + newQuote.RfqNumber + "','" + newQuote.RevNumber + "' "
                                            + " )", insertConnection);

#1