我的SQL准备语句不工作

本文关键字:工作 语句 SQL 我的 | 更新日期: 2023-09-27 18:14:33

我试图将一些数据插入到我最近安装的本地MYSQL数据库中。这是我第一次尝试使用MYSQL,因为我通常使用MSSQL。我从未遇到过使用MSSQL准备语句的问题。

下面准备好的语句将不工作,MySqlCommand comm的输出总是与初始命令字符串完全相同。我试过把所有的@换成?这是行不通的。我删除了字符串前面的@符号和其他几个组合。我在谷歌上搜索了几个小时,没有真正的答案。通常有人会在他们的字符串中出现某种打字错误。我的插入字符串直接从HeidiSQL的插入语句复制,我替换了c#代码和常规插入语句工作的所有@变量。

 string cs = @"server=localhost;userid=root;database=busdata;";
         MySqlConnection conn = null;
         conn = new MySqlConnection(cs);
         conn.Open();
         try
         {
             MySqlCommand comm = conn.CreateCommand();
             comm.CommandText = @"INSERT INTO `busdata`.`buses` (`RecordedAtTime`, `DirectionRef`, `JourneyPatternRef`, `PublishedLineName`, `OperatorRef`, `OriginRef`, `DestinationRef`, `DestinationName`, `Monitored`, `Bearing`, `ProgressRate`, `BlockRef`, `VehicleRef`, `OriginAimedDepartureTime`, `ProgressStatus`, `Longitude`, `Latitude`) VALUES (@RecordedAtTime, @DirectionRef, @JourneyPatternRef, @PublishedLineName, @OperatorRef, @OriginRef, @DestinationRef, @DestinationName, @Monitored, @Bearing, @ProgressRate, @BlockRef, @VehicleRef, @OriginAimedDepartureTime, @ProgressStatus, @Longitude, @Latitude`); ";

             foreach (Vehicleactivity bus in buses)
             {              
                 comm.Parameters.AddWithValue("@RecordedAtTime", bus.RecordedAtTime.ToString());
                 comm.Parameters.AddWithValue("@DirectionRef", bus.MonitoredVehicleJourney.DirectionRef);
                 comm.Parameters.AddWithValue("@JourneyPatternRef", bus.MonitoredVehicleJourney.JourneyPatternRef);
                 comm.Parameters.AddWithValue("@PublishedLineName", bus.MonitoredVehicleJourney.PublishedLineName);
                 comm.Parameters.AddWithValue("@OperatorRef", bus.MonitoredVehicleJourney.OperatorRef);
                 comm.Parameters.AddWithValue("@OriginRef", bus.MonitoredVehicleJourney.OriginRef);
                 comm.Parameters.AddWithValue("@DestinationRef", bus.MonitoredVehicleJourney.DestinationRef);
                 comm.Parameters.AddWithValue("@DestinationName", bus.MonitoredVehicleJourney.DestinationName);
                 comm.Parameters.AddWithValue("@Monitored", bus.MonitoredVehicleJourney.Monitored.ToString());
                 comm.Parameters.AddWithValue("@Bearing", bus.MonitoredVehicleJourney.Bearing.ToString());
                 comm.Parameters.AddWithValue("@ProgressRate", bus.MonitoredVehicleJourney.ProgressRate);
                 comm.Parameters.AddWithValue("@BlockRef", bus.MonitoredVehicleJourney.BlockRef);
                 comm.Parameters.AddWithValue("@VehicleRef", bus.MonitoredVehicleJourney.VehicleRef);
                 comm.Parameters.AddWithValue("@OriginAimedDepartureTime", bus.MonitoredVehicleJourney.OriginAimedDepartureTime);
                 comm.Parameters.AddWithValue("@ProgressStatus", bus.MonitoredVehicleJourney.ProgressStatus);
                 comm.Parameters.AddWithValue("@Longitude", bus.MonitoredVehicleJourney.VehicleLocation.Longitude.ToString());
                 comm.Parameters.AddWithValue("@Latitude", bus.MonitoredVehicleJourney.VehicleLocation.Latitude.ToString());
                 comm.Prepare();
                 Console.WriteLine(comm.CommandText.ToString());
                 comm.ExecuteNonQuery();
                 comm.Parameters.Clear();

             }
         }
         catch (MySqlException ex)
         {
             Console.WriteLine("Error: {0}", ex.ToString());

我的SQL准备语句不工作

您在@location附近的插入查询中有一个反勾号。删除反勾号…它应该工作