c# - 在 UWP 中使用 data.sqlclient 的 SQL Server 请求出错

Question

我正在尝试对我的 UWP 代码运行 SQL 查询。我不使用 Linq 或 EF。与基础工作的连接和简单的请求工作。这给我带来了问题：在我第一次用一个简单请求的结果填充列表视图时，我选择了一个元素并单击了一个搜索按钮。调用此请求时出现错误：

    static public ObservableCollection GetGaz(string connectionString, 
         string selectedOrder)
        {

            string GetGazQuery =

            "SELECT " +

                    "tbl_607_gaz_type.gaz_type," +
                    "tbl_607_theorical_content.theorical_content," +
                    "tbl_607_made_tolerance.made_tolerance," +
                    "tbl_607_order_details.gaz_lifetime," +
                    "tbl_607_gaz.gaz_comments," +
                    "tbl_607_order_details.FK_ID_order," +
                    "tbl_607_order_details.poste_number, " +
                    "tbl_607_order.order_number" +

                "FROM " +
                    "tbl_607_provider join tbl_607_order on tbl_607_provider.ID = tbl_607_order.FK_ID_provider " +
                    "join tbl_607_order_details on tbl_607_order.ID = tbl_607_order_details.FK_ID_order" +
                    "join tbl_607_gaz on tbl_607_order_details.FK_ID_gaz = tbl_607_gaz.ID " +
                    "join tbl_607_gaz_type on tbl_607_gaz.FK_ID_gaz_type = tbl_607_gaz_type.ID " +
                    "join tbl_607_made_tolerance on tbl_607_gaz.FK_ID_made_tolerence = tbl_607_made_tolerance.ID " +
                    "join tbl_607_theorical_content on tbl_607_gaz.FK_ID_theorical_content = tbl_607_theorical_content.ID " +

            "WHERE " +
                "tbl_607_order.order_number" + " LIKE " + "'%" + selectedOrder + "%'";


            ObservableCollection GazList = new ObservableCollection();
            try
            {
                using (SqlConnection conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    if (conn.State == System.Data.ConnectionState.Open)
                    {
                        using (SqlCommand cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = GetGazQuery;
                            using (SqlDataReader reader = cmd.ExecuteReader())
                            {
                                while (reader.Read())
                                {
                                    Gaz gaz = new Gaz
                                    {
                                        Gaz_type = reader.GetString(0),
                                        Theorical_content = reader.GetString(1),
                                        Made_tolerance = reader.GetDouble(2),
                                        Gaz_lifetime = reader.GetInt32(3),
                                        Gaz_comments = reader.GetString(4),
                                        Poste_number = reader.GetInt32(6)
                                    };

                                    GazList.Add(gaz);
                                }
                            }
                        }
                    }
                }
                return GazList;
            }
            catch (Exception eSql)
            {
                Debug.WriteLine("Exception: " + eSql.Message);
            }
            return null;
        }
    }

    private string selectedOrder;
        public Gestion_Stock()
        {
            this.InitializeComponent();
            SelectOrders.ItemsSource = OrdersDataHelper.GetOrders(connectionString: (Windows.UI.Xaml.Application.Current as App).ConnectionString);

        }

        private void Search_Click(object sender, RoutedEventArgs e)
        {
            Affichage_Stock_Gaz.ItemsSource = GazDataHelper.GetGaz((Windows.UI.Xaml.Application.Current as App).ConnectionString, selectedOrder);
        }

        private void SelectOrders_SelectionChanged (object sender, SelectionChangedEventArgs e)
        {
            ListView selectOrders = sender as ListView;
            Orders orders = SelectOrders.SelectedItem as Orders;
            selectedOrder = orders.Order_Number;

        }

输出：

Exception thrown: 'System.Data.SqlClient.SqlException' in System.Data.SqlClient.dll
Exception: Incorrect syntax near the keyword 'join'

这个简单的请求有效，我不使用变量和“where”命令。

这项工作：

    static public ObservableCollection GetOrders(string connectionString)
        {
            const string GetOrdersQuery = "" +
                "select " +

                    "tbl_607_order.start_date," +
                    "tbl_607_order.end_date," +
                    "tbl_607_provider.provider_name," +
                    "tbl_607_order.order_number," +
                    "tbl_607_order.shipping_request_active," +
                    "tbl_607_order.item_reception_active " +

                "from " +
                    "tbl_607_provider join tbl_607_order on tbl_607_provider.ID = tbl_607_order.FK_ID_provider " 
                 ;

有人会有什么想法吗？非常感谢！

标签： c#uwp-xaml

Answer 1

通过更改添加缺少的空间

"SELECT " +

                    "tbl_607_gaz_type.gaz_type," +
                    "tbl_607_theorical_content.theorical_content," +
                    "tbl_607_made_tolerance.made_tolerance," +
                    "tbl_607_order_details.gaz_lifetime," +
                    "tbl_607_gaz.gaz_comments," +
                    "tbl_607_order_details.FK_ID_order," +
                    "tbl_607_order_details.poste_number, " +
                    "tbl_607_order.order_number" +

                "FROM " +
                    "tbl_607_provider join tbl_607_order on tbl_607_provider.ID = tbl_607_order.FK_ID_provider " +
                    "join tbl_607_order_details on tbl_607_order.ID = tbl_607_order_details.FK_ID_order" +
                    "join tbl_607_gaz on tbl_607_order_details.FK_ID_gaz = tbl_607_gaz.ID " +
                    "join tbl_607_gaz_type on tbl_607_gaz.FK_ID_gaz_type = tbl_607_gaz_type.ID " +
                    "join tbl_607_made_tolerance on tbl_607_gaz.FK_ID_made_tolerence = tbl_607_made_tolerance.ID " +
                    "join tbl_607_theorical_content on tbl_607_gaz.FK_ID_theorical_content = tbl_607_theorical_content.ID " +

            "WHERE " +
                "tbl_607_order.order_number" + " LIKE " + "'%" + selectedOrder + "%'";

到

"SELECT " +

                    "tbl_607_gaz_type.gaz_type," +
                    "tbl_607_theorical_content.theorical_content," +
                    "tbl_607_made_tolerance.made_tolerance," +
                    "tbl_607_order_details.gaz_lifetime," +
                    "tbl_607_gaz.gaz_comments," +
                    "tbl_607_order_details.FK_ID_order," +
                    "tbl_607_order_details.poste_number, " +
                    "tbl_607_order.order_number" +

                "FROM " +
                    "tbl_607_provider join tbl_607_order on tbl_607_provider.ID = tbl_607_order.FK_ID_provider " +
                    "join tbl_607_order_details on tbl_607_order.ID = tbl_607_order_details.FK_ID_order " + // note the missing space added here
                    "join tbl_607_gaz on tbl_607_order_details.FK_ID_gaz = tbl_607_gaz.ID " +
                    "join tbl_607_gaz_type on tbl_607_gaz.FK_ID_gaz_type = tbl_607_gaz_type.ID " +
                    "join tbl_607_made_tolerance on tbl_607_gaz.FK_ID_made_tolerence = tbl_607_made_tolerance.ID " +
                    "join tbl_607_theorical_content on tbl_607_gaz.FK_ID_theorical_content = tbl_607_theorical_content.ID " +

            "WHERE " +
                "tbl_607_order.order_number" + " LIKE " + "'%" + selectedOrder + "%'";