c# - 运行 2 个单独的查询但获取参数不应为空错误

Question

我尝试运行以下 2 个单独的查询，但不断收到以下错误消息:

我查看了数据库，第一个查询成功(因此参数不为空)，但在 cmd.ExecuteNonQuery(); 处失败。在for (var i = 0; i < _waypointList.Count; i++) { ... }

这有什么原因吗？

        var query =
            @"INSERT INTO booking (operator_id, plot_id, postcode, datetime, stops, " +
            "mileage, price, passengers, name, note, phone, status, reference) " +
            "VALUES (@operator_id, @plot_id, @postcode, @datetime, @stops, " +
            "@mileage, @price, @passengers, @name, @note, @phone, @status, @reference);";
        var cmd = new MySqlCommand(query, DbConnect.Connection);
        cmd.Parameters.AddWithValue(("@operator_id"), _operatorId);
        cmd.Parameters.AddWithValue(("@plot_id"), _plotId);
        cmd.Parameters.AddWithValue(("@postcode"), _postcode);
        cmd.Parameters.AddWithValue(("@datetime"), _datetime);
        cmd.Parameters.AddWithValue(("@stops"), _stops);
        cmd.Parameters.AddWithValue(("@mileage"), _mileage);
        cmd.Parameters.AddWithValue(("@price"), _price);
        cmd.Parameters.AddWithValue(("@passengers"), _passengers);
        cmd.Parameters.AddWithValue(("@name"), _name);
        cmd.Parameters.AddWithValue(("@note"), _note);
        cmd.Parameters.AddWithValue(("@phone"), _phone);
        cmd.Parameters.AddWithValue(("@status"), Status);
        cmd.Parameters.AddWithValue(("@reference"), _reference);
        cmd.ExecuteNonQuery();

        for (var i = 0; i < _waypointList.Count; i++)
        {
            query +=
                @"INSERT INTO waypoint 
                    (booking_id, sequence, address, lat, lng, reference) 
                VALUES 
                    ((select id FROM booking WHERE reference=@reference" + i + @"),
                    @sequence" + i + @", 
                    @address" + i + @", 
                    @lat" + i + @", 
                    @lng" + i + @", 
                    @reference" + i + ")";
            cmd = new MySqlCommand(query, DbConnect.Connection);
            cmd.Parameters.AddWithValue(("@reference" + i), _reference);
            cmd.Parameters.AddWithValue(("@sequence" + i), i);
            cmd.Parameters.AddWithValue(("@address" + i), _waypointList[i]);
            cmd.Parameters.AddWithValue(("@lat" + i), _lat);
            cmd.Parameters.AddWithValue(("@lng" + i), _lng);
            cmd.ExecuteNonQuery();
        }

Answer 1

这解决了它。

for (var i = 0; i < _waypointList.Count; i++)
        {
            query =
                @"INSERT INTO waypoint 
                    (booking_id, sequence, address, lat, lng, reference) 
                VALUES 
                    ((select id FROM booking WHERE reference=@reference" + i + @"),
                    @sequence" + i + @", 
                    @address" + i + @", 
                    @lat" + i + @", 
                    @lng" + i + @", 
                    @reference" + i + ")";
            cmd = new MySqlCommand(query, DbConnect.Connection);
            cmd.Parameters.AddWithValue(("@reference" + i), _reference);
            cmd.Parameters.AddWithValue(("@sequence" + i), i);
            cmd.Parameters.AddWithValue(("@address" + i), _waypointList[i]);
            cmd.Parameters.AddWithValue(("@lat" + i), _lat);
            cmd.Parameters.AddWithValue(("@lng" + i), _lng);
            cmd.ExecuteNonQuery();
        }